Bug #53798 OPTIMIZE TABLE breaks repeatable read

The problem was that OPTMIZE TABLE was allowed to run on a table
in use by a transaction in a different connection. This caused
repeatable read to break.

This bug was fixed by the introduction of metadata locking, WL#4284.
OPTIMIZE TABLE will now be blocked until the transaction using the
table, has ended.

This patch contains a regression test added to innodb_mysql_lock.test
and no code changes.

1 files changed, 39 insertions, 0 deletions

diff --git a/mysql-test/t/innodb_mysql_lock.test b/mysql-test/t/innodb_mysql_lock.test
index 6469ef2d229..36d09b4c411 100644
--- a/mysql-test/t/innodb_mysql_lock.test
+++ b/mysql-test/t/innodb_mysql_lock.test
@@ -170,6 +170,45 @@ connection default;
 disconnect con2;
 DROP TABLE t1;
 
+
+--echo #
+--echo # Bug#53798 OPTIMIZE TABLE breaks repeatable read
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (a INT) engine=innodb;
+INSERT INTO t1 VALUES (1), (2), (3);
+
+--echo # Connection con1
+connect (con1, localhost, root);
+START TRANSACTION WITH CONSISTENT SNAPSHOT;
+SELECT * FROM t1;
+
+--echo # Connection default
+connection default;
+--echo # This should block
+--echo # Sending:
+--send OPTIMIZE TABLE t1
+
+--echo # Connection con1
+connection con1;
+let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
+  WHERE state='Waiting for table' AND info='OPTIMIZE TABLE t1';
+--source include/wait_condition.inc
+SELECT * FROM t1;
+COMMIT;
+
+--echo # Connection default
+connection default;
+--echo # Reaping OPTIMIZE TABLE t1
+--reap
+disconnect con1;
+DROP TABLE t1;
+
+
 # Check that all connections opened by test cases in this file are really
 # gone so execution of other tests won't be affected by their presence.
 --source include/wait_until_count_sessions.inc