SKIP SLAVE COUNTER WITH GTID_MODE ON #ERROR 1858 (HY000)

ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON.

With generic asynchronous MySQL Replication we often use skip_slave_counter to tackle / skip anonymous transactions which creates errors or halt MySQL Replication. But if we try to skip slave counter whilst GTID_MOD is ON we get following error.


In this case we should do following steps

Stop Replication.
.Set GTID_MODE to ON_PERMISSIVE
Skip slave counter.
Start slave.
Set GTID_MODE to ON once it catches the master.
GTID_MODE is dynamic variable which can be set on the run. Here we need to look at GTID_MODE following options.

OFF: Both new and replicated transactions must be anonymous.
OFF_PERMISSIVE: New transactions are anonymous. Replicated transactions can be either anonymous or GTID transactions.
ON_PERMISSIVE: New transactions are GTID transactions. Replicated transactions can be either anonymous or GTID transactions.
ON: Both new and replicated transactions must be GTID transactions.
All these steps are shown below

mysql> stop slave;
mysql> set global gtid_mode=ON_PERMISSIVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
### Once caught up set it back
mysql> set global gtid_mode=ON;