Binary Log File Corruption - Happens each day...
Note: This page is obsolete. The information is old, outdated, or otherwise currently incorrect. We are keeping the page for historical reasons only. Do not rely on the information in this article.
We are getting the following error in our MariadB 5.2.4 slaves: - it happens about once per day.
Relay log read failure: 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 binary 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.
Doing a RESET SLAVE and CHANGE MASTER to and using the next log file gets around the problem - but that means we are missing blocks of transactions in the slaves.
Doing a RESET SLAVE and CHANGE MASTER to the original place in the master's binary log does not get around the problem.
Doing a : mysqlbinlog -Hvv mysql-bin.000681 --start-position=48954702 |more
Yields the following:
/*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #110114 6:06:57 server id 1 end_log_pos 106 # Position Timestamp Type Master ID Size Master Pos Flags # 4 51 2e 30 4d 0f 01 00 00 00 66 00 00 00 6a 00 00 00 00 00 # 17 04 00 35 2e 32 2e 34 2d 4d 61 72 69 61 44 42 2d |..5.2.4.MariaDB.| # 27 6d 61 72 69 61 64 62 39 34 2d 6c 6f 67 00 00 00 |mariadb94.log...| # 37 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| # 47 00 00 00 00 00 00 00 00 13 38 0d 00 08 00 12 00 |.........8......| # 57 04 04 04 04 12 00 00 53 00 04 1a 08 00 00 00 08 |.......S........| # 67 08 08 02 |...| # Start: binlog v 4, server v 5.2.4-MariaDB-mariadb94-log created 110114 6:06:57 BINLOG ' US4wTQ8BAAAAZgAAAGoAAAAAAAQANS4yLjQtTWFyaWFEQi1tYXJpYWRiOTQtbG9nAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC '/*!*/; # at 48954702
The my.cnf files have not changed between when we were running the standard CENTOS 5.5 version of MySQL and MariaDB 5.2.4
I dont know how to overcome the problem. Do you recommend shifting back to the 5.1 series? Or is there an easier correction?
The master servers my.cnf file is below:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # # Replication Settings # max_allowed_packet=128M log-bin=mysql-bin server-id=1 innodb_flush_log_at_trx_commit=1 sync_binlog=1 binlog-do-db=deepercatalog user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 ## ARIA Tuning Settings # ## MyISAM Tuning Settings # key_buffer_size=4096M table_cache=1400 # # INNODB Tuning Parameters # innodb_file_per_table innodb_buffer_pool_size=32768M innodb_log_file_size=64M innodb_log_buffer_size=8M innodb_checksums=0 innodb_thread_concurrency=24 innodb_flush_log_at_trx_commit=1 sync_binlog=1 # # General Tuning Parameters # read_buffer_size=2M join_buffer_size=8M query_cache_size=8192M query_cache_type=1 query_prealloc_size=6536 query_alloc_block_size=131072 max_connections=2048 ft_min_word_len=1 ft_stopword_file='' myisam_sort_buffer_size=512M sort_buffer_size=2M tmp_table_size=512M max_heap_table_size=512M read_rnd_buffer_size=524288 bulk_insert_buffer_size=8M set-variable=long_query_time=5 log-slow-queries=/var/log/mysql/log-slow-queries.log [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
Answer
Ok,
i just had a thought, and it turned out to be correct.
It seems we needed to increase the master and the slave max_allowed_packet from 32M to 256M
While it was never a problem in our old configuration with MySQL 5.0 series, it seems for some reason that with MariaDB we need a larger packet size.
We apologize for not thinking about this before asking the question.