»_

2013-05-19 15:21:20

SQL Modes - MySQL "magic"

Share

Categories mysql databases

SQL modes are settings for behavior of MySQL server consisting of modes that control aspects of interpreting user's requests.

Possibilities of SQL mode:

  1. Set strict of soft validation of input data
  2. Enable or disable following of SQL standards
  3. Enables better syntax compatibility between other databases.

By all means, SQL mode is a very powerful mechanism of database tuning, allowing flexible manipulation of MySQL input/output.

Before we continue you have to remember that changing SQL mode settings after creating and inserting data into partitioning tables may can cause substantial changes in table behavior and you may lose or damage your data.
It is strongly advised that you don't change SQL mode after creating partitioning tables.
When replicating partitioning tables, different SQL mode settings on Primary and Slave MySQL servers can also lead to problems. For stable replications between servers, SQL mode settings must be identical, if not the same.
Now that you have been warned about possible misuse of SQL modes, let get into some details.

Controlling current SQL mode is being made trough system variable called sql_mode. For setting it's values, system command SET is used. Here are some examples of setting current SQL mode:

1. Setting default value:
 
SET sql_mode = '';

2. Setting single mode can be done with or without quotes:
 
SET sql_mode = ANSI_QUOTES;
SET sql_mode = 'TRADITIONAL';

3. Setting several sql modes. Quotes are mandatory!
 
SET sql_mode = 'ANSI_QUOTES,IGNORE_SPACE';
SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO, STRICT_ALL_TABLES';

In above examples we did set sql mode for current session, but if you have privileges of superuser, you can set sql modes for whole server and all current connections by setting GLOBAL parameter. Syntax looks like this:
 
SET [GLOBAL|SESSION] sql_mode='PARAMS'

To see what is the current/global sql modes, use the following commands:
 
SELECT @@global.sql_mode;
SELECT @@session.sql_mode;

Besides SET command there are also two ways of setting sql modes:
  1. Start server with --sql-mode="MODE" params
  2. Setup my.cnf/my.ini (nix/win) with sql-mode="MODE" parameter

Short SQL modes reference:

ANSI_QUOTES

Makes our server accept " as ' but also loses ability to brake lines. As you can imagine, this mode makes MySQL behave more closely to SQL standard.
 
mysql> CREATE TABLE test_1 (`order` INT NULL) ENGINE = InnoDB;
Query OK, 0 rows affected (0.28 sec)

mysql> CREATE TABLE test_2 ("order" INT NULL) ENGINE = InnoDB;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"order" INT NOT NULL) ENGINE = InnoDB' at line 1

mysql> SET sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE test_2 ("order" INT NULL) ENGINE = InnoDB;
Query OK, 0 rows affected (0.02 sec)

IGNORE_SPACE

By default, between function and opening bracket you can't have any spaces. By turning this mode on you allow your server ignore spaces, but unfortunately all functions become system reserved words. This means that when you column name has the same name as the function, you will have to quote it.
 
mysql> SELECT COUNT   (*) FROM test_1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) FROM test_1' at line 1

mysql> SET sql_mode = 'IGNORE_SPACE';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT   (*) FROM test_1;
+-------------+
| COUNT (*) |
+-------------+
|                0 |
+-------------+
1 row in set (0.01 sec)

ERROR_FOR_DIVISION_BY_ZERO

When making division by zero, in strict mode you will get an error, in non-strict: a warning when inserting or updating. Without this parameter, dividing by zero will return a warning and will insert NULL value. About strict/non-strict modes we will talk later.
 
mysql> SELECT 1 / 0;
+-------+
 | 1 / 0  |
+-------+
|  NULL |
+-------+
1 row in set (0.00 sec)

mysql> SHOW WARNINGS;
Empty set (0.00 sec)

mysql> INSERT INTO test_1 VALUES (1/0);
Query OK, 1 row affected (0.01 sec)

mysql> SHOW WARNINGS;
Empty set (0.00 sec)

mysql> SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 1 / 0;
+-------+
 | 1 / 0  |
+-------+
 | NULL |
+-------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+----------+------+------------------+
| Level     | Code | Message        |
+----------+------+------------------+
| Warning | 1365 | Division by 0 |
+----------+------+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO test_1 VALUES (1/0);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+----------+------+-------------------+
| Level     | Code | Message          |
+----------+------+-------------------+
| Warning | 1365 | Division by 0   |
+----------+------+-------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO test_1 VALUES ('some string'/0);
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> SHOW WARNINGS;
+----------+-------+---------------------------------------------------------------+
|   Level   | Code | Message                                                                  |
+----------+-------+---------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'some string' |
| Warning | 1365 | Division by 0                                                          |
+----------+-------+---------------------------------------------------------------+
2 rows in set (0.00 sec)

In provided examples we get only warning because strict mode was turned off. Understanding strict modes is very important for working with MySQL databases because in more classical databases there is no such concept. Jumping ahead let me tell you that all databases are strict and don't allow syntax liberties that MySQL does. This was in MySQL's philosophy way before InnoDB. Let's analyze.. in a non-transactional tables we have a completely different rules and following strict rules often let to undesired results.

STRICT_TRANS_TABLES

Turns "strict mode" on for all the tables that support transaction, for example InnoDB and BDB. This mode returns an error instead of warning in the following cases
  1. The type of the input data doesn't match the type of the column. For example inserting strings into a numeric column.
  2. Numbers or dates are outside of allowed boundaries.
  3. Skipping column that is set with NOT NULL attribute.
  4. Length of the value is larger than defined. For example for VARCHAR(5) the max length that you will be able to insert is obviously
  5. When values for ENUM or SET are not set.

More about specifics of each mode is explained later in this article.

STRICT_ALL_TABLES

STRICT_ALL_TABLES is totally identical to STRICT_TRANS_TABLES, but applies to all the tables in MySQL and not only transactional.
Due to differences in approaching work with transactional and non-transactional tables it doesn't always make sense to use this mode.

TRADITIONAL

This is a composite mode that contains several modes including "strict mode". It makes MySQL act as most  "traditional" SQL databases.

Lets take a look at full list of modes that are included when "TRADITIONAL" is set.
 
mysql> SET sql_mode = 'TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

ANSI

Another composite mode that makes MySQL behave as "ANSI-compatible".. this meaning it's closer to standard SQL.

Includes the following modes: ANSI_QUOTES, IGNORE_SPACE, REAL_AS_FLOAT, PIPES_AS_CONCAT.
First two modes were described already so le me explain briefly the last two:
REAL_AS_FLOAT – Makes real as float and not double.
PIPES_AS_CONCAT – Allows || for string concatenating instead of being interpreted as logical OR.

ONLY_FULL_GROUP_BY

Generates an error when using GROUP BY without full list of non-aggregated parameters from SELECT and HAVING.
 
mysql> SELECT name, last_name, MAX(age) FROM t_test GROUP BY name;
ERROR 1055 (42000): 't.last_name' isn't in GROUP BY

mysql> SELECT name, MAX(age) as max_age FROM t_test GROUP BY name HAVING max_age < 30;
Empty set (0.00 sec)
ERROR 1463 (42000): Non-grouping field 'max_age' is used in HAVING clause

If you wish to know about all SQL mode states and dig deeper, please check official documentation @ http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html
 

Strict mode

We have met with this mode in earlier when was learning about STRICT_TRANS_TABLES, STRICT_ALL_TABLES and composed  TRADITIONAL. From it's name is easy to guess that all the input data will be strictly checked and in case of incompatibilities, errors or limitations you will be struck with an error.

An error in transactional tables will be rolled back. Even if  you don't use start transaction, your query will be surrounded with start transaction and commit. All traditional databases work like this. So keep in mind that in case of an error everything will be rolled back. For non transactional tables everthing is a little bit more tricky. In case of an error only the last action will be rolled back. Let's take a look at this example:
 
mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE test2 (
    -> x TINYINT NOT NULL,
    -> y TINYINT NOT NULL
    -> ) ENGINE = MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO test2 VALUES (1, 1), (2, 2), (3, 3), (NULL, 4), (5, 5);
ERROR 1048 (23000): Column 'x' cannot be null

mysql> SELECT * FROM test2;
+---+---+
| x   |  y  |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+---+
3 rows in set (0.01 sec)

mysql> UPDATE test20 SET x = x + 125, y = y + 1;
ERROR 1264 (22003): Out of range value for column 'x' at row 3

mysql> SELECT * FROM test2;
+-----+---+
| x      |  y |
+-----+---+
| 126 | 2 |
| 127 | 3 |
|     3 | 3 |
+-----+---+
3 rows in set (0.00 sec)

Error generation occurs in the following situations:
1. Type of input data is different from one defined for the column.
2. Omitting column that is defined as NOT NULL.
3. Out of boundaries for dates and numbers.
4. For strings – Exceed maximum characters number.
5. For ENUM and SET – invalid values.
6. Inserting NULL into a NOT NULL column.

Default values for data types

If in INSERT statement no data is specified the column then MySQL with act in the following order:
1. If the column has a default value, then it will be used, otherwise:
2. If the column doesn't have NOT NULL value than it will be set to NULL and that's it but this depends on what sql_mode is set.

As you can guess, strict mode will throw an error and will rollback the transaction for transactional tables or will cancel last action for non-transactional tables.

For non-strict mode default value will be inserted.

Default values for each type:

    For int the default value is zero (0)
    For all strings except for enum – empty string ('')
    For enum – first element on the list
    For date and time – null values: '0000-00-00 00:00:00'
    For timestamp — current time and date if column is automatically initialized, otherwise '0000-00-00 00:00:00'
 

Non-strict mode

Finally we get to the most mysterious part of the article that is seen by many as 'dark magic' but fortunately for us it's just configuration tricks and their effects.

Maybe I should have explained  earlier in what cases data validation is done, but I will do it now. There are only tree but each one of them needs detailed attention.
Data modification: INSERT, UPDATE, REPLACE, LOAD DATA INFILE
Schema update: ALTER TABLE
Setting default values: DEFAULT

Keep in mind that in strict mode errors will be generated (plus rollback) and in non-strict mode values will be set to a sort-of correct values and a warning will be thrown. To see warning use SHOW WARNINGS.
Lets take a more detailed look:

Getting outside of boundaries

If the value is less than allowed,  then the minimum allowed value will be set. If it's bigger, then the maximum will be set.
 
mysql> CREATE TABLE test3_1 (
    -> i TINYINT NOT NULL,
    -> j TINYINT NOT NULL,
    -> k TINYINT NOT NULL
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO test3_1 (i, j, k) VALUES (-500, 10, 500);
Query OK, 1 row affected, 2 warnings (0.02 sec)

mysql> SELECT * FROM test3_1;
+------+----+-----+
| i    | j  | k   |
+------+----+-----+
| -128 | 10 | 127 |
+------+----+-----+
1 row in set (0.00 sec)
 

String processing

Strings bigger than allowed size — truncated.
 
mysql> CREATE TABLE test2 ( col1 VARCHAR(10) ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO test3 VALUES ('This is really a big string');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'col1' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test3;
+------------+
| col1         |
+------------+
| This is re |
+------------+
1 row in set (0.00 sec)
 

ENUM and SET data types

If value is not present in ENUM array then MySQL will transform it into an empty string
If values that are being passed to SET contain elements not present in SET's definition, then MySQL will only save the ones that are valid and discard the others.
 
mysql> CREATE TABLE test3 (
    -> col1 ENUM('One', 'Two', 'Three'),
    -> col2 SET('One', 'Two', 'Three')
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO test3 VALUES ('Five', 'One,Three,Five');
Query OK, 1 row affected, 2 warnings (0.02 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level     | Code | Message                                       |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'col1' at row 1 |
| Warning | 1265 | Data truncated for column 'col2' at row 1 |
+---------+------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test33;
+------+---------------+
| col1  | col2             |
+------+---------------+
|          | One,Three   |
+------+---------------+
2 rows in set (0.00 sec)
 

Conversion into date type

When trying to save a value that can't be converted into column's type, MySQL will set a default value of it's type.
 
mysql> CREATE TABLE test3 (
    -> col1 DATETIME,
    -> col2 DATETIME
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO test3 VALUES ('string', 123);
Query OK, 1 row affected, 1 warning (0.12 sec)

mysql> SHOW WARNINGS;
+-----------+-------+---------------------------------------------------------------+
| Level       | Code | Message                                                                 |
+-----------+-------+---------------------------------------------------------------+
| Warning  | 1264 | Out of range value for column 'col1' at row 1          |
+-----------+-------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test3;
+------------------------------+------------------------------+
| col1                                | col2                                |
+------------------------------+------------------------------+
| 0000-00-00 00:00:00     | 2013-01-01 00:00:00     |
+------------------------------+------------------------------+
1 row in set (0.00 sec)
 

Conversion table

STRING               DATE                  INT
'2010-03-12'      '2010-03-12'     2010
'03-12-2010'      '0000-00-00'     3
'0017'                 '0000-00-00'     17
'500 hats'           '0000-00-00'     500
'bartholomew'    '0000-00-00'     0
 

Setting NULL for NOT NULL column

The result depends on number of lines in the INSERT query.
When inserting only one line, then error is generated and no changes are made. When multiple inserts are made — MySQL converts it into a default value silently.


Updating tables: ALTER TABLE

When changing data type of the column, restrictions of the new type are being applied and this can cause an unexpected change of the data based on the rules described before.

If a column is being changed to NOT NULL, t hen all the NULL values will be converted to a default value for current type of the column. Take a look at the "default values for data types" described above.
 
mysql> CREATE TABLE test3 (
    -> i INT NULL,
    -> i2 INT NOT NULL,
    -> j VARCHAR(20) NOT NULL,
    -> k DATETIME
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO test3 VALUES (NULL, 1111, 'some random stuff', '1990-01-01 14:00:00');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test3;
+------+--------+---------------------------+-------------------------------+
| i        | i2        | j                                  | k                                     |
+------+--------+---------------------------+-------------------------------+
| NULL | 1111 | some random stuff     | 1990-01-01 14:00:00      |
+------+--------+---------------------------+-------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE test3
    -> CHANGE COLUMN `i` `i` INT(11) NOT NULL,
    -> CHANGE COLUMN `i2` `i2` TINYINT(1) NOT NULL,
    -> CHANGE COLUMN `j` `j` VARCHAR(2) NOT NULL,
    -> CHANGE `k` `k` TIMESTAMP NULL DEFAULT NULL;
Query OK, 1 row affected, 4 warnings (0.31 sec)
Records: 1  Duplicates: 0  Warnings: 4

mysql> SHOW WARNINGS;
+---------+--------+-----------------------------------------------------------+
| Level    | Code | Message                                                           |
+---------+--------+-----------------------------------------------------------+
| Warning | 1265 | Data truncated for column 'i' at row 1          |
| Warning | 1264 | Out of range value for column 'i2' at row 1  |
| Warning | 1265 | Data truncated for column 'j' at row 1          |
| Warning | 1264 | Out of range value for column 'k' at row 1   |
+---------+------+-------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM test3;
+---+-----+----+------------------------------+
| i     | i2    | j     | k                                    |
+---+-----+----+------------------------------+
| 0   | 127 | so | 0000-00-00 00:00:00      |
+---+-----+----+------------------------------+
1 row in set (0.00 sec)
 

IGNORE

Keyword IGNORE makes MySQL turn non strict mode for current query. You can use it to generate warnings instead of errors when violating a constraint for a PRIMARY KEY or UNIQUE values.
 
mysql> CREATE TABLE test4 (
    -> x INT NOT NULL PRIMARY KEY
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT IGNORE INTO test4 VALUES (1), (2), (2), (3), (4);
Query OK, 4 rows affected (0.02 sec)
Records: 5  Duplicates: 1  Warnings: 0

mysql> SELECT * FROM test4;
+---+
|   x |
+---+
|   1 |
|   2 |
|   3 |
|   4 |
+---+
4 rows in set (0.02 sec)

mysql> INSERT INTO test4 VALUES (1), (2), (2), (3), (4);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> UPDATE IGNORE test4 SET x = 3 WHERE x = 2;
Query OK, 0 rows affected (0.05 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> UPDATE test4 SET x = 3 WHERE x = 2;
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
 

ON DUPLICATE KEY UPDATE

This kind of a query is not the best example but at least this approch exists.
 
mysql> INSERT INTO test4 VALUES (10) ON DUPLICATE KEY UPDATE x = x + 10;
Query OK, 1 row affected (0.2 sec)

mysql> SELECT * FROM test4;
+----+
| x    |
+----+
|  1   |
|  2   |
|  3   |
|  4   |
| 10 |
+----+
5 rows in set (0.03 sec)

mysql> INSERT INTO test4 VALUES (10) ON DUPLICATE KEY UPDATE x = x + 10;
Query OK, 2 rows affected (0.03 sec)

mysql> SELECT * FROM test4;
+----+
| x     |
+----+
|  1   |
|  2   |
|  3   |
|  4   |
| 20  |
+----+
5 rows in set (0.00 sec)

Hope this helps.
Many thanks to Yuri Afanasiev for his article @ habrahabr.

2 responses to "SQL Modes - MySQL "magic""

Comment

2019-02-14 10:04:27

Instantly create an app for your website with the click of buttons! Appy Pie, is an unrivalled leader in the mobile app bandwagon that allows everyone to transform their app ideas into reality, without any technical knowledge. Simply drag and drop the features and create an advanced Android or iOS application for mobiles and smartphones, as easy as a pie. Start creating your app on the fly & even convert your website into an app with Appy Pie. http://www.tkqlhce.com/click-8095449-11578080

Comment

2019-02-14 14:26:03

Instantly create an app for your website with the click of buttons! Appy Pie, is an unrivalled leader in the mobile app bandwagon that allows everyone to transform their app ideas into reality, without any technical knowledge. Simply drag and drop the features and create an advanced Android or iOS application for mobiles and smartphones, as easy as a pie. Start creating your app on the fly & even convert your website into an app with Appy Pie. http://www.tkqlhce.com/click-8095449-11578080

Leave a comment

Array
(
    [type] => 32
    [message] => Module 'mssql' already loaded
    [file] => Unknown
    [line] => 0
)