Is MySQL Binary Data Replication Broken?
What’s the deal with replicating binary data in MySQL? Is anyone out there doing this successfully?
We ran into an evil problem last night with 4.1.22. Apparently, if you replicate binary data (in this case a gzip stream) it will break replication. The data inserts correctly on the master but fails to insert correctly on the slave.
Here’s the real kicker. It fails to replicate SILENTLY. Replication falls behind but Seconds_Behind_Master still reports zero seconds .
We can of course base64 encode the data but this will result in a 33% data bloat. Not exactly fun. We’re going to do this in the short term but this bug really needs to be fixed.
It really bothers me that it’s almost 2008 and MySQL has problems where it can’t do something as simple as store binary data.
1. This isn’t the only insanity I’ve found with Seconds_Behind_Master. If the master has an INSERT larger than max_allowed_packet the slave will get stuck in a retry loop writing errors to the log and reporting Seconds_Behind_Master of either NULL or zero the whole time.