Mysql – How to suppress specific warnings in thesql error log when ignoring a table to replicate

error logMySQLmysql-5.6

Is there a way to make the mysql error log less chatty with certain warnings? I just threw in the replicate-ignore-table option in my.cnf so our health check table doesn't get replicated. However now I'm getting a couple warnings every minute telling me something that I specifically want to happen… and it's annoying, not to mention that the log will grow at a much faster rate now.

I thought adding slave-skip-errors=1237 would fix it, but it's not an actual error that's stopping replication.

Any ideas?

2015-07-16 21:51:02 31210 [Warning] Error log throttle: 4 time(s) Error_code: 1237 "Slave SQL thread ignored the query because of replicate-*-table rules" got suppressed.
2015-07-16 21:51:02 31210 [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
2015-07-16 21:53:01 31210 [Warning] Error log throttle: 3 time(s) Error_code: 1237 "Slave SQL thread ignored the query because of replicate-*-table rules" got suppressed.
2015-07-16 21:53:01 31210 [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
2015-07-16 21:54:01 31210 [Warning] Error log throttle: 2 time(s) Error_code: 1237 "Slave SQL thread ignored the query because of replicate-*-table rules" got suppressed.
2015-07-16 21:54:01 31210 [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
2015-07-16 21:55:01 31210 [Warning] Error log throttle: 3 time(s) Error_code: 1237 "Slave SQL thread ignored the query because of replicate-*-table rules" got suppressed.
2015-07-16 21:55:01 31210 [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
2015-07-16 21:56:02 31210 [Warning] Error log throttle: 2 time(s) Error_code: 1237 "Slave SQL thread ignored the query because of replicate-*-table rules" got suppressed.
2015-07-16 21:56:02 31210 [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

Best Answer

You can:

Set global log_warnings=0;

CAVEAT

Make sure the network connection is clear (with no dropped packets) between Master and Slave.

Give it a Try !!!

MySQL Slow Log – Understanding ‘Throttle: Index Not Used’ Warning

Unfortunately, you are not going to get those queries from mysql.slow_log. Why ?

There are two options evidently being used

According to MySQL Documentation (The Slow Query Log, Paragraph 9)

To include queries that do not use indexes for row lookups in the statements written to the slow query log, enable the log_queries_not_using_indexes system variable. When such queries are logged, the slow query log may grow quickly. It is possible to put a rate limit on these queries by setting the log_throttle_queries_not_using_indexes system variable. By default, this variable is 0, which means there is no limit. Positive values impose a per-minute limit on logging of queries that do not use indexes. The first such query opens a 60-second window within which the server logs queries up to the given limit, then suppresses additional queries. If there are suppressed queries when the window ends, the server logs a summary that indicates how many there were and the aggregate time spent in them. The next 60-second window begins when the server logs the next query that does not use indexes.

What this shows is that your log_throttle_queries_not_using_indexes is set so that SQL text is suppressed. You can verify this with

mysql> SELECT @@global.log_throttle_queries_not_using_indexes;

Since it is a global dynamic variable, you must login as root@localhost and run

mysql> SET GLOBAL log_throttle_queries_not_using_indexes = 0;

This should expose new queries entering mysql.slow_log, but you will never see what those previous slow queries were.

You must run this

mysql> SELECT @@global.log_output;

If you see FILE,TABLE that means there is a text version of the slow query log. You will have to get your SysAdmin to get you the desired slow log info (Starbucks GiftCard might be required for this one. If the slow log file contains the same messages, take your Starbucks GitftCard back.)

Best Answer

Related Solutions

MySQL – How to Fix Replication Event Checksum Verification Failed Error Code 1743

CAVEAT

Give it a Try !!!

MySQL Slow Log – Understanding ‘Throttle: Index Not Used’ Warning

Related Question