I got 'Event too big' error at replication.
Hello.
I am facing an error that slave server cannot continue replication because of big log.
First, I saw that slave server stopped I/O thread. Then I checked error log.
It says the event is bigger than max_allowed_packet.
--- slave error log ---
110808 11:11:03 [Note] Slave I/O thread: connected to master 'user@db:3306', replication started in log 'mysql-bin.000233' at position 406351699
110808 11:11:03 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236)
110808 11:11:03 [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
110808 11:11:03 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000233', position 406351699
--- end ---
I have set the value. (in fact, the value is already set, so i double-checked)
mysql> set global max_allowed_packet=1025000000;
but its failed. Then I check binary logs on master server.
--- master binlog ---
shell> mysqlbinlog mysql-bin.000233 --position=406351699
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#110731 23:50:53 server id 84035 end_log_pos 106 Start: binlog v 4, server v 5.1.42-log created 110731 23:50:53
BINLOG '
zWs1Tg9DSAEAZgAAAGoAAAAAAAQANS4xLjQyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1634741366, event_type: 111
ERROR: Could not read entry at offset 406351699: Error in log format or read error.
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
--- end ---
I might found the event, but its length looks 1,634,741,366.
I cannot set max_allowed_packet over than 1024MB. The value is automaticaly return 1024MB when I set 2GB, 4GB or more.
I cannot check logs. So I cannot do anything to fix this problem.
I can skip this log on my management side. I want to skip or fix log. (the log broken?)
I tried to check logs a few position before.
--- Additional master binlog information --- (start position changed)
shell> mysqlbinlog mysql-bin.000233 --position=406001000
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#110731 23:50:53 server id 84035 end_log_pos 106 Start: binlog v 4, server v 5.1.42-log created 110731 23:50:53
BINLOG '
zWs1Tg9DSAEAZgAAAGoAAAAAAAQANS4xLjQyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 553656576, event_type: 115
ERROR: Could not read entry at offset 406001000: Error in log format or read error.
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
---- end ---
Do you give me any ideas?
1. Can I set max_allowed_packet over 1GB? or should I?
2. How can I see logs with this error?
3. Is the log broken? and how can I fix it?
Thanks.