Bug#34891 sp_notembedded.test fails sporadically

The problem is that since MyISAM's concurrent_insert is on by
default some concurrent SELECT statements might not see changes
made by INSERT statements in other connections, even if the
INSERT statement has returned.

The solution is to disable concurrent_insert so that INSERT
statements returns after the data is actually visible to other
statements.


mysql-test/r/flush_read_lock_kill.result:
  Restore old value of @@global.concurrent_insert
mysql-test/r/kill.result:
  Restore old value of @@global.concurrent_insert
mysql-test/r/sp_notembedded.result:
  Update test case result
mysql-test/t/flush_read_lock_kill.test:
  Restore old value of @@global.concurrent_insert so it
  doesn't affect other tests.
mysql-test/t/kill.test:
  Restore old value of @@global.concurrent_insert so it
  doesn't affect other tests.
mysql-test/t/sp_notembedded.test:
  Disable and restore concurrent_insert value at the end of the
  test case. The test case for Bug 29936 requires that the inserted
  rows need to be visible before a SELECT statement is queued in
  another connection.
  
  Remove sleep at the start of the test, it's not necessary to log
  the result of the processlist command, showing the warnings has
  the same end result.

6 files changed, 122 insertions, 71 deletions

diff --git a/mysql-test/r/flush_read_lock_kill.result b/mysql-test/r/flush_read_lock_kill.result
index f69656806da..0b599f343f7 100644
--- a/mysql-test/r/flush_read_lock_kill.result
+++ b/mysql-test/r/flush_read_lock_kill.result
@@ -1,3 +1,4 @@
+set @old_concurrent_insert= @@global.concurrent_insert;
 set @@global.concurrent_insert= 0;
 drop table if exists t1;
 create table t1 (kill_id int);
@@ -8,3 +9,4 @@ select ((@id := kill_id) - kill_id) from t1;
 0
 kill connection @id;
 drop table t1;
+set @@global.concurrent_insert= @old_concurrent_insert;
diff --git a/mysql-test/r/kill.result b/mysql-test/r/kill.result
index a522d18f36b..8b6830d4798 100644
--- a/mysql-test/r/kill.result
+++ b/mysql-test/r/kill.result
@@ -1,3 +1,4 @@
+set @old_concurrent_insert= @@global.concurrent_insert;
 set @@global.concurrent_insert= 0;
 drop table if exists t1, t2, t3;
 create table t1 (kill_id int);
@@ -137,3 +138,4 @@ KILL CONNECTION_ID();
 # of close of the connection socket
 SELECT 1;
 Got one of the listed errors
+set @@global.concurrent_insert= @old_concurrent_insert;
diff --git a/mysql-test/r/sp_notembedded.result b/mysql-test/r/sp_notembedded.result
index 0b1fa565d28..3efb01fdb94 100644
--- a/mysql-test/r/sp_notembedded.result
+++ b/mysql-test/r/sp_notembedded.result
@@ -1,3 +1,5 @@
+set @old_concurrent_insert= @@global.concurrent_insert;
+set @@global.concurrent_insert= 0;
 drop table if exists t1,t3;
 drop procedure if exists bug4902|
 create procedure bug4902()
@@ -17,11 +19,11 @@ begin
 show processlist;
 end|
 call bug4902_2()|
-Id	User	Host	db	Command	Time	State	Info
-#	root	localhost	test	Query	#	NULL	show processlist
+show warnings|
+Level	Code	Message
 call bug4902_2()|
-Id	User	Host	db	Command	Time	State	Info
-#	root	localhost	test	Query	#	NULL	show processlist
+show warnings|
+Level	Code	Message
 drop procedure bug4902_2|
 drop table if exists t1|
 create table t1 (
@@ -68,7 +70,7 @@ c
 2
 show status like 'Qcache_hits'|
 Variable_name	Value
-Qcache_hits	2
+Qcache_hits	0
 set global query_cache_size = @x|
 flush status|
 flush query cache|
@@ -208,3 +210,27 @@ GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
 drop user mysqltest_1@localhost;
 drop procedure 15298_1;
 drop procedure 15298_2;
+drop table if exists t1;
+drop procedure if exists p1;
+create table t1 (value varchar(15));
+create procedure p1() update t1 set value='updated' where value='old';
+call p1();
+insert into t1 (value) values ("old");
+select get_lock('b26162',120);
+get_lock('b26162',120)
+1
+select 'rl_acquirer', value from t1 where get_lock('b26162',120);;
+set session low_priority_updates=on;
+call p1();;
+select 'rl_contender', value from t1;
+rl_contender	value
+rl_contender	old
+select release_lock('b26162');
+release_lock('b26162')
+1
+rl_acquirer	value
+rl_acquirer	old
+drop procedure p1;
+drop table t1;
+set session low_priority_updates=default;
+set @@global.concurrent_insert= @old_concurrent_insert;
diff --git a/mysql-test/t/flush_read_lock_kill.test b/mysql-test/t/flush_read_lock_kill.test
index c3926d09205..c03f3be2534 100644
--- a/mysql-test/t/flush_read_lock_kill.test
+++ b/mysql-test/t/flush_read_lock_kill.test
@@ -14,6 +14,7 @@
 
 # Disable concurrent inserts to avoid test failures when reading the
 # connection id which was inserted into a table by another thread.
+set @old_concurrent_insert= @@global.concurrent_insert;
 set @@global.concurrent_insert= 0;
 
 connect (con1,localhost,root,,);
@@ -58,3 +59,7 @@ reap;
 
 connection con2;
 drop table t1;
+connection default;
+
+# Restore global concurrent_insert value
+set @@global.concurrent_insert= @old_concurrent_insert;
diff --git a/mysql-test/t/kill.test b/mysql-test/t/kill.test
index b7e1e82fe5d..8ef668f542b 100644
--- a/mysql-test/t/kill.test
+++ b/mysql-test/t/kill.test
@@ -8,6 +8,7 @@
 
 # Disable concurrent inserts to avoid test failures when reading the
 # connection id which was inserted into a table by another thread.
+set @old_concurrent_insert= @@global.concurrent_insert;
 set @@global.concurrent_insert= 0;
 
 connect (con1, localhost, root,,);
@@ -326,3 +327,9 @@ KILL CONNECTION_ID();
 --echo # of close of the connection socket
 --error 2013, 2006 
 SELECT 1;
+--connection default
+
+###########################################################################
+
+# Restore global concurrent_insert value. Keep in the end of the test file.
+set @@global.concurrent_insert= @old_concurrent_insert;
diff --git a/mysql-test/t/sp_notembedded.test b/mysql-test/t/sp_notembedded.test
index 4e298b2076a..16ebb710f25 100644
--- a/mysql-test/t/sp_notembedded.test
+++ b/mysql-test/t/sp_notembedded.test
@@ -1,7 +1,9 @@
 # Can't test with embedded server
 -- source include/not_embedded.inc
+# Disable concurrent inserts to avoid test failures
+set @old_concurrent_insert= @@global.concurrent_insert;
+set @@global.concurrent_insert= 0;
 
---sleep 2
 --disable_warnings
 drop table if exists t1,t3;
 --enable_warnings
@@ -39,10 +41,14 @@ create procedure bug4902_2()
 begin
   show processlist;
 end|
---replace_column 1 # 6 # 3 localhost
+--disable_result_log
 call bug4902_2()|
---replace_column 1 # 6 # 3 localhost
+--enable_result_log
+show warnings|
+--disable_result_log
 call bug4902_2()|
+--enable_result_log
+show warnings|
 drop procedure bug4902_2|
 
 #
@@ -268,69 +274,72 @@ drop procedure 15298_1;
 drop procedure 15298_2;
 
 #
-# Test case disabled due to Bug#34891: sp_notembedded.test fails sporadically.
+# Bug#29936 Stored Procedure DML ignores low_priority_updates setting
 #
-# #
-# # Bug#29936 Stored Procedure DML ignores low_priority_updates setting
-# #
-#
-# --disable_warnings
-# drop table if exists t1;
-# drop procedure if exists p1;
-# --enable_warnings
-#
-# create table t1 (value varchar(15));
-# create procedure p1() update t1 set value='updated' where value='old';
-#
-# # load the procedure into sp cache and execute once
-# call p1();
-#
-# insert into t1 (value) values ("old");
-#
-# connect (rl_holder, localhost, root,,);
-# connect (rl_acquirer, localhost, root,,);
-# connect (rl_contender, localhost, root,,);
-# connect (rl_wait, localhost, root,,);
-#
-# connection rl_holder;
-# select get_lock('b26162',120);
-#
-# connection rl_acquirer;
-# --send select 'rl_acquirer', value from t1 where get_lock('b26162',120);
-#
-# # we must wait till this select opens and locks the tables
-# connection rl_wait;
-# let $wait_condition=
-#   select count(*) = 1 from information_schema.processlist
-#   where state = "User lock" and
-#   info = "select 'rl_acquirer', value from t1 where get_lock('b26162',120)";
-# --source include/wait_condition.inc
-#
-# connection default;
-# set session low_priority_updates=on;
-# --send call p1();
-#
-# connection rl_wait;
-# let $wait_condition=
-#   select count(*) = 1 from information_schema.processlist
-#   where state = "Locked" and
-#   info = "update t1 set value='updated' where value='old'";
-# --source include/wait_condition.inc
-#
-# connection rl_contender;
-# select 'rl_contender', value from t1;
-#
-# connection rl_holder;
-# select release_lock('b26162');
+
+--disable_warnings
+drop table if exists t1;
+drop procedure if exists p1;
+--enable_warnings
+
+create table t1 (value varchar(15));
+create procedure p1() update t1 set value='updated' where value='old';
+
+# load the procedure into sp cache and execute once
+call p1();
+
+insert into t1 (value) values ("old");
+
+connect (rl_holder, localhost, root,,);
+connect (rl_acquirer, localhost, root,,);
+connect (rl_contender, localhost, root,,);
+connect (rl_wait, localhost, root,,);
+
+connection rl_holder;
+select get_lock('b26162',120);
+
+connection rl_acquirer;
+--send select 'rl_acquirer', value from t1 where get_lock('b26162',120);
+
+# we must wait till this select opens and locks the tables
+connection rl_wait;
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "User lock" and
+  info = "select 'rl_acquirer', value from t1 where get_lock('b26162',120)";
+--source include/wait_condition.inc
+
+connection default;
+set session low_priority_updates=on;
+--send call p1();
+
+connection rl_wait;
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Locked" and
+  info = "update t1 set value='updated' where value='old'";
+--source include/wait_condition.inc
+
+connection rl_contender;
+select 'rl_contender', value from t1;
+
+connection rl_holder;
+select release_lock('b26162');
+
+connection rl_acquirer;
+--reap
+connection default;
+--reap
+
+disconnect rl_holder;
+disconnect rl_acquirer;
+disconnect rl_wait;
+drop procedure p1;
+drop table t1;
+set session low_priority_updates=default;
+
 #
-# connection rl_acquirer;
-# --reap
-# connection default;
-# --reap
+# Restore global concurrent_insert value. Keep in the end of the test file.
 #
-# disconnect rl_holder;
-# disconnect rl_acquirer;
-# disconnect rl_wait;
-# drop procedure p1;
-# drop table t1;
-# set session low_priority_updates=default;
+
+set @@global.concurrent_insert= @old_concurrent_insert;