A mysql 5.0 master compiled from source on Debian squeeze breaks mysql replication

One of our database backend systems consists of a number of mysql 5.0 machines that are set up in a single master-multiple slave configuration. Although we’re planning to migrate this cluster to a non-end-of-life version of mysql, it needs to be kept up and running for a bit longer.

A while ago we found out that running a mysql 5.0 master built from source on a Debian squeeze machine broke replication. Since that time (which was during a nightly scheduled down-time unrelated to this particular problem and which caused us some sweaty brows in the chilled datacenter) we’ve been running our master on the ‘obsolete’ Debian lenny, as well as a few slaves in case we need to do a master switch.

The problem is that what we always used to do to compile mysql, on Debian squeeze produced a mysql master that corrupts its binary logs. It’s important to note that the problem is indeed on the master, because this problem will usually show up as errors on the slave when they try to start replicating (i.e. when you tell them to ‘start slave;‘). It’s easily checked that the problem is indeed on the master, though, because if you try to dump the binlog (using ‘mysqlbinlog‘) you’ll see problems right away.

On a test machine set up to do binlogging with a broken mysql, after just adding a few rows to an empty table in a test database, I get this:

#> mysqlbinlog binary-log.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#120601 16:51:34 server id 50 end_log_pos 98 Start: binlog v 4, server v 5.0.96-log created 120601 16:51:34 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 106, event_type: 2
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

The important bit is this:

ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 106, event_type: 2

After some digging it seems that the compiler options we use to build mysql in combination with the default gcc that comes with Debian squeeze is to blame. We use basically the same options as the INSTALL-SOURCE file recommends:

CFLAGS="-O3" CXX=gcc \
CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" \
./configure --prefix=/usr/local/mysql --enable-assembler \
--with-mysqld-ldflags=-all-static

If we remove the optimizations (the -O3 parts) we get a functioning mysql master. We don’t really want to do that (mysql would most likely be slower without optimizations) so we decided to go the other route: use gcc-4.3, which is easily installed on Debian squeeze:

aptitude install gcc-4.3 g++-4.3 cpp-4.3

After that, the compile line becomes:

CC=gcc-4.3 CFLAGS="-O3" CXX=gcc-4.3 \
CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" \
./configure --prefix=/usr/local/mysql --enable-assembler \
--with-mysqld-ldflags=-all-static

The resulting mysqld binary produces correct binlogs and can be used as a replication master.

Maybe this problem could also be solved by going the other way: upgrade gcc to a newer version. That would involve more work on Debian squeeze (gcc-4.3 and gcc-4.4 are available by default from the repositories, other versions aren’t) so we haven’t investigated that option. For the time that we’ll still be running a mysql replication setup with mysql 5.0 we’ll be okay with gcc-4.3.