Blackhole tables and auto-increment keys

Blackhole tables are often used on a so-called “relay replica” where some operation needs to happen but no data needs to exist. This used to have a bug that prevented AUTO_INCREMENT columns from propagating the right values through replication, but that was fixed. It turns out there’s another bug, though, that has the same effect. This one is caused when there is an INSERT into a Blackhole table, where the source data is SELECT-ed from another Blackhole table.

I think it’s wise to keep it simple. MySQL has tons of cool little features that theoretically suit edge-case uses and make ninja tricks possible, but I really trust the core plain-Jane functionality so much more than these edge-case features. That’s precisely because they often have some edge-case bugs, especially with replication.

Something that’s new to MySQL recently is Galera replication. The more I think about it, the more I think it’s fundamentally the right way to replicate. Statement-based replication was brittle; row-based is less so, but still has all kinds of gotchas. The real problem with both is that they are built into the server, not the storage engine. Engine-level replication is the way to go. PBXT has had engine-level replication for a while, although I’ve never used PBXT in production (and kudos to PostgreSQL for adding built-in replication, too). I used to want InnoDB to do replication via streaming the redo logs and applying them, but that actually has a lot of limitations. Galera is InnoDB’s answer to engine-level replication. I think Galera holds a lot of promise for the future.

See Also

I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and lots of open-source software for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB. More about me.