MySQL mode

by

Updated: 11 April 2024

Unexpected results can occur if mysql mode is not set. On a fresh cloud9 workspace (for example) mysql server has no mode settings

mysql> SELECT @@SQL_MODE;
+------------+
| @@SQL_MODE |
+------------+
|            |
+------------+

Consequently truncation and attempts to insert the wrong types can pass without error

mysql> CREATE TABLE TEST (boolfield TINYINT(1), varcharfield VARCHAR(3));
mysql> INSERT INTO TEST VALUES ('A', 'wxyz');                                                                    
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> SELECT * FROM TEST;                                                                                        
+-----------+--------------+
| boolfield | varcharfield |
+-----------+--------------+
|         0 | wxy          |
+-----------+--------------+

Two warnings are issued but the query does run ok and results in an unintended boolfield value and a truncation.

Now set (one of many possible) sql modes

mysql> SET @@SQL_MODE = 'STRICT_TRANS_TABLES';

mysql> INSERT INTO TEST (boolfield) VALUES ('A');                                                          
ERROR 1366 (HY000): Incorrect integer value: 'A' for column 'boolfield' at row 1

and

mysql> INSERT INTO TEST (varcharfield) VALUES ('wxyz');                                                                                                                                 
ERROR 1406 (22001): Data too long for column 'varcharfield' at row 1

Leave a comment