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.dimov | 27 Jun |
