MySQL Bugs: #26540: relay-log.info out of sync after crash

Bug #26540	relay-log.info out of sync after crash
Submitted:	21 Feb 2007 20:02	Modified:	24 Nov 2010 14:55
Reporter:	Kolbe Kegel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Any
Assigned to:	Alfranio Tavares Correia Junior	CPU Architecture:	Any
Tags:	bfsm_2007_03_01

Description:
If the Slave crashes between the time the server executes and commits a transactional statement and the time the server would have updated and synced the relay-log.info file, the Exec_Master_Log_Pos value in relay-log.info could be inconsistent with the transactions actually applied to any transactional engines.

How to repeat:
Difficult...

Suggested fix:
Either...

1. Use XA for relay-log.info
2. Store relay-log info in a transactional table

The suggested solution for this bug is in WL#3970.

Adding link to public worklog.
http://forge.mysql.com/worklog/task.php?id=3970

Please check BUG#31665.

Details:

- For MySQL 6.0, this is already fixed for most failure scenarious by
  the implementation of a relay log synchronization feature
  (specified in BUG#31665).

- For MySQL 6.0, with the synchronization feature enabled, there are
  today some performance problems.  These problems are reported as
  BUG#40337 and this will be fixed for 6.0.

- The feature introduced in MySQL 6.0 (BUG#31665) has already proven
  useful for some users and it makes replication withstands some hard
  tests.  The complete solution with complete transactional relay log
  synchronization will probably be implemented in 6.1 to cover the few
  remaining situations where the relay log can be incorrect.

Lars,
You are more of an optimist than I am with your use of 'most' and 'few'. Prior to the sync_binlog changes the sequence is:
1) commit to innodb
2) write updated relay-log.info

with the change the sequence is:
1) commit to innodb
2) write updated relay-log.info
3) sync relay-log.info

Then a crash before step 3) still leaves the storage engine and relay-log.info out of sync.

Pushed WL#2775 into mysql-trunk (5.6.1-m5) and fixed the problem
reported in BUG#26540.

WL#2775 introduces two system tables mysql.slave_master_info and mysql.slave_relay_log_info to replace, respectively, master.info
and relay-log.info files. 

If we enable mysql.slave_relay_log_info and update only transactional
tables such as Innodb tables, the slave will not go out of sync after
a crash. Note that this feature does not work with DDL statements even
when Innodb is in use, because DDL statements are not transactional. 

If you want to have a crash-safe slave run it with the following
additional options:

  . --relay-log-info-repository=TABLE  -> to enable
    mysql.slave_relay_log_info

  . --relay-log-recovery=1 -> to sync the relay log info with the
  master info after a crash and discard possible corrupted relay
  log files.

Finally, we must execute
 
ALTER TABLE mysql.slave_relay_log_info ENGINE= Innodb;

By default, all system tables are created using the MyIsam engine. In particular, when installing a database one can choose another engine
for mysql.slave_relay_log_info or can do so after running the slave
by executing an alter table. 

See further details at http://forge.mysql.com/worklog/task.php?id=2775.