I recently had to port an old database over to a new Google Cloud SQL instance that was running MySQL 5.7. I had done this so many times in the past with previous versions of MySQL, so I didn’t think much of it. Boy was I in for a surprise.
It turns out MySQL 5.7 is really strict with its flag sql_mode. This can cause a ton of different issues, which I’ll go through along with a way to fix this for Google Cloud SQL specifically.
What sql_mode Flag Does
SQL_Mode is a controversial part of MySQL because it can cause two different databases to have incompatibilities even though they’re both using the same version of MySQL. This is a scary thought, because not only do you need to check what version of MySQL you’re running, but also the sql_mode’s that are enabled.
From MySQL’s documentation:
Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.
Default sql_mode Settings in MySQL 5.7
MySQL 5.7 made a major change by defaulting to a very strict set of sql_mode settings. This means that if you upgrade and try to import your old data, you may run into errors. Also, if you had specific types of queries in your application, they may break in 5.7.
As of right now, using Google Cloud SQL with MySQL 5.7, the default sql_modes are as follows. I’ve included some of the effects of having these settings enabled (and in most cases with strict mode on too, which is default).
- only_full_group_by
- Causes problems when having HAVING or ORDER BY in queries where the columns aren’t included in GROUP BY
- strict_trans_table
- Prevents producing temporary TABLES through methods like
CREATE TABLE SELECTqueries
- Prevents producing temporary TABLES through methods like
- no_zero_in_date
- Prevents any portion of dates from having a zero value such as ‘2010-00-01’ or ‘2010-01-00’
- no_zero_date
- Prevents dates from being completely zero like ‘0000-00-00’
- error_for_division_by_zero
- Error occurs when division by zero instead of just NULL or just a warning
- no_auto_create_user
- User accounts require explicit passwords to be set or they will not be created
- no_engine_substitution
- This will cause MyISAM databases to fail to create since it is not longer supported.
Determining your Enabled sql_mode
It’s pretty straight forward, just issue the following command from within MySQL command line:
mysql> SELECT @@sql_mode;
You’ll get an output like below (this is the default from my instance of Google Cloud SQL on MySQL 5.7):
mysql> 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 | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
Adjusting the sql_mode in Google Cloud SQL
Within Google Cloud SQL console, navigate to your list of SQL instances. Select the one you want and click edit. Now, scroll to the bottom of the page until you get to the option to add Cloud SQL flags.
Unfortunately, you can only add one flag, but in my situation I found that by adding the TRADITIONAL flag, a lot of the trouble from the other strict flags goes away. To learn more about check out the documentation for the TRADITIONAL flag.
Important Note: Whatever flag you select will replace existing ones. So if you don’t really want any, you can choose a flag like ALLOW_INVALID_DATES and basically you’ll have no sql_mode flags set except for that.
Adjusting the sql_mode in MySQL manually
If you’re not using Google Cloud SQL, you can adjust your sql_mode flag manually by logging into your MySQL instance and issuing the following command:
mysql > SET sql_mode = '';
Note that the above snippet will clear ALL the flags. If you want to just remove a single flag, use the following command and substitute the flag you want to remove:
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Verify Your Changes
Once you’ve made your adjustment, make sure you log out of your MySQL command line session. Reconnect and then issue the following command again and your output should look something like this:
mysql> SELECT @@sql_mode; +------------------------------------------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +------------------------------------------------------------------------------------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
You’ll notice for me, all the previous settings are there but TRADITIONAL was added as well. This was enough for my application’s queries to work as expected.
Conclusion
So in conclusion it’s important to be aware of sql_mode, especially in MySQL 5.7+. You’ll save countless hours of debugging if you first check which modes are currently being used. Also, pay attention to your error logs to see what MySQL is telling you. Chances are, the error logs will point you in the right direction.
If you have more to add or have questions, please feel free to comment. I hope this helps!