Help Center/ Data Replication Service/ FAQs/ Real-Time Synchronization/ What Are Syntax Differences Between MySQL or MariaDB Versions?
Updated on 2024-03-27 GMT+08:00
View PDF
Share

What Are Syntax Differences Between MySQL or MariaDB Versions?

If the source and destination database versions are different, syntax compatibility issues may occur due to feature differences between the source and destination database versions.

The source database binlogs received by DRS contain the original SQL statements, which will be executed in the destination database. If the destination database does not support the syntax constraints of the SQL statements in the source database, DRS will report an error and the task will fail.

For example, the syntax of the source database is incompatible with that of the destination database in the following scenarios (not all scenarios):

  • In the source database MySQL 5.7.34, the DDL create table t1 (id int, c1 varchar(16), FULLTEXT INDEX (c1 ASC)) ENGINE = INNODB is executed to create a table structure. The DDL stored in the source database Binlog is the preceding native SQL statement. During the incremental synchronization, an error is reported when the DDL is executed in the destination database MySQL 8.0.27, and the error information is 1221 - Incorrect usage of spatial/fulltext/hash index and explicit index order. The destination database MySQL 8.0.27 does not support collation rules on full-text indexes.
  • In the source database MariaDB 10.5.8, the DDL statement create table t1(id INT, c1 VARCHAR(32)) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_nopad_bin is executed to create a table structure. The DDL stored in the source database Binlog is the preceding native SQL statement. During the incremental synchronization, an error is reported when the DDL is executed in the destination database MySQL 8.0.27, and the error information is 1273 - Unknown collation: 'utf8mb4_nopad_bin'. The destination database MySQL 8.0.27 does not support the utf8mb4_nopad_bin collation character set.
  • In the source database MariaDB 10.5.8, the DDL statement create table t1(c1 INT, c2 VARCHAR(32) NOT NULL DEFAULT UUID()) ENGINE = INNODB is executed to create a table structure. The DDL stored in the source database Binlog is the preceding native SQL statement. During the incremental synchronization, an error is reported when the DDL is executed in the destination database MySQL 8.0.27, and the error information is 1064 - 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 UUID(). The destination database MySQL 8.0.27 does not support functions whose default value is UUID().
  • In the source database MariaDB 10.5.8, the DDL statement create table t1(c1 int, c2 inet6) ENGINE = INNODB is executed to create a table structure. The DDL stored in the source database Binlog is the preceding native SQL statement. During the incremental synchronization, an error is reported when the DDL is executed in the destination database MySQL 8.0.27, and the error information is 1064 - 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 inet6. The destination database MySQL 8.0.27 does not support data of the inet6 type.