[Background]
The master-err.log on the production database master shows the following:
130111 20:48:07 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master', Error_code: 1236
130111 20:48:07 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.001017', position 108609133
[Solution]
Increase the max_allowed_packet size as suggested. The current max_allowed_packet was set to 200M. Checking the master’s mysql-bin.001017 log, its size was 500M, and the parsed log came out to 1.1G.
We decided to adjust max_allowed_packet to 1G.
Execute the following on both the master and the slave:
stop slave;
set global max_allowed_packet=1*1024*1024*1024;
start slave;
Problem solved!
[Related Knowledge]
Let’s talk about max_allowed_packet here.
The MySQL service transmits data via network packets (a communication packet refers to a single SQL statement sent to the MySQL server or a single row sent to the client). The size of the data packet that the MySQL protocol can recognize is controlled by max_allowed_packet.
When a MySQL client or the mysqld server receives a packet larger than max_allowed_packet bytes, it issues a “log event entry exceeded max_allowed_packet;” error and closes the connection. This is exactly what happened with this master-slave replication issue: the I/O process fetched logs from the master, but the size of an individual SQL statement within the log exceeded the max_allowed_packet limit, resulting in an error. The I/O thread process stopped, while the SQL thread showed as yes. For clients, if the communication packet is too large, you might encounter a “Lost connection to MySQL server” error during query execution.