Over the past few years of dealing with mysql in larger environments, one thing I’ve always felt that ALTER TABLE statements are flaky with replication, but could never really prove it. I never had a chance to dig into some of strange things I saw, and would tend to overlook and fix them

While working for a client, I encountered an issue that I could no longer ignore. In order to reload a small table from a master database to a number of slaves, I simply piped the output of mysqldump into the master and expected everything to flow into the slaves.

The bug is very specific, and probably not very common. If you send a statement like below, as mysqldump automatically adds, to your master:

/*!40000 ALTER TABLE table_name DISABLE KEYS */;

and have configured something like

replicate-rewrite-db=mydb->mydb_slave

on your slave,  it will cause memory corruption on the slave.

You might see something like this in your mysql error logs:

*** glibc detected *** /opt/mysql-5.0.51a-linux-i686-glibc23/bin/mysqld-debug: double free or corruption (out): 0×0a2eb1f0 ***
======= Backtrace: =========
/lib/libc.so.6[0x166f5d]
/lib/libc.so.6(cfree+0×90)[0x16a5b0]
/opt/mysql-5.0.51a-linux-i686-glibc23/bin/mysqld-debug(my_no_flags_free+0×64)[0x8481d8c]
/opt/mysql-5.0.51a-linux-i686-glibc23/bin/mysqld-debug(free_root+0×122)[0x848279a]
/opt/mysql-5.0.51a-linux-i686-glibc23/bin/mysqld-debug(free_rows+0×1c)[0x82e62d7]
/opt/mysql-5.0.51a-linux-i686-glibc23/bin/mysqld-debug(unpack_fields+0×3fb)[0x82e734b]
/opt/mysql-5.0.51a-linux-i686-glibc23/bin/mysqld-debug[0x82e9a72]
/opt/mysql-5.0.51a-linux-i686-glibc23/bin/mysqld-debug(mysql_real_query+0xdf)[0x82e9c82]
/opt/mysql-5.0.51a-linux-i686-glibc23/bin/mysqld-debug[0x82d6f66]
/opt/mysql-5.0.51a-linux-i686-glibc23/bin/mysqld-debug(handle_slave_io+0×3c7)[0x82dd0cd]
/lib/libpthread.so.0[0xcf82db]
/lib/libc.so.6(clone+0×5e)[0x1ce12e]
This goes to show the risk involved in using ‘nifty’ but perhaps not-so-often used features in a complex product like a database. If we’d just kept the same database names we’d have been fine. C’est la vie.

I’m happy to report that my patch for a potentially nasty bug in the mysqlhotcopy script has been accepted into the mysql codebase. It’s a great feeling to finally contribute something, however small, back to the mysql community.

If you have a master/slave environment with multiple slaves, and you do periodic backups of your system on the slave with mysqlhotcopy, the –record_log_pos parameter will pull the wrong co-ordinates from the master and cause you to restore new copies of your database that are potentially corrupt.
An example failure scenario can be found here.