Has behavior of SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; changed in Percona5.7 (when the MASTER is 5.7)?
- Or, is it not supported/possible to slave a Percona 5.6 server from a Percona 5.7 server due to binlog modifications/incompatibilities?
- I can upgrade the slave if that will fix this, but before I do, I wanted to understand what is happening here
We used to use GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; successfully to skip bad entries. I am confident it worked for binlog_format=MIXED(, although I have not re-tested, we’ve used this technique for years).
On a Percona5.6 slave of a Percona5.7 master, I recently tried to use this method, and here was the result:
2018-05-23 09:59:43 10396 [Note] ‘SQL_SLAVE_SKIP_COUNTER=1’ executed at relay_log_file=’./server-relay-bin.000020’, relay_log_pos=‘500859725’, master_log_name=‘master-bin.007892’, master_log_pos=‘500859548’ and new position at relay_log_file=’./server-relay-bin.000020’, relay_log_pos=‘500859725’, master_log_name=‘master-bin.007892’, master_log_pos=‘500859548’
The slave server recognized it should skip once, and then it didn’t advance either the relay log position or the master log position at all.
To get past the objectionable query, I had to: SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2;
After doing this, I recorded the former and new master_log_pos, and ran mysqlbinlog to see what was skipped. It appeared to be one statement.
I suppose I can just script around this, and first try SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;, thereafter if the bin log_pos does not change, I can try setting SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2;, but, this seems a little odd, especially given the prior method worked for years and seems well-documented online, and I haven’t seen anyone mention this feature.
Thanks for any help!
MASTER Server version: 5.7.21-20-log Percona Server (GPL), Release 20, Revision ed217b06ca3
SLAVE Server version: 5.6.25-73.0-log Percona Server (GPL), Release 73.0, Revision 5ccddf8