MDEV-11802 innodb.innodb_bug14676111 fails on buildbot

The test was unnecessarily depending on InnoDB purge, which can
sometimes fail to proceed.

Let us rewrite the test to use BEGIN;INSERT;ROLLBACK to cause the
immediate removal of the desired records.

2 files changed, 55 insertions, 43 deletions

diff --git a/mysql-test/suite/innodb/r/innodb_bug14676111.result b/mysql-test/suite/innodb/r/innodb_bug14676111.result
index 1135259cfbf..eda04736cc0 100644
--- a/mysql-test/suite/innodb/r/innodb_bug14676111.result
+++ b/mysql-test/suite/innodb/r/innodb_bug14676111.result
@@ -1,28 +1,39 @@
+set @old_innodb_limit_optimistic_insert_debug = @@innodb_limit_optimistic_insert_debug;
 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB STATS_PERSISTENT=0;
 set global innodb_limit_optimistic_insert_debug = 2;
 insert into t1 values (1);
+connect  con5,localhost,root;
+begin;
 insert into t1 values (5);
+connect  con4,localhost,root;
+begin;
 insert into t1 values (4);
+connection default;
 insert into t1 values (3);
+connect  con2,localhost,root;
+begin;
 insert into t1 values (2);
+connection default;
 analyze table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	OK
 select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1';
 CLUST_INDEX_SIZE
 10
-delete from t1 where a=4;
-set global innodb_purge_stop_now=ON;
-set global innodb_purge_run_now=ON;
+connection con4;
+rollback;
+disconnect con4;
+connection default;
 analyze table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	OK
 select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1';
 CLUST_INDEX_SIZE
 8
-delete from t1 where a=5;
-set global innodb_purge_stop_now=ON;
-set global innodb_purge_run_now=ON;
+connection con5;
+rollback;
+disconnect con5;
+connection default;
 analyze table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	OK
@@ -30,29 +41,28 @@ select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME
 CLUST_INDEX_SIZE
 5
 set global innodb_limit_optimistic_insert_debug = 0;
-delete from t1 where a=2;
-set global innodb_purge_stop_now=ON;
-set global innodb_purge_run_now=ON;
+connection con2;
+rollback;
+disconnect con2;
+connection default;
 analyze table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	OK
 select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1';
 CLUST_INDEX_SIZE
 3
+begin;
 insert into t1 values (2);
-delete from t1 where a=2;
-set global innodb_purge_stop_now=ON;
-set global innodb_purge_run_now=ON;
+rollback;
 analyze table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	OK
 select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1';
 CLUST_INDEX_SIZE
 2
+begin;
 insert into t1 values (2);
-delete from t1 where a=2;
-set global innodb_purge_stop_now=ON;
-set global innodb_purge_run_now=ON;
+rollback;
 analyze table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	OK
@@ -60,3 +70,4 @@ select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME
 CLUST_INDEX_SIZE
 1
 drop table t1;
+set global innodb_limit_optimistic_insert_debug = @old_innodb_limit_optimistic_insert_debug;
diff --git a/mysql-test/suite/innodb/t/innodb_bug14676111.test b/mysql-test/suite/innodb/t/innodb_bug14676111.test
index 68054d7654f..d8b78394cc2 100644
--- a/mysql-test/suite/innodb/t/innodb_bug14676111.test
+++ b/mysql-test/suite/innodb/t/innodb_bug14676111.test
@@ -2,13 +2,9 @@
 
 -- source include/have_innodb.inc
 -- source include/have_debug.inc
+-- source include/count_sessions.inc
 
---disable_query_log
 set @old_innodb_limit_optimistic_insert_debug = @@innodb_limit_optimistic_insert_debug;
-set @old_innodb_undo_logs = @@innodb_undo_logs;
-# Limit undo segments for stable progress of purge.
-set global innodb_undo_logs = 1;
---enable_query_log
 
 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB STATS_PERSISTENT=0;
 
@@ -17,22 +13,29 @@ CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB STATS_PERSISTENT=0;
 #
 set global innodb_limit_optimistic_insert_debug = 2;
 insert into t1 values (1);
+--connect (con5,localhost,root)
+begin;
 insert into t1 values (5);
 #current tree form
 # (1, 5)
 
+--connect (con4,localhost,root)
+begin;
 insert into t1 values (4);
 #records in a page is limited to 2 artificially. root rise occurs
 #current tree form
 #   (1, 5)
 #(1, 4) (5)
 
+--connection default
 insert into t1 values (3);
 #current tree form
 #    (1, 5)
 #  (1, 4) (5)
 #(1, 3) (4) (5)
 
+--connect (con2,localhost,root)
+begin;
 insert into t1 values (2);
 #current tree form
 #      (1, 5)
@@ -40,13 +43,15 @@ insert into t1 values (2);
 #  (1, 3) (4) (5)
 #(1, 2) (3) (4) (5)
 
+--connection default
 analyze table t1;
 select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1';
 
-delete from t1 where a=4;
-set global innodb_purge_stop_now=ON;
-set global innodb_purge_run_now=ON;
---source include/wait_innodb_all_purged.inc
+--connection con4
+rollback;
+--disconnect con4
+--connection default
+
 #deleting 1 record of 2 records don't cause merge artificially.
 #current tree form
 #      (1, 5)
@@ -57,10 +62,11 @@ set global innodb_purge_run_now=ON;
 analyze table t1;
 select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1';
 
-delete from t1 where a=5;
-set global innodb_purge_stop_now=ON;
-set global innodb_purge_run_now=ON;
---source include/wait_innodb_all_purged.inc
+--connection con5
+rollback;
+--disconnect con5
+--connection default
+
 #deleting 1 record of 2 records don't cause merge artificially.
 #current tree form
 #      (1)
@@ -77,10 +83,11 @@ select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME
 
 #disable the artificial limitation of records in a page
 set global innodb_limit_optimistic_insert_debug = 0;
-delete from t1 where a=2;
-set global innodb_purge_stop_now=ON;
-set global innodb_purge_run_now=ON;
---source include/wait_innodb_all_purged.inc
+--connection con2
+rollback;
+--disconnect con2
+--connection default
+
 #merge page occurs. and lift up occurs.
 #current tree form
 #      (1)
@@ -90,16 +97,14 @@ set global innodb_purge_run_now=ON;
 analyze table t1;
 select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1';
 
+begin;
 insert into t1 values (2);
 #current tree form
 #      (1)
 #    (1) <- lift up this level next, because it is not root
 #  (1, 2, 3)
+rollback;
 
-delete from t1 where a=2;
-set global innodb_purge_stop_now=ON;
-set global innodb_purge_run_now=ON;
---source include/wait_innodb_all_purged.inc
 #current tree form
 #      (1)
 #    (1, 3)
@@ -107,15 +112,13 @@ set global innodb_purge_run_now=ON;
 analyze table t1;
 select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1';
 
+begin;
 insert into t1 values (2);
 #current tree form
 #      (1)
 #    (1, 2, 3) <- lift up this level next, because the father is root
 
-delete from t1 where a=2;
-set global innodb_purge_stop_now=ON;
-set global innodb_purge_run_now=ON;
---source include/wait_innodb_all_purged.inc
+rollback;
 #current tree form
 #      (1, 3)
 
@@ -124,7 +127,5 @@ select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME
 
 drop table t1;
 
---disable_query_log
 set global innodb_limit_optimistic_insert_debug = @old_innodb_limit_optimistic_insert_debug;
-set global innodb_undo_logs = @old_innodb_undo_logs;
---enable_query_log
+-- source include/wait_until_count_sessions.inc