Check and Change SQL_MODE setting in MYSQL

Check and change the SQL_MODE parameter in MYSQL

SQL_MODE means how the server executes SQL statements.
In MySQL 8.0 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,
NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION.
Note:
Global SQL mode to match site server operating requirements
Each application can set its session SQL mode to its own requirements.

Check the SQL_MODE in MySQL


mysql> SELECT @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.25 sec)

mysql> SELECT @@SESSION.sql_mode;
+--------------------------------------------+
| @@SESSION.sql_mode                         |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

Change the value of SQL_MODE in MySQL

-- AT database level
SET GLOBAL sql_mode = 'modes';

--AT session level
SET SESSION sql_mode = 'modes';

--SET at my.ini file
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

List of few SQL MODE:

STRICT_TRANS_TABLES: If a value could not be inserted as given into a transactional table, abort the statement.
ALLOW_INVALID_DATES: requires that month and day will be valid from 1 to 12 month, date 1 to 31.
With disabled, invalid dates eg ‘2004-04-31’ are converted to ‘0000-00-00’ and a warning is generated.
With strict mode enabled, invalid dates generate an error.
NO_ENGINE_SUBSTITUTION: Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.

More details of Mode:
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

Leave a Reply

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.