I know, we just released version 8.1, so isn't it a bit early to think about version 10 now? But 5.7 has almost reached its end of life, and MySQL engineers are already working on 8.2 and 8.3. With this speed, we can expect to get to version 10 within a few years. And what is so special about version 10, anyway? Nothing, really, except that it is the first version that uses two digits for the major version number.

In MySQL 8.1 we have made some changes to prepare for the day that MySQL 10 is released. Have you verified that your applications are ready for MySQL 10?

Let's have a look at what we've done in MySQL Server to prepare for this, and what you can and should do to future proof your applications.

What we have done

MySQL 8.1 makes one small, but important, change: Version numbers in special comments (so called "version comments") can now be six digits, as opposed to five digits in earlier versions. It's as simple as that, but it has some consequences that we will discuss, but first let's rewind to look at the whole concept of version numbers in comments and why and when comments are not always comments.

Different RDBMSs have different dialects of SQL. Different versions of MySQL also support different features of SQL. In order to help users write SQL that is compatible across DBMSs and different versions of MySQL, there are special comments that look like comments to other DBMSs but as SQL to all or some versions of MySQL. This is used extensively, e.g., in tools like mysqldump, to provide backward compatible SQL statements. Let's look at a simple example, a table of points of interest:

mysql> CREATE TABLE locations (id INTEGER PRIMARY KEY, name VARCHAR(100), position POINT SRID 4326);
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE locations\G
*************************** 1. row ***************************
       Table: locations
Create Table: CREATE TABLE `locations` (
  `id` int NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `position` point /*!80003 SRID 4326 */ DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

In this case, the SRID specification on the position column was a new feature in MySQL 8.0.3. Older versions of MySQL are not able to parse this syntax, so the syntax is enclosed in a comment (that is not really a comment) with some special syntax: /*!80003 SRID 4326 */

The syntax /*...*/ makes this a comment. However, since the first character of the comment is an exclamation mark, it is not treated as a comment by MySQL. So /*!...*/ is a comment to other DBMSs, but not MySQL. Further, the five first characters following the exclamation mark are digits, and to MySQL this means that the comment is not a comment in the version specified by those digits (or in later versions), but still a comment to older versions of MySQL and other DBMSs. The format is /*!xyyzz...*/ for comments which contents are not a comment in MySQL version x.yy.zz or later.

The problem with this format is obvious. There is only one digit for x, and there are two digits in version 10. If we try using six digits to represent version 10.0.0 as /*!100000...*/, MySQL will interpret that as five digits specifying version 1.0.0. The sixth digit is interpreted as part of the text to insert in version 1.0.0 or later. If SRID specifications were introduced in version 10.0.0, the comment would read /*!100000 SRID 4326 */, which would be interpreted as "0 SRID 4326" and cause a syntax error in MySQL 1.0.0 and later.

To demonstrate the effect, we can construct an example that is syntactically valid in all versions, but has different semantics:

mysql-8.0.33> SELECT 2 * /*!100000 +2*/ +1;
+-------------+
| 2 * 0 +2 +1 |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

mysql-8.1.0> SELECT 2 * /*!100000 +2*/ +1;
+---------+
| 2 *  +1 |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

mysql-10.0.0> SELECT 2 * /*!100000 +2*/ +1;
+------------+
| 2 *  +2 +1 |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

Here we use the fact that the plus sign can be used both unary (signifying a positive number) and binary (as the addition operator) to make this valid in all versions. The column heading shows how each MySQL version interprets the statement, and we see that it is handled differently by 8.0, 8.1 and 10.0 (I admit that the MysQL 10.0 result is faked, but that is the way it will be interpreted by a future version 10.0.)

The difference between 8.1 and 10.0 is what we would expect. Both versions understand that this is text to insert in version 10.0.0 and later, but not in older versions.  This means that 8.1.0 correctly understands that the contents of this comment is for version 10.0.0 and later and skips it, while version 10.0.0 decides to include it in the query. But 8.0.33 handles this badly. MySQL 8.0 only understands five digit version numbers, and the sixth digit is interpreted as part of the text to insert, so we multiply 2 with the last digit of the version number, which is zero. This is clearly not what is intended, and the user should be warned. Which is why MySQL starting with 8.0.34 issues this warning:

mysql-8.0.34> SELECT 2 * /*!100000 +2*/ +1;
+-------------+
| 2 * 0 +2 +1 |
+-------------+
|           3 |
+-------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 4164): Immediately starting the version comment after the version number is deprecated and may change behavior in a future release. Please insert a white-space character after the version number.

The result in 8.0.34 is the same as in 8.0.33, but there is a warning that this statement may behave differently in the future. And as we saw above, it definitely will.

MySQL itself outputs version comments in the result of SHOW commands, and mysqldump uses them everywhere, but we have made sure to always print a space character to delimit the version number from the text to insert, so they will not be interpreted differently in future versions. You should do the same when you write version comments.

There is also a backward compatibility issue: MySQL 8.0 will not always be able to load dumps from MySQL 10 or later without modification. If a feature introduced in version 10 or later is used, the dump will contain version comments that 8.0 will misunderstand. It is unlikely that the dump can be loaded in 8.0 without error. You will probably experience a syntax error whenever a version comment with six digits occur. In any case, 8.0 is expected to be EOL by the time MySQL 10 is released, so the use case for importing dumps from version 10 in 8.0 should be very limited.

What you should do

As shown above, it takes some tricks to construct a query that has two valid interpretations in 8.0 and 10.0. It is very unlikely that you have anything like this in your applications. What may occur, however, is version comments that do not separate the version number and the text to insert with a whitespace character. Starting with 8.0.34, MySQL will issue a warning every time it sees this. You should fix all those occurrences to be sure to be forward compatible with 10.0 and later. If you don't, your queries will most likely fail with a syntax error in 10.0. MySQL doesn't care if you use spaces, tabs or new lines (CR, LF and CRLF are all accepted). Just separate the version number from the text to insert.

In addition to comments, there are several other interfaces that include the version number, either as strings or integers:

Your code is probably already capable of handling version 10 in all these cases, unless you have put in explicit checks for version numbers. Still, it doesn't hurt to search for these symbols in your source code and verify that it handles the version number correctly.

Do it now! MySQL 10 is coming!