How to find and disable MySQL strict mode?

Trainee Asked on October 10, 2019 in Cpanel,   MySQL.
Add Comment
2 Answer(s)

Open SSH/Console and verify the current mode:

# mysql -e "SELECT @@sql_mode;"

+----------------------------------------------------------------------+
| @@sql_mode                                                           |
+----------------------------------------------------------------------+
|STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, |
|NO_ENGINE_SUBSTITUTION                                                |
+----------------------------------------------------------------------+

Locate and edit the “my.cnf” file

vim /etc/my.cnf

Find and change the following or add the line if it’s not present

Strict Mode Enabled:

sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Strict Mode Disabled:

sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Restart the MySQL Service

/etc/init.d/mysql restart

Verify strict mode has been disabled:

# mysql -e "SELECT @@sql_mode;"

+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

Done 🙂

Reviewer Answered on October 10, 2019.
Add Comment

To turn off (or on) mysql strict access from cpanel.

1, search for ‘phpmyadmin’, in the search box, click on it

2, once phpmyadmin is loaded up, click on the ‘variables’ tab

3, search for ‘sql mode’

then

to turn strict mode on enter

STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

to turn strict mode off, enter

NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

If you have control of the script you’ll be using then it’s a good idea to leave strict mode on, it’ll alert you to any bugs in your code before they become an issue down the line.

Reviewer Answered on October 10, 2019.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.