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                                                                                                                                |

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:


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

# ... other stuff will probably be here

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 *

Change the CAPTCHA codeSpeak the CAPTCHA code