Sunday, May 1, 2011

InnoDB hidden auto-increment and Dual Masters

When an InnoDB table is created without a primary key a "hidden" auto inc key is created and used for indexes. Does anyone know if this key is multi-master safe?

If setting up auto incs in a database that was to be multi-master the standard mysql approach is to set auto_increment_increment and auto_increment_offset to appropriate values for your number of servers.

Does InnoDB's hidden key respect these settings, or does it even need to?

From stackoverflow
  • Since the hidden values are not shared over replication (regardless of STATEMENT or ROW binlog formats), they are safe. It doesn't matter if a slave holds the same internal value as the master. There is no relationship between an auto_incrementing field and InnoDB's internal key.

    The usual caveat regarding replication of a table with no primary key applies.


Post a Comment