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.