List:General Discussion« Previous MessageNext Message »
From:Mikael Fridh Date:June 16 2004 9:10pm
Subject:Replication, max_allowed_packet and LOAD DATA INFILE
View as plain text  
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 'repl_bass@stripped: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 'repl_bass@stripped: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 'repl_bass@stripped: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

Thread
Replication, max_allowed_packet and LOAD DATA INFILEMikael Fridh16 Jun
Re: Replication, max_allowed_packet and LOAD DATA INFILEGuilhem Bichot29 Jun