replicate_from command raise SQL thread error when GTID=ON

[kakuka4430]

Describe the bug

When I execute replicate_from command between MySQL 5.7.29 and 5.7.30 in case of "GTID=ON".
This behavior could happen in other versions.

To Reproduce

[vagrant@server1 ~]$ dbdeployer deploy single 5.7.30 --gtid -c log_slave_updates
Database installed in $HOME/sandboxes/msb_5_7_30
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
[vagrant@server1 ~]$ dbdeployer deploy single 5.7.29 --gtid -c log_slave_updates
Database installed in $HOME/sandboxes/msb_5_7_29
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
[vagrant@server1 ~]$ ls ~/sandboxes/
msb_5_7_29  msb_5_7_30

[vagrant@server1 ~]$ ~/sandboxes/msb_5_7_30/replicate_from msb_5_7_29
Connecting to /home/vagrant/sandboxes/msb_5_7_29
--------------
CHANGE MASTER TO master_host="127.0.0.1",
master_port=5729,
master_user="rsandbox",
master_password="rsandbox"
, master_auto_position=1
--------------

--------------
start slave
--------------

              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4089
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 154
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 00005730-0000-0000-0000-000000005730:1-16
                Auto_Position: 1

[vagrant@server1 ~]$ ~/sandboxes/msb_5_7_30/use -e "show slave status\G"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 5729
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4089
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 616
        Relay_Master_Log_File: mysql-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: 1396
                   Last_Error: Error 'Operation CREATE USER failed for 'msandbox'@'127.%'' on query. Default database: 'mysql'. Query: 'CREATE USER 'msandbox'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 403
              Relay_Log_Space: 4505
              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: 1396
               Last_SQL_Error: Error 'Operation CREATE USER failed for 'msandbox'@'127.%'' on query. Default database: 'mysql'. Query: 'CREATE USER 'msandbox'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747''
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 5729
                  Master_UUID: 00005729-0000-0000-0000-000000005729
             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: 200703 13:39:17
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 00005729-0000-0000-0000-000000005729:1-16
            Executed_Gtid_Set: 00005729-0000-0000-0000-000000005729:1,
00005730-0000-0000-0000-000000005730:1-16
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

Expected behavior

I guess this is because Replica (= 5.7.30 instance) tried to execute queries from the beginning of Source (= 5.7.29 instance) binary log.
However Replica have already done the same queries (ex. CREATE USER 'msandbox'@'127.%' ...), these SQL went ERROR.

In conclusion, "replicate_from" have to start GTID replication since the position after finish dbdeployer's setup process (ex. creating user).
So I think that command should issue "RESET MASTER" and "SET GLOBAL gtid_purged = '***' (= GTID of the last query of setup)" for skip all queries already done.

Environment:

• OS
  CentOS Linux release 7.8.2003 (Core)
• dbdeployer version
  1.52.0
• tarball full name
  mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
  mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
• tarball origin (from where it was downloaded)
  Oracle Official Website

[datacharmer]

Hi,
Thanks for this report.
I remember that I had fixed this particular problem, and I went to check my notes.

I did, indeed, fix it, but unfortunately I tested it only with 8.0.x

With 8.0 (both source and replica) the script works without modifications. For 5.6 and 5.7, it seems that I need to add a "reset master", which is not an ideal solution is someone wants to replicate from a server that has already some data.

For now, the workaround is to run "reset master" in the master before running replicate_from. No need to do it with MySQL 8.0.

I will look around to see whether there is a solution for 5.x.