| 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: | |
| 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 |
[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.
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.