Wednesday 13 November 2013

MySQL 5.6 GTID replication "doesn't exists on query" error fix


Master-slave environment encounter error 1858, it's happening when drop an object on master, but the object doesn't on slave , this is the error:
 Last_SQL_Error: Error 'Can't drop database 'dba1'; database doesn't exist' on query. Default database: 'dba1'. Query: 'drop database dba1'
firstly I try to skip this error:
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

Oops, GTID can't use sql_slave_skip_counter any more, try to generate a null transaction to skip the error, check the binlog file and get the last gtid
mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| log-bin.000020 |       196 |
| log-bin.000021 |      1119 |
+----------------+-----------+
2 rows in set (0.00 sec)
mysql> exit
Bye
[dbaroot@dba ~]$ su -
Password:
[root@dba ~]# mysqlbinlog /usr/local/mysql/data/log-bin.000021





Run next on slave MySql
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> set session gtid_next='6433e872-0fad-11e3-a916-00155d01c300:24';
Query OK, 0 rows affected (0.00 sec)
mysql> begin;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> set session gtid_next=AUTOMATIC;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
Show slave status\G again, that error transaction skip already, master-slave working fine.
-------------------------------the end-------------------------------------------

No comments:

Post a Comment