Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.0, 10.1, 10.0.33, 10.2
Description
This bug is a regression introduced with this commit:
commit 8bf6152ba08d02b7d5cf14495dc37fc7b8845957 (HEAD)
|
Author: Michael Widenius <monty@askmonty.org>
|
Date: Wed Feb 5 19:01:59 2014 +0200
|
|
Replication changes for CREATE OR REPLACE TABLE
|
The problem with this patch is that makes the slave SQL thread always use
the binlog cache, also for non-transactional tables. Consider a myisam
update that is binlogged like this:
BEGIN |
UPDATE myisam_table SET b=2 WHERE a=1 |
COMMIT |
In order to ensure correct binlog order, it is essential that the binlog is
written before table locks are relased. However, after the above commit, the
SQL thread will release table locks after the UPDATE, but not write to the
slave's binlog until COMMIT.
A simple testcase shows the problem. First, apply this patch to recent 10.0:
index b66ceac72bf..457e93dc1c1 100644
|
--- a/sql/log_event.cc
|
+++ b/sql/log_event.cc
|
@@ -4302,6 +4302,7 @@ int Query_log_event::do_apply_event(rpl_group_info *rgi,
|
*/
|
if (current_stmt_is_commit)
|
{
|
+my_sleep(4000000);
|
thd->variables.option_bits&= ~OPTION_GTID_BEGIN;
|
if (rgi->gtid_pending)
|
{ |
Then run this testcase:
--let $rpl_topology=1->2->3
|
--source include/rpl_init.inc
|
|
--connection server_1
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (1, 1); |
--save_master_pos
|
|
--connection server_2
|
--sync_with_master
|
--save_master_pos
|
|
--connection server_3
|
--sync_with_master
|
SELECT * FROM t1 ORDER BY a; |
|
--connection server_1
|
UPDATE t1 SET b=2 WHERE a=1; |
--save_master_pos
|
|
--connection server_2
|
SELECT SLEEP(2); |
UPDATE t1 SET b=3 WHERE a=1; |
SELECT * FROM t1 ORDER BY a; |
--sync_with_master
|
--save_master_pos
|
SELECT * FROM t1 ORDER BY a; |
|
--connection server_3
|
--sync_with_master
|
SELECT * FROM t1 ORDER BY a; |
|
--connection server_1
|
DROP TABLE t1; |
|
--source include/rpl_end.inc |
Before the patch 8bf6152b, the result is correct on all three servers. After
the patch (and in current 10.0), the last slave server_3 gets the wrong
value 2 for b in the table because of incorrect binlog order on server_2:
include/rpl_init.inc [topology=1->2->3]
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (1, 1);
|
SELECT * FROM t1 ORDER BY a;
|
a b
|
1 1
|
UPDATE t1 SET b=2 WHERE a=1;
|
SELECT SLEEP(2);
|
SLEEP(2)
|
0
|
UPDATE t1 SET b=3 WHERE a=1;
|
SELECT * FROM t1 ORDER BY a;
|
a b
|
1 3
|
SELECT * FROM t1 ORDER BY a;
|
a b
|
1 3
|
SELECT * FROM t1 ORDER BY a;
|
a b
|
1 2
|
DROP TABLE t1;
|
include/rpl_end.inc
|
This problem can also occur without direct changes on the slave if parallel
replication is enabled on server_2.