Bug #69444 Replication is not crash safe with DDL statements
Submitted: 11 Jun 2013 21:25 Modified: 12 Jun 2013 12:14
Reporter: Santosh Praneeth Banda Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.6.12 OS:Any
Assigned to: CPU Architecture:Any
Tags: replication ddl statements

[11 Jun 2013 21:25] Santosh Praneeth Banda
The positions for DDL statements are updated after processing the event on a slave. If a crash happens after processing the event and before updating the position in info repositories, the ddl statement will be re-executed again causing the sql_thread to error.

  I know DDL statements are not transactional, but is there any way to fix this. One needs to manually skip the event, but this is a problem with multi threaded slave where it is difficult to see which worker thread failed.

How to repeat:
mtr test reproducing the error

-- source include/master-slave.inc
-- source include/have_innodb.inc

-- let $old_debug = `select @@global.debug;`
connection master;
create table t1 (a int) ENGINE=Innodb;
insert into t1 values(1);

-- eval SET GLOBAL debug = '+d,crash_before_update_pos_rli'
-- exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.2.expect

connection master;
drop table t1;

connection slave;
-- source include/wait_until_disconnected.inc
-- let $rpl_server_number = 2
-- source include/rpl_reconnect.inc

-- eval SET GLOBAL debug = `$old_debug`
-- source include/start_slave.inc

connection master;
-- source include/rpl_end.inc

This requires a debug_execute_if in sql/rpl_slave.cc

diff --git a/sql/rpl_slave.cc b/sql/rpl_slave.cc
index 99e6a25..3d7eaec 100644
--- a/sql/rpl_slave.cc
+++ b/sql/rpl_slave.cc
@@ -3601,6 +3601,7 @@ apply_event_and_update_pos(Log_event** ptr_ev, THD* thd, Relay_log_info* rli)
                                 ev->get_type_str(), explain[reason]));
+      DBUG_EXECUTE_IF("crash_before_update_pos_rli", DBUG_SUICIDE(););
       error= ev->update_pos(rli);

Suggested fix:
Don't know.
[12 Jun 2013 12:14] MySQL Verification Team
Hello Santosh,

Thank you for the report.
Verified as described.