FLUSH TABLE WITH READ LOCK, FLUSH FOR EXPORT cause TOI to break

[Philip Stoev]

When running a TOI DDL, where one of the nodes has the table locked, the DDL fails on that node with

2014-10-02 17:01:00 34030 [Warning] Slave SQL: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'test'. Query: 'ALTER TABLE t1 ADD COLUMN f2 INTEGER', Error_code: 1205

Instead, the DDL should block until the lock has been unlocked and proceed.

This is especially important in light of the FLUSH FOR EXPORT, which is supposed to be used to take backups of InnoDB tables. If a DDL arrives on a node while such a backup is being taken, that DDL will not be applied on that node.

Test case:

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

#
# Ensure that FLUSH TABLES ... FOR EXPORT is observed. No table changes are permitted
#

CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);

--connection node_2
FLUSH TABLES t1 WITH READ LOCK;

--connection node_1
ALTER TABLE t1 ADD COLUMN f2 INTEGER;

--connection node_2
UNLOCK TABLES;
# New column is not here
SHOW CREATE TABLE t1;

[Philip Stoev (philip-stoev-f)]

FLUSH TABLES FOR EXPORT is new in 5.6.6. FLUSH TABLE WITH READ LOCK has been there since forever.

[Philip Stoev (philip-stoev-f)]

Teemu says "applier threads should not time out for any lock waits". Maybe this can be converted into an assertion?

[Philip Stoev (philip-stoev-f)]

Teemu says "I have fixed this in innodb some time ago but it seems that it should be fixed in MDL wait too"

[Raghavendra D Prabhu (raghavendra-prabhu)]

I don't see this happening with FLUSH TABLE WITH READ LOCK since provider is paused there and after it is resumed after 'unlock tables', the DDL is applied.

Wouldn't it be prudent to do the same with 'FLUSH TABLES ... FOR EXPORT' as well?

[Miguel Angel Nieto (miguelangelnieto)]

In a three node cluster, I can also make two nodes to fail applying the ALTER with the following error:

Creating index 'PRIMARY' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

So, from three nodes two failed adding the column. ALTER TABLE is not cluster-wide transactional at all. And there could be possible more error to trigger the problem.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1745