Siusic Dot Com

I mentioned in my previous post that MySQL server has replication feature. It is a very useful feature but from time to time I experience problems with it.

One problem I experienced with MySQL replication was that it stopped by

itself after replicating for a while. I could not re-start replication by running command “start slave” on the slave server. When I ran that command I found error message similar to the following in MySQL error log

[Note] Slave I/O thread: connected to master ‘repl@master:3306′, replication started in log ‘mysql-bin.000205′ at position 419477229
[ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236)
[ERROR] Got fatal error 1236: ‘log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master’ from master when reading data from binary log
080116 14:09:21 [Note] Slave I/O thread exiting, read up to log ‘mysql-bin.000205′, position 419477229

The error message basically tells you why it failed and how to fix it. It seems to tell that there may be a long query logged in the binary log of the master and it exceeds the max_allowed_packet configured for the master server. I couldn’t understand why the query was able to go into binary log at the first place. Since the global variable max_allowed_packet will prevent the execution of any query that is bigger than the size specified by max_allowed_packet. When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection. In this case the party that receives packet should be the slave.

Increase max_allowed_packet bytes is indeed able solved the problem for me so I didn’t go further to research what was the real cause of the problem. I did increased max_allowed_packet bytes on both the master and the slave server though.

After running the command “set global max_allowed_packet=10485760” on both the master and slave I can re-start replication on the slave server by running “start slave” again.

Related Posts:

SQL Server Backup Device Error or Device off-line

An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode

Recover Data Using Transaction Log

Master.mdf is Compressed But Does not Reside in a Read-only Database or Filegroup. The File Must be Decompressed.

Covering up an Annoying Bug of Sidebar Widgets