Re: bk commit into 5.1 tree (davi:1.2558) BUG#34891

[Alexander Nozdrin]

Approved over the IRC with the following minor notes:
1. restore global concurrent_insert variable to the original value
2. Elaborate the problem in the CS comment -- what we've just discussed.
Something like:
Default value of concurrent_insert is 1. This among others means that INSERT 
statement returns before the value was actually written down to the table ( 
the ok packet is sent before the rows are actually inserted).
That caused the problem, because SELECT-statament in another connection might 
not see the changes INSERT-statement made in the default connection.
The fix is to disable concurrent_insert, so that INSERT returns after the data 
is actually in the table.
3. Mention that the problem also occurs in kill.test
4. Mention cleanup in the CS comment;

Thanks!

On 11 March 2008 20:58:44 Davi Arnaut wrote:
> Below is the list of changes that have just been committed into a local
> 5.1 repository of davi.  When davi does a push these changes
> will be propagated to the main repository and, within 24 hours after the
> push, to the public repository.
> For information on how to access the public repository
> see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
>
> ChangeSet@stripped, 2008-03-11 14:58:35-03:00, davi@stripped +2 -0
>   Bug#34891 sp_notembedded.test fails sporadically
>
>   Clean up sp_notembedded test case to avoid test failures
>   due to timing issues.
>
>   mysql-test/r/sp_notembedded.result@stripped, 2008-03-11 14:58:31-03:00,
> davi@stripped +29 -5 Update test case result
>
>   mysql-test/t/sp_notembedded.test@stripped, 2008-03-11 14:58:31-03:00,
> davi@stripped +70 -68 Disable concurrent inserts, remove sleep and hide
> result
>     log of show process list command.
>
> diff -Nrup a/mysql-test/r/sp_notembedded.result
> b/mysql-test/r/sp_notembedded.result ---
> a/mysql-test/r/sp_notembedded.result	2008-02-28 11:28:32 -03:00 +++
> b/mysql-test/r/sp_notembedded.result	2008-03-11 14:58:31 -03:00 @@ -1,3
> +1,4 @@
> +set @@global.concurrent_insert= 0;
>  drop table if exists t1,t3;
>  drop procedure if exists bug4902|
>  create procedure bug4902()
> @@ -17,11 +18,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 +69,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 +209,26 @@ GRANT ALL PRIVILEGES ON *.* TO 'root'@'l
>  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;
> diff -Nrup a/mysql-test/t/sp_notembedded.test
> b/mysql-test/t/sp_notembedded.test ---
> a/mysql-test/t/sp_notembedded.test	2008-02-28 11:28:32 -03:00
> +++ b/mysql-test/t/sp_notembedded.test	2008-03-11 14:58:31 -03:00
> @@ -1,7 +1,8 @@
>  # Can't test with embedded server
>  -- source include/not_embedded.inc
> +# Disable concurrent inserts to avoid test failures
> +set @@global.concurrent_insert= 0;
>
> ---sleep 2
>  --disable_warnings
>  drop table if exists t1,t3;
>  --enable_warnings
> @@ -39,10 +40,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 +273,66 @@ 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');
> -#
> -# 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;
> +
> +--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;



-- 
Alexander Nozdrin, Software Developer
MySQL AB, Moscow, Russia, www.mysql.com