How to Make MySQL 8 Compatible With Older MySQL Versions

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 mysqld.cnf:

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 mysqld.cnf:

sort_buffer_size = 512K

You may need to apply the following changes in mysqld.cnf:

default_authentication_plugin=mysql_native_password

mysql
ALTER USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword';
FLUSH PRIVILEGES;

character-set-server = latin1

[client]
default-character-set=latin1

[mysql]
default-character-set=latin1

If you made any changes in mysqld.cnf, restart the service mysql:

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:

SELECT @@GLOBAL.sql_mode;

The output would be:

In MySQL 5.7:

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

In MySQL 8:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

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;

(source)

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.

2.1. ONLY_FULL_GROUP_BY

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;

will produce:

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

because 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.

2.2 STRICT_TRANS_TABLES

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:

SHOW WARNINGS\G

'\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.

2.3 NO_ZERO_IN_DATE

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. '2021-11-00' or '').

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;

will produce:

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 '2021-01-00'.

NO_ZERO_IN_DATE does not affect dates like '0000-00-00'. They can be allowed / disallowed by NO_ZERO_DATE:

2.4 NO_ZERO_DATE

NO_ZERO_DATE – forbids zero dates '0000-00-00'. 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,NO_ZERO_DATE';

UPDATE wp_posts 
   SET post_date = '0000-00-00' 
 WHERE ID = 3;

will produce:

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.

2.5 ERROR_FOR_DIVISION_BY_ZERO

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;

will produce:

ERROR 1365 (22012): Division by 0

The same error would be generated if we tried to INSERT a value with a division by 0.

If ERROR_FOR_DIVISION_BY_ZERO is set, but STRICT_TRANS_TABLES is not set:

• 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.

If STRICT_TRANS_TABLES is set, but ERROR_FOR_DIVISION_BY_ZERO is not set:

• 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.

2.6 NO_AUTO_CREATE_USER

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';

2.7 NO_ENGINE_SUBSTITUTION

NO_ENGINE_SUBSTITUTION – gives an error when CREATE TABLE or ALTER TABLE contains a non-existent storage engine.

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;

will produce:

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.

Conclusion

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.