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.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.