MBug#687320: Fix sporadic test failures in innodb_mysql.test and partition_innodb_semi_consistent.test

Problem is that these tests run with --innodb-lock-wait-timeout=2 in .opt
(and this is necessary as built-in innodb does not allow to change this
dynamically). This cases another part of the test to occasionally time
out an UPDATE, which subsequently caused the test case to timeout due to
waiting for a condition (successful UPDATE) that never occurs.

Fixed by re-trying the update in case of timeout.

Tested by inserting a sleep() in the connection that the UPDATE is waiting
for, and checking that the retry loops a couple of times until the other
connection is done and COMMITs.

5 files changed, 52 insertions, 2 deletions

diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
index 194d9e41108..7ef8adf8e08 100644
--- a/mysql-test/include/mix1.inc
+++ b/mysql-test/include/mix1.inc
@@ -1359,7 +1359,17 @@ INSERT INTO t1 VALUES (1,'init');
 DELIMITER |;
 CREATE PROCEDURE p1()
 BEGIN
-  UPDATE t1 SET b = CONCAT(b, '+con2')  WHERE a = 1;
+  # retry the UPDATE in case it times out the lock before con1 has time
+  # to COMMIT.
+  DECLARE do_retry INT DEFAULT 0;
+  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET do_retry = 1;
+  retry_loop:LOOP
+    UPDATE t1 SET b = CONCAT(b, '+con2')  WHERE a = 1;
+    IF do_retry = 0 THEN
+      LEAVE retry_loop;
+    END IF;
+    SET do_retry = 0;
+  END LOOP;
   INSERT INTO t2 VALUES ();
 END|
 DELIMITER ;|
diff --git a/mysql-test/r/partition_innodb_semi_consistent.result b/mysql-test/r/partition_innodb_semi_consistent.result
index 471da4c1c2e..fbdd70528c5 100644
--- a/mysql-test/r/partition_innodb_semi_consistent.result
+++ b/mysql-test/r/partition_innodb_semi_consistent.result
@@ -75,7 +75,17 @@ TRUNCATE t1;
 INSERT INTO t1 VALUES (1,'init');
 CREATE PROCEDURE p1()
 BEGIN
+# retry the UPDATE in case it times out the lock before con1 has time
+# to COMMIT.
+DECLARE do_retry INT DEFAULT 0;
+DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET do_retry = 1;
+retry_loop:LOOP
 UPDATE t1 SET b = CONCAT(b, '+con2')  WHERE a = 1;
+IF do_retry = 0 THEN
+LEAVE retry_loop;
+END IF;
+SET do_retry = 0;
+END LOOP;
 INSERT INTO t2 VALUES ();
 END|
 BEGIN;
diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result
index 5d90a96f61c..86a51b337ff 100644
--- a/mysql-test/suite/innodb/r/innodb_mysql.result
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result
@@ -1594,7 +1594,17 @@ TRUNCATE t1;
 INSERT INTO t1 VALUES (1,'init');
 CREATE PROCEDURE p1()
 BEGIN
+# retry the UPDATE in case it times out the lock before con1 has time
+# to COMMIT.
+DECLARE do_retry INT DEFAULT 0;
+DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET do_retry = 1;
+retry_loop:LOOP
 UPDATE t1 SET b = CONCAT(b, '+con2')  WHERE a = 1;
+IF do_retry = 0 THEN
+LEAVE retry_loop;
+END IF;
+SET do_retry = 0;
+END LOOP;
 INSERT INTO t2 VALUES ();
 END|
 BEGIN;
diff --git a/mysql-test/suite/innodb_plugin/r/innodb_mysql.result b/mysql-test/suite/innodb_plugin/r/innodb_mysql.result
index 32ee3c21946..1aee85b5288 100644
--- a/mysql-test/suite/innodb_plugin/r/innodb_mysql.result
+++ b/mysql-test/suite/innodb_plugin/r/innodb_mysql.result
@@ -1594,7 +1594,17 @@ TRUNCATE t1;
 INSERT INTO t1 VALUES (1,'init');
 CREATE PROCEDURE p1()
 BEGIN
+# retry the UPDATE in case it times out the lock before con1 has time
+# to COMMIT.
+DECLARE do_retry INT DEFAULT 0;
+DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET do_retry = 1;
+retry_loop:LOOP
 UPDATE t1 SET b = CONCAT(b, '+con2')  WHERE a = 1;
+IF do_retry = 0 THEN
+LEAVE retry_loop;
+END IF;
+SET do_retry = 0;
+END LOOP;
 INSERT INTO t2 VALUES ();
 END|
 BEGIN;
diff --git a/mysql-test/t/partition_innodb_semi_consistent.test b/mysql-test/t/partition_innodb_semi_consistent.test
index 2711d79f194..9d3e2570256 100644
--- a/mysql-test/t/partition_innodb_semi_consistent.test
+++ b/mysql-test/t/partition_innodb_semi_consistent.test
@@ -117,7 +117,17 @@ INSERT INTO t1 VALUES (1,'init');
 DELIMITER |;
 CREATE PROCEDURE p1()
 BEGIN
-  UPDATE t1 SET b = CONCAT(b, '+con2')  WHERE a = 1;
+  # retry the UPDATE in case it times out the lock before con1 has time
+  # to COMMIT.
+  DECLARE do_retry INT DEFAULT 0;
+  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET do_retry = 1;
+  retry_loop:LOOP
+    UPDATE t1 SET b = CONCAT(b, '+con2')  WHERE a = 1;
+    IF do_retry = 0 THEN
+      LEAVE retry_loop;
+    END IF;
+    SET do_retry = 0;
+  END LOOP;
   INSERT INTO t2 VALUES ();
 END|
 DELIMITER ;|