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!

About these ads

3 thoughts on “Stored Routines to easily work with the SQL_MODE

    • Hi
      I know, but these routines are not meant to be called inside procedures. On my local machine, I have a SQL_MODE as strict as possible. When needed, I use these routines to add or drop a flag, just before CREATE proc/func/view.

    • However, please note that in this example x() doesnt produce any warnings:

      MariaDB [(none)]> \W
      Show warnings enabled.
      MariaDB [(none)]> DELIMITER ||
      MariaDB [(none)]> DROP PROCEDURE IF EXISTS `test`.`x`;
      -> CREATE PROCEDURE `test`.`x`()
      -> BEGIN
      -> SET @@session.sql_mode = ”;
      -> SELECT 1/0;
      -> END;
      -> ||
      Query OK, 0 rows affected (0.00 sec)

      Query OK, 0 rows affected (0.19 sec)

      MariaDB [(none)]> DELIMITER ;
      MariaDB [(none)]> SET @@session.sql_mode = ‘ERROR_FOR_DIVISION_BY_ZERO';
      Query OK, 0 rows affected (0.00 sec)

      MariaDB [(none)]> SELECT 1/0;
      +——+
      | 1/0 |
      +——+
      | NULL |
      +——+
      1 row in set, 1 warning (0.01 sec)

      Warning (Code 1365): Division by 0
      MariaDB [(none)]> CALL `test`.`x`();
      +——+
      | 1/0 |
      +——+
      | NULL |
      +——+
      1 row in set (0.04 sec)

      Query OK, 0 rows affected (0.81 sec)

      MariaDB [(none)]>

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s