Slave SQL error 1237 warnings

[monty solomon]

On the 5.6 slave there are many entries warning about Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules;, Error_code: 1237

2014-03-23 03:23:32 20142 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules;, Error_code: 1237
2014-03-23 03:23:32 20142 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules;, Error_code: 1237
2014-03-23 03:23:32 20142 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules;, Error_code: 1237
2014-03-23 03:23:32 20142 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules;, Error_code: 1237
2014-03-23 03:23:32 20142 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules;, Error_code: 1237
2014-03-23 03:23:32 20142 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules;, Error_code: 1237
2014-03-23 03:23:32 20142 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules;, Error_code: 1237

[mysqld]
replicate-wild-do-table = test%.%
replicate-wild-do-table = percona.%
replicate-wild-do-table = mysql.%
replicate-wild-do-table = heartbeat.%

Server version: 5.6.16-64.1-56-log Percona Server (GPL), Release 64.1, Revision 563
Linux 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
CentOS release 6.5 (Final)

These warnings do not appear in the 5.5 logs. Why do they appear in the 5.6 logs? How can they be disabled?

Thanks.

[Valerii Kravchuk (valerii-kravchuk)]

What is the version of master that these events are coming from? I also wonder what binlog-format is used there?

As for disabling these messages, try to set --slave-skip-errors=1237 on slave.

[monty solomon (monty+launchpad)]

The master is running 5.5.35-33.0-log Percona Server (GPL), Release rel33.0, Revision 611

[Valerii Kravchuk (valerii-kravchuk)]

What about binlog format used on master by default?

Do you have the same replicate-* options set for 5.5 slave? Entire my.cnf content from 5.5 and 5.6 slaves may be useful to compare.

[monty solomon (monty+launchpad)]

The master is using MIXED format.

The replicate-* options are the same

[monty solomon (monty+launchpad)]

Here is an excerpt from the MySQL 5.6.17 change history
http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-17.html

Replication: When a slave was configured with replication filters and --log-warnings=2, every statement which was filtered caused an entry to be written in the error log. For busy servers which generated many statements to be filtered, the result was that the error log could quickly grow to many gigabytes in size. Now a throttle is used for such errors, so that an error message is printed only once in a given interval, saying that this particular error occurred a specific number of times during that interval. (Bug #17986385)

[Muhammad Irfan (muhammad-irfan)]

Can you please provide my.cnf from master and slave.

[monty solomon (monty+launchpad)]

Rename attached files as specified below

master-my.cnf -> /etc/my.cnf
master-001-basic-config.cnf -> /etc/mysql/conf.d/001-basic-config.cnf

slave-my.cnf -> /etc/my.cnf
slave-001-basic-config.cnf -> /etc/mysql/conf.d/001-basic-config.cnf
slave-002-basic-config.cnf -> /etc/mysql/conf.d/002-basic-config.cnf

[monty solomon (monty+launchpad)]

Those are the files from the 5.6 master/slave

I upgraded the slave to server version 5.6.17-65.0-56-log Percona Server (GPL), Release 65.0, Revision 587

The error message displayed is now

2014-06-10 22:18:21 11152 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules; Error log throttle is enabled. This error will not be displayed for next 60 secs. It will be suppressed, Error_code: 1237

[Nilnandan Joshi (nilnandan-joshi)]

Able to reproduce with Master (5.5) and two slaves, slave1(5.6) and slave2 (5.5)
warnings are appears in error.log for 5.6 but not in 5.5

2014-06-17 13:23:33 11855 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules;, Error_code: 1237
2014-06-17 13:23:49 11855 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules;, Error_code: 1237

[Laurynas Biveinis (laurynas-biveinis)]

Monty -

Is 5.6.17 diagnostics throttling enough for your purposes?

Nil -

What 5.6 minor version have you used? Perhaps the warning rate is too low for the throttle to kick in?

[Nilnandan Joshi (nilnandan-joshi)]

Laurynas,

I have used 5.6.16 to generate those warnings.

It seems, the existence of warning is a problem to fix but having error log throttle is just a workaround like with high rate of these warnings, we can get just one warning message in every 60 seconds or something, instead of lots of messages. But if they are essential to 5.6, we need a solution to disable them entirely.

[Laurynas Biveinis (laurynas-biveinis)]

Nil -

 --slave-skip-errors=1237 as suggested by Valerii should disable the warning if throttling it is not enough.

[monty solomon (monty+launchpad)]

slave-skip-errors is used to prevent replication from failing for the specified error codes

[monty solomon (monty+launchpad)]

Changes in MySQL 5.6.17 (2014-03-27)
https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-17.html

Replication: When a slave was configured with replication filters and --log-warnings=2, every statement which was filtered caused an entry to be written in the error log. For busy servers which generated many statements to be filtered, the result was that the error log could quickly grow to many gigabytes in size. Now a throttle is used for such errors, so that an error message is printed only once in a given interval, saying that this particular error occurred a specific number of times during that interval. (Bug #17986385)

[monty solomon (monty+launchpad)]

It is much better with that throttling but it still generates noise in the log file.

[Laurynas Biveinis (laurynas-biveinis)]

A request to remove this noise altogether should be filed to Oracle first and then linked from here.

[Valerii Kravchuk (valerii-kravchuk)]

I've added upstream feature request

[monty solomon (monty+launchpad)]

Thanks for creating the upstream issue.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1488