Showing entries 31 to 40 of 1078
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: sql (reset)
MySQL auto update date_created and date_updated columns

It's usually a good practice to have two columns called date_created and date_updated in every table. One can always use it in application and it helps in debugging too as to when a record was created and last updated in various circumstances and contexts.




This responsibility can be given to MySQL to automatically assign current time values to these columns.

In MySQL 5.6 onwards, this can be done by setting the data type of the columns to be either date time or timestamp and creating date_created column with NOT NULL DEFAULT CURRENT_TIMESTAMP  schema and date_updated column with NOT NULL DEFAULT '0000-00-00 00:00:00' as schema with attribute ON UPDATE CURRENT_TIMESTAMP.

[Read more]
MySQL/MariaDB cursors and temp tables

In MariaDB and MySQL, cursors create a temporary table.

Does this statement deserve a whole blog post? Apparently not. However, in some cases one does not expect a temporary table to be created:

  • SELECT ... FOR UPDATE: An exclusive lock is created, yes, but you still read data from a temporary table.
  • SELECT FROM a temporary table: you are reading from a temporary tables, yes, but an internal temporary table is created anyway.
  • Impossible WHERE and LIMIT 0.

A quick example:

CREATE TEMPORARY TABLE t
(
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
);

CREATE PROCEDURE p()
BEGIN
        DECLARE c CURSOR FOR
                SELECT id FROM t WHERE 0 LIMIT 0 FOR UPDATE;
        OPEN c;
        CLOSE c;
END;

MySQL [test]> SHOW STATUS LIKE 'Created_tmp_tables';
+--------------------+-------+
| Variable_name …
[Read more]
MaxScale binaries are now available

UPDATE: Short story: ignore the next update and read the post. Long story: the original post was a mistake, as explained in the next update. But then, MariaDB released free MaxScale binaries and everything I wrote in the post is now correct.

UPDATE 2016-04-14: It seems that I was mistaken. MaxScale download page is a bit different from MariaDB Enterprise page, and does not explicitly require us to accept terms of use before download. But we accept those terms while creating an account.

So, MaxScale binaries cannot be used in production without paying for MariaDB enterprise. Thanks to the persons who commented this post and pointed my mistake. My apologies to my readers.

I won’t delete this post because I don’t want the comments to disappear, as they express opinions of some community members.

My jestarday’s post …

[Read more]
Comments on MaxScale binaries

I’m writing this post after reading Downloading MariaDB MaxScale binaries, from Percona’s MySQL Performance Blog.

I was already aware about the problem: MaxScale is open source, but the binaries are not free. You can download them and use them for testing, but if you want to use them in production, you’ll need to buy MariaDB Enterprise.

Note that MaxScale Docker images seem to have the same problem. I’ve tried some of them, but all those I’ve tried were running MariaDB Enterprise binaries, so using them in production is illegal (unless you pay).

The alternative is… compiling MaxScale. I had problems in doing so and couldn’t solve those problems myself. From the MaxScale Google Group, I see that Justin Swanhart had the same problems… so I don’t feel particularly stupid for that.

After some …

[Read more]
MariaDB/MySQL missing features: View comments

The COMMENT clause

All database components (and database themselves) should have a COMMENT clause in their CREATE/ALTER statements, and a *_COMMENT column in the information_schema. For example:

CREATE PROCEDURE do_nothing()
        COMMENT 'We''re lazy. Let''s do nothing!'
BEGIN
        DO NULL;
END;
SELECT ROUTINE_COMMENT FROM information_schema.ROUTINES;

In fact most database objects have those clauses in MySQL/MariaDB, but not all. Views are an exception.

Comments in code

MariaDB and MySQL have multiple syntaxes for comments. Including executable comments (commented code that is only executed on some MySQL/MariaDB versions).

One can use comments in stored procedures and triggers, and those codes are preserved:

CREATE PROCEDURE do_nothing()
BEGIN
        -- We're lazy. …
[Read more]
Announcing MySQL Utilities 1.6.3 Beta!

The MySQL Utilities Team is pleased to announce a new beta release of MySQL Utilities. This release includes a number of improvements for usability, stability, and a few enhancements. A complete list of all improvements can be found in our release notes.

New Enhancements!
This release represents a stable release of the product. Along with several defect patches, we also include the following enhancements.

Improved support for MySQL 5.7
Improved functionality of --exclude option with SQL wildcards
Improved packaging in RPM and Windows distributions
Improved accuracy of calculated disk usage for mysqldiskusage
...and a host of minor improvements for quality and robustness


How Can I Download MySQL Utilities?
You can download MySQL Utilities 1.6.3 Beta from the …

[Read more]
Digging Down into JSON data with the MySQL Functions -- A Question from Peter Zaitsev -- Follow Up

Last time this blog covered digging into a JSON document in a MySQL 5.7 table. The goal was to pull certain records matching a particular criteria. Both Peter Zaitsev and Morgan Tocker get my thanks for their kind comments. My example was a little contrived in that an application would be used to fine tune seeking for a particular key value pair. I was trying to pull single records which is kind of silly when it is much easier to use PHP to parse the data. What follows below is a sample PHP script to grab out the matching records and then feed the results, the JSON document, into an array.


#!/usr/bin/php
<?php
$mysqli = new mysqli("localhost", "root", "hidave", "test");

/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", …
[Read more]
When simple SQL can be complex

I think SQL is a very simple language, but ofcourse I'm biased.

But even a simple statement might have more complexity to it than you might think.

Do you know what the result is of this statement?

SELECT FALSE = FALSE = TRUE;

scroll down for the answer.



























The answer is: it depends.

You might expect it to return false because the 3 items in the comparison are not equal. But that's not the case.

In PostgreSQL this is the result:

postgres=# SELECT FALSE = FALSE = TRUE;
?column?
----------
t
(1 row)

So it compares FALSE against FALSE, which results in TRUE …

[Read more]
New Release! MySQL Connector/Arduino 1.1

The newest release of the MySQL Connector/Arduino library release-1.1 alpha is available for download. This new version represents a major step forward for the library in ease of use. Here are just a few of the important changes in this release.

  • Added to Library Manager : yes, you can download and install the library from the Arduino IDE now. Just open the Library Manager and search for "MySQL".
  • More Example Sketches : there are many more example sketches of how to use the new library from basic connections to complex queries and more! 
  • Redesigned Classes : the library has been redesigned with new classes making it much easier to use a wider variety of shields and modules. Now, you pass in the Client class for your shield and so long as it adheres to the Ethernet.Client primitive, you can use any library to initiate …
[Read more]
SQL Games

I have just created a GitHub repository called sql_games. It contains games implemented as stored procedures, that can run on MariaDB or (with some changes) on Percona Server or Oracle MySQL.

You play the games via the command-line client. You call a procedure to make your move, then a text or an ASCII image appears.

Of course the same call should produce a different effect, depending on the game’s current state. To remember the state I use both user variables and temporary tables.

Why did I do that? Mainly because it was funny. I can’t explain why. And I can’t tell that it is funny for anyone: perhaps, where I’ve found interesting challenges, someone else would find a cause a frustration. But yes, it has been funny for me.

Also, I did it because I could. This means that others can do it. Stored procedures are not a useless and nasty …

[Read more]
Showing entries 31 to 40 of 1078
« 10 Newer Entries | 10 Older Entries »