Hi, I experienced a problem when slaves stopped replicating today.
A LOAD DATA INFILE was performed on the master DB. Seems the data exceeded the max_allowed_packet size which is 1M on master and slaves so all slaves stopped replicating. Question is, why did this happen and how could this have been avoided? Should max_allowed_packet also limit the LOAD DATA INFILE size so that it would stop and warn on the master instead of on the slaves...? Because the data loaded wasn't important I did not care much to find a better solution... I just wanted to skip the errors and let mysql get on with it... After I got one slave running I did the following on the rest of the slaves: SLAVE STOP; SET GLOBAL max_allowed_packet=10000000; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; SLAVE START; I could probably have changed position with CHANGE MASTER TO instead... (finding the proper "good" position by running mysqlbinlog on the master logs) *** Events description follows: 1. LOAD DATA INFILE statement replicated to slaves. The CSV file was larger than max_allowed_packet: Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master (server_errno=1236) 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 Slave I/O thread exiting, read up to log 'george-bin.3794', position 241147667 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306\', replication started in log 'george-bin.3794' at position 241147667 2. max_allowed_packet was increased on the MASTER with the following command: MASTER: SET GLOBAL max_allowed_packet=10475220 (10M) Now the complaint is because of the slave's max_allowed_packet instead: Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'george-bin.3794' at position 241147667 Error reading packet from server: Packet too large - increase max_allowed_packet on this server (server_errno=1153) Log entry on master is longer than max_allowed_packet (1047552) on slave. If the entry is correct, restart the server with a higher value of max_allowed_packet Slave I/O thread exiting, read up to log 'george-bin.3794', position 241147667 3. So I increase the max_allowed_packet on the slave as well: SLAVE: SET GLOBAL max_allowed_packet=10475220 This time when I do the SLAVE START it messes up some more: Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'george-bin.3794' at position 241147667 Error in Log_event::read_log_event(): 'Event too big', data_len: 1069482, event_type: 8 --- Why does it say Event too big? max_allowed_packet should now have been changed to 10M, data_len above == 1.1M. --- Or did the replication SQL thread fail to understand the new max_allowed_packet I set? Error reading relay log event: slave SQL thread aborted because of I/O error Slave: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the bina ry log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master\'s or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 0 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'george-bin.3794' position 241 4. So the next step SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; followed by another SLAVE START: Slave: Error in Exec_load event: could not open file '/tmp/SQL_LOAD-30020-10061-1.info', Error_code: 2 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'george-bin.3794' position 242 217149 5. another set global SQL_SLAVE_SKIP_COUNTER=1 and it was replicating again! Someone please clear things up for me a little! Thanks -- Mikael Fridh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
