bzr commit into mysql-5.1-bugteam branch (horst:2758) Bug#39382

[Horst Hunger]

#At file:///work/bzr/mysql-5.1-39382/

 2758 Horst Hunger	2009-01-29
      Fix for bug#39382: Replaced sleep by wait condition, inserted some comments and
beautyfications.
modified:
  mysql-test/r/innodb_max_dirty_pages_pct_func.result
  mysql-test/t/innodb_max_dirty_pages_pct_func.test

=== modified file 'mysql-test/r/innodb_max_dirty_pages_pct_func.result'
--- a/mysql-test/r/innodb_max_dirty_pages_pct_func.result	2008-04-10 13:14:28 +0000
+++ b/mysql-test/r/innodb_max_dirty_pages_pct_func.result	2009-01-29 18:35:03 +0000
@@ -19,8 +19,7 @@ FLUSH STATUS;
 CALL add_until(10);
 FLUSH TABLES;
 CALL add_records(500);
-'--sleep 5'
-'We expect dirty pages pct to be BELOW_MAX'
+'We expect dirty pages pct to be BELOW_MAX after some time depending on performance' 
 CALL check_pct(10);
 PCT_VALUE
 BELOW_MAX

=== modified file 'mysql-test/t/innodb_max_dirty_pages_pct_func.test'
--- a/mysql-test/t/innodb_max_dirty_pages_pct_func.test	2008-04-10 13:14:28 +0000
+++ b/mysql-test/t/innodb_max_dirty_pages_pct_func.test	2009-01-29 18:35:03 +0000
@@ -10,20 +10,23 @@
 #                                                                             #
 # Creation Date: 2008-03-08                                                   #
 # Author:  Rizwan                                                             #
+# Modified: HHunger 2009-01-29 Fix for bug#39382, replaced sleep by wait cond.#
+#                              added comments, beautifications.               #
 #                                                                             #
 #Description: Test Cases of Dynamic System Variable innodb_max_dirty_pages_pct#
 #             that checks the behavior of this variable                       #
 #                                                                             #
-# Reference: http://dev.mysql.com/doc/refman/5.1/en/                          #
-#  server-system-variables.html                                               #
+# Reference:                                                                  #
+# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html         #
 #                                                                             #
 ###############################################################################
 
 --source include/have_innodb.inc
+
 --echo '#--------------------FN_DYNVARS_044_02-------------------------#'
-###########################################################################
-# Check if setting innodb_max_dirty_pages_pct is changed in new connection# 
-###########################################################################
+############################################################################
+# Check if setting innodb_max_dirty_pages_pct is changed in new connection #
+############################################################################
 
 SET @@global.innodb_max_dirty_pages_pct = 80;
 --echo 'connect (con1,localhost,root,,,,)'
@@ -59,12 +62,12 @@ DROP TABLE IF EXISTS t1;
 --enable_warnings
 
 DELIMITER //;
-CREATE PROCEDURE add_records(IN NUM INT)
+CREATE PROCEDURE add_records(IN num INT)
 BEGIN
    START TRANSACTION;
-   WHILE (NUM>0) DO
+   WHILE (num > 0) DO
       INSERT INTO t1(b) VALUES('MYSQL');
-      SET NUM = NUM - 1;
+      SET num = num - 1;
    END WHILE;
    COMMIT;
 END//
@@ -74,9 +77,9 @@ BEGIN
   DECLARE res DECIMAL(20,17);
   DECLARE a1,b1 VARCHAR(256);
   DECLARE a2,b2 VARCHAR(256);
-  DECLARE dirty CURSOR FOR SELECT * FROM information_schema.global_status 
+  DECLARE dirty CURSOR FOR SELECT * FROM information_schema.global_status
                 WHERE variable_name LIKE 'Innodb_buffer_pool_pages_dirty'
-                     UNION SELECT * FROM information_schema.session_status 
+                     UNION SELECT * FROM information_schema.session_status
                 WHERE variable_name LIKE 'Innodb_buffer_pool_pages_dirty';
   DECLARE total CURSOR FOR SELECT * FROM information_schema.global_status
                 WHERE variable_name LIKE 'Innodb_buffer_pool_pages_total'
@@ -89,33 +92,32 @@ BEGIN
   FETCH dirty INTO a1, b1;
   FETCH total INTO a2, b2;
   
-  SET res = ( CONVERT(b1,DECIMAL)*100)/CONVERT(b2,DECIMAL);
+  SET res = (CONVERT(b1,DECIMAL)*100)/CONVERT(b2,DECIMAL);
 
   CLOSE dirty;
   CLOSE total;
   RETURN res;
 END//
 
-CREATE PROCEDURE add_until(IN NUM DECIMAL)
+CREATE PROCEDURE add_until(IN num DECIMAL)
 BEGIN
    DECLARE pct,last DECIMAL(20,17);
-
    SET pct = dirty_pct();
    SET last = 0;
-   WHILE (pct<NUM and pct<100) DO
+   WHILE (pct < num AND pct < 100) DO
       CALL add_records(500);
       SET pct = dirty_pct();
-      IF (pct<last) THEN
-         SET pct = NUM+1;
+      IF (pct < last) THEN
+         SET pct = num+1;
       ELSE
          SET last = pct;
       END IF;
    END WHILE;
 END//
 
-CREATE PROCEDURE check_pct(IN NUM DECIMAL)
+CREATE PROCEDURE check_pct(IN num DECIMAL)
 BEGIN
-   IF (dirty_pct() < NUM) THEN
+   IF (dirty_pct() < num) THEN
       SELECT 'BELOW_MAX' AS PCT_VALUE;
    ELSE
       SELECT 'ABOVE_MAX' AS PCT_VALUE;
@@ -139,16 +141,20 @@ SET @@global.innodb_max_dirty_pages_pct 
 
 FLUSH STATUS;
 
-# add rows until dirty pages pct is less than this value
+# add rows until dirty pages pct is about @@global.innodb_max_dirty_pages_pc
 CALL add_until(10);
 
 # give server some time to flush dirty pages
 FLUSH TABLES;
+
+# Add more pages to be over @@global.innodb_max_dirty_pages_pc
 CALL add_records(500);
---echo '--sleep 5'
---sleep 5
 
---echo 'We expect dirty pages pct to be BELOW_MAX'
+# Give server time to write pages to disk (depends on performance of the system)
+let $wait_condition= SELECT (dirty_pct() <= @@global.innodb_max_dirty_pages_pct);
+--source include/wait_condition.inc
+
+--echo 'We expect dirty pages pct to be BELOW_MAX after some time depending on
performance'
 CALL check_pct(10);
 
 #SHOW STATUS LIKE 'innodb%';
@@ -157,6 +163,7 @@ DROP PROCEDURE add_records;
 DROP PROCEDURE add_until;
 DROP PROCEDURE check_pct;
 DROP FUNCTION dirty_pct;
+DROP TABLE t1;
 
 ##################################################################
 # End of functionality Testing for innodb_max_dirty_pages_pct    #