Technology Sharing | Some precautions for migrating from MariaDB to MySQL

Author: Qin Fulang

A member of the Aikesheng DBA team, responsible for the daily problem handling of the project and the troubleshooting of the company's platform. Love IT, like to swim on the Internet, good at photography and cooking, DBA who doesn’t know how to cook is not a good driver, didi~

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

1. Background

The background of MariaDB must be known to everyone. It is an open source database named after his daughter after MySQL was sold to SUN and acquired by Oracle after the founder of MySQL. At first, it was welcomed by everyone for its basic and complete compatibility with MySQL and free and open source. Even CentOS7 replaced MySQL with MariaDB. Therefore, there are many cases about the migration of MariaDB and MySQL. Some colleagues have written related articles before this official account. If you are interested, you can check it out. This article writes about the migration problems and precautions encountered in recent projects.

The environment is MariaDB 10.3 to MySQL 5.7.25.

Second, the simple operation process

Because MariaDB no longer follows the MySQL version number after 5.5, the version number starts with 10.0.0 after 5.5. The official query found that MariaDB 10.3 and MySQL 5.7 are theoretically compatible. The project operator put MariaDB as my. The cnf configuration file removed some of the parameters that did not exist in MySQL, and then installed MySQL with this configuration file, and then mysqldump the database table data of MariaDB and imported it into MySQL (this is the usual migration method).

Three, import data error

1、md5(uuid())

The following error messages are reported during the import process:

It can be seen from the figure that there is a syntax error during the import process. MySQL does not recognize md5(uuid()). Follow the prompts to view the table structure:

You can see that the primary key column instance_id has a default attribute, and the md5 function nests the uuid function. I haven't seen this usage in my impressions, so I took this as a direction to check the official document regulations, as follows:

• MariaDB：

It is clearly stated that after 10.2.1, default can be used with expressions or functions.

• MySQL：

You can see that in MySQL, the default value specified in the default clause must be a literal constant, not an expression or function. This explains why the import error is reported. MySQL does not support the function form of md5(uuid()) in default.

2、PAGE_CHECKSUM

It can still be seen from the figure that there is a syntax error during the import process, MySQL does not recognize "PAGE_CHECKSUM=1", follow the prompts to view the table structure:

You can see that the engine is Aria, and there is this parameter after the default of the engine. Check the official MariaDB documentation, as follows:

It can be seen that PAGE_CHECKSUM only applies to the Aria table. Compared with the MySQL document, it can be seen that the Aria engine is unique to MariaDB, and MySQL does not. Therefore, MySQL does not have the PAGE_CHECKSUM parameter attribute, so an error will be reported here.

This Aria engine is used by MariaD B to replace the Myisam engine. Even after version 10.4, the system tables are all this engine. Therefore, you must pay more attention to this problem in later versions.

Four, application test error

The following error is reported when the application test executes the SQL statement:

It can be seen from the error report that the more classic select field after the query does not appear in the group by example, but my.cnf uses MariaDB, there should be no change, and then check the MySQL sql_mode:

You can see that ONLY_FULL_GROUP_BY appears in it, which can explain why the error is reported, but this parameter is not found in MariaDB, and the sql_mode parameter does not exist in the my.cnf file. That's why, continue to check the official documentation at this time (Really good stuff):

• MySQL：

You can see that the default value of sql_mode of MySQL 5.7 includes the ONLY_FULL_GROUP_BY parameter.

• MariaDB：

In MariaDB, you can see that the default value of sql_mode after 10.2.4 does not include ONLY_FULL_GROUP_BY.

It can be explained that sql_mode is not configured in the my.cnf file, and the default value of sql_mode is used when the new MySQL is deployed, so sql_mode will contain ONLY_FULL_GROUP_BY at this time, and the business program SQL is designed without ONLY_FULL_GROUP_BY enabled, so An error will be generated at runtime.

After modifying sql_mode, the business is running normally.

V. Conclusion

For the migration of the two databases, instead of looking at the compatibility and incompatibility items mentioned in the online documents or official documents, the operation is as stable as a mountain. The migration still hopes to combine the official documents on the basis of comprehensive testing, and try to find all kinds of details. If there is any inconsistency, then do a formal migration. After all, the safe use of data is the most important.