Category Archives: Mysql

Fixing Broken GTID enabled replication ERROR 1858 (HY000):

Broken replication because of duplicate values MySQL 5.6 onward :

In non GTID enabled replication , we use to skip duplicate errors in case of Last_Errno: 1062 . But once we enable GTID we will have to deal it differently. The procedure is to follow the following simple steps

Let use assume two server  A as master B as slave.

  •  Check  slave status as shown below

mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: A
 Master_User: svc-mysql-repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000167
 Read_Master_Log_Pos: 72611417
 Relay_Log_File: relay-log.000364
 Relay_Log_Pos: 776
 Relay_Master_Log_File: mysql-bin.000161
 Slave_IO_Running: Yes
 Slave_SQL_Running: No
 Replicate_Do_DB:
 Replicate_Ignore_DB: admindb
 Replicate_Do_Table:
 Replicate_Ignore_Table: mysql.ibbackup_binlog_marker,mysql.backup_history,mysql.backup_progress,mysql.backup_sbt_history,mysql.inventory
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 1062
 Last_Error: Error 'Duplicate entry '21555' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'INSERT INTO auditlogs (text, category, datetime, username)
VALUES ('Starting application Web version 5.4', 'ACTION', '2017-07-26 09:49:41', '')'
 Skip_Counter: 0
 Exec_Master_Log_Pos: 611
 Relay_Log_Space: 5064799212
 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: 1062
 Last_Error: Error 'Duplicate entry '21555' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'INSERT INTO auditlogs (text, category, datetime, username)
VALUES ('Starting application Web version 5.4', 'ACTION', '2017-07-26 09:49:41', '')'
 Replicate_Ignore_Server_Ids:
 Master_Server_Id: 1
 Master_UUID: 4b7a0a70-146b-11e7-80f7-941882781532
 Master_Info_File: /opsrt12p/mysql/data/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: 170727 08:59:35
 Master_SSL_Crl:
 Master_SSL_Crlpath:
 Retrieved_Gtid_Set: 4b7a0a70-146b-11e7-80f7-941882781532:1-87044138
 Executed_Gtid_Set: 4b7a0a70-146b-11e7-80f7-941882781532:1-82186600
 Auto_Position: 0
 Replicate_Rewrite_DB:
 Channel_Name:
 Master_TLS_Version:
1 row in set (0.00 sec)

In above example we can see two Gtid values which are

Executed_Gtid_Set : represents the last executed statement , The set of global transaction IDs written in the binary log. This is the same as the value for the global gtid_executed system variable on this server.

In above example we have following last executed Gtid value.

Executed_Gtid_Set: 4b7a0a70-146b-11e7-80f7-941882781532:1-82186600

and we have following master binlog file and position

 Master_Log_File: mysql-bin.000167 

 Read_Master_Log_Pos: 72611417

 

  •  If we try to skip the counter we will get following .
mysql> stop slave;

Query OK, 0 rows affected (0.00 sec) 

mysql> set global sql_slave_skip_counter=1;

ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. 
Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

 

  • Now we should check the entry int the mentioned binary log  at specific position  on server A (master server) and we need to check how many more steps needs to be executed from the perticular Gtid. using following commands.
    • create an temporary txt file using mysqlbinlog utility as shown
bash-4.2$ /apps/mysql/opsrt11p/install/bin/mysqlbinlog --base64-output=decode-rows --verbose mysql-bin.000167> /tmp/temp-binlog.txt
  •  Now view the temp-binlog.txt using less or any other utility  and find out set of statement executed under 4b7a0a70-146b-11e7-80f7-941882781532:1-82186600 Gtid at step number 82186600 there may be few more steps to be executed therefore we need to skip step number 82186600 and move to next step of the statement under particular Gtid. 
  • Skip to next step of Gtid using following statement.
mysql> set GTID_NEXT="4b7a0a70-146b-11e7-80f7-941882781532:82186601;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
  • Now do show slave status again and you will see statement has been skipped and there are no duplicate errors any more.

 

Thanks for visiting , please dont forget to leave you feedback

Regards

Raja Naveed