Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Best Practices for Migrating MariaDB 10.2 to PolarDB for MySQL 5.7

Best Practices for Migrating MariaDB 10.2 to PolarDB for MySQL 5.7

This article describes the common problems encountered during the migration process from MariaDB 10.2 to PolarDB for MySQL 5.7

By Daoke

Upgrade Overview

Why Did You Choose to Upgrade to PolarDB for MySQL 5.7?

PolarDB for MySQL 5.7 is a next-generation distributed relational cloud-native database developed by Alibaba Cloud. It is 100% compatible with MySQL and is based on MySQL 5.7.28. It incorporates separation between storage and computation, shared storage (up to 100 TB), and physical replication mechanisms. It also offers significant architecture enhancements and kernel capabilities, providing more flexible technical solutions and achieving powerful performance improvements. The main benefits of PolarDB for MySQL 5.7 include:

  • Physical replication: PolarDB implements physical replication based on Redolog instead of logical replication based on binary log for the data flow between the primary database and read-only instances. This eliminates the need for XA transactions with binary logs, resulting in shorter transaction execution paths and reduced I/O overhead. In the secondary database, physical replication can be executed in multiple threads, ensuring data correctness. The overhead is much lower compared to traditional multi-replica replication.
  • Database storage engine that integrates software and hardware: PolarDB utilizes advanced hardware technologies, such as Optane memory cards using 3DXpoint storage media, NVMe SSDs, upgraded 100G RoCE RDMA network, and Alibaba's advanced Aliflash V5 SMART-SSD technology. It optimizes hardware and software as a whole for new hardware architectures, optimizing the I/O chain across all layers of the software stack. It is the first storage engine among cloud vendors to be based on these advanced hardware integrations.
  • Serverless: Serverless databases allow database cluster resources to dynamically scale up and down based on customer business loads, eliminating the need for complex resource evaluation and operations and maintenance.
  • DDL enhancement and optimization: PolarDB supports parallel DDL, quick addition of fields, DDL read-ahead, multi-way merging and sorting, and DDL asynchronous I/O. These enhancements improve the performance, stability, and ease of use of DDL operations.
  • Transaction system enhancement: PolarTrans is a new transaction system that utilizes commit timestamp (CTS) technology to optimize high-concurrency online transaction scenarios. It improves the read and write performance of databases and integrates with RDMA (Remote Direct Memory Access) technology to launch the SCC (Super Computing Cluster) function, ensuring strong consistency within high-performance clusters. SCC guarantees strict read-after-write consistency on the primary node and all read-only nodes in a cluster. By offloading strictly consistent read query workloads to read-only nodes, SCC reduces the load on the primary node and significantly improves the throughput of clusters in OLTP scenarios.
  • Global database: A global database network (GDN) consists of multiple PolarDB for MySQL clusters distributed in multiple regions within a country. Data is synchronized across all clusters in a GDN. Each cluster in the GDN provides read services (write services are forwarded to the primary cluster for processing) and geo-disaster recovery capability, meeting application scenarios such as active geo-redundancy and geo-disaster recovery.
  • High-concurrency optimization: This includes Concurrency Control (CCL) rules based on SQL statements, Inventory Hint for quick transaction commits and rollbacks, Statement Queue mechanism to minimize conflict overhead, hot row performance optimization, and Thread Pool optimization.

Additionally, data can be migrated to PolarDB for MySQL 8.0 with a single click to leverage more enterprise-level capabilities. For more information, please refer to the Release Logs.

Pre-inspection

During the migration process of MariaDB 10.2 to PolarDB for MySQL 5.7, the main problems often encountered are related to performance, syntax compatibility, and support for peripheral components. Performance problems with queries are generally caused by changes in the execution plan resulting from the optimizer upgrade. These problems require targeted performance optimization for statements with low performance, but they do not cause business errors or code rewriting problems. Therefore, this article will not discuss such issues.

This article focuses on real compatibility problems, which require corresponding code updates or changes to environment configuration during database upgrades. The main causes of compatibility problems are syntax changes, feature updates, and removals after version upgrades. A pre-inspection provides a brief list to help users better understand the problems that need to be noticed during the upgrade process. If you encounter the following problems, you can refer to the Version Upgrade Details section for operation and inspection.

  • Ensure that the storage engine is not using a storage engine that is separately supported by MariaDB.
  • Ensure that dynamic column functions, JSON, regular expression functions, and Window functions are not used in the application program.
  • Ensure that table column types do not contain types such as JSON and INET.
  • Ensure that the keywords do not conflict with the reserved keywords. For more information, see the official documentation Keywords and Reserved Words.
  • Ensure that the BLOB and TEXT types in the table definition do not have default values of definitions and expressions, and the Sequences and System-versioned attributes are not used.
  • Ensure that the charsets are supported by MySQL 5.7. For more information, see the official documentation Charsets and Collations.

Version Migration Details

Storage Engines

In addition to the standard InnoDB, MyISAM, BLACKHOLE, CSV, MEMORY, ARCHIVE, and MERGE, the MariaDB 10.2 supports MyRocks, Aria, TokuDB, CONNECT, SEQUENCE, SphinxSE, Spider, FederatedX, and OQGRAPH. Therefore, you need to convert the engine not supported by MySQL to another standard engine supported by InnoDB or PolarDB for MySQL 5.7 before migration.

SELECT DISTINCT ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

Find the corresponding and specific engine. For example, if there is connect or federatedx, execute the engine.

SELECT COUNT(*) as '# TABLES', 
  CONCAT(ROUND(sum(data_length) / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
  CONCAT(ROUND(sum(index_length) / ( 1024 * 1024 * 1024 ), 2), 'G') INDEXES,
  CONCAT(sum(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2)), 'G') 'TOTAL SIZE', ENGINE 
FROM information_schema.TABLES
WHERE TABLE_SCHEMA 
  NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') 
  GROUP BY engine;
+----------+-------+---------+------------+--------+
| # TABLES | DATA  | INDEXES | TOTAL SIZE | ENGINE |
+----------+-------+---------+------------+--------+
|        1 | 0.02G | 0.01G   | 0.00G      | Aria   |
|        5 | 3.00G | 2.00G   | 0.00G      | InnoDB |
|        1 | 1.00G | 1.20G   | 0.00G      | MyISAM |
+----------+-------+---------+------------+--------+
3 rows in set (0.002 sec)

Change the engine not supported to the InnoDB engine or another standard engine:

<mysql> ALTER TABLE part ENGINE = INNODB;
Query OK, 0 rows affected (0.09 sec)

Functions

Some functions in MariaDB are not supported by the PolarDB for MySQL 5.7. You need to check whether such functions are contained in your application. If such a function exists, make a rewrite solution, such as JSON_DETAILED. You can only use JSON_PRETTY instead of JSON_DETAILED when you upgrade to PolarDB for MySQL 8.0.

Category Functions Description
Dynamic columns COLUMN_ADDCOLUMN_CHECKCOLUMN_CREATECOLUMN_DELETECOLUMN_EXISTSCOLUMN_GETCOLUMN_JSONCOLUMN_LIST
JSON JSON_COMPACTJSON_DETAILEDJSON_EXISTSJSON_LOOSEJSON_MERGE_PATCHJSON_MERGE_PRESERVEJSON_QUERYJSON_VALUE For more information, see JSON Function Reference.
Regular Expressions REGEXP_INSTRREGEXP_REPLACEREGEXP_SUBSTR For more information, see Regular Expression Functions and Operators.
Window Functions CUME_DISTDENSE_RANKLAGLAST_VALUELEADNTH_VALUENTILEPERCENT_RANKRANKROW_NUMBER Only MySQL 8.0 has correspondent functions. For more information, see Window Functions.
Category Functions Description
Dynamic columns COLUMN_ADDCOLUMN_CHECKCOLUMN_CREATECOLUMN_DELETECOLUMN_EXISTSCOLUMN_GETCOLUMN_JSONCOLUMN_LIST
JSON JSON_COMPACTJSON_DETAILEDJSON_EXISTSJSON_LOOSEJSON_MERGE_PATCHJSON_MERGE_PRESERVEJSON_QUERYJSON_VALUE For more information, see JSON Function Reference.
Regular Expressions REGEXP_INSTRREGEXP_REPLACEREGEXP_SUBSTR For more information, see Regular Expression Functions and Operators.
Window Functions CUME_DISTDENSE_RANKLAGLAST_VALUELEADNTH_VALUENTILEPERCENT_RANKRANKROW_NUMBER Only MySQL 8.0 has correspondent functions. For more information, see Window Functions.
Category Functions Description
Dynamic columns COLUMN_ADDCOLUMN_CHECKCOLUMN_CREATECOLUMN_DELETECOLUMN_EXISTSCOLUMN_GETCOLUMN_JSONCOLUMN_LIST
JSON JSON_COMPACTJSON_DETAILEDJSON_EXISTSJSON_LOOSEJSON_MERGE_PATCHJSON_MERGE_PRESERVEJSON_QUERYJSON_VALUE For more information, see JSON Function Reference.
Regular Expressions REGEXP_INSTRREGEXP_REPLACEREGEXP_SUBSTR For more information, see Regular Expression Functions and Operators.
Window Functions CUME_DISTDENSE_RANKLAGLAST_VALUELEADNTH_VALUENTILEPERCENT_RANKRANKROW_NUMBER Only MySQL 8.0 has correspondent functions. For more information, see Window Functions.

JSON Function Official Document

Category Functions Description
Dynamic columns COLUMN_ADDCOLUMN_CHECKCOLUMN_CREATECOLUMN_DELETECOLUMN_EXISTSCOLUMN_GETCOLUMN_JSONCOLUMN_LIST
JSON JSON_COMPACTJSON_DETAILEDJSON_EXISTSJSON_LOOSEJSON_MERGE_PATCHJSON_MERGE_PRESERVEJSON_QUERYJSON_VALUE For more information, see JSON Function Reference.
Regular Expressions REGEXP_INSTRREGEXP_REPLACEREGEXP_SUBSTR For more information, see Regular Expression Functions and Operators.
Window Functions CUME_DISTDENSE_RANKLAGLAST_VALUELEADNTH_VALUENTILEPERCENT_RANKRANKROW_NUMBER Only MySQL 8.0 has correspondent functions. For more information, see Window Functions.
Category Functions Description
Dynamic columns COLUMN_ADDCOLUMN_CHECKCOLUMN_CREATECOLUMN_DELETECOLUMN_EXISTSCOLUMN_GETCOLUMN_JSONCOLUMN_LIST
JSON JSON_COMPACTJSON_DETAILEDJSON_EXISTSJSON_LOOSEJSON_MERGE_PATCHJSON_MERGE_PRESERVEJSON_QUERYJSON_VALUE For more information, see JSON Function Reference.
Regular Expressions REGEXP_INSTRREGEXP_REPLACEREGEXP_SUBSTR For more information, see Regular Expression Functions and Operators.
Window Functions CUME_DISTDENSE_RANKLAGLAST_VALUELEADNTH_VALUENTILEPERCENT_RANKRANKROW_NUMBER Only MySQL 8.0 has correspondent functions. For more information, see Window Functions.

Regular Expression Functions and Operators

Category Functions Description
Dynamic columns COLUMN_ADDCOLUMN_CHECKCOLUMN_CREATECOLUMN_DELETECOLUMN_EXISTSCOLUMN_GETCOLUMN_JSONCOLUMN_LIST
JSON JSON_COMPACTJSON_DETAILEDJSON_EXISTSJSON_LOOSEJSON_MERGE_PATCHJSON_MERGE_PRESERVEJSON_QUERYJSON_VALUE For more information, see JSON Function Reference.
Regular Expressions REGEXP_INSTRREGEXP_REPLACEREGEXP_SUBSTR For more information, see Regular Expression Functions and Operators.
Window Functions CUME_DISTDENSE_RANKLAGLAST_VALUELEADNTH_VALUENTILEPERCENT_RANKRANKROW_NUMBER Only MySQL 8.0 has correspondent functions. For more information, see Window Functions.
Category Functions Description
Dynamic columns COLUMN_ADDCOLUMN_CHECKCOLUMN_CREATECOLUMN_DELETECOLUMN_EXISTSCOLUMN_GETCOLUMN_JSONCOLUMN_LIST
JSON JSON_COMPACTJSON_DETAILEDJSON_EXISTSJSON_LOOSEJSON_MERGE_PATCHJSON_MERGE_PRESERVEJSON_QUERYJSON_VALUE For more information, see JSON Function Reference.
Regular Expressions REGEXP_INSTRREGEXP_REPLACEREGEXP_SUBSTR For more information, see Regular Expression Functions and Operators.
Window Functions CUME_DISTDENSE_RANKLAGLAST_VALUELEADNTH_VALUENTILEPERCENT_RANKRANKROW_NUMBER Only MySQL 8.0 has correspondent functions. For more information, see Window Functions.

MySQL official WINDOW function

Category Functions Description
Dynamic columns COLUMN_ADDCOLUMN_CHECKCOLUMN_CREATECOLUMN_DELETECOLUMN_EXISTSCOLUMN_GETCOLUMN_JSONCOLUMN_LIST
JSON JSON_COMPACTJSON_DETAILEDJSON_EXISTSJSON_LOOSEJSON_MERGE_PATCHJSON_MERGE_PRESERVEJSON_QUERYJSON_VALUE For more information, see JSON Function Reference.
Regular Expressions REGEXP_INSTRREGEXP_REPLACEREGEXP_SUBSTR For more information, see Regular Expression Functions and Operators.
Window Functions CUME_DISTDENSE_RANKLAGLAST_VALUELEADNTH_VALUENTILEPERCENT_RANKRANKROW_NUMBER Only MySQL 8.0 has correspondent functions. For more information, see Window Functions.

For more information, see the official description of MariaDB: Function Differences Between MariaDB 10.2 and MySQL 5.7

System Variables

MariaDB 10.2 and PolarDB MySQL 5.7 also have many differences in system variables. You can use SHOW VARIABLES to compare the differences between the two systems. For more information, see MariaDB official documentation System Variable Differences Between MariaDB 10.2 and MySQL 5.7

SHOW VARIABLES;

NOTES: The unit of max_statement_time is milliseconds, which is different from that of MariaDB, whose unit is seconds.

Encryption Compatibility

The encryption method of MariaDB 10.2 implemented in InnoDB is different from that of PolarDB for MySQL 5.7. The encryption tables must be processed separately. For more information, see InnoDB encryption.

Keyword Compatibility

You must view the MySQL Keyword List to ensure that reserved keywords do not conflict with the keywords in the list.

Type Compatibility

Some special types of MariaDB, such as JSON, INET, and MySQL, are incompatible. You can use the following statements to view the types:

SELECT  DATA_TYPE , count(*) TOT  
  FROM information_schema.COLUMNS  
 WHERE TABLE_SCHEMA NOT 
   IN ('mysql', 'sys', 'information_schema', 'performance_schema') 
GROUP BY 1;
+-----------+-----+
| DATA_TYPE | TOT |
+-----------+-----+
| bigint    |  14 |
| datetime  |   1 |
| inet6     |   1 |
| int       |  10 |
| longtext  |   3 |
| tinyint   |   2 |
+-----------+-----+
6 rows in set (0.001 sec)

INET6 type

MariaDB INET6 is not supported in MySQL. Therefore, we recommend that you use the VARBINARY(16) type to store IPv6 values.

Table Types Incompatibility

Sequences attribute

PolarDB for MySQL does not support Sequences. Therefore, you must use the following SQL statements to query:

SELECT COUNT(*), TABLE_TYPE FROM information_schema.TABLES GROUP BY table_type;
+----------+------------------+
| COUNT(*) | TABLE_TYPE       |
+----------+------------------+
|      117 | BASE TABLE       |
|        2 | SEQUENCE         |
|        1 | SYSTEM VERSIONED |
|       79 | SYSTEM VIEW      |
|      101 | VIEW             |
+----------+------------------+
5 rows in set (0.0250 sec)
  
WITH seqlist (a) AS (
  SELECT CONCAT('%`',TABLE_SCHEMA,'`.`', TABLE_NAME,'`%') a
    FROM information_schema.TABLES 
   WHERE table_type="SEQUENCE")  
SELECT TABLE_NAME, COLUMN_NAME 
  FROM information_schema.COLUMNS
  JOIN seqlist WHERE COLUMN_DEFAULT LIKE seqlist.a;
+------------+-------------+
| TABLE_NAME | COLUMN_NAME |
+------------+-------------+
| t1         | a           |
+------------+-------------+
2 rows in set (0.023 sec)

Delete Sequences or replace it with auto_increment.

CREATE TABLE IF NOT EXISTS `t1` (
  `a` int(11) NOT NULL DEFAULT nextval(`mydatabase`.`s1`),
  ......
->
CREATE TABLE IF NOT EXISTS `t1` (
  `a` int(11) NOT NULL auto_increment,

System-versioned attribute

PolarDB for MySQL does not support the System-versioned table type either. You must use the following SQL statements to check whether this type is available:

SELECT TABLE_SCHEMA, TABLE_NAME 
  FROM information_schema.TABLES 
 WHERE TABLE_TYPE='system versioned';
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| test         | t          |
+--------------+------------+
1 row in set (0.0090 sec)

Delete the attribute

ALTER TABLE test.t DROP SYSTEM VERSIONING;
Query OK, 0 rows affected (0.0232 sec)

Table DEFAULT value

The SHOW CREATE TABLE function of MariaDB 10.2 does not have numeric types quoted in quotation marks.

In MariaDB

CREATE TABLE td (link TINYINT DEFAULT 1);

SHOW CREATE TABLE td\G
*************************** 1. row ***************************
       Table: td
Create Table: CREATE TABLE `td` (
  `link` tinyint(4) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=latin1

In PolarDB for MySQL

mysql> show create table td\G
*************************** 1. row ***************************
       Table: td
Create Table: CREATE TABLE `td` (
  `link` tinyint(4) DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MariaDB 10.2 also supports BLOB and TEXT DEFAULT values, but MySQL does not.

MariaDB 10.2 supports the expression in the DEFAULT statement. You can use table INFORMATION_SCHEMA.COLUMNS to check whether the table is compatible. However, PolarDB for MySQL 5.7 does not support this expression. Only PolarDB for MySQL 8.0 supports this expression. For more information, see MySQL official documentation.

SELECT TABLE_NAME, COLUMN_NAME
  FROM information_schema.COLUMNS
 WHERE COLUMN_DEFAULT LIKE '%add_months%';
Empty set (0.055 sec)

Syntax Compatibility

CTE(Recursive Common Table Expressions)

MariaDB 10.2 supports the CTE syntax, but PolarDB for MySQL 5.7 does not. You must upgrade PolarDB for MySQL 5.7 to PolarDB for MySQL 8.0. For more information, see MySQL official documentation.

Set operations

MariaDB supports the INTERSECT and EXCEPT set operations. However, PolarDB for MySQL 5.7 does not support these operations. You must upgrade PolarDB for MySQL 5.7 to PolarDB for MySQL 8.0. For more information, see MySQL official documentation.

TRUNCATE TABLE

The TRUNCATE TABLE statement of PolarDB for MySQL does not support the CASCADE attribute.

LIST DEFAULT partition type

MariaDB supports the DEFAULT partition type, but PolarDB for MySQL 5.7 does not. You must upgrade PolarDB for MySQL 5.7 to PolarDB for MySQL 8.0. For more information, see PolarDB for MySQL official documentation.

PARTITION BY LIST (partitioning_expression)
(
    PARTITION partition_name VALUES IN (value_list),
    [ PARTITION partition_name VALUES IN (value_list), ... ]
        [ PARTITION partition_name DEFAULT ]
)

RETURNING syntax

MariaDB supports the RETURNING syntax. PolarDB for MySQL 5.7 is implemented by using functions. For more information, see PolarDB for MySQL official documentation.

Case sensitivity

PolarDB MySQL can convert all table names to lowercase format, and queries can also support all lowercase formats, while MariaDB depends on the case requirements of the operating system. The lower_case_table_names can be set as needed.

Character Set

Not all character sets and collations are supported in MySQL. MariaDB supports 40 character sets and 322 collations, while MySQL supports 41 character sets (gb18030 ) and 222 collations. You can use the following SQL statements to query whether the character set is supported.

SHOW CHARACTER SET like 'utf16';
+---------+----------------+-------------------+--------+
| Charset | Description    | Default collation | Maxlen |
+---------+----------------+-------------------+--------+
| utf16   | UTF-16 Unicode | utf16_general_ci  |      4 |
+---------+----------------+-------------------+--------+
1 row in set (0.00 sec)

For more information, see MariaDB official documentation Character Sets and Collations and MySQL 5.7 official documentation Charset and Collation.

References

Database Developers Migration POLARDB MariaDB PolarDB for MySQL Database Migration， Best Practices
0 1 0
Share on

Read previous post:

New Feature Syntax and Compatibility Improvements in Community Edition of PolarDB for MySQL 8.0.x

Read next post:

Best Practices for Upgrading PolarDB for MySQL 5.6/MySQL 5.6 to PolarDB for MySQL 8.0

Morningking

7 posts | 0 followers

You may also like

Comments

Morningking

7 posts | 0 followers

Related Products

  • PolarDB for PostgreSQL

    Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.

    Learn More

  • PolarDB for Xscale

    Alibaba Cloud PolarDB for Xscale (PolarDB-X) is a cloud-native high-performance distributed database service independently developed by Alibaba Cloud.

    Learn More

  • PolarDB for MySQL

    Alibaba Cloud PolarDB for MySQL is a cloud-native relational database service 100% compatible with MySQL.

    Learn More

  • Oracle Database Migration Solution

    Migrate your legacy Oracle databases to Alibaba Cloud to save on long-term costs and take advantage of improved scalability, reliability, robust security, high performance, and cloud-native features.

    Learn More

More Posts by Morningking

See All