Bug #102756 Error 1782 Replication broken while using ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS
Submitted: 28 Feb 8:37 Modified: 2 Mar 10:43
Reporter: Aakash Muthuramalingam Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:8.0.23 OS:Linux (CentOS 7)
Assigned to: CPU Architecture:x86

[28 Feb 8:37] Aakash Muthuramalingam
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.
[2 Mar 10:43] MySQL Verification Team
Hi Aakash,

I can only set this to "Feature Request" as this is not a bug. 

I will also suggest you contact our MySQL Support team to help you out with this migration

all best
Bogdan
[2 Mar 17:45] MySQL Verification Team
Hi,

Few additional pieces of information after discussing this with the team.

Thanks for handling this.

* we do not support replication from 5.6 to 8.0.

* It is possible to remove this particular problem using an intermediate 5.7 server but we do not recommend that since we do not support it, we have not tested it, nor we plan to test it since 5.6 is out of active support

* Implementing this Feature Request will probably also not happen as 5.6 is out of active support, so only security fixes get backported to it, active development / implementing feature request does not.

all best
Bogdan
[5 Mar 14:05] Sven Sandberg
Reported BUG#102828 to clarify the manual page for ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS regarding the supported server versions of the source.