Merge lp:~tsarev/percona-server/5.5-processlist_rows_stats-sporadic_fails-fix into lp:percona-server/5.5

[Oleg Tsarev]

1) Now every time before I call SHOW PROCESSLIST or SELECT ... FROM INFORMATION_SCHEMA.PROCESSLIST I first switch threads to consistent state.
  Before that connection conn1 sometime not complete run of previous query, and result of test was sporadic.
  Example of fail available here:
  http://jenkins.percona.com/view/Percona%20Server%205.5/job/percona-server-5.5-param/66/BUILD_TYPE=debug,Host=ubuntu-natty-32bit/consoleText
  Example of fail:
   Id User Host db Command Time State Info Rows_sent Rows_examined Rows_read
   ### root ### test Query ### ### SHOW PROCESSLIST 0 0 4
  -### root ### test Sleep ### ### NULL 0 0 1
  +### root ### test Query ### ### CREATE TABLE t1 (a INT) 0 0 1
   ### root ### test Query ### ### SELECT a FROM t2 WHERE a > 15 1 0 3

  As you can see, conn1 not complete yet CREATE TABLE (table was created, but thread not switched to state "Sleep" yet)

2) I removed useless echo messages. This messages doesn't have any usefull information, but do noise in output

3) Also I extend test by additional queries (need for (1) - consistent state of 1)

[Laurynas Biveinis (laurynas-biveinis)]

LGTM if the Jenkins test results are clean

review: Approve

[Oleg Tsarev (tsarev)]

http://jenkins.percona.com/view/Percona%20Server%205.5/job/percona-server-5.5-param/71

=== modified file 'mysql-test/processlist_row_stats.patch/percona_processlist_row_stats.result'
--- mysql-test/processlist_row_stats.patch/percona_processlist_row_stats.result	2011-04-11 19:43:06 +0000
+++ mysql-test/processlist_row_stats.patch/percona_processlist_row_stats.result	2011-08-03 17:46:56 +0000
@@ -6,69 +6,65 @@
 INSERT INTO t2 VALUES(20);
 INSERT INTO t2 VALUES(10);
 INSERT INTO t2 VALUES(20);
-Issuing operation that should not return any rows and stopping the thread #1
 SET DEBUG_SYNC= 'locked_table_name SIGNAL thread1_ready WAIT_FOR threads_dumped';
 CREATE TABLE t1 (a INT);
 SET DEBUG_SYNC= 'now WAIT_FOR thread1_ready';
-Thread #1 stopped
-Issuing row-returning query and stopping the thread #2 at the end of query
 SET DEBUG_SYNC= 'execute_command_after_close_tables SIGNAL thread2_ready WAIT_FOR threads_dumped';
 SELECT a FROM t2 WHERE a > 15;
 SET DEBUG_SYNC= 'now WAIT_FOR thread2_ready';
-Thread #2 stopped
-Look at thread states
 SHOW PROCESSLIST;
 Id	User	Host	db	Command	Time	State	Info	Rows_sent	Rows_examined	Rows_read
 ###	root	###	test	Query	###	###	SHOW PROCESSLIST	0	0	2
 ###	root	###	test	Query	###	###	CREATE TABLE t1 (a INT)	0	0	1
 ###	root	###	test	Query	###	###	SELECT a FROM t2 WHERE a > 15	2	5	6
-SELECT id, info, rows_sent, rows_examined, rows_read FROM information_schema.processlist 
-ORDER BY id;
+SELECT id, info, rows_sent, rows_examined, rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY id;
 id	info	rows_sent	rows_examined	rows_read
-###	SELECT id, info, rows_sent, rows_examined, rows_read FROM information_schema.processlist 
-ORDER BY id	0	0	1
+###	SELECT id, info, rows_sent, rows_examined, rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY id	0	0	1
 ###	CREATE TABLE t1 (a INT)	0	0	1
 ###	SELECT a FROM t2 WHERE a > 15	2	5	6
-Let threads #1 and #2 finish their job
 SET DEBUG_SYNC= 'now SIGNAL threads_dumped';
 a
 20
 20
-Issuing row-returning query and stopping the thread #2 in the middle of query
+SET DEBUG_SYNC= 'sent_row SIGNAL thread1_ready WAIT_FOR threads_dumped';
+SELECT a FROM t2 WHERE a < 15;
+SET DEBUG_SYNC= 'now WAIT_FOR thread1_ready';
 SET DEBUG_SYNC= 'sent_row SIGNAL thread2_ready WAIT_FOR threads_dumped';
 SELECT a FROM t2 WHERE a > 15;
 SET DEBUG_SYNC= 'now WAIT_FOR thread2_ready';
-Thread #2 stopped, look at its state
 SHOW PROCESSLIST;
 Id	User	Host	db	Command	Time	State	Info	Rows_sent	Rows_examined	Rows_read
 ###	root	###	test	Query	###	###	SHOW PROCESSLIST	0	0	4
-###	root	###	test	Sleep	###	###	NULL	0	0	1
+###	root	###	test	Query	###	###	SELECT a FROM t2 WHERE a < 15	1	0	1
 ###	root	###	test	Query	###	###	SELECT a FROM t2 WHERE a > 15	1	0	3
-SELECT id,rows_sent,rows_examined,rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY Id;
-id	rows_sent	rows_examined	rows_read
-###	0	0	1
-###	0	0	1
-###	1	0	3
+SELECT id, info, rows_sent, rows_examined, rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY id;
+id	info	rows_sent	rows_examined	rows_read
+###	SELECT id, info, rows_sent, rows_examined, rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY id	0	0	1
+###	SELECT a FROM t2 WHERE a < 15	1	0	1
+###	SELECT a FROM t2 WHERE a > 15	1	0	3
 SET DEBUG_SYNC= 'now SIGNAL threads_dumped';
-Let thread #2 finish its job
-a
-20
-20
-Issuing an UPDATE and stopping thread #2
+a
+10
+10
+10
+a
+20
+20
+SET DEBUG_SYNC= 'execute_command_after_close_tables SIGNAL thread1_ready WAIT_FOR threads_dumped';
+UPDATE t2 SET a = 15 WHERE a = 20;
+SET DEBUG_SYNC= 'now WAIT_FOR thread1_ready';
 SET DEBUG_SYNC= 'execute_command_after_close_tables SIGNAL thread2_ready WAIT_FOR threads_dumped';
 UPDATE t2 SET a = 15 WHERE a = 10;
 SET DEBUG_SYNC= 'now WAIT_FOR thread2_ready';
-Thread #2 stopped, look at its state
 SHOW PROCESSLIST;
 Id	User	Host	db	Command	Time	State	Info	Rows_sent	Rows_examined	Rows_read
 ###	root	###	test	Query	###	###	SHOW PROCESSLIST	0	0	4
-###	root	###	test	Sleep	###	###	NULL	0	0	1
+###	root	###	test	Query	###	###	UPDATE t2 SET a = 15 WHERE a = 20	0	5	6
 ###	root	###	test	Query	###	###	UPDATE t2 SET a = 15 WHERE a = 10	0	5	6
-SELECT id,rows_sent,rows_examined,rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY Id;
-id	rows_sent	rows_examined	rows_read
-###	0	0	1
-###	0	0	1
-###	0	5	6
+SELECT id, info, rows_sent, rows_examined, rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY id;
+id	info	rows_sent	rows_examined	rows_read
+###	SELECT id, info, rows_sent, rows_examined, rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY id	0	0	1
+###	UPDATE t2 SET a = 15 WHERE a = 20	0	5	6
+###	UPDATE t2 SET a = 15 WHERE a = 10	0	5	6
 SET DEBUG_SYNC= 'now SIGNAL threads_dumped';
-Let thread #2 finish its job
 DROP TABLES t1, t2;

=== modified file 'mysql-test/processlist_row_stats.patch/percona_processlist_row_stats.test'
--- mysql-test/processlist_row_stats.patch/percona_processlist_row_stats.test	2011-04-11 19:43:06 +0000
+++ mysql-test/processlist_row_stats.patch/percona_processlist_row_stats.test	2011-08-03 17:46:56 +0000
@@ -13,76 +13,63 @@
 INSERT INTO t2 VALUES(10);
 INSERT INTO t2 VALUES(20);
 
-connect (conn1, localhost, root, ,);
-connect (conn2, localhost, root, ,);
+--connect (conn1, localhost, root, ,)
+--connect (conn2, localhost, root, ,)
 
 --connection conn1
-echo Issuing operation that should not return any rows and stopping the thread #1;
 SET DEBUG_SYNC= 'locked_table_name SIGNAL thread1_ready WAIT_FOR threads_dumped';
 send CREATE TABLE t1 (a INT);
-
 --connection default
 SET DEBUG_SYNC= 'now WAIT_FOR thread1_ready';
---echo Thread #1 stopped
 
 --connection conn2
-echo Issuing row-returning query and stopping the thread #2 at the end of query;
 SET DEBUG_SYNC= 'execute_command_after_close_tables SIGNAL thread2_ready WAIT_FOR threads_dumped';
 send SELECT a FROM t2 WHERE a > 15;
-
 --connection default
 SET DEBUG_SYNC= 'now WAIT_FOR thread2_ready';
---echo Thread #2 stopped
---echo Look at thread states
-
---replace_column 1 ### 3 ### 6 ### 7 ###
-SHOW PROCESSLIST;
-# The running threads are different between SHOW above and SELECT below.  Thus select info too to 
-# show the difference.  Results are in the same order as in SHOW.
---replace_column 1 ###
-SELECT id, info, rows_sent, rows_examined, rows_read FROM information_schema.processlist 
-ORDER BY id;
-
-echo Let threads #1 and #2 finish their job;
-SET DEBUG_SYNC= 'now SIGNAL threads_dumped';
-
---connection conn1
-reap;
-
---connection conn2
-reap;
-echo Issuing row-returning query and stopping the thread #2 in the middle of query;
+
+--source include/percona_processlist_row_stats_show.inc
+
+--connection conn1
+reap;
+--connection conn2
+reap;
+
+--connection conn1
+SET DEBUG_SYNC= 'sent_row SIGNAL thread1_ready WAIT_FOR threads_dumped';
+send SELECT a FROM t2 WHERE a < 15;
+--connection default
+SET DEBUG_SYNC= 'now WAIT_FOR thread1_ready';
+
+--connection conn2
 SET DEBUG_SYNC= 'sent_row SIGNAL thread2_ready WAIT_FOR threads_dumped';
 send SELECT a FROM t2 WHERE a > 15;
-
 --connection default
 SET DEBUG_SYNC= 'now WAIT_FOR thread2_ready';
-echo Thread #2 stopped, look at its state;
-
---replace_column 1 ### 3 ### 6 ### 7 ###
-SHOW PROCESSLIST;
---replace_column 1 ###
-SELECT id,rows_sent,rows_examined,rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY Id;
-SET DEBUG_SYNC= 'now SIGNAL threads_dumped';
-echo Let thread #2 finish its job;
-
---connection conn2
-reap;
-echo Issuing an UPDATE and stopping thread #2;
+
+--source include/percona_processlist_row_stats_show.inc
+
+--connection conn1
+reap;
+--connection conn2
+reap;
+
+--connection conn1
+SET DEBUG_SYNC= 'execute_command_after_close_tables SIGNAL thread1_ready WAIT_FOR threads_dumped';
+send UPDATE t2 SET a = 15 WHERE a = 20;
+--connection default
+SET DEBUG_SYNC= 'now WAIT_FOR thread1_ready';
+
+--connection conn2
 SET DEBUG_SYNC= 'execute_command_after_close_tables SIGNAL thread2_ready WAIT_FOR threads_dumped';
 send UPDATE t2 SET a = 15 WHERE a = 10;
-
 --connection default
 SET DEBUG_SYNC= 'now WAIT_FOR thread2_ready';
-echo Thread #2 stopped, look at its state;
-
---replace_column 1 ### 3 ### 6 ### 7 ###
-SHOW PROCESSLIST;
---replace_column 1 ###
-SELECT id,rows_sent,rows_examined,rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY Id;
-SET DEBUG_SYNC= 'now SIGNAL threads_dumped';
-echo Let thread #2 finish its job;
-
+
+--source include/percona_processlist_row_stats_show.inc
+
+--connection conn1
+reap;
 --connection conn2
 reap;
 

=== added file 'mysql-test/processlist_row_stats.patch/percona_processlist_row_stats_show.inc'
--- mysql-test/processlist_row_stats.patch/percona_processlist_row_stats_show.inc	1970-01-01 00:00:00 +0000
+++ mysql-test/processlist_row_stats.patch/percona_processlist_row_stats_show.inc	2011-08-03 17:46:56 +0000
@@ -0,0 +1,7 @@
+--replace_column 1 ### 3 ### 6 ### 7 ###
+SHOW PROCESSLIST;
+
+--replace_column 1 ###
+SELECT id, info, rows_sent, rows_examined, rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY id;
+
+SET DEBUG_SYNC= 'now SIGNAL threads_dumped';