bk commit into 5.0 tree (pcrews:1.2623) BUG#36345

[pcrews]

Below is the list of changes that have just been committed into a local
5.0 repository of pcrews.  When pcrews 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-05-08 14:26:37-04:00, pcrews@stripped +2 -0
  Bug#36345 - Test 'func_misc' fials on RHAS3 x86_64
  
  Altered the test to incorporate qcache status variables as
  an extra check.
  
  Wanted to keep the check of actual SLEEP() performance to ensure
  no other optimizations affect this function.
  
  It was determined this bug was due to host timing issues, added
  a loop to provide the test 2 extra attempts to have a successful
  run.
  
  Will still output t2 contents in the event of a failure to assist
  with troubleshooting.

  mysql-test/r/func_misc.result@stripped, 2008-05-08 14:26:35-04:00,
pcrews@stripped +26 -11
    Bug#36345 - Test 'func_misc' fials on RHAS3 x86_64
    
    Altered the test to incorporate qcache status variables as
    an extra check.
    
    Wanted to keep the check of actual SLEEP() performance to ensure
    no other optimizations affect this function.
    
    It was determined this bug was due to host timing issues, added
    a loop to provide the test 2 extra attempts to have a successful
    run.
    
    Will still output t2 contents in the event of a failure to assist
    with troubleshooting.

  mysql-test/t/func_misc.test@stripped, 2008-05-08 14:26:35-04:00,
pcrews@stripped +47 -12
    Bug#36345 - Test 'func_misc' fials on RHAS3 x86_64
    
    Altered the test to incorporate qcache status variables as
    an extra check.
    
    Wanted to keep the check of actual SLEEP() performance to ensure
    no other optimizations affect this function.
    
    It was determined this bug was due to host timing issues, added
    a loop to provide the test 2 extra attempts to have a successful
    run.
    
    Will still output t2 contents in the event of a failure to assist
    with troubleshooting.

diff -Nrup a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result
--- a/mysql-test/r/func_misc.result	2008-03-15 13:51:30 -04:00
+++ b/mysql-test/r/func_misc.result	2008-05-08 14:26:35 -04:00
@@ -111,26 +111,41 @@ timediff(b, a) >= '00:00:03'
 1
 drop table t1;
 set global query_cache_size=1355776;
+RESET QUERY CACHE;
+Should be 0 queries in cache
+SHOW STATUS WHERE Variable_name in ('Qcache_inserts','Qcache_not_cached',
+'Qcache_queries_in_cache');
+Variable_name	Value
+Qcache_inserts	0
+Qcache_not_cached	1
+Qcache_queries_in_cache	0
 create table t1 (a int);
 insert into t1 values (1),(1),(1);
 create table t2 (a datetime default null, b datetime default null);
-insert into t2 set a = now();
-select a from t1 where sleep(1);
+INSERT INTO t2 SET a = NOW();
+SELECT a FROM t1 WHERE SLEEP(1);
 a
-update t2 set b = now() where b is null;
-insert into t2 set a = now();
-select a from t1 where sleep(a);
+UPDATE t2 SET b = NOW() WHERE b IS NULL;
+INSERT INTO t2 SET a = NOW();
+SELECT a FROM t1 WHERE SLEEP(1);
 a
-update t2 set b = now() where b is null;
-insert into t2 set a = now();
-select a from t1 where sleep(1);
+UPDATE t2 SET b = NOW() WHERE b IS NULL;
+INSERT INTO t2 SET a = NOW();
+SELECT a FROM t1 WHERE SLEEP(1);
 a
-update t2 set b = now() where b is null;
-select timediff(b, a) >= '00:00:03' from t2;
-timediff(b, a) >= '00:00:03'
+UPDATE t2 SET b = NOW() WHERE b IS NULL;
+SELECT TIMEDIFF(b, a) >= '00:00:03' FROM t2;
+TIMEDIFF(b, a) >= '00:00:03'
 1
 1
 1
+queries_in_cache should = 1 after last SELECT
+SHOW STATUS WHERE Variable_name in ('Qcache_inserts','Qcache_not_cached',
+'Qcache_queries_in_cache');
+Variable_name	Value
+Qcache_inserts	1
+Qcache_not_cached	8
+Qcache_queries_in_cache	1
 drop table t2;
 drop table t1;
 set global query_cache_size=default;
diff -Nrup a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test
--- a/mysql-test/t/func_misc.test	2008-03-15 13:51:30 -04:00
+++ b/mysql-test/t/func_misc.test	2008-05-08 14:26:35 -04:00
@@ -104,23 +104,58 @@ update t1 set b = now();
 select timediff(b, a) >= '00:00:03' from t1;
 drop table t1;
 
-#
+################################################################################
 # Bug #12689: SLEEP() gets incorrectly cached/optimized-away
-#
+################################################################################
 set global query_cache_size=1355776;
+RESET QUERY CACHE;
+--echo Should be 0 queries in cache
+SHOW STATUS WHERE Variable_name in ('Qcache_inserts','Qcache_not_cached',
+'Qcache_queries_in_cache');
 create table t1 (a int);
 insert into t1 values (1),(1),(1);
 create table t2 (a datetime default null, b datetime default null);
-insert into t2 set a = now();
-select a from t1 where sleep(1);
-update t2 set b = now() where b is null;
-insert into t2 set a = now();
-select a from t1 where sleep(a);
-update t2 set b = now() where b is null;
-insert into t2 set a = now();
-select a from t1 where sleep(1);
-update t2 set b = now() where b is null;
-select timediff(b, a) >= '00:00:03' from t2;
+################################################################################
+# Fix for Bug#36345 - Test 'func_misc' fails on RHAS x86_64
+# Bug occurs due to mysql host timing issues
+# Give the test some extra attempts to work to accommodate timing issues
+# Clears out 'bad' rows from timing, but only give two extra chances to 
+# Have a 'good' run
+################################################################################
+# Maximum number of times to work for a good test
+let $max_runs = 5;
+let $good_count = 3;
+while($max_runs)
+{
+     INSERT INTO t2 SET a = NOW();
+     SELECT a FROM t1 WHERE SLEEP(1);
+     UPDATE t2 SET b = NOW() WHERE b IS NULL;
+     # remove any bad rows due to changes in host timing
+     --disable_query_log
+     DELETE FROM t2 WHERE TIMEDIFF(b,a) < '00:00:03';
+     let $done = `SELECT SQL_NO_CACHE count(a) = $good_count from t2`;
+     if($done)
+     {
+          let $max_runs = 1;
+     }
+     --enable_query_log
+     dec $max_runs;
+}
+SELECT TIMEDIFF(b, a) >= '00:00:03' FROM t2;
+--echo queries_in_cache should = 1 after last SELECT
+SHOW STATUS WHERE Variable_name in ('Qcache_inserts','Qcache_not_cached',
+'Qcache_queries_in_cache');
+# Output more information for troubleshooting in the event of a failure
+let $min_diff = `SELECT MIN(TIMEDIFF(b,a) >= '00:00:03') FROM t2`;
+if(!$min_diff)
+{
+    --echo BUG#36345 - Test 'func_misc' fails on RHAS3 x86_64
+    --echo on error, output table for troubleshooting
+    --echo this occurs due to MySQL host timing issues
+    --echo == t2 Contents ==
+    select * from t2;
+}
+
 drop table t2;
 drop table t1;
 set global query_cache_size=default;