MySQL Bugs: #37492: timing bug in subselect.test

Bug #37492	timing bug in subselect.test
Submitted:	18 Jun 2008 19:07	Modified:	10 Jul 2008 19:48
Reporter:	Timothy Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Tests	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, 6.0	OS:	Any
Assigned to:	Matthias Leich	CPU Architecture:	Any
Tags:	pbfail

Description:
A very rare timing bug in subselect.test due to --sleep 1 and REPLACE DELAYED interaction.

main.subselect_no_opts         [ fail ]

--- C:/cygwin/home/pushbuild/pb1/pb/mysql-6.0-build/187/mysql-6.0.6-alpha-pb187/mysql-test/r/subselect_no_opts.result	2008-05-29 08:11:23.000000000 +0300
+++ C:\cygwin\home\pushbuild\pb1\pb\mysql-6.0-build\187\mysql-6.0.6-alpha-pb187\mysql-test\r\subselect_no_opts.reject	2008-05-29 09:46:30.905625000 +0300
@@ -706,7 +706,7 @@
 select * from t1;
 x	y
 1	3
-4	2
+4	1
 replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
 select * from t1;
 x	y

mysqltest: Result content mismatch

Looking at the test case, it appears simple:

replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
-- sleep 1
select * from t1;

The --sleep 1 looks to be insufficient.

How to repeat:
This is the only example I find in PushBuild:

https://intranet.mysql.com/secure/pushbuild/xref.pl?startdate=&enddate=&dir=&plat=&testtyp...

Suggested fix:
Perhaps ignore this as too infrequent to care about.  Perhaps increase sleep time (not ideal, but easy).  Perhaps find some way to inspect the delayed insert queue (sounds too difficult).

I had a short look into the 5.0 version of this test
and it looks like that
- there are several places with the too optimistic
  assumption that the modifications made by a statement
  with DELAYED must be visible after a sleep time of
  1 second
  IMHO all should be fixed. It's just a question of time
  when the next weak subtest will fail on an overloaded box.
- most probably a wait routine checking if all expected
  changes are in place could be used.
- the test needs also some basic maintenance (replace
  error numbers by error names)

Minor weakness found during stability testing and also
to be fixed:
./mysql-test-run.pl `perl -e 'for (1 .. 20) { print "subselect "}'`
Logging: ./mysql-test-run.pl subselect subselect .....
.....
TEST                           RESULT         TIME (ms)
-------------------------------------------------------

subselect                      [ pass ]          19531
subselect                      [ fail ]

mysqltest: At line 1039: query 'select a, (select max(b) 
from t1) into outfile "subselect.out.file.1" from t1' failed:
 1086: File 'subselect.out.file.1' already exists

= The test does not remove this file after testing

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/48336

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/48382

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/48385

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/48418

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/48500

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/48507

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/48509

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/48510

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/48511

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/48512

Pushed to
mysql-5.0-bugteam
mysql-5.1-bugteam
mysql-6.0-bugteam
The fix removes weaknesses within the tests. There is
no documentation needed.

Pushed into 5.0.66

Pushed into 5.0.66

Pushed into 5.0.66

Pushed into 5.0.66

Pushed into 5.0.66

No changelog entry needed.

Pushed into 5.0.68  (revid:kpettersson@mysql.com-20080715152926-s5kgnqhtu93b3c8v) (pib:2)
(Retry automatic marking, to ensure nothing is missed. cm01)

Pushed into 5.1.27  (revid:joerg@mysql.com-20080626161101-h5hsaluwtf05xdr2) (version source revid:joerg@mysql.com-20080626161101-h5hsaluwtf05xdr2) (pib:3)