MySQL Bugs: #81252: slave_skip_errors is not dynamic

Bug #81252	slave_skip_errors is not dynamic
Submitted:	30 Apr 2016 8:21	Modified:	1 May 2016 14:48
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.5/5.6/5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	slave_skip_errors

Description:
I recent breakage while using MysQL 5.6 to MysQL 5.7 (as part of an upgrade process) triggered a replication error due to a column name being used which is reserved in 5.7 but not in 5.6.

I've slightly munged the output but you get the idea:

2016-04-30T08:03:05.785263Z 1876586 [ERROR] Slave SQL for channel '': Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'generated=generated+11,col1=col1+7
WHERE some_date = '2016-04-30' at line 2' on query. Default database: 'db'. Query: 'UPDATE db_generator_log
SET updated=updated+4,col2=col2+0,generated=generated+11,col1=col1+7
WHERE some_date = '2016-04-30 00:00:10' AND some_id = '14' AND some_code = 'xxxx'', Error_code: 1064
2016-04-30T08:03:05.785319Z 1876586 [Warning] Slave: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'generated=generated+11,col1=col1+7
WHERE some_date = '2016-04-30' at line 2 Error_code: 1064

'generated' is now a reserved word in MySQL 5.7.

How to repeat:
The master runs 5.6.27, the slaves 5.7.11 or .12.

The breakage is expected. It wasn't noticed that I was using a column name which is invalid in 5.7.

I tried to apply a replication filter fix to skip these statements as there were a large number of them but that did not seem to work.

such as:

change replication filter replicate_wild_ignore_table= ('db.db_generator_log');
and did various combinations of this. Prefixed by stop slave, and then appying start slave.
This did not seem to work. I need to double check this later.

I also tried to set slave_exec_mode to IDEMPOTENT but that did not work either.

So I finally resorted to do the following which does work but requires mysqld to be restarted and is what the new dynamic replication filters were supposed to avoid:

$ sudo /etc/init.d/mysqld restart --slave-skip-errors=1064

Suggested fix:
Please ensure that slave-skip-errors can be set dynamically as it's still a static configuration setting. This would have allowed me to ignore the errors which will get dealt with later without restarting mysqld.

So just tried with 5.7.12 but this is the same:

root@host [(none)]> set global slave_skip_errors = '1064';
ERROR 1238 (HY000): Variable 'slave_skip_errors' is a read only variable
root@host [(none)]> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.12-log |
+------------+
1 row in set (0.00 sec)

also: http://bugs.mysql.com/bug.php?id=35611

Shane, indeed.
The problem with all of these options is you don't use them "much". So you tend to forget you need them until the next time you need them which may be some time later.

Kris' report in 2008 was a known issue then. I guess if you can afford to restart servers you do not care that much but for anyone running a system 24x7x265 having to restart a server to fix a problem such as this is a major nuisance. On one box it may be a pain but if it's on a large number of slaves at the same time then that's worse.  (Think of the server being unavailable and also that it will take some time to warm up again.)

I would look forward to seeing this addressed as several other replication issues have been changed in 5.7 to make dynamic configuration possible.

Thank you for the bug report.

Also affects MySQL 8.0.3