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 Correia CPU Architecture:Any
Tags: bfsm_2007_03_01
Triage: Triaged: D2 (Serious) / R5 (Severe) / E5 (Major)

[21 Feb 2007 20:02] Kolbe Kegel
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
[9 Jul 2007 9:31] Lars Thalmann
The suggested solution for this bug is in WL#3970.
[25 Feb 2008 13:44] Johan Idrén
Adding link to public worklog.
http://forge.mysql.com/worklog/task.php?id=3970
[10 Nov 2008 10:16] Lars Thalmann
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.
[6 Apr 2009 16:46] Mark Callaghan
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.
[24 Nov 2010 13:03] Alfranio Correia
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.