Invalid deadlock error if ALTER fails during wsrep_desync + wsrep_on

[Philip Stoev]

If a concurrent DML causes an ALTER executing under wsrep_desync + wsrep_on to fail, subsequent statements on that connection fail with "Deadlock found when trying to get lock; try restarting transaction" . I think this shows that locks were not released properly.

Test case:

--source include/galera_cluster.inc
--source include/have_innodb.inc

--connection node_1
CREATE TABLE ten (f1 INTEGER);
INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TABLE t1 (f1 INTEGER) Engine=InnoDB;

# Insert some values before the ALTER
INSERT INTO t1 (f1) SELECT 000000 + (10000 * a1.f1) + (1000 * a2.f1) + (100 * a3.f1) + (10 * a4.f1) + a5.f1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5;

# Insert more values while the ALTER is running
--send INSERT INTO t1 (f1) SELECT 100000 + (10000 * a1.f1) + (1000 * a2.f1) + (100 * a3.f1) + (10 * a4.f1) + a5.f1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5;

--connection node_2
SET GLOBAL wsrep_desync = TRUE;
SET SESSION wsrep_on = FALSE;
--error ER_QUERY_INTERRUPTED
ALTER TABLE t1 ADD PRIMARY KEY (f1);

SET GLOBAL wsrep_desync = FALSE;
SET SESSION wsrep_on = TRUE;

produces:

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
CREATE TABLE ten (f1 INTEGER);
INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE t1 (f1 INTEGER) Engine=InnoDB;
INSERT INTO t1 (f1) SELECT 000000 + (10000 * a1.f1) + (1000 * a2.f1) + (100 * a3.f1) + (10 * a4.f1) + a5.f1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5;
INSERT INTO t1 (f1) SELECT 100000 + (10000 * a1.f1) + (1000 * a2.f1) + (100 * a3.f1) + (10 * a4.f1) + a5.f1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5;;
SET GLOBAL wsrep_desync = TRUE;
SET SESSION wsrep_on = FALSE;
ALTER TABLE t1 ADD PRIMARY KEY (f1);
ERROR 70100: Query execution was interrupted
SET GLOBAL wsrep_desync = FALSE;
galera.desync_bug [ fail ]
        Test ended at 2014-09-30 02:41:35

CURRENT_TEST: galera.desync_bug
mysqltest: At line 23: query 'SET SESSION wsrep_on = TRUE' failed: 1213: Deadlock found when trying to get lock; try restarting transaction

[Philip Stoev (philip-stoev-f)]

Furthermore, the error from the ALTER is ER_QUERY_INTERRUPTED , which is not very informative.