Bug#11853126 RE-ENABLE CONCURRENT READS WHILE CREATING

             SECONDARY INDEX IN INNODB

The patches for Bug#11751388 and Bug#11784056 enabled concurrent
reads while creating secondary indexes in InnoDB. However, they
introduced a regression. This regression occured if ALTER TABLE
failed after the index had been added, for example during the
lock upgrade needed to update .FRM. If this happened, InnoDB
and the server got out of sync with regards to which indexes
actually existed. Therefore the patch for Bug#11815600 again
disabled concurrent reads.

This patch re-enables concurrent reads. The original regression
is fixed by splitting the ADD INDEX operation into two parts.
First the new index is created but not made active. This is
done while concurrent reads are allowed. The second part of
the operation makes the index active (or reverts the change).
This is done after lock upgrade, which prevents the original
regression.

In order to implement this change, the patch changes the storage
API for in-place index creation. handler::add_index() is split
into two functions, handler_add_index() and
handler::final_add_index(). The former for creating indexes without
making them visible and the latter for commiting (i.e. making
visible) new indexes or reverting the changes.

Large parts of this patch were written by Marko Mäkelä.

Test case added to innodb_mysql_lock.test.

1 files changed, 22 insertions, 0 deletions

diff --git a/mysql-test/t/innodb_mysql_lock.test b/mysql-test/t/innodb_mysql_lock.test
index f1dc0d52484..a5dcb4d31bf 100644
--- a/mysql-test/t/innodb_mysql_lock.test
+++ b/mysql-test/t/innodb_mysql_lock.test
@@ -290,6 +290,8 @@ DROP TABLE IF EXISTS t1;
 
 --connect (con1,localhost,root)
 
+--echo # Test 1: Secondary index
+
 --echo # Connection default
 connection default;
 CREATE TABLE t1 (id INT PRIMARY KEY, value INT) ENGINE = InnoDB;
@@ -300,6 +302,10 @@ SELECT * FROM t1;
 --echo # Connection con1
 --connection con1
 SET lock_wait_timeout=1;
+# Test with two timeouts, as the first version of this patch
+# only worked with one timeout.
+--error ER_LOCK_WAIT_TIMEOUT
+ALTER TABLE t1 ADD INDEX idx(value);
 --error ER_LOCK_WAIT_TIMEOUT
 ALTER TABLE t1 ADD INDEX idx(value);
 
@@ -308,6 +314,22 @@ ALTER TABLE t1 ADD INDEX idx(value);
 SELECT * FROM t1;
 COMMIT;
 DROP TABLE t1;
+
+--echo # Test 2: Primary index
+CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb;
+INSERT INTO t1 VALUES (1, 12345), (2, 23456);
+
+--echo # Connection con1
+--connection con1
+SET SESSION debug= "+d,alter_table_rollback_new_index";
+--error ER_UNKNOWN_ERROR
+ALTER TABLE t1 ADD PRIMARY KEY(a);
+SELECT * FROM t1;
+
+--echo # Connection default
+--connection default
+SELECT * FROM t1;
+DROP TABLE t1;
 disconnect con1;