The replication of MySQL is asynchronous. And InnoDB and relay log at replication slave are also asynchronous. The position of replication is updated after InnoDB commit of the transaction at slave. So, if the crash occurs before the update of the position, the committed transaction is executed again after the crash recovery as the replication.

With this patch, InnoDB records appropriate position of replication (information to write to relay-log.info) and shows it at crash recovery. We can check it manually.

InnoDB: Starting crash recovery.
....
InnoDB: Apply batch completed
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 468, file name gauntlet3-bin.000015
InnoDB: and relay log file
InnoDB: position 0 617, file name ./gauntlet3-relay-bin.000111

In addition, the new option is provided to overwrite relay-log.info automatically.

• innodb_overwrite_relay_log_info (default false) - if set true, InnoDB overwrite relay-log.info at crash recovery when the information is different from record in InnoDB.

<restrictions>

• When we use innodb_overwrite_relay_log_info = true, we must not update other storage engine during our workload. (e.g. Don't update MyISAM for the workload)
• We must not use relay/bin -log filename longer than 480 characters. (normal: up to 512). if longer, the information is not recorded to InnoDB.

<Whole example of .err file>

  ....
+ InnoDB: Warning: innodb_overwrite_relay_log_info is enabled. Updates of other storage engines may have problem of consistency.
+ InnoDB: relay-log.info is detected.
+ InnoDB: relay log: position 429, file name ./gauntlet3-relay-bin.000111
+ InnoDB: master log: position 280, file name gauntlet3-bin.000015
  ....
  InnoDB: Starting crash recovery.
  ....
  InnoDB: Apply batch completed
+ InnoDB: In a MySQL replication slave the last master binlog file
+ InnoDB: position 0 468, file name gauntlet3-bin.000015
+ InnoDB: and relay log file
+ InnoDB: position 0 617, file name ./gauntlet3-relay-bin.000111
  090205 17:41:31 InnoDB Plugin 1.0.2-3 started; log sequence number 57933
+ InnoDB: relay-log.info have been overwritten.
  ....
  090205 17:41:31 [Note] Slave SQL thread initialized, starting replication in log 'gauntlet3-bin.000015' at position 468, relay log './gauntlet3-relay-bin.000111' position: 617

In this case, the master log position was overwritten to 468 from 280.

<references>

Another solution for 5.0 http://code.google.com/p/google-mysql-tools/wiki/TransactionalReplication

Related bug (fixed and re-implemented in this patch) http://bugs.mysql.com/bug.php?id=34058