Stored Routines to easily work with the SQL_MODE

Versione italiana

Working with the SQL_MODE can be tricky, because it’s a command separated list involving long keywords. To be honest, I hate to rewrite the flags, trying to read an unreadable list, etc. Of course some string functions can help (Justin Swanhart suggests to use REPLACE() to make comma-separated lists readable).

I made a small set of Stored Routines wich allow me to easily show SQL_MODE, add a flag, drop a flag and check if a flag is set. These routines work with the GLOBAL SQL_MODE; if you don’t like this, simply replace “@@global.” with “@@session.” in the SQL file before installing.

You can download (or improve) my routines using this SQL_MODER GitHub repo.

And now, some examples.

Show currently selected flags:

MariaDB [(none)]> CALL _.sql_mode_show();
+----------------------------+
| FLAG                       |
+----------------------------+
| ERROR_FOR_DIVISION_BY_ZERO |
| NO_AUTO_CREATE_USER        |
| NO_ENGINE_SUBSTITUTION     |
| NO_ZERO_DATE               |
| NO_ZERO_IN_DATE            |
| ONLY_FULL_GROUP_BY         |
| STRICT_ALL_TABLES          |
| STRICT_TRANS_TABLES        |
+----------------------------+
8 rows in set (0.46 sec)

(this is the SQL_MODE I suggest to use)

Check wether a flag is set:

MariaDB [(none)]> SELECT _.sql_mode_is_set('HIGH_NOT_PRECEDENCE');
+------------------------------------------+
| _.sql_mode_is_set('HIGH_NOT_PRECEDENCE') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.38 sec)

Add one or more flags:

MariaDB [(none)]> CALL _.sql_mode_set('NO_UNSIGNED_SUBTRACTION,HIGH_NOT_PRECEDEN
CE');
Query OK, 0 rows affected (0.07 sec)

MariaDB [(none)]> CALL _.sql_mode_show();
+----------------------------+
| FLAG                       |
+----------------------------+
| ERROR_FOR_DIVISION_BY_ZERO |
| HIGH_NOT_PRECEDENCE        |
| NO_AUTO_CREATE_USER        |
| NO_ENGINE_SUBSTITUTION     |
| NO_UNSIGNED_SUBTRACTION    |
| NO_ZERO_DATE               |
| NO_ZERO_IN_DATE            |
| ONLY_FULL_GROUP_BY         |
| STRICT_ALL_TABLES          |
| STRICT_TRANS_TABLES        |
+----------------------------+
10 rows in set (0.13 sec)

Query OK, 0 rows affected (2.09 sec)

Drop a flag:

MariaDB [(none)]> CALL _.sql_mode_unset('HIGH_NOT_PRECEDENCE');
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT _.sql_mode_is_set('HIGH_NOT_PRECEDENCE');
+------------------------------------------+
| _.sql_mode_is_set('HIGH_NOT_PRECEDENCE') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.00 sec)

If you mistyped the flag (or it wasn’t set):

MariaDB [(none)]> CALL _.sql_mode_unset('hello world');
ERROR 1644 (45000): Flag 'hello world' was not set

Enjoy!

MariaDB/MySQL: Procedures to easily work with the Diagnostics Area

Versione italiana

UPDATE 2013-08-30: I fixed 2 bugs and create a GitHub repo called sql_diagnostix. Sorry for not doing this before. If I (or someone else) find new bugs, I’ll update the repo.

The problem

To quickly see information about the errors and warnings generated by the last executed statement, we can use SHOW WARNINGS. However this statement’s results cannot be accessed via SQL, so they cannot be used for error handling within a stored program.

For that purpose we can use GET DIAGNOSTICS, which has two problems:

  • It requires a lot of code
  • There is not a trivial way to quicly show all info from Diagnostics Area.

The first problem may discourage developers to use GET DIAGNOSTICS at all.

The second problem is not so important in most cases, because the missing info are SQLSTATE and the information which can only be set via SIGNAL and RESIGNAL, and cannot be read by an external program (SCHEMA_NAME, CURSOR_NAME, etc). However SQLSTATE can be important for you and setting/reading all SIGNAL‘s clauses could help debugging and problem solving… that’s why they exist 🙂

Oracle MySQL 5.7 has two Diagnostics Areas, and supports the STACKED keyword to access the second DA from an error HANDLER. However, this doesn’t help much: it just allows to execute another table-based statement before reading information about the error conditions which occurred. So, the STACKED DA doesn’t seem to solve any real life problem.

The solution

I created three Stored Procedures to address these problems:

  • _.materialize_diagnostics_area()
  • _.show_diagnostics_area()
  • _.show_full_diagnostics_area()

All the Procedures create a MEMORY temporary table called DIAGNOSTICS_AREA in a database called _. If the table already exists, it is DROPped and reCREATEd. Incidentally they also empty server’s Diagnostics Area, but the information will be avaible in that table until you call one of those routines again.

_.DIAGNOSTICS_AREA has a column called ID, which indicates each condition’s position in the Diagnostics Area. Other columns names and values are equal to the conditions properties in the diagnostics area. The only exception is the SQLSTATE column, which has the name that can be used with SIGNAL and RESIGNAL, not the name used in the diagnostics area (RETURNED_SQLSTATE).

Since both Stored Programs and external programs will be able to access _.DIAGNOSTICS_AREA, this technique exposes all the information that you can set with SIGNAL to external programs (MYSQL_ERRNO, etc).

Directly call materialize_diagnostics_area() if you want to easily copy the DA into a table and later run queries on it.

show_full_diagnostics_area() shows all the information, after populating the table. It’s a sort of SHOW WARNINGS with a lot of columns.

show_diagnostics_area() only shows the ID, SQLSTATE, MYSQL_ERRNO, MESSAGE_TEXT columns.

Example:

MariaDB [test]> INSERT INTO `t` VALUES (1/0), (1/0), (1/0), (-1);
Query OK, 4 rows affected, 4 warnings (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 4
 
MariaDB [test]> CALL _.show_diagnostics_area();
+----+----------+-------------+--------------------------------------------+
| ID | SQLSTATE | MYSQL_ERRNO | MESSAGE_TEXT                               |
+----+----------+-------------+--------------------------------------------+
|  1 | 22012    |        1365 | Division by 0                              |
|  2 | 22012    |        1365 | Division by 0                              |
|  3 | 22012    |        1365 | Division by 0                              |
|  4 | 22003    |        1264 | Out of range value for column 'c' at row 4 |
+----+----------+-------------+--------------------------------------------+
4 rows in set (0.09 sec)

Limitations

If your Stored Program needs to be fast, use these Procedures only for debug.

As mentioned above, each call will erase old data from _.DIAGNOSTICS_AREA. This is by design.

An information is present in SHOW WARNING‘s output but missing from _.DIAGNOSTICS_AREA: the Level column. The reason is that I wasn’t able to find in MySQL documentation how to distinguish a Warning from a Note, except using SHOW WARNINGS. If there is no way, or a way exists but is not documented, I can’t add that information.

5.7’s STACKED diagnostics area is not used.

The code

Okay, I wrote enough. Now, if you are interested:

Download the SQL code

I won’t explain the code, because it seems to me very simple and it’s commented. Just take a look to materialize_diagnostics_area() and you’ll understand what I did.

Enjoy!

Quickly shutdown MariaDB 10

Italian version

How do you normally shutdown a MariaDB 5.5 or MySQL server?

mysqladmin shutdown -uroot -p

But MariaDB 10 (beta quality, at the time of this writing) has a quick way to do the same:

C:\Documents and Settings\utente1>mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.0.4-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT 'I\'m alive!';
+------------+
| I'm alive! |
+------------+
| I'm alive! |
+------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHUTDOWN;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT 'I\'m gone away';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061 "Unknown
 error")
ERROR: Can't connect to the server

unknown [(none)]> \q
Bye

Update: SHUTDOWN works in stored programs. This allows to write an event which turns the server off everyday at a certain time, or when a task is finished. This is safer than a cron job which kills it, and works on any system.

DELIMITER ||
CREATE EVENT `test`.`shutd`
    ON SCHEDULE
        EVERY 1 DAY
        STARTS '2014-01-01 20:00:00'
    COMMENT 'Shutdown Maria when the office closes'
DO BEGIN
    SHUTDOWN;
END;
||
DELIMITER ;

Enjoy!

JavaScript doesn’t have associative arrays

Versione italiana

This post is about one common JavaScript misconception: associative arrays.

JavaScript supports associative array syntax, but it doesn’t really have associative arrays. They are useless, because you can use objects.

Let’s create a simple object:

var movie = {
    title : '2001: A space Odissey',
    director : 'Stanley Kubrick',
    year : 1968
};

Here, we define an object variable (movie) with 3 properties (title, director, year). Since properties are always public, we can read them:

alert('Title: ' + movie.title);

Also, JavaScript is very dynamic, so we can also modify or set new properties:

movie.genre = 'sf';

We can also use the classic associative array syntax to access object properties:

alert('Title: ' + movie['title']);

This may seem useless… and in this example, it is. We shouldn’t use that syntax when it is not necessary. But we can use it to access a property that we don’t know a priori:

var prop = 'title';
alert('Title: ' + movie[prop]);

This is particularly useful within for … in loops, which are the JavaScript equivalent of foreach loops:

for (var i in movie) {
    alert(i + ': ' + movie[i]);
}

Enjoy!

MariaDB/MySQL Error HANDLERs: advanced uses

This article explains some advanced uses for MySQL and MariaDB’s error HANDLERs. Some information is in MySQL documentation, but it’s rather sparse. Also, while MySQL manual is probably the best manual ever, I’ve found a major bug in errors documentation. So, these use cases are not so obvious. At least they weren’t for me, so I had to test them.

Notes about the examples in this article

All the examples are tested on MariaDB 10.0.3, but I’m sure that there is not difference in MySQL 5.6, and there should be no difference in any 5.* version.

In these examples we need to produce an error to test the server’s behavior. We’ll always use the same method: we’ll declare a `cur` cursor and we’ll try to CLOSE it without OPENing it – this causes a 1326 error ‘Cursor is not open’.

SELECTs are used to show what path the execution takes.

Fight fire with fire

When an error happens within a CONTINUE HANDLER (not an EXIT HANDLER, though), another HANDLER can be triggered. However, this can not lead to infinite loops (at least from my tests).

USE `test`;
DROP PROCEDURE IF EXISTS `sp1`;
DELIMITER ||
CREATE PROCEDURE `sp1`()
BEGIN
    DECLARE `cur` CURSOR FOR
        SELECT 1 LIMIT 0;
    
    DECLARE CONTINUE HANDLER
        FOR 1326
    BEGIN
        SELECT 'cursor not open';
        -- no loop here
        CALL `sp1`();
    END;
    
    DECLARE CONTINUE HANDLER
        FOR 1146
    BEGIN
        SELECT 'table doesnt exist';
        CLOSE `cur`;
    END;
    
    DECLARE CONTINUE HANDLER
        FOR 1456
    BEGIN
        SELECT 'recursion not allowed';
        SELECT 1 FROM `not-exists`;
    END;
    
    CALL `sp1`();
END;
||
DELIMITER ;
CALL `sp1`();

Output:

MariaDB [test]> CALL `sp1`();
+-----------------------+
| recursion not allowed |
+-----------------------+
| recursion not allowed |
+-----------------------+
1 row in set (0.01 sec)

+--------------------+
| table doesnt exist |
+--------------------+
| table doesnt exist |
+--------------------+
1 row in set (0.51 sec)

+-----------------+
| cursor not open |
+-----------------+
| cursor not open |
+-----------------+
1 row in set (1.02 sec)

ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth vari
able) was exceeded for routine sp1

Inner blocks

I’m not sure that this feature is widely known, but a Stored Program can contain nested BEGIN..END blocks. Each block can contain its own declared objects (local variables, named error conditions, cursors, handlers) and inherit or override declared objects from the outer blocks. In inner blocks, EXIT HANDLER means “exit from the current block”, not from the whole program.

This example shows a Procedure which declares a HANDLER for 1326 error and an inner block. The inner block overrides that HANDLER, and triggers the error. Only the inner HANDLER is used.

DROP PROCEDURE IF EXISTS `p1`;
DELIMITER ||
CREATE PROCEDURE `p1`()
BEGIN
    DECLARE `cur` CURSOR FOR
        SELECT 1 LIMIT 0;
    
    DECLARE EXIT HANDLER
        FOR 1326
    BEGIN
        SELECT 'outer handler';
    END;
    
    BEGIN
        DECLARE EXIT HANDLER
            FOR 1326
        BEGIN
            SELECT 'inner handler';
        END;
        SELECT 'p1 handler';
        CLOSE `cur`;
    END;
    
    -- error is not RESIGNAL'd, so execution continues
    SELECT 'after begin..end';
END;
||
DELIMITER ;
CALL `p1`();

Output:

MariaDB [test]> CALL `p1`();
+------------+
| p1 handler |
+------------+
| p1 handler |
+------------+
1 row in set (0.40 sec)

+---------------+
| inner handler |
+---------------+
| inner handler |
+---------------+
1 row in set (1.01 sec)

+------------------+
| after begin..end |
+------------------+
| after begin..end |
+------------------+
1 row in set (1.67 sec)

Query OK, 0 rows affected, 1 warning (2.16 sec)

Nested HANDLERs

HANDLERs contain a BEGIN..END block, too (or a DO clause: semantically there is no difference). In this block, the same rules apply. So HANDLERs can be nested.

In the following example, when p1() outmost block produces an error the execution enters the outer HANDLER. When the outer HANDLER produces an error, the execution enters the inner HANDLER.

DROP PROCEDURE IF EXISTS `p1`;
DELIMITER ||
CREATE PROCEDURE `p1`()
BEGIN
    DECLARE `cur` CURSOR FOR
        SELECT 1 LIMIT 0;
    
    -- can be both CONTINUE or EXIT
    DECLARE EXIT HANDLER
        FOR 1326
    BEGIN
        DECLARE CONTINUE HANDLER
            FOR 1326
        BEGIN
            SELECT 'inner handler';
        END;
        SELECT 'outer handler';
        CLOSE `cur`;
    END;
    SELECT 'p1';
    CLOSE `cur`;
END;
||
DELIMITER ;
CALL `p1`();

Output:

MariaDB [test]> CALL `p1`();
+----+
| p1 |
+----+
| p1 |
+----+
1 row in set (0.02 sec)

+---------------+
| outer handler |
+---------------+
| outer handler |
+---------------+
1 row in set (0.52 sec)

+---------------+
| inner handler |
+---------------+
| inner handler |
+---------------+
1 row in set (1.00 sec)

Query OK, 0 rows affected, 1 warning (1.38 sec)

Caller and callee’s HANDLERs precedence

If a Stored Program calls or triggers another Stored Program, the HANDLERs in the callee program have the precedence over caller’s HANDLERs. This happens even if the caller has a HANDLER for the specific error number (usually high precedence) and the callee has a HANDLER for a whole error class (usually low precedence).

Of course this only applies if the callee has a HANDLER for the proper error class, or the proper SQLSTATE.

USE test;
DROP PROCEDURE IF EXISTS `callee`;
DROP PROCEDURE IF EXISTS `caller`;
DELIMITER ||
CREATE PROCEDURE `callee`()
BEGIN
    DECLARE `cur` CURSOR FOR
        SELECT 1 LIMIT 0;
    
    DECLARE CONTINUE HANDLER
        FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
    BEGIN
        SELECT 'callee handler';
    END;
    
    CLOSE `cur`;
END;
 
CREATE PROCEDURE `caller`()
BEGIN
    DECLARE CONTINUE HANDLER
        FOR 1326
    BEGIN
        SELECT 'caller handler';
    END;
    
    CALL `callee`();
END;
||
DELIMITER ;
CALL `caller`();

Output:

MariaDB [test]> CALL `caller`();
+----------------+
| callee handler |
+----------------+
| callee handler |
+----------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.43 sec)

Cannot LEAVE a HANDLER

While MySQL documentation clearly states this “limitation”, I’ve seen people trying to use the LEAVE statement to exit a HANDLER block. This can’t be done in the most obvious way, but if you want to exit a whole Procedure or Trigger, then you can do something like this:

USE test;
DROP PROCEDURE IF EXISTS `sp1`;
DELIMITER ||
CREATE PROCEDURE `sp1`()
`lbl_proc`:
BEGIN
    DECLARE found_error BOOLEAN DEFAULT FALSE;
    DECLARE `cur` CURSOR FOR
        SELECT 1 LIMIT 0;
    
    DECLARE CONTINUE HANDLER
        FOR 1326
    `lbl_handler`:
    BEGIN
        SELECT 'cursor not open';
        -- exit procedure
        SET found_error = TRUE;
        LEAVE `lbl_handler`;
    END;
    
    CLOSE `cur`;
    IF found_error IS TRUE THEN
        LEAVE `lbl_proc`;
    END IF;
    SELECT 'go on...';
END;
||
DELIMITER ;
CALL `sp1`();

To leave a Function, of course you can use RETURN.

CONDITIONs must be local

A final note about the named error conditions. They must be local: a CONDITION defined in the caller program does not exist in the callee program.

So, the following example does not work:

-- doesn't work
USE test;
DROP PROCEDURE IF EXISTS `callee`;
DROP PROCEDURE IF EXISTS `caller`;
DELIMITER ||
CREATE PROCEDURE `callee`()
BEGIN
    DECLARE `cur` CURSOR FOR
        SELECT 1 LIMIT 0;
    
    DECLARE CONTINUE HANDLER
        FOR `cursor_not_open`
    BEGIN
        SELECT 'callee handler';
    END;
    
    CLOSE `cur`;
END;
 
CREATE PROCEDURE `caller`()
BEGIN
    DECLARE CONDITION `cursor_not_open` FOR 1326;
    
    CALL `callee`();
END;
||
DELIMITER ;

Output:

ERROR 1319 (42000): Undefined CONDITION: cursor_not_open

EDIT: Suppress errors and warnings

Sometimes you don’t want to take a particular action when an error occurs – sometimes you just want to suppress the error, so that the user doesn’t see it. The typical case is when you execute CREATE TABLE IF NOT EXISTS or DROP TABLE IF EXISTS: you know that the table may (not) exist, it’s ok for you, so you don’t want to see a 1050 or 1051 wanings. The HANDLER to suppress an error is:

DECLARE CONTINUE HANDLER FOR 1051
BEGIN END;

Enjoy!