Description:
Planned to migrate the database from MySQL 5.6(non-gtid, not possible for restart to enable GTID) to the MySQL latest version 8.0.23 using ASSIGN_GTID_TO_ANONYMOUS_TRANSACTIONS.
Test case 1: (Non GTID 5.6 to Non GTID 8.0 working fine)
Source 5.6.44:
mysql [localhost:5644] {root} ((none)) > create database migration;
Query OK, 1 row affected (0.00 sec)
mysql [localhost:5644] {root} ((none)) > use migration
Database changed
mysql [localhost:5644] {root} (migration) > create table t1(id int,name varchar(50));
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:5644] {root} (migration) > insert into t1(id,name) values(1,'Aakash');
Query OK, 1 row affected (0.00 sec)
mysql [localhost:5644] {root} (migration) > select * from migration.t1;
+------+--------+
| id | name |
+------+--------+
| 1 | Aakash |
+------+--------+
1 row in set (0.00 sec)
Replica 8.0.23:
mysql [localhost:8023] {root} ((none)) > select * from migration.t1;
+------+--------+
| id | name |
+------+--------+
| 1 | Aakash |
+------+--------+
1 row in set (0.00 sec)
Test case 2: (Non GTID 5.6 to GTID 8.0.23 using new feature become problematic)
Source 5.6.44:
mysql [localhost:5644] {root} ((none)) > create database migration;
Query OK, 1 row affected (0.00 sec)
mysql [localhost:5644] {root} ((none)) > show schemas;
+--------------------+
| Database |
+--------------------+
| information_schema |
| migration |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
Replica 8.0.23:
mysql [localhost:8023] {root} ((none)) > show schemas;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
Migration database is not replicated and replica status got with the below error
mysql [localhost:8023] {root} ((none)) > show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: repl
Master_Port: 5644
Connect_Retry: 60
Master_Log_File: mysql_sandbox5644-bin.000001
Read_Master_Log_Pos: 229
Relay_Log_File: mydbopslabs192-relay-bin.000002
Relay_Log_Pos: 331
Relay_Master_Log_File: mysql_sandbox5644-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1782
Last_Error: Error '@@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.' on query. Default database: 'migration'. Query: 'create database migration'
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 658
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1782
Last_SQL_Error: Error '@@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.' on query. Default database: 'migration'. Query: 'create database migration'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 453
Master_UUID: 00005644-0000-0000-0000-000000005644
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 210228 08:20:05
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
How to repeat:
create two mysql instance one with MySQL 5.6 and another with MySQL 8.0.23.
Test Case 1:
Configure replication without enabling GTID on replica.
change master to master_host='127.0.0.1',master_port=5644,master_user='repl',master_password='Repl@123',master_log_file='mysql_sandbox5644-bin.000001',master_log_pos=120;
Start doing writes on source, it will work as expected.
Test Case 2:
Configure replication with enabling GTID on replica.
change master to master_host='127.0.0.1',master_port=5644,master_user='repl',master_password='Repl@123',master_log_file='mysql_sandbox5644-bin.000001',master_log_pos=120,ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS=LOCAL;
Initial replica status will be running, but when we start to make writes on source, it will break the replication.
Suggested fix:
Having an intermediate relay server with the MySQL version 5.7 will fix this issue.
MySQL 5.6 (Non GTID Source) ---> MySQL 5.7 (Non GTID Intermediate Relay Replica) ---> MySQL 8.0 (GTID Replica, replication with anonymous transaction feature)
Knowing that replication between servers(5.6 Non GTID-> 8.0 GTID) by skipping a major version is not suggestable, but considering the production cases having 5.7 can be GTID enabled online itself(No need of this feature if the source is 5.7). So, this features needs to support from MySQL 5.6. Also, test case 1 succeeds(Replication working fine) without this feature. Only getting failed while using this new feature in 8.0.23.
Description: Planned to migrate the database from MySQL 5.6(non-gtid, not possible for restart to enable GTID) to the MySQL latest version 8.0.23 using ASSIGN_GTID_TO_ANONYMOUS_TRANSACTIONS. Test case 1: (Non GTID 5.6 to Non GTID 8.0 working fine) Source 5.6.44: mysql [localhost:5644] {root} ((none)) > create database migration; Query OK, 1 row affected (0.00 sec) mysql [localhost:5644] {root} ((none)) > use migration Database changed mysql [localhost:5644] {root} (migration) > create table t1(id int,name varchar(50)); Query OK, 0 rows affected (0.01 sec) mysql [localhost:5644] {root} (migration) > insert into t1(id,name) values(1,'Aakash'); Query OK, 1 row affected (0.00 sec) mysql [localhost:5644] {root} (migration) > select * from migration.t1; +------+--------+ | id | name | +------+--------+ | 1 | Aakash | +------+--------+ 1 row in set (0.00 sec) Replica 8.0.23: mysql [localhost:8023] {root} ((none)) > select * from migration.t1; +------+--------+ | id | name | +------+--------+ | 1 | Aakash | +------+--------+ 1 row in set (0.00 sec) Test case 2: (Non GTID 5.6 to GTID 8.0.23 using new feature become problematic) Source 5.6.44: mysql [localhost:5644] {root} ((none)) > create database migration; Query OK, 1 row affected (0.00 sec) mysql [localhost:5644] {root} ((none)) > show schemas; +--------------------+ | Database | +--------------------+ | information_schema | | migration | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) Replica 8.0.23: mysql [localhost:8023] {root} ((none)) > show schemas; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) Migration database is not replicated and replica status got with the below error mysql [localhost:8023] {root} ((none)) > show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: repl Master_Port: 5644 Connect_Retry: 60 Master_Log_File: mysql_sandbox5644-bin.000001 Read_Master_Log_Pos: 229 Relay_Log_File: mydbopslabs192-relay-bin.000002 Relay_Log_Pos: 331 Relay_Master_Log_File: mysql_sandbox5644-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1782 Last_Error: Error '@@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.' on query. Default database: 'migration'. Query: 'create database migration' Skip_Counter: 0 Exec_Master_Log_Pos: 120 Relay_Log_Space: 658 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1782 Last_SQL_Error: Error '@@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.' on query. Default database: 'migration'. Query: 'create database migration' Replicate_Ignore_Server_Ids: Master_Server_Id: 453 Master_UUID: 00005644-0000-0000-0000-000000005644 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 210228 08:20:05 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec) How to repeat: create two mysql instance one with MySQL 5.6 and another with MySQL 8.0.23. Test Case 1: Configure replication without enabling GTID on replica. change master to master_host='127.0.0.1',master_port=5644,master_user='repl',master_password='Repl@123',master_log_file='mysql_sandbox5644-bin.000001',master_log_pos=120; Start doing writes on source, it will work as expected. Test Case 2: Configure replication with enabling GTID on replica. change master to master_host='127.0.0.1',master_port=5644,master_user='repl',master_password='Repl@123',master_log_file='mysql_sandbox5644-bin.000001',master_log_pos=120,ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS=LOCAL; Initial replica status will be running, but when we start to make writes on source, it will break the replication. Suggested fix: Having an intermediate relay server with the MySQL version 5.7 will fix this issue. MySQL 5.6 (Non GTID Source) ---> MySQL 5.7 (Non GTID Intermediate Relay Replica) ---> MySQL 8.0 (GTID Replica, replication with anonymous transaction feature) Knowing that replication between servers(5.6 Non GTID-> 8.0 GTID) by skipping a major version is not suggestable, but considering the production cases having 5.7 can be GTID enabled online itself(No need of this feature if the source is 5.7). So, this features needs to support from MySQL 5.6. Also, test case 1 succeeds(Replication working fine) without this feature. Only getting failed while using this new feature in 8.0.23.