MySQL developers, with each new version, try to make MySQL more SQL standard compliant. So, when you move your web project to a new server, your old SQL queries could stop working. Because the newer version of MySQL does not support them. And, if you do not have the time (or budget) to rewrite all those incompatible queries, you’ll need a quick temporary solution.
The solution is to set SQL Modes in MySQL to make the newer version of MySQL compatible with your old queries. Plus do some additional adjustments. This solution is described below.
Of course, it is a temporary solution. The right thing would be to rewrite the SQL queries of your project. To make them compatible with the new version of MySQL.
1. Change MySQL 8 configuration
All the console commands and file names/locations below are given for Ubuntu (14.04 – 22.04) operating system.
1. Edit the MySQL configuration file
cd /etc/mysql/mysql.conf.d sudo nano mysqld.cnf
For big systems, I normally increase the sort_buffer_size. So I add to the end of
sort_buffer_size = 512K
You may need to apply the following changes in
If you made any changes in
mysqld.cnf, restart the service
sudo service mysql restart
2. Set the SQL modes to make your queries compatible with the new version of MySQL:
First, check which SQL modes are currently set:
The output would be:
In MySQL 5.7:
In MySQL 8:
These constants are explained below. You need to remove only the constants which make the project incompatible with MySQL 8.
E.g. edit the file
/etc/mysql/mysql.conf.d/mysqld.cnf, and under the section
[mysqld] add the line:
sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
You could also read this article.
Or, for testing purposes, you could temporarily (it will not persist after MySQL server restart) set the SQL mode globally (= for all MySQL clients) as:
SET GLOBAL sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"; SELECT @@GLOBAL.sql_mode;
or for the current MySQL client only:
SET SESSION sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"; SELECT @@SESSION.sql_mode;
2 Examples of SQL Modes in MySQL 8
Disclaimer: All links in this section lead to the official MySQL documentation. I just give some examples of SQL queries where setting SQL modes would lead to errors. The description of the SQL modes in this section is the most basic. It is neither detailed nor complete. For complete descriptions, please click on the SQL mode names below and read the official MySQL documentation.
ONLY_FULL_GROUP_BY – when set, MySQL forces us to group on all non-aggregate columns (as per SQL 92) used in SELECT, ORDER BY or HAVING. Though grouping by functionally dependent columns is not necessary (as per SQL:1999).
E.g. the queries:
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY'; SELECT post_title , post_status FROM wp_posts GROUP BY post_title;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘wpdiaries.wp_posts.post_status’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
post_title is not a unique or primary key. And
post_status is not functionally dependent on it.
If we grouped by
ID here instead of
post_title, the error would not arise. Because all columns in the table
wp_posts functionally depend on
ID. And SQL:1999 would allow us such grouping.
STRICT_TRANS_TABLES – sets the Strict SQL Mode (also, see STRICT_ALL_TABLES). Strict Mode affects data changing queries only. E.g. INSERT or UPDATE. Without this mode, MySQL will insert/update an adjacent value and give us a warning.
If you try to set this mode alone, like
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
, the warning would be generated:
Warning 3135: ‘NO_ZERO_DATE’, ‘NO_ZERO_IN_DATE’ and ‘ERROR_FOR_DIVISION_BY_ZERO’ sql modes should be used with strict mode. They will be merged with strict mode in a future release.
You can see warnings with the command SHOW WARNINGS:
'\G' here is not a part of the SQL statement. It tells the MySQL client to show results in rows rather than columns. If you used a semi-column
';' (rather than
'\G') at the end of the statement, the result would be shown in the column output.
Please also see the section Comparison of the IGNORE Keyword and Strict SQL Mode of the MySQL documentation.
NO_ZERO_IN_DATE – if set, does not allow a zero month or a zero day in dates when the year is non-zero (e.g.
E.g. the queries:
SET SESSION sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE'; UPDATE wp_posts SET post_date = '2021-01-00 00:00:00' WHERE ID = 3;
ERROR 1292 (22007): Incorrect datetime value: ‘2021-01-00 00:00:00’ for column ‘post_date’ at row 1
because we used a zero day in the date
E.g. the queries:
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE'; UPDATE wp_posts SET post_date = '0000-00-00' WHERE ID = 3;
ERROR 1292 (22007): Incorrect datetime value: ‘0000-00-00’ for column ‘post_date’ at row 1
because we tried to set the value ‘
0000-00-00‘. The same error arises if we try to insert
'0000-00-00' to a DATE or DATETIME field.
If NO_ZERO_DATE is set, but STRICT_TRANS_TABLES is not set, INSERT queries will insert
'0000-00-00' and UPDATE queries will update to
'0000-00-00' just fine. But the warning 1264 Out of range value for column ‘post_date’ at row 1 would be generated.
ERROR_FOR_DIVISION_BY_ZERO – disallows division by 0 in INSERT and UPDATE queries. Works in conjunction with the strict mode only. Deprecated (will be part of the Strict SQL Mode in the future version of MySQL).
E.g. the queries:
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO'; UPDATE wp_comments SET comment_karma = 5 / 0 WHERE comment_ID = 4;
ERROR 1365 (22012): Division by 0
The same error would be generated if we tried to INSERT a value with a division by 0.
- an INSERT query would try to insert NULL and generate a warning 1365 Division by 0. If the column is declared as NOT NULL, the error 1048 (23000) Column cannot be null would be generated.
- an UPDATE query would try to set the field value to NULL and generate a warning 1365 Division by 0. If we column was declared as NOT NULL, the value would be set to 0 for numeric columns and to empty string for character type columns. And an additional warning 1048 Column cannot be null would be generated.
- an INSERT query would try to insert NULL – no warnings. If the column is declared as NOT NULL, the error 1048 (23000) Column cannot be null would be generated.
- an UPDATE query would try to set the field value to NULL – no warnings. If we column was declared as NOT NULL, the error 1048 (23000) Column cannot be null would be generated.
For SELECT queries, in the case of division by 0, NULL is always returned, no matter which modes are set. E.g. the query:
SELECT 5 / 0;
would always return NULL. But if the flag ERROR_FOR_DIVISION_BY_ZERO is set, the warning 1365 Division by 0 would be also generated.
NO_AUTO_CREATE_USER – was removed in MySQL 8.0.11. Prevents GRANT from creating MySQL users automatically. Since GRANT can not create users in the recent versions of MySQL 8, this SQL is not available anymore. And we do not give any examples here.
Still, as a small side note, if you try to run a GRANT command in MySQL 8 like:
GRANT ALL on wpdiaries.* TO myuser@localhost IDENTIFIED BY 'mypass';
, you’ll get an error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘IDENTIFIED BY ‘mypass” at line 1
You need to create the user first. And only then run the GRANT command:
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass'; GRANT ALL ON wpdiaries.* TO 'myuser'@'localhost';
E.g. the queries:
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION'; CREATE TABLE `wp_my_table` ( `ID` bigint unsigned NOT NULL AUTO_INCREMENT, `content` TEXT, PRIMARY KEY (`ID`) ) ENGINE=MyUknownEngine;
ERROR 1286 (42000): Unknown storage engine ‘MyUknownEngine’
because the storage engine ‘MyUknownEngine’ does not exist.
Without NO_ENGINE_SUBSTITUTION, the table would have been created with the default storage engine. And a warning would have been generated.
Thank you for reading to the end.
There are many cases where this could be possibly useful.
For example, you could have performance issues with your project. And you decide to scale your project horizontally (by adding new servers). Often, the project performance bottleneck is SQL queries. SQL queries are often much slower than your CMS or framework PHP code. In this case, you can add more MySQL servers using replication. And the replication servers could have much newer versions of MySQL. In this case, you would need to make the new versions of MySQL compatible with the old one.
Or, if you are a web developer, you may need to deploy some old project on a VirtualBox virtual machine for web development locally. In this case, if you have a recent version of MySQL on your virtual machine. And you may need to make it compatible with the old SQL queries of your project.
Of course, there could be different solutions.
For example, you could set up your project using Docker containers. In this case, changing the MySQL version (and experimenting with different versions of MySQL) would be really quick. If you decide to use Docker, and if your project is in WordPress, you may find this article on adding XDebug to the official WordPress image useful.
Thanks again for reading this article.
If you have any comments, I would be very glad if you left them below.