List:Commits« Previous MessageNext Message »
From:vasil.dimov Date:June 27 2012 12:00pm
Subject:bzr push into mysql-trunk branch (vasil.dimov:3910 to 3911)
View as plain text  
 3911 Vasil Dimov	2012-06-27
      Eliminate nondeterminism in innodb-system-table-view.test caused
      by varying order of rows in the output of some SELECTS.
      
      Use mtr's --sorted_result to always have the output in the same order.
      
      This patch is based on a patch which Sunny posted on IM, but uses
      --sorted_result instead of ORDER BY.

    modified:
      mysql-test/suite/innodb/r/innodb-system-table-view.result
      mysql-test/suite/innodb/t/innodb-system-table-view.test
 3910 Olav Sandstaa	2012-06-27
      Fix for Bug#14185642 - WL#6082: FAILURES ON TRUNK
      
      Test failures occurring when running the tests with the non-default 4K
      page size for InnoDB. The tests fail due to changes in the "disk sweep
      cost model" for reading data from disk introduced in WL#6082. The disk
      sweep cost is influenced by the number of blocks used for storing the
      table which can be different when using 4K page size compared to 16K
      page size.
      
      opt_trace.bugs_no_prot_all:
      ===========================
      
      Cause: this test fails due to the cost estimate in the optimizer trace
             is slightly different due to change in "file size" when using
             4K blocks instead of 16K blocks.
      
      Fix: This is an optimizer trace test. Fixes this problem by only running
           it with the default 16K block size for InnoDB.
      
      innodb_explain_json_non_select_all, innodb_explain_non_select_none,
      innodb_explain_json_non_select_none, nnodb_explain_non_select_all:
      ==================================================================
      
      Cause: These tests fail due to change in execution plan between 16K
      and 4K InnoDB page sizes. For 16 page size the failing query is done
      as a table scan while for 4K page size it is done as an index-merge.
      Before WL#6082 it would use a table scan in both cases. The reason for
      the change to do index-merge for the 4K case is that the size of the
      table is smaller when using 4K pages compared to using 16K pages and
      thus the cost for doing index merge is now lower.
      
      Fix: We need to have the same query plan for both 4K and 16K pages
      (since the test has the same result file). For the same tests when run
      with MyISAM the queries are done using index-merge. So instead of
      "forcing" the 4K InnoDB test to run as a "table scan", we change the
      default/16K InnoDB test to run using index-merge by adding more
      records to the table. This increases the cost of doing a full table
      scan and makes index-merge cheaper than a table scan in both 16K and
      4K test case.
     @ mysql-test/collections/default.experimental
        Remove tests from being experimental after fixing Bug#14185642.
     @ mysql-test/include/explain_non_select.inc
        Bug#14185642: Add more records to the test table for test case 35 and 45
        in order to make the query use index-merge for InnoDB when running the
        test with both 16K and 4K page size. Before this change it would
        use a full table scan when running with 16K page size.
     @ mysql-test/r/innodb_explain_json_non_select_all.result
        Bug#14185642: Plan changes after having incresed the size of the table.
        The query will now be run using index merge instead of table scan also
        when using 16 page size.
     @ mysql-test/r/innodb_explain_json_non_select_none.result
        Bug#14185642: Plan changes after having incresed the size of the table.
        The query will now be run using index merge instead of table scan also
        when using 16 page size.
     @ mysql-test/r/innodb_explain_non_select_all.result
        Bug#14185642: Plan changes after having incresed the size of the table.
        The query will now be run using index merge instead of table scan also
        when using 16 page size.
     @ mysql-test/r/innodb_explain_non_select_none.result
        Bug#14185642: Plan changes after having incresed the size of the table.
        The query will now be run using index merge instead of table scan also
        when using 16 page size.
     @ mysql-test/r/myisam_explain_json_non_select_all.result
        Bug#14185642: Change in result file after adding more records in the
        test table.
     @ mysql-test/r/myisam_explain_json_non_select_none.result
        Bug#14185642: Change in result file after adding more records in the
        test table.
     @ mysql-test/r/myisam_explain_non_select_all.result
        Bug#14185642: Change in result file after adding more records in the
        test table.
     @ mysql-test/r/myisam_explain_non_select_none.result
        Bug#14185642: Change in result file after adding more records in the
        test table.
     @ mysql-test/suite/opt_trace/include/bugs.inc
        Bug#14185642: Only run this test when using default page size for 
        InnoDB. With non-default page size cost numbers in the optimizer trace
        will be different.

    modified:
      mysql-test/collections/default.experimental
      mysql-test/include/explain_non_select.inc
      mysql-test/r/innodb_explain_json_non_select_all.result
      mysql-test/r/innodb_explain_json_non_select_none.result
      mysql-test/r/innodb_explain_non_select_all.result
      mysql-test/r/innodb_explain_non_select_none.result
      mysql-test/r/myisam_explain_json_non_select_all.result
      mysql-test/r/myisam_explain_json_non_select_none.result
      mysql-test/r/myisam_explain_non_select_all.result
      mysql-test/r/myisam_explain_non_select_none.result
      mysql-test/suite/opt_trace/include/bugs.inc
=== modified file 'mysql-test/suite/innodb/r/innodb-system-table-view.result'
--- a/mysql-test/suite/innodb/r/innodb-system-table-view.result	revid:olav.sandstaa@stripped
+++ b/mysql-test/suite/innodb/r/innodb-system-table-view.result	revid:vasil.dimov@stripped
@@ -48,32 +48,32 @@ CREATE TABLE t_compressed (a INT KEY, b 
 CREATE TABLE t_dynamic (a INT KEY, b TEXT) ROW_FORMAT=DYNAMIC ENGINE=innodb;
 SELECT name, n_cols, file_format, row_format
 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
-WHERE space > 0 ORDER BY table_id;
+WHERE space > 0;
 name	n_cols	file_format	row_format
-mysql/innodb_table_stats	9	Antelope	Compact
 mysql/innodb_index_stats	11	Antelope	Compact
-mysql/slave_relay_log_info	11	Antelope	Compact
+mysql/innodb_table_stats	9	Antelope	Compact
 mysql/slave_master_info	27	Antelope	Compact
+mysql/slave_relay_log_info	11	Antelope	Compact
 mysql/slave_worker_info	16	Antelope	Compact
-test/t_redundant	5	Antelope	Redundant
 test/t_compact	5	Antelope	Compact
 test/t_compressed	5	Barracuda	Compressed
 test/t_dynamic	5	Barracuda	Dynamic
+test/t_redundant	5	Antelope	Redundant
 SELECT name, file_format, row_format
 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES
-WHERE name LIKE 'test%' ORDER BY space;
+WHERE name LIKE 'test%';
 name	file_format	row_format
-test/t_redundant	Antelope	Compact or Redundant
 test/t_compact	Antelope	Compact or Redundant
 test/t_compressed	Barracuda	Compressed
 test/t_dynamic	Barracuda	Dynamic
+test/t_redundant	Antelope	Compact or Redundant
 SELECT path FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES
-WHERE path LIKE '%test%' ORDER BY space;
+WHERE path LIKE '%test%';
 path
-MYSQLD_DATADIR/test/t_redundant.ibd
 MYSQLD_DATADIR/test/t_compact.ibd
 MYSQLD_DATADIR/test/t_compressed.ibd
 MYSQLD_DATADIR/test/t_dynamic.ibd
+MYSQLD_DATADIR/test/t_redundant.ibd
 DROP TABLE t_redundant, t_compact, t_compressed, t_dynamic;
 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS;
 count(*)

=== modified file 'mysql-test/suite/innodb/t/innodb-system-table-view.test'
--- a/mysql-test/suite/innodb/t/innodb-system-table-view.test	revid:olav.sandstaa@stripped
+++ b/mysql-test/suite/innodb/t/innodb-system-table-view.test	revid:vasil.dimov@stripped
@@ -13,10 +13,10 @@ LET $innodb_file_per_table_orig=`select 
 --enable_query_log
 
 # The IDs of mysql.innodb_table_stats and mysql.innodb_index_stats are
-# unpredictable, probably they on whether mtr has created the database for
-# this test from scratch or is using a previously created database where
-# those tables have been dropped and recreated. If we can force mtr to
-# use a freshly created database for this test then the following
+# unpredictable, probably they depend on whether mtr has created the
+# database for this test from scratch or is using a previously created
+# database where those tables have been dropped and recreated. If we can
+# force mtr to use a freshly created database for this test then the following
 # complications can be removed and the test be reverted to the version
 # it was before the patch that adds this comment.
 --let $table_stats_id = `SELECT table_id FROM information_schema.innodb_sys_tables WHERE name = 'mysql/innodb_table_stats'`
@@ -39,8 +39,10 @@ SELECT index_id,pos,name FROM INFORMATIO
        WHERE name NOT IN ('database_name', 'table_name', 'index_name', 'stat_name', 'Master_Id', 'Worker_Id')
        ORDER BY index_id, pos;
 
+--sorted_result
 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
 
+--sorted_result
 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
 
 SET GLOBAL innodb_file_format=`Barracuda`;
@@ -55,15 +57,18 @@ CREATE TABLE t_compact (a INT KEY, b TEX
 CREATE TABLE t_compressed (a INT KEY, b TEXT) ROW_FORMAT=COMPRESSED ENGINE=innodb;
 CREATE TABLE t_dynamic (a INT KEY, b TEXT) ROW_FORMAT=DYNAMIC ENGINE=innodb;
 
+--sorted_result
 SELECT name, n_cols, file_format, row_format
        FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
-       WHERE space > 0 ORDER BY table_id;
+       WHERE space > 0;
+--sorted_result
 SELECT name, file_format, row_format
        FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES
-       WHERE name LIKE 'test%' ORDER BY space;
+       WHERE name LIKE 'test%';
 --replace_result  ./ MYSQLD_DATADIR/  $MYSQLD_DATADIR MYSQLD_DATADIR
+--sorted_result
 SELECT path FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES
-       WHERE path LIKE '%test%' ORDER BY space;
+       WHERE path LIKE '%test%';
 
 DROP TABLE t_redundant, t_compact, t_compressed, t_dynamic;
 --disable_query_log
@@ -85,32 +90,39 @@ CREATE TABLE child (id INT, parent_id IN
                     FOREIGN KEY (parent_id) REFERENCES parent(id)
                       ON DELETE CASCADE) ENGINE=INNODB;
 
+--sorted_result
 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
 
+--sorted_result
 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
 
 # Insert a row in the table "parent", and see whether that reflected in
 # INNODB_SYS_TABLESTATS
 INSERT INTO parent VALUES(1);
 
+--sorted_result
 SELECT name, num_rows, ref_count 
 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
 WHERE name LIKE "%parent";
 
+--sorted_result
 SELECT NAME, FLAG, N_COLS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES;
 
+--sorted_result
 SELECT name, n_fields
 from INFORMATION_SCHEMA.INNODB_SYS_INDEXES
 WHERE table_id In (SELECT table_id from
 	INFORMATION_SCHEMA.INNODB_SYS_TABLES
 	WHERE name LIKE "%parent%");
 
+--sorted_result
 SELECT name, n_fields
 from INFORMATION_SCHEMA.INNODB_SYS_INDEXES
 WHERE table_id In (SELECT table_id from
 	INFORMATION_SCHEMA.INNODB_SYS_TABLES
 	WHERE name LIKE "%child%");
 
+--sorted_result
 SELECT name, pos, mtype, len
 from INFORMATION_SCHEMA.INNODB_SYS_COLUMNS
 WHERE table_id In (SELECT table_id from
@@ -131,15 +143,19 @@ CREATE TABLE child (id INT, parent_id IN
                     FOREIGN KEY (id, parent_id) REFERENCES parent(id, newid)
                       ON DELETE CASCADE) ENGINE=INNODB;
 
+--sorted_result
 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
 
+--sorted_result
 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
 
 INSERT INTO parent VALUES(1, 9);
 
 # Nested query will open the table handle twice
+--sorted_result
 SELECT * FROM parent WHERE id IN (SELECT id FROM parent);
 
+--sorted_result
 SELECT name, num_rows, ref_count 
 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
 WHERE name LIKE "%parent";

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (vasil.dimov:3910 to 3911) vasil.dimov27 Jun