How to change MySQL sql-mode

mysql> show global variables like 'sql_mode';
--------------
show global variables like 'sql_mode'
--------------

+---------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SET SESSION sql_mode = sys.list_drop(@@session.sql_mode, 'NO_ZERO_DATE');
--------------
SET SESSION sql_mode = sys.list_drop(@@session.sql_mode, 'NO_ZERO_DATE')
--------------

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

mysql> show global variables like 'sql_mode';
--------------
show global variables like 'sql_mode'
--------------

+---------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show session variables like 'sql_mode';
--------------
show session variables like 'sql_mode'
--------------

+---------------+-----------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'NO_ZERO_DATE');
--------------
SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'NO_ZERO_DATE')
--------------

Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like 'sql_mode';
--------------
show session variables like 'sql_mode'
--------------

+---------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> mysql>


Comments

Popular posts from this blog

Check MySQL query history from command line