Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog New Feature Syntax and Compatibility Improvements in Community Edition of PolarDB for MySQL 8.0.x

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

This article describes the new feature syntax and compatibility improvements in the Community Edition of PolarDB for MySQL 8.0.x.

By Daoke

Compatibility With MySQL Versions

Compatibility with MySQL versions depends on /*! 12345 spaces for the format, and 12345 represents the format of the version number:

/* Accept 'M' 'm' 'm' 'd' 'd' */
1 digit (major), 2 digits (minor), then 2 digits (dot).
32302 -> 3.23.02
50032 -> 5.0.32
50114 -> 5.1.14
80018 -> 8.0.18

M represents a major version, mm represents a two-digit feature version change, and dd represents a two-digit bugfix version change.

For PolarDB for MySQL 8.0.1.xx.xx and 8.0.2.xx.xx, 123456789 is needed to represent the version number of the PolarDB. The format is as follows:

/* Accept MySQL-'M' MySQL-'m' MySQL-'m' 'M' 'M' 'm' 'm' 'd' 'd' */
1 digit (MySQL-major), 2 digits (MySQL-minor), 1 digit (major), 2 digits (minor),then 2 digits (dot).
800010101 -> 8.0.1.1.1
800020200 -> 8.0.2.2.0

First, to be compatible with the MySQL version, we need to introduce a special MySQL version number, 99990, as the PREFIX version of POLARDB. After checking the particularity of this version, we will continue to check whether the PolarDB version is compatible with itself. Therefore, the comments are relatively long. The format is as follows:

/*!50100 Special new MySQL Syntax SQL supported >= version 5.1.0 */
Evolve into:
/*!99990 800020200 Special new PolarDB Syntax SQL supported >= version 2.2.0 */

Compatibility Implementation

Parse the Comments of the PolarDB Version

Customers can add the comments of PolarDB syntax compatibility.

CREATE TABLE `t1` (c1 int)
/*!99990 900020200 UNIQUE CHECK IGNORE=1 */
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`c1`) */;
SHOW CREATE TABLE t1;
DROP TABLE `t1`;

Show create table/mysqldump Tool Supports

To Automatically add show create Syntax

CREATE TABLE `t1` (c1 int) ENGINE=InnoDB UNIQUE CHECK IGNORE=1
PARTITION BY HASH (`c1`) PARTITIONS 4;

--- result 
SHOW CREATE TABLE t1;
Table Create Table
t1  CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) /*!99990 800020200 UNIQUE CHECK IGNORE=1 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`c1`)
PARTITIONS 4 */

Support sqldump Tool to Automatically Add create table

CREATE TABLE `t1` (c1 int) UNIQUE CHECK IGNORE=1 ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci PARTITION BY HASH (`c1`);
--result
mysql_dump --compact test t1

include/mysqlbinlog.inc
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
ROLLBACK/*!*/;
# [empty]
# original_commit_timestamp= MICROSECONDS-FROM-EPOCH (YYYY-MM-DD HOURS:MINUTES:SECONDS TZ)
# immediate_commit_timestamp= MICROSECONDS-FROM-EPOCH (YYYY-MM-DD HOURS:MINUTES:SECONDS TZ)
/*!80001 SET @@session.original_commit_timestamp= MICROSECONDS-FROM-EPOCH*//*!*/;
/*!80014 SET @@session.original_server_version= ORIGINAL_SERVER_VERSION*//*!*/;
/*!80014 SET @@session.immediate_server_version= IMMEDIATE_SERVER_VERSION*//*!*/;
SET @@SESSION.GTID_NEXT= '#'/*!*/;
use `test`/*!*/;
SET TIMESTAMP=#/*!*/;
SET @@session.pseudo_thread_id=#/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) /*!99990 800020200 UNIQUE CHECK IGNORE=1 */ ENGINE=InnoDB
/*!50100 PARTITION BY HASH (`c1`)
PARTITIONS 4 */
/*!*/;
......

Limitation

Since only store_create_info is officially implemented, statements other than create table are not supported to be automatically added to binary logs. You need to implement the corresponding logic according to the new function.

If users have compatibility requirements, they need to modify the SQL statements in the application by adding /!99990 800 020200 SQL /, which is unlimited and can be directly written into binary logs to support various versions.

Test Result

PolarDB Synchronizes MySQL Test

The lab creates a primary/standby build from PolarDB 8.0.2.2.0 to MySQL 8.0.27 to simulate binary log synchronization.

1) PolarDB 8.0.2.2.0

Configuration file

polar_log_bin=on
server-id=1
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
log-bin=mysql-bin
expire_logs_days = 10
max_binlog_size = 100M
Create the rep user.
create user 'rep'@'ip' identified by 'xxxxxxx';
grant replication slave on . to 'rep'@'ip';
ALTER USER 'rep'@'xxxx' IDENTIFIED WITH mysql_native_password BY 'xxxxxxx';
Export the current data schema and information.
mysqldump -uroot -S /tmp/mysql.sock -A -B -F --master-data=2 >/tmp/full.sql
Show master statusG
1. row **
File: mysql-bin.000021
Position: 155
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

2) MySQL 8.0.27

Configuration file

log-bin=mysql-bin
server_id=2
Set the master information.
CHANGE MASTER TO
MASTER_HOST='ip',
MASTER_USER='rep',
MASTER_PASSWORD='xxxxxxx',
MASTER_PORT=33006,
MASTER_LOG_FILE='mysql-bin.000021',
MASTER_LOG_POS=155;
Enable binary log synchronization.
Start SLAVE;

PolarDB 8.0.2.2.0

1

MySQL 8.0.27

2

MySQL Developers POLARDB Compatibility Syntax PolarDB for MySQL
1 1 0
Share on

Read previous post:

Code Explanation of MySQL 8.0.23 Hypergraph Join Optimizer

Read next post:

Best Practices for Migrating MariaDB 10.2 to PolarDB for MySQL 5.7

Morningking

7 posts | 0 followers

You may also like

Comments

5746396189141014 October 2, 2023 at 11:06 am

If you are looking for a good Digital Marketing company in India then you can visit the website where you can find a number of fascinating companies which help you find better SEO services. To know more, please visit the website: Read More:- https://webthynk.com/

Morningking

7 posts | 0 followers

Related Products

  • PolarDB for MySQL

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

    Learn More

  • 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

  • AnalyticDB for MySQL

    AnalyticDB for MySQL is a real-time data warehousing service that can process petabytes of data with high concurrency and low latency.

    Learn More

More Posts by Morningking

See All