GTID Replication (Skip Transaction using empty transaction)

GTID Replication (Skip Transaction using empty transaction)

GTID Replication (Skip Transaction using empty transaction)

To skip SQL thread's error in GTID replication setup, Insert empty transaction.

PROBLEM : 

db-test (none)> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxxxx
                  Master_User: rep_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: db-test-02-bin.006725
          Read_Master_Log_Pos: 4508785
               Relay_Log_File: db-test-relay-bin.000015
                Relay_Log_Pos: 2146333
        Relay_Master_Log_File: db-test-02-bin.006725
             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: 1146
                   Last_Error: Error 'Table 'mysql.gtid_executed' doesn't exist' on query. Default database: 'mysql'. Query: 'REPLACE INTO `xxxx`.`xxxxx` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'mysql', 'gtid_executed', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `source_uuid`, `interval_start`, `interval_end`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `mysql`.`gtid_executed` /*checksum table*/'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3010072
              Relay_Log_Space: 4510135
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /mysql/conf/keys/replication/ca-cert.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert: /mysql/conf/keys/replication/db-test-cert.pem
            Master_SSL_Cipher:
               Master_SSL_Key: /mysql/conf/keys/replication/db-test-key.pem
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1146
               Last_SQL_Error: Error 'Table 'mysql.gtid_executed' doesn't exist' on query. Default database: 'mysql'. Query: 'REPLACE INTO `xxxx`.`xxxxx` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'mysql', 'gtid_executed', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `source_uuid`, `interval_start`, `interval_end`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `mysql`.`gtid_executed` /*checksum table*/'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 562
                  Master_UUID: 11387751-34f3-11e4-a177-005056881ef0
             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: 190509 06:33:35
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 11387751-34f3-11e4-a177-005056881ef0:1244-88891
            Executed_Gtid_Set: 11387751-34f3-11e4-a177-005056881ef0:1233-86906,
cdad9e89-e19f-11e3-821e-f01fafd8ff5a:1-335866966
                Auto_Position: 0
1 row in set (0.00 sec)

SOLUTION:

------------------------------------------------------------------------------------------------------
STOP SLAVE;
SET GTID_NEXT="11387751-34f3-11e4-a177-005056881ef0:86907";
BEGIN; COMMIT;
SET GTID_NEXT="AUTOMATIC";
START SLAVE;
------------------------------------------------------------------------------------------------------

Photo by Jordan Donaldson | @jordi.d on Unsplash

Comments

Popular

SLEEP thread causing "Waiting for table metadata lock"

How to properly shutdown MySQL before any maintenance activity

How to Prepare for MySQL Certification Exam

errno: 24 - Too many open files

Quickly configure replication using DBdeployer [SandBox]

ERROR 1040 (HY000): Too many connections

How to create CentOS VM using Vagrant

MySQL 8.0 : Error Logging

Install MySQL on CentOS using DBdeployer