Experiments around
Bug#40179 Test main.query_cache failing randomly on Pushbuild
=============================================================

query_cache (QC on):
-----------------------------
I was unable to replay the problem, but Patrick was
successful on his box with MySQL for Win in a VM.


ml_qc2 (Just the failing subtest, QC on):
-----------------------------------------
I was unable to replay the problem


ml_qc3 (based on ml_qc2, poll routine instead of sleep 5, QC on):
-----------------------------------------------------------------
The replacement of the "sleep 5" by a poll routine makes the comment
--echo Next select should contain 4 rows, as the insert is long finished.
of course plain wrong. There is now a high risk to get concurrent_inserts
effects.

./mysql-test-run.pl --force  --mem `perl -e 'for (1 .. 100) { print "ml_qc3 "}'`
Failed 51/100 tests, 49.00% were successful.

Typical differences:
--- /work2/5.1/mysql-5.1-bugteam-work1/mysql-test/r/ml_qc3.result
+++ /work2/5.1/mysql-5.1-bugteam-work1/mysql-test/r/ml_qc3.reject
@@ -24,7 +24,6 @@
 1      0
 2      0
 3      0
-4      0
 reset query cache;
 select *, (select count(*) from t2) from t1;
 a      (select count(*) from t2)

@@ -24,12 +24,10 @@
 1      0
 2      0
 3      0
-4      0
 reset query cache;
 select *, (select count(*) from t2) from t1;
 a      (select count(*) from t2)
 1      0
 2      0
 3      0
-4      0
 drop table t1,t2;


ml_qc4 (improved ml_qc3, QC is off):
------------------------------------
- Beautify the statements and comments
- Ensure that concurrent_inserts effects are avoided.
  Typical effect of concurrent_inserts
     session1: SELECT * FROM t1 --> result_before_insert
     session1: INSERT INTO t1 ...
     session2: SELECT * FROM t1 --> result_before_insert
     wait sufficient time (no activity on t1)
     session2: SELECT * FROM t1 --> result_after_insert = see the inserted row
  IMHO two theoretic not acceptable "concurrent_inserts" effects would be:
  1. session1: SELECT * FROM t1 --> result_before_insert
     session1: INSERT INTO t1 ...
     session1: SELECT * FROM t1 --> result_before_insert
  2. session1: SELECT * FROM t1 --> result_before_insert
     session1: INSERT INTO t1 ...
     session2: SELECT * FROM t1 --> result_after_insert = see the inserted row
     any session: SELECT * FROM t1 --> result_before_insert
  I made a lot experiments around "concurrent_inserts" and never found
  such bad effects.
  It looks like that the last select in
     session1: SELECT * FROM t1
     session1: INSERT INTO t1 ...
     session1: SELECT * FROM t1
  enforces that starting at this point of time any session
  could see the inserted row.
  I exploit this behaviour since some time in tests because it is very comfortable
  compared to temporary switching concurrent_inserts off and later on.

./mysql-test-run.pl --force  --mem `perl -e 'for (1 .. 1000) { print "ml_qc4 "}'`
All 1000 tests were successful.

Maybe I/O load is able to show remaining weaknesses.
rm var
./mysql-test-run.pl --force  `perl -e 'for (1 .. 1000) { print "ml_qc4 "}'`
All 1000 tests were successful.

Ok, lets increase the load.
Root: dd if=/dev/mapper/system-WORK2 of=/dev/null
      My vardir is on this disk.
wait some seconds
./mysql-test-run.pl --force  `perl -e 'for (1 .. 300) { print "ml_qc4 "}'`
All 300 tests were successful.


ml_qc5 (based on ml_qc4 but QC is on):
--------------------------------------
./mysql-test-run.pl --force  --mem `perl -e 'for (1 .. 1000) { print "ml_qc5 "}'`
Failed 807/1000 tests, 19.30% were successful.

Typical differences:
--- /work2/5.1/mysql-5.1-bugteam-work1/mysql-test/r/ml_qc5.result
+++ /work2/5.1/mysql-5.1-bugteam-work1/mysql-test/r/ml_qc5.reject
@@ -42,7 +42,6 @@
 1      0
 2      0
 3      0
-4      0
 RESET QUERY CACHE;
 SELECT *, (SELECT COUNT(*) FROM t2) FROM t1;
 a      (SELECT COUNT(*) FROM t2)

This difference is a failure caused by using the QC.
IMHO a regression of
Bug#28249 Query Cache returns wrong result with concurrent insert / certain lock
!!


ml_qc6 (based on ml_qc5, MEMORY instead of MyISAM + adjustments for INSERT):
----------------------------------------------------------------------------
I am aware that MEMORY instead of MyISAM violates the requirements for
replaying
Bug#28249 Query Cache returns wrong result with concurrent insert / certain lock

./mysql-test-run.pl --force  --mem `perl -e 'for (1 .. 1000) { print "ml_qc6 "}'`
All 1000 tests were successful.


ml_qc7 (based on ml_qc5, concurrent_inserts off, adjustments for INSERT):
-------------------------------------------------------------------------
I am aware that concurrent_inserts off violates the requirements for
replaying
Bug#28249 Query Cache returns wrong result with concurrent insert / certain lock

./mysql-test-run.pl --force  --mem `perl -e 'for (1 .. 1000) { print "ml_qc7 "}'`
All 1000 tests were successful.


Matthias Leich, 2008-11-27
