Bug #29288 myisam transactions replicated to a transactional slave leaves slave unstable
Submitted: 21 Jun 2007 21:23 Modified: 14 Oct 2010 12:23
Reporter: Jonathan Miller Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1.19, <=5.1.47 OS:Linux (32 bit)
Assigned to: Mats Kindahl CPU Architecture:Any

[21 Jun 2007 21:23] Jonathan Miller
Description:
Hi,

If you for some weired reason use transaction with myisam and it replicates to an InnoDB of a NDB slave, the slave becomes unstable. This is due to the commit not being recorded in the binlog, there for the transactions is never completed on the slave.

070622  0:13:16 [ERROR] Slave: Error 'Can't execute the given command because you have active locked tables or an active transaction' on query. Default database: 'tester'. Query: 'DROP DATABASE tester', Error_code: 1192
070622  0:13:16 [Warning] Slave: Can't execute the given command because you have active locked tables or an active transaction Error_code: 1192
070622  0:13:16 [Warning] Slave: Unknown error Error_code: 1105

So the problem is that the DROP fails because the commit is never replicated.

In the bin log posted below it shows 2 test that are basically the same.

Test 1) replicates from innodb to innodb and completes correctly.
Test 2) does the same but uses myisam to NDB (or could be innodb) and fails on the slave during the drop command.

master-bin.000001       106     Query   1       193     CREATE DATABASE tester
master-bin.000001       193     Query   1       306     use `tester`; create table t1 (a in
t key, b int) engine innodb
master-bin.000001       306     Query   1       419     use `tester`; create table t2 (a in
t key, b int) engine innodb
master-bin.000001       419     Query   1       489     use `tester`; BEGIN
master-bin.000001       489     Query   1       92      use `tester`; insert into t1 values
 (1,2)
master-bin.000001       581     Query   1       184     use `tester`; insert into t2 values
 (1,2)
master-bin.000001       673     Xid     1       700     COMMIT /* xid=23 */
master-bin.000001       700     Query   1       785     DROP DATABASE tester
master-bin.000001       785     Query   1       872     CREATE DATABASE tester
master-bin.000001       872     Query   1       984     use `tester`; create table t1 (a in
t key, b int)ENGINE=myisam
master-bin.000001       984     Query   1       1096    use `tester`; create table t2 (a in
t key, b int)ENGINE=myisam
master-bin.000001       1096    Query   1       1188    use `tester`; insert into t1 values
 (1,2)
master-bin.000001       1188    Query   1       1280    use `tester`; insert into t2 values
 (1,2)
master-bin.000001       1280    Query   1       1365    DROP DATABASE tester

How to repeat:
master-opt:
--innodb --default-storage-engine=innodb
slave-opt:
--innodb --default-storage-engine=ndb --ndbcluster

Test Case:
--source include/have_ndb.inc
--source include/have_innodb.inc
--source include/master-slave.inc
CREATE DATABASE tester;

USE tester;
create table t1 (a int key, b int) engine innodb;
create table t2 (a int key, b int) engine innodb;

SET AUTOCOMMIT=0;
START TRANSACTION;
insert into t1 values (1,2);
insert into t2 values (1,2);
eval SET @my_errno= $mysql_errno;
let $run_good= `SELECT @my_errno = 0`;
let $run_bad= `SELECT @my_errno <> 0`;
if ($run_good)
{
  COMMIT;
}
if ($run_bad)
{
  ROLLBACK;
}
SET AUTOCOMMIT=1;
DROP DATABASE tester;
SHOW BINLOG EVENTS;
--sync_slave_with_master

connection master;

CREATE DATABASE tester;

USE tester;
create table t1 (a int key, b int)ENGINE=myisam;
create table t2 (a int key, b int)ENGINE=myisam;

--sync_slave_with_master
USE tester;

ALTER TABLE t1 ENGINE NDB;
ALTER TABLE t2 ENGINE NDB;
connection master;
USE tester;

SET AUTOCOMMIT=0;
START TRANSACTION;
insert into t1 values (1,2);
insert into t2 values (1,2);
eval SET @my_errno= $mysql_errno;
let $run_good= `SELECT @my_errno = 0`;
let $run_bad= `SELECT @my_errno <> 0`;
if ($run_good)
{
  COMMIT;
}
if ($run_bad)
{
  ROLLBACK;
}
SET AUTOCOMMIT=1;
DROP DATABASE tester;
SHOW BINLOG EVENTS;
--sync_slave_with_master

Suggested fix:
Always replicate the commit and or rollback and ignore on slave if not needed.
[23 Oct 2007 14:30] Mats Kindahl
Replicating from a non-transactional table to a transactional table is a fundamental problem with expecting non-transactional tables to behave in any transactional manner at all.

Even by adding commits after each statement (which will increase the size of the binary log significantly, for a scenario that is very unlikely), there remains the problem of replicating a non-transactional table to a non-transactional table. A crash in the middle of a non-transactional write will leave the master and the slave in inconsistent states, whatever is done to handle the situation.

The solution is to use a transactional table on the master, if transactional behavior is expected.

Ensuring a reasonably safe replication from non-transactional table requires re-designing parts of the binary log structure, which is the goal of WL#4093
[14 Feb 2008 11:57] Sven Sandberg
The situation is as follows. In the ideal world, everything is inside a transaction (autocommit is just a funny syntax for that, it's the same as BEGIN before and END after each statement). However, in the non-transactional world, we have "punch transactions": even inside a BEGIN...COMMIT transaction there may be modifications to a non-transactional table. These take effect immediately, instead of at commit point. If there is a ROLLBACK, some statements have taken effect and some have not.

Normal transactions are logged in log.cc, MYSQL_BIN_LOG::write. Punch transactions are logged elsewhere. After fixing BUG#26395, MYSQL_BIN_LOG::write always writes BEGIN and END for all transactions (even autocommit ones). However, with autocommit=1 and a pure non-transactional statement, this code will not be reached, so no BEGIN will be written.

We should ultimately refactor this (see Mats comment above). But until then, we can force autocommit=1 on the slave. This mimics the behavior on master, independently of engines:
 - If slave sees BEGIN and END, everything is ok: it mimics exactly the master's behavior (unless master uses a transactional engine and slave uses a non-transactional engine, but of course that can never work).
 - If slave does not see BEGIN and END, it means that the statement is from a non-transactional engine. Since slave runs with autocommit=1, it will mimic the master's behavior in this case too.

(There is still one case that cannot be logged correctly: rolled back transactions involving *both* transactional and non-transactional updates. On master, the transactional updates will modify the database even if there is a rollback. If the slave has only transactional tables, it will not modify them if there is a rollback. For this case to work, we would need some syntax to explicitly say that a statement is a punch transaction.)

We need this to work also in upgrade scenarios. To this end, slave should not assume autocommit=1. Instead, master should always replicate over autocommit=1. The reason is:
 - Assume we replicate from old master to new slave. If slave assumed autocommit=1, it would commit too often, because the old master may not log BEGIN if it has autocommit=1.
 - Assume we replicate from new master to old slave. Then the slave will function correctly if it gets the autocommit flag replicated explicitly.
[28 Feb 2008 12:40] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43140

ChangeSet@1.2545, 2008-02-28 13:39:45+01:00, sven@riska.(none) +11 -0
  BUG#29288: myisam transactions replicated to a transactional slave leaves slave unstable
  Problem: when replicating from myisam to transactional engine with
  autocommit on, no BEGIN/COMMIT is written to the binlog. When the slave
  replicates, it will start a transaction that never ends.
  Fix: Force autocommit=1 on slave by always replicating autocommit=1 from
  the master.
  BUG#35541: mysqlbinlog prints 'set;' in stm mode after changing autocommit mode
  Problem: a typo in the code. When autocommit, foreign_key_checks,
  sql_auto_is_null, or unique_checks changes, it prints "SET", and then a
  comma-separated list of assignments. However, it does not print the
  assignment to the @@autocommit variable.
  Fix: print the @@autocommit variable.
[28 Feb 2008 12:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43141

ChangeSet@1.2545, 2008-02-28 13:44:11+01:00, sven@riska.(none) +12 -0
  BUG#29288: myisam transactions replicated to a transactional slave leaves slave unstable
  Problem: when replicating from myisam to transactional engine with
  autocommit on, no BEGIN/COMMIT is written to the binlog. When the slave
  replicates, it will start a transaction that never ends.
  Fix: Force autocommit=1 on slave by always replicating autocommit=1 from
  the master.
  BUG#35541: mysqlbinlog prints 'set;' in stm mode after changing autocommit mode
  Problem: a typo in the code. When autocommit, foreign_key_checks,
  sql_auto_is_null, or unique_checks changes, it prints "SET", and then a
  comma-separated list of assignments. However, it does not print the
  assignment to the @@autocommit variable.
  Fix: print the @@autocommit variable.
[28 Feb 2008 13:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43146

ChangeSet@1.2589, 2008-02-28 14:11:14+01:00, sven@riska.(none) +12 -0
  BUG#29288: myisam transactions replicated to a transactional slave leaves slave unstable
  Problem: when replicating from myisam to transactional engine with
  autocommit on, no BEGIN/COMMIT is written to the binlog. When the slave
  replicates, it will start a transaction that never ends.
  Fix: Force autocommit=1 on slave by always replicating autocommit=1 from
  the master.
  BUG#35541: mysqlbinlog prints 'set;' in stm mode after changing autocommit mode
  Problem: a typo in the code. When autocommit, foreign_key_checks,
  sql_auto_is_null, or unique_checks changes, it prints "SET", and then a
  comma-separated list of assignments. However, it does not print the
  assignment to the @@autocommit variable.
[11 Mar 2008 6:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43725

ChangeSet@1.2565, 2008-03-11 07:57:22+01:00, sven@riska.(none) +11 -0
  BUG#29288: myisam transactions replicated to a transactional slave leaves slave unstable
  Problem: when replicating from non-transactional to transactional engine
  with autocommit off, no BEGIN/COMMIT is written to the binlog. When the
  slave replicates, it will start a transaction that never ends.
  Fix: Force autocommit=1 on slave by always replicating autocommit=1 from
  the master.
  BUG#35541: mysqlbinlog prints 'set;' in stm mode after changing autocommit mode
  Problem: a typo in the code. When autocommit, foreign_key_checks,
  sql_auto_is_null, or unique_checks changes, it prints "SET", and then a
  comma-separated list of assignments. However, it does not print the
  assignment to the @@autocommit variable.
  Fix: print the @@autocommit variable.
[11 Mar 2008 7:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43730

ChangeSet@1.2565, 2008-03-11 08:35:38+01:00, sven@riska.(none) +11 -0
  BUG#29288: myisam transactions replicated to a transactional slave leaves slave unstable
  Problem: when replicating from non-transactional to transactional engine
  with autocommit off, no BEGIN/COMMIT is written to the binlog. When the
  slave replicates, it will start a transaction that never ends.
  Fix: Force autocommit=1 on slave by always replicating autocommit=1 from
  the master.
  BUG#34541: mysqlbinlog prints 'set;' in stm mode after changing autocommit mode
  Problem: a typo in the code. When autocommit, foreign_key_checks,
  sql_auto_is_null, or unique_checks changes, it prints "SET", and then a
  comma-separated list of assignments. However, it does not print the
  assignment to the @@autocommit variable.
  Fix: print the @@autocommit variable.
[7 Apr 2008 12:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44987

ChangeSet@1.2559, 2008-04-07 19:01:51+07:00, sven@riska.(none) +5 -0
  BUG#29288: myisam transactions replicated to a transactional slave leaves slave unstable
  Problem: when replicating from non-transactional to transactional engine
  with autocommit off, no BEGIN/COMMIT is written to the binlog. When the
  slave replicates, it will start a transaction that never ends.
  Fix: Force autocommit=on on slave by always replicating autocommit=1 from
  the master.
[9 Jan 2009 12:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/62811

2953 Mats Kindahl	2009-01-09
      BUG#29288: myisam transactions replicated to a transactional slave
      leaves slave unstable Problem: when replicating from non-transactional
      to transactional engine with autocommit off, no BEGIN/COMMIT is
      written to the binlog. When the slave replicates, it will start a
      transaction that never ends.  Fix: Force autocommit=on on slave by
      always replicating autocommit=1 from the master.
[20 Jan 2009 18:54] Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:mats@sun.com-20090109121247-z0tt8ezk8pn1o7ya) (merge vers: 6.0.10-alpha) (pib:6)
[20 Jan 2009 21:53] Jon Stephens
Documented as follows in the 6.0.10 changelog:

        MyISAM transactions replicated to a transactional slave left the
        slave in an unstable condition. This was due to the fact that,
        when replicating from a non-transactional storage engine to a
        transactional engine with AUTOCOMMIT turned off, no BEGIN and
        COMMIT statements were written to the binary log; thus, on the
        slave, a never-ending transaction was started.

        The fix for this issue includes enforcing AUTOCOMMIT mode on the
        slave by always replicating AUTOCOMMIT=1 from the master.

Change also noted in 'Replications and Transactions' in the 6.0 Manual.

Closed.
[23 Sep 2009 9:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/84277

3107 Mats Kindahl	2009-09-23
      BUG#29288: myisam transactions replicated to a transactional
      slave leaves slave unstable
      
      Problem: when replicating from non-transactional to
      transactional engine with autocommit off, no BEGIN/COMMIT
      is written to the binlog. When the slave replicates, it
      will start a transaction that never ends.
      
      Fix: Force autocommit=on on slave by always replicating
      autocommit=1 from the master.
[27 Oct 2009 9:48] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091027094604-9p7kplu1vd2cvcju) (version source revid:zhenxing.he@sun.com-20091026140226-uhnqejkyqx1aeilc) (merge vers: 6.0.14-alpha) (pib:13)
[27 Oct 2009 18:45] Jon Stephens
Already documented in the 6.0.10 changelog. Closed.
[12 Nov 2009 8:17] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:alik@sun.com-20091027095744-rf45u3x3q5d1f5y0) (merge vers: 5.5.0-beta) (pib:13)
[12 Nov 2009 12:21] Jon Stephens
Also documented fix in the 5.5.0 changelog; closed.
[28 May 2010 5:47] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (pib:16)
[28 May 2010 6:17] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100524190409-5w4l7mje1wk1c90l) (merge vers: 6.0.14-alpha) (pib:16)
[28 May 2010 6:44] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100523204118-0tl3goawu658rxh6) (merge vers: 5.5.5-m3) (pib:16)
[2 Jun 2010 8:50] Bugs System
Pushed into 5.1.48 (revid:georgi.kodinov@oracle.com-20100602084411-2yu607bslbmgufl3) (version source revid:luis.soares@sun.com-20100519234918-lwg5aqhatjuesp9l) (merge vers: 5.1.47) (pib:16)
[3 Jun 2010 7:46] Jon Stephens
Also documented fix in the 5.1.48 changelog, and in 'Replications and Transactions' section of the 5.1 Manual. Closed.
[14 Oct 2010 8:29] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 8:43] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 8:58] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 12:23] Jon Stephens
No new changelog entry required. Setting back to Closed state.
[21 Dec 2010 20:27] Sveta Smirnova
Fix for this bug does not work in 5.1. See bug #59097 for details.