Quick Tip: How to Permanently Change SQL Mode in MySQL

Share this article

I was working on a legacy project recently and needed to import some data from MySQL 5.5. All the queries in the code worked perfectly in MySQL 5.5, so I assumed an upgrade to 5.7 would be seamless. Not so.

MySQL logo

First I got errors due to DateTime columns being populated with zeros during import, then when running this query:

select * 
  from ebay_order_items 
  where 
    z_shipmentno is null 
    and ExternalTransactionID is not null 
    and orderstatus = 'Completed' 
    and timestamp > '2015-02-25' 
  group by ExternalTransactionID 
  order by timestamp desc

I got this:

Expression #1 of SELECT list is not in GROUP BY 
clause and contains nonaggregated column 
'1066export.ebay_order_items.TransactionID' which 
is not functionally dependent on columns in GROUP BY 
clause; this is incompatible with sql_mode=only_full_group_by

It turned out that the only_full_group_by mode was made default in version 5.7.5., which breaks many of such naïve queries. In fact, see here for more info.

I could rewrite all queries to be 5.7 compatible (hat tip to George Fekete for the solution) and do something as atrocious as this:

select extf.* from (
	select ExternalTransactionID
	from ebay_order_items
	where ExternalTransactionID is not null
	group by ExternalTransactionID
) extf JOIN ebay_order_items eoi 
ON (eoi.ExternalTransactionID = extf.ExternalTransactionID)
where eoi.z_shipmentno is null
and eoi.orderstatus = 'Completed'
and eoi.timestamp > '2015-02-25'
order by eoi.timestamp desc

… but this would make the already complex refactoring much more difficult. It would be much better to temporarily disable these checks and force MySQL to act like it did in 5.6.

Permanently changing SQL mode

First, we find out which configuration file our MySQL installation prefers. For that, we need the binary’s location:

$ which mysqld
/usr/sbin/mysqld

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.

Frequently Asked Questions on Permanently Changing SQL Mode in MySQL

What is SQL Mode in MySQL and why is it important?

SQL Mode in MySQL is a set of server system variables that adjust the SQL query syntax and data validation. It’s important because it allows you to control the level of strictness for data validation and error handling in your database. By changing the SQL Mode, you can make your database more flexible or more strict, depending on your needs.

How can I check the current SQL Mode in MySQL?

You can check the current SQL Mode by running the following command in your MySQL client: SELECT @@sql_mode;. This will return a list of modes that are currently enabled.

What are the different SQL Modes available in MySQL?

There are several SQL Modes available in MySQL, including STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION, ONLY_FULL_GROUP_BY, and many others. Each mode has a different effect on the behavior of MySQL, and you can enable multiple modes at once by listing them in a comma-separated list.

How can I temporarily change the SQL Mode in MySQL?

You can temporarily change the SQL Mode for the current session by running the following command: SET sql_mode = 'your_desired_mode';. This change will only last until the end of the current session.

How can I permanently change the SQL Mode in MySQL?

To permanently change the SQL Mode, you need to edit the MySQL configuration file (my.cnf or my.ini, depending on your system). Add or modify the sql_mode line under the [mysqld] section to include your desired modes. After saving the changes, you need to restart the MySQL server for the changes to take effect.

What happens if I set an invalid SQL Mode?

If you set an invalid SQL Mode, MySQL will ignore it and continue with the valid modes. If all modes are invalid, MySQL will operate with its default mode.

Can I set different SQL Modes for different databases in the same MySQL server?

No, the SQL Mode is a global setting that applies to all databases on the same MySQL server. However, you can temporarily change the mode for a specific session.

What is the default SQL Mode in MySQL?

The default SQL Mode depends on the version of MySQL you are using. In recent versions, the default mode includes ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION, and several others.

How can I reset the SQL Mode to its default value?

You can reset the SQL Mode to its default value by removing the sql_mode line from the MySQL configuration file and restarting the server. Alternatively, you can set the mode to the default value for your MySQL version.

What should I do if I encounter problems after changing the SQL Mode?

If you encounter problems after changing the SQL Mode, you should first try to identify the cause of the problem. Check if the problem is related to a specific mode and try disabling it. If you can’t solve the problem, consider seeking help from the MySQL community or a professional.

Bruno SkvorcBruno Skvorc
View Author

Bruno is a blockchain developer and technical educator at the Web3 Foundation, the foundation that's building the next generation of the free people's internet. He runs two newsletters you should subscribe to if you're interested in Web3.0: Dot Leap covers ecosystem and tech development of Web3, and NFT Review covers the evolution of the non-fungible token (digital collectibles) ecosystem inside this emerging new web. His current passion project is RMRK.app, the most advanced NFT system in the world, which allows NFTs to own other NFTs, NFTs to react to emotion, NFTs to be governed democratically, and NFTs to be multiple things at once.

BrunoSconfigurationdatabasegroupmysqlquick-tipsql
Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week