MYSQL strict mode

MYSQL strict mode

Step 1: Check if strict mode is enabled
We first need to establish if strict mode is enabled on the MySQL
server. To check this, type the command below on a terminal server:

$ sudo mysql -u root -p
Enter your MySQL database root password and press Enter.

Then, you will need to run the query below on the MySQl command prompt:

$ SHOW VARIABLES LIKE 'sql_mode';
A table is displayed on the screen with some sql_mode values separated
with commas as shown below. If you find a value like
“STRICT_TRANS_TABLES”, then, MySQL strict mode is enabled.



Step 2: Create a new configuration file using a nano editor
Using a nano editor, create a new configuration file under the
/etc/mysql/conf.d/ directory. The new configuration file will override
the default MySQL configuration file.

Use the command below:

$ sudo nano /etc/mysql/conf.d/disable_strict_mode.cnf
Then, enter the text below on the text editor:

[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Press CTRL +X and Y to save the changes.

Step 3: Restart MySQL
When you make any change to any MySQL configuration directory, you
should restart MySQL service for the changes to take effect using the
command below:

$ sudo service mysql restart
Step 4: Confirming the change
Log to your MySQL server one more time using the command below:

$ sudo mysql -u root -p
Enter your root password and press Enter.

Run the query below one more time on the MySQl command prompt:

$ SHOW VARIABLES LIKE 'sql_mode';
As you can see from the screenshot below, the value
‘STRICT_TRANS_TABLES’ in the list of sql_mode values and this means it
has been disabled successfully.