MySQL parameter "max_binlog_cache_size" too small causes SQL to fail

Today, when the development colleague failed to publish a SQL, I found that I reported the following error:

ERROR 1197 (HY000) at line 4:multi-statement transaction required more than ' max_binlog_cache_size ' bytes of storage; Increase this mysqld variable and try again

This means that multi-statement food requests a larger max_binlog_cache_size, which needs to be added after this parameter value is tried again

At this time, I was alerted that the Lord never synchronized, and the SQL thread hung up.

After logging into the system to view the master-slave status, and really with the colleague of this SQL is related

Queried the SQL of the colleague's operation:

First, copy the table by using the CREATE table Table_b like table_a, then use the INSERT INTO Table_b select * from Table_a

It's a total of four tables, and then only a successful table is executed, followed by a report of the error.

Note: A table created using the like method has the advantage of getting a table-structured index and storage engine that is identical to the source table.

The disadvantage is that you create an empty table, and you need to insert the data into the new table again

But how does this approach cause a copy outage from the library? And the other from the library is normal

The reason for this is this: the role of the copy interrupt from the library is the backup library, the Binlog is turned on and the Binlog format is row, the other from the library is not open Binlog

Mysql> Show variables like ' Binlog_format ';

+---------------+-------+

| variable_name | Value |

+---------------+-------+

| Binlog_format | ROW |

+---------------+-------+

The Binlog feature of the row format: In row mode, all executed statements are recorded in the log with the modification of each row, which can result in a large amount of log content. So it causes the Binlog cache to be interrupted because it is too small.

Knowing the cause of the error is good to solve the problem:

First increase the size of this parameter: Set global MAX_BINLOG_CACHE_SIZE=XXXXXXX (this will fail after rebooting the system)

Then start the replication process start slave;

View replication status Show Slave status\g

Master-slave replication is back to normal

The colleague's SQL re-execution did not occur with the above error.

This article is from "Shadow Knight" blog, please make sure to keep this source http://andylhz2009.blog.51cto.com/728703/1852007

MySQL parameter "max_binlog_cache_size" too small causes SQL to fail