MySQL Bugs: #38270: Test "processlist_priv_ps" fails on varying "processlist" output

Bug #38270	Test "processlist_priv_ps" fails on varying "processlist" output
Submitted:	21 Jul 2008 18:59	Modified:	1 Oct 2008 17:14
Reporter:	Joerg Bruehe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Tests: Server	Severity:	S3 (Non-critical)
Version:	5.1.26-rc	OS:	Any
Assigned to:	Matthias Leich	CPU Architecture:	Any

Description:
Detected in 5.1.26-rc

Test "funcs_1.processlist_priv_ps" has "show processlist" output
which is not reliable, in some runs the output differs.

In release builds, the highest likelihood for this was on Windows-64 (all tests failed), but I do not think this is platform-related.
Maybe scheduling on Windows just increases the likelihood.

In 5.1.27, it also showed on other platforms:

=====
funcs_1.processlist_priv_ps    [ fail ]

--- /PATH/mysql-test/suite/funcs_1/r/processlist_priv_ps.result
+++ /PATH/mysql-test/suite/funcs_1/r/processlist_priv_ps.reject
@@ -36,15 +36,15 @@
 SHOW processlist;
 Id     User    Host    db      Command Time    State   Info
 ID     root    HOST_NAME       information_schema      Query   TIME    NULL    SHOW processlist
-ID     ddicttestuser1  HOST_NAME       information_schema      Sleep   TIME            NULL
+ID     ddicttestuser1  HOST_NAME       information_schema      Connect TIME    Writing to net  NULL
 SELECT * FROM processlist  ORDER BY id;
 ID     USER    HOST    DB      COMMAND TIME    STATE   INFO
 ID     root    HOST_NAME       information_schema      Execute TIME    executing       SELECT * FROM processl
ist  ORDER BY id
-ID     ddicttestuser1  HOST_NAME       information_schema      Sleep   TIME            NULL
+ID     ddicttestuser1  HOST_NAME       information_schema      Connect TIME    Writing to net  NULL
 SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist  ORDER BY id;
 ID     USER    HOST    DB      COMMAND TIME    STATE   INFO
 ID     root    HOST_NAME       information_schema      Execute TIME    executing       SELECT ID, USER, HOST,
 DB, COMMAND, TIME, STATE, INFO FROM processlist  ORDER BY id
-ID     ddicttestuser1  HOST_NAME       information_schema      Sleep   TIME            NULL
+ID     ddicttestuser1  HOST_NAME       information_schema      Connect TIME    Writing to net  NULL
 CREATE TEMPORARY TABLE test.t_processlist AS SELECT * FROM processlist;
 UPDATE test.t_processlist SET user='horst' WHERE id=1  ;
 INSERT INTO processlist SELECT * FROM test.t_processlist;
=====
Windows, 64 bit (6 tests = 6 occurrences !)
Linux, IA64, RPM build

=====
funcs_1.processlist_priv_ps    [ fail ]

--- /PATH/mysql-test/suite/funcs_1/r/processlist_priv_ps.result
+++ /PATH/mysql-test/suite/funcs_1/r/processlist_priv_ps.reject
@@ -35,14 +35,17 @@
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8
 SHOW processlist;
 Id     User    Host    db      Command Time    State   Info
+ID     root    HOST_NAME       test    Sleep   TIME            NULL
 ID     root    HOST_NAME       information_schema      Query   TIME    NULL    SHOW processlist
 ID     ddicttestuser1  HOST_NAME       information_schema      Sleep   TIME            NULL
 SELECT * FROM processlist  ORDER BY id;
 ID     USER    HOST    DB      COMMAND TIME    STATE   INFO
+ID     root    HOST_NAME       test    Sleep   TIME            NULL
 ID     root    HOST_NAME       information_schema      Execute TIME    executing       SELECT * FROM processl
ist  ORDER BY id
 ID     ddicttestuser1  HOST_NAME       information_schema      Sleep   TIME            NULL
 SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist  ORDER BY id;
 ID     USER    HOST    DB      COMMAND TIME    STATE   INFO
+ID     root    HOST_NAME       test    Sleep   TIME            NULL
 ID     root    HOST_NAME       information_schema      Execute TIME    executing       SELECT ID, USER, HOST,
 DB, COMMAND, TIME, STATE, INFO FROM processlist  ORDER BY id
 ID     ddicttestuser1  HOST_NAME       information_schema      Sleep   TIME            NULL
 CREATE TEMPORARY TABLE test.t_processlist AS SELECT * FROM processlist;
=====
Solaris 9, Sparc, 64 bit

=====
funcs_1.processlist_priv_ps    [ fail ]

--- /PATH/mysql-test/suite/funcs_1/r/processlist_priv_ps.result
+++ /PATH/mysql-test/suite/funcs_1/r/processlist_priv_ps.reject
@@ -35,14 +35,17 @@
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8
 SHOW processlist;
 Id     User    Host    db      Command Time    State   Info
+ID     root    HOST_NAME       test    Quit    TIME    NULL    NULL
 ID     root    HOST_NAME       information_schema      Query   TIME    NULL    SHOW processlist
 ID     ddicttestuser1  HOST_NAME       information_schema      Sleep   TIME            NULL
 SELECT * FROM processlist  ORDER BY id;
 ID     USER    HOST    DB      COMMAND TIME    STATE   INFO
+ID     root    HOST_NAME       test    Quit    TIME    NULL    NULL
 ID     root    HOST_NAME       information_schema      Execute TIME    executing       SELECT * FROM processl
ist  ORDER BY id
 ID     ddicttestuser1  HOST_NAME       information_schema      Sleep   TIME            NULL
 SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist  ORDER BY id;
 ID     USER    HOST    DB      COMMAND TIME    STATE   INFO
+ID     root    HOST_NAME       test    Quit    TIME    NULL    NULL
 ID     root    HOST_NAME       information_schema      Execute TIME    executing       SELECT ID, USER, HOST,
 DB, COMMAND, TIME, STATE, INFO FROM processlist  ORDER BY id
 ID     ddicttestuser1  HOST_NAME       information_schema      Sleep   TIME            NULL
 CREATE TEMPORARY TABLE test.t_processlist AS SELECT * FROM processlist;
=====
Linux, PPC

How to repeat:
Run the test
(sufficiently often, or on Windows-64).

The first bad effect 
Example
  - ... Sleep   TIME NULL
  + ... Connect TIME Writing to net  NULL
seems to be caused by a weakness within the test script.
The phase where a session is in state "Connect" is
usually very short and already completed when a following
SHOW PROCESSLIST is executed. But this is no more valid
in case of a testing box with high load.

The second bad effect, reported for 
- Solaris 9, Sparc, 64 bit
- Linux, PPC
shows an unexpected additional session which seems
to disconnect just now.
  ID  USER  HOST       DB    COMMAND TIME STATE INFO
  ID  root  HOST_NAME  test  Quit    TIME NULL  NULL
This session cannot be explained by the testcase itself.
There is no disconnect of a session between the start
of the test and the statements where we get this
as result set.
I experimented with
  A = a test which creates a second session of user root
      but does not disconnect at end of test
  B = Duplicate of processlist_priv_ps copied to
      mysql-test/t
a) ./mysql-test-run.pl --ps-protocol A B A B A B ....
   and a parallel
   root: dd if=/dev/<my disk> of=/dev/null
   which causes high disk I/O load
and
b) ./mysql-test-run.pl --mem --ps-protocol A B A B A B ....
and was unable to reproduce the problem on my Linux box.
This means it does not look like whatever previous
test which does an imperfect cleanup (=forgets to
disconnect some additional session) causes the observed
effect.
Summary:
- The second bad effect is not caused by a weakness
  within the current test.
- Experiments to replay this effect failed
Speculations:
Maybe
- the OS mentioned behave different (very unlikely)
- an additional mysqltest/mysql process
  - intended for observation of this server but
    unfortunately forgotten by the reporter
  - intended for observation of a different server
    and connected from whatever reason (tool
    failure?) to the wrong server
  disturbs the current test
  But why is it in state "Quit" (ok, amount of data
  is not sufficient for good statistics)?

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52219

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52231

Ok to push.
Tested on Mac OSX 10.5 and Windows XP 32 bit.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52456

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52468

Pushed to
5.1-bugteam
6.0-bugeam

Pushed into 6.0.7-alpha  (revid:mleich@mysql.com-20080825132311-h707dil785ggs720) (version source revid:sven@mysql.com-20080818195835-r615g9zz6xphmkzg) (pib:3)

Test case changes. No changelog entry needed, but setting to NDI pending push into 5.1.x.

Pushed into 5.1.28  (revid:mleich@mysql.com-20080825132311-h707dil785ggs720) (version source revid:mleich@mysql.com-20080825132311-h707dil785ggs720) (pib:4)

Test case changes. No changelog entry needed.

Pushed into 5.1.29-ndb-6.2.17  (revid:mleich@mysql.com-20080825132311-h707dil785ggs720) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)

Pushed into 5.1.29-ndb-6.3.19  (revid:mleich@mysql.com-20080825132311-h707dil785ggs720) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)

Pushed into 5.1.29-ndb-6.4.0  (revid:mleich@mysql.com-20080825132311-h707dil785ggs720) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)