Friday, November 28, 2008

The meaning of a binlog event

There is a post about the use of SET commands that don't replicate to allow an InnoDB table to be created on a master and a MyISAM table to be created on slaves.

The problem is:
set storage_engine=innodb;
create table foo (i int);
If the default storage engine is MyISAM, the the master will create an InnoDB table and the slave will create a MyISAM table. That is fine. But what happens when you use the binlog and a backup to prepare a new master? The new master will create a MyISAM table and that can be a disaster if you don't catch it.

I think this feature is a bug. When a new master is prepared from a backup and binlog files, it will have the wrong table type because of this feature. The binlog must reflect the state of the master. Things done to make slaves differ must be done on the slave. And please, make it explicit when binlog events are not generated for a statement that changes persistent database state via set sql_log_bin=0 or a no_replicate clause on the SQL statement.

There are too many special cases today in replication that increase the TCO of a MySQL deployment.

2 comments:

  1. Yep. Did anybody file a bug on this yet, so we may all put in our vote rather than just blog comments?
    I'm kinda on the road right now, otherwise I'll bugreport it later.

    ReplyDelete
  2. @Arjen - the bug is listed in the blog post. Search for 'bug'.

    ReplyDelete

 
Creative Commons License
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.