The Blog

Permanently changing MySQL mode Posted on

Permanently changing SQL mode

Then, we use this path to execute the lookup:

$ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

We can see that the first favored configuration file is one in the root of the etc folder. That file, however, did not exist on my system so I opted for the second one.

First, we find out the current sql mode:

mysql -u homestead -psecret -e "select @@sql_mode"

+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

Then, we copy the current string this query produced and remove everything we don’t like. In my case, I needed to get rid of NO_ZERO_IN_DATE, NO_ZERO_DATE and of course ONLY_FULL_GROUP_BY. The newly formed string then looks like this:

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

We open the configuration file we decided on before (/etc/mysql/my.cnf) and add the following line into the [mysqld] section:

[mysqld]
# ... other stuff will probably be here
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Save, exit, and restart MySQL:

sudo service mysql restart

Voilà, the SQL mode is permanently changed and I can continue developing the legacy project until I need some additional strictness.

This entry was posted in MySQL. Bookmark the permalink.

Please Post Your Comments & Reviews

Your email address will not be published. Required fields are marked *



CAPTCHA
Change the CAPTCHA codeSpeak the CAPTCHA code