diff options
| author | Alexander Barkov <bar@mariadb.org> | 2017-10-31 13:00:20 +0400 |
|---|---|---|
| committer | Alexander Barkov <bar@mariadb.org> | 2017-10-31 13:00:20 +0400 |
| commit | 5d3ed9acdda80828f7f3be8f7322b3e8366eab5f (patch) | |
| tree | f2b0c44e9429a0aaf0d4af63c9a66ae870b2ff35 | |
| parent | 835cbbcc7b797188a89671019f2b2844e1a14e0c (diff) | |
| download | mariadb-git-5d3ed9acdda80828f7f3be8f7322b3e8366eab5f.tar.gz | |
(Part#2) MDEV-13049 Querying INFORMATION_SCHEMA becomes slow in MariaDB 10.1
This is a 10.3 specific part of MDEV-13049.
It disables automatic sorting for
"SELECT .. FROM INFORMATION_SCHEMA.{SCHEMATA|TABLES}"
and adjusts the affected tests accordingly.
64 files changed, 366 insertions, 212 deletions
diff --git a/mysql-test/include/mtr_check.sql b/mysql-test/include/mtr_check.sql index 89f9f66ae1d..f2c0b70e192 100644 --- a/mysql-test/include/mtr_check.sql +++ b/mysql-test/include/mtr_check.sql @@ -39,12 +39,14 @@ BEGIN -- Dump all databases, there should be none -- except those that was created during bootstrap - SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; + SELECT * FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY BINARY SCHEMA_NAME; -- and the mtr_wsrep_notify schema which is populated by the std_data/wsrep_notify.sh script -- and the suite/galera/t/galera_var_notify_cmd.test -- and the wsrep_schema schema that may be created by Galera - SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema'); + SELECT * FROM INFORMATION_SCHEMA.SCHEMATA + WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema') + ORDER BY BINARY SCHEMA_NAME; -- The test database should not contain any tables SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES diff --git a/mysql-test/r/compound.result b/mysql-test/r/compound.result index 49ad567a055..a54faff4562 100644 --- a/mysql-test/r/compound.result +++ b/mysql-test/r/compound.result @@ -112,7 +112,8 @@ select @a| /**/ while (select count(*) from information_schema.tables where table_schema='test') do select concat('drop table ', table_name) into @a -from information_schema.tables where table_schema='test' limit 1; +from information_schema.tables where table_schema='test' + order by table_name limit 1; select @a as 'executing:'; prepare dt from @a; execute dt; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 1c073881a9d..23fbfad09d4 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -586,8 +586,8 @@ select s1 from t1 where s1 in (select version from information_schema.tables) union select version from information_schema.tables; s1 -11 10 +11 drop table t1; SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets; Table Create Table @@ -770,11 +770,18 @@ table_name v2 v3 select column_name from information_schema.columns -where table_schema='test'; +where table_schema='test' and table_name='t4'; column_name f1 +select column_name from information_schema.columns +where table_schema='test' and table_name='v2'; +column_name Warnings: Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +select column_name from information_schema.columns +where table_schema='test' and table_name='v3'; +column_name +Warnings: Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them select index_name from information_schema.statistics where table_schema='test'; index_name @@ -839,7 +846,8 @@ drop view a2, a1; drop table t_crashme; select table_schema,table_name, column_name from information_schema.columns -where data_type = 'longtext' and table_schema != 'performance_schema'; +where data_type = 'longtext' and table_schema != 'performance_schema' +order by binary table_name, ordinal_position; table_schema table_name column_name information_schema ALL_PLUGINS PLUGIN_DESCRIPTION information_schema COLUMNS COLUMN_DEFAULT @@ -860,7 +868,8 @@ information_schema TRIGGERS ACTION_CONDITION information_schema TRIGGERS ACTION_STATEMENT information_schema VIEWS VIEW_DEFINITION select table_name, column_name, data_type from information_schema.columns -where data_type = 'datetime' and table_name not like 'innodb_%'; +where data_type = 'datetime' and table_name not like 'innodb_%' +order by binary table_name, ordinal_position; table_name column_name data_type EVENTS EXECUTE_AT datetime EVENTS STARTS datetime @@ -1275,7 +1284,7 @@ sql security definer view v2 as select 1; connect con16681,localhost,mysqltest_1,,test; connection con16681; select * from information_schema.views -where table_name='v1' or table_name='v2'; +where table_name='v1' or table_name='v2' order by table_name; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM def test v1 NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED def test v2 select 1 AS `1` NONE NO mysqltest_1@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED @@ -1288,7 +1297,7 @@ set @a:= '.'; create table t1(f1 char(5)); create table t2(f1 char(5)); select concat(@a, table_name), @a, table_name -from information_schema.tables where table_schema = 'test'; +from information_schema.tables where table_schema = 'test' order by table_name; concat(@a, table_name) @a table_name .t1 . t1 .t2 . t2 @@ -1375,7 +1384,8 @@ create table t2 (f1 int(11), f2 int(11)); select table_name from information_schema.tables where table_schema = 'test' and table_name not in (select table_name from information_schema.columns -where table_schema = 'test' and column_name = 'f3'); +where table_schema = 'test' and column_name = 'f3') +order by table_name; table_name t1 t2 @@ -1384,7 +1394,7 @@ create table t1(f1 int); create view v1 as select f1+1 as a from t1; create table t2 (f1 int, f2 int); create view v2 as select f1+1 as a, f2 as b from t2; -select table_name, is_updatable from information_schema.views; +select table_name, is_updatable from information_schema.views order by table_name; table_name is_updatable v1 NO v2 YES @@ -1843,12 +1853,12 @@ rename table t2 to t3; connection default; # These statements should not be blocked by pending lock requests select table_name, column_name, data_type from information_schema.columns -where table_schema = 'test' and table_name in ('t1', 't2'); +where table_schema = 'test' and table_name in ('t1', 't2') order by table_name, column_name; table_name column_name data_type t1 i int t2 j int select table_name, auto_increment from information_schema.tables -where table_schema = 'test' and table_name in ('t1', 't2'); +where table_schema = 'test' and table_name in ('t1', 't2') order by table_name; table_name auto_increment t1 NULL t2 1 @@ -1997,7 +2007,7 @@ connect con12828477_2, localhost, root,,mysqltest; # Wait while the above RENAME is blocked. # Issue query to I_S which will open 't0' and get # blocked on 't1' because of RENAME. -select table_name, auto_increment from information_schema.tables where table_schema='mysqltest'; +select table_name, auto_increment from information_schema.tables where table_schema='mysqltest' and table_name='t0' union select table_name, auto_increment from information_schema.tables where table_schema='mysqltest' and table_name<>'t0' order by table_name; connect con12828477_3, localhost, root,,mysqltest; # Wait while the above SELECT is blocked. # diff --git a/mysql-test/r/information_schema2.result b/mysql-test/r/information_schema2.result index 7e9bdd7088f..e23e81b885c 100644 --- a/mysql-test/r/information_schema2.result +++ b/mysql-test/r/information_schema2.result @@ -11,7 +11,7 @@ create table t2 (x int); create table t3 (x int); create table t4 AS select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE' ; delete from t4 where table_name not in (select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE'); -select * from t4; +select * from t4 order by table_name; table_name t1 t2 diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index ca1ab45b98b..45ade65c502 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -72,14 +72,14 @@ create function f2 () returns int return (select max(i) from t2); create view v2 as select f2(); drop table t2; select table_name, table_type, table_comment from information_schema.tables -where table_schema='test'; +where table_schema='test' order by table_name; table_name table_type table_comment t1 BASE TABLE v1 VIEW VIEW v2 VIEW VIEW drop table t1; select table_name, table_type, table_comment from information_schema.tables -where table_schema='test'; +where table_schema='test' order by table_name; table_name table_type table_comment v1 VIEW VIEW v2 VIEW VIEW diff --git a/mysql-test/r/information_schema_inno.result b/mysql-test/r/information_schema_inno.result index 7755d112f8e..d952e4372ca 100644 --- a/mysql-test/r/information_schema_inno.result +++ b/mysql-test/r/information_schema_inno.result @@ -10,18 +10,18 @@ TABLE_SCHEMA= "test"; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE def test PRIMARY test t1 PRIMARY KEY def test PRIMARY test t2 PRIMARY KEY +def test PRIMARY test t3 PRIMARY KEY def test t2_ibfk_1 test t2 FOREIGN KEY def test t2_ibfk_2 test t2 FOREIGN KEY -def test PRIMARY test t3 PRIMARY KEY def test t3_ibfk_1 test t3 FOREIGN KEY select * from information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA= "test"; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME def test PRIMARY def test t1 id 1 NULL NULL NULL NULL def test PRIMARY def test t2 id 1 NULL NULL NULL NULL +def test PRIMARY def test t3 id 1 NULL NULL NULL NULL def test t2_ibfk_1 def test t2 t1_id 1 1 test t1 id def test t2_ibfk_2 def test t2 t1_id 1 1 test t1 id -def test PRIMARY def test t3 id 1 NULL NULL NULL NULL def test t3_ibfk_1 def test t3 id 1 1 test t2 t1_id def test t3_ibfk_1 def test t3 t2_id 2 2 test t2 id drop table t3, t2, t1; @@ -72,11 +72,12 @@ constraint fk_t1_1 foreign key (idtype) references `t-2` (id) use test; select referenced_table_schema, referenced_table_name from information_schema.key_column_usage -where constraint_schema = 'db-1'; +where constraint_schema = 'db-1' +order by referenced_table_schema, referenced_table_name; referenced_table_schema referenced_table_name NULL NULL -db-1 t-2 NULL NULL +db-1 t-2 drop database `db-1`; create table t1(id int primary key) engine = Innodb; create table t2(pid int, foreign key (pid) references t1(id)) engine = Innodb; diff --git a/mysql-test/r/information_schema_part.result b/mysql-test/r/information_schema_part.result index 081631a512f..4c0bb8908da 100644 --- a/mysql-test/r/information_schema_part.result +++ b/mysql-test/r/information_schema_part.result @@ -61,7 +61,7 @@ partition x2 values less than (5) ( subpartition x21 tablespace t1, subpartition x22 tablespace t2) ); -select * from information_schema.partitions where table_schema="test"; +select * from information_schema.partitions where table_schema="test" order by table_name, partition_name; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME def test t1 x1 x11 1 1 RANGE HASH `a` `a` + `b` 1 0 0 0 # 1024 0 # # NULL NULL default t1 def test t1 x1 x12 1 2 RANGE HASH `a` `a` + `b` 1 0 0 0 # 1024 0 # # NULL NULL default t2 diff --git a/mysql-test/r/mysql_upgrade_noengine.result b/mysql-test/r/mysql_upgrade_noengine.result index 09e705abb69..ef6657e0a0c 100644 --- a/mysql-test/r/mysql_upgrade_noengine.result +++ b/mysql-test/r/mysql_upgrade_noengine.result @@ -2,7 +2,7 @@ install soname 'ha_blackhole'; install soname 'ha_archive'; create table t1 (a int) engine=blackhole; create table t2 (a int) engine=archive; -select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; table_catalog def table_schema test table_name t1 @@ -12,6 +12,7 @@ row_format Fixed table_rows 0 data_length 0 table_comment +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t2'; table_catalog def table_schema test table_name t2 @@ -24,7 +25,7 @@ table_comment flush tables; uninstall plugin blackhole; uninstall plugin archive; -select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; table_catalog def table_schema test table_name t1 @@ -34,6 +35,11 @@ row_format NULL table_rows NULL data_length NULL table_comment Unknown storage engine 'BLACKHOLE' +Warnings: +Level Warning +Code 1286 +Message Unknown storage engine 'BLACKHOLE' +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t2'; table_catalog def table_schema test table_name t2 @@ -46,9 +52,6 @@ table_comment Unknown storage engine 'ARCHIVE' Warnings: Level Warning Code 1286 -Message Unknown storage engine 'BLACKHOLE' -Level Warning -Code 1286 Message Unknown storage engine 'ARCHIVE' Phase 1/7: Checking and upgrading mysql database Processing databases @@ -109,7 +112,7 @@ Error : Unknown storage engine 'ARCHIVE' error : Corrupt Phase 7/7: Running 'FLUSH PRIVILEGES' OK -select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; table_catalog def table_schema test table_name t1 @@ -119,6 +122,11 @@ row_format NULL table_rows NULL data_length NULL table_comment Unknown storage engine 'BLACKHOLE' +Warnings: +Level Warning +Code 1286 +Message Unknown storage engine 'BLACKHOLE' +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t2'; table_catalog def table_schema test table_name t2 @@ -131,9 +139,6 @@ table_comment Unknown storage engine 'ARCHIVE' Warnings: Level Warning Code 1286 -Message Unknown storage engine 'BLACKHOLE' -Level Warning -Code 1286 Message Unknown storage engine 'ARCHIVE' alter table mysql.user drop column default_role, drop column max_statement_time; Phase 1/7: Checking and upgrading mysql database @@ -195,7 +200,7 @@ Error : Unknown storage engine 'ARCHIVE' error : Corrupt Phase 7/7: Running 'FLUSH PRIVILEGES' OK -select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; table_catalog def table_schema test table_name t1 @@ -205,6 +210,11 @@ row_format NULL table_rows NULL data_length NULL table_comment Unknown storage engine 'BLACKHOLE' +Warnings: +Level Warning +Code 1286 +Message Unknown storage engine 'BLACKHOLE' +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t2'; table_catalog def table_schema test table_name t2 @@ -217,9 +227,6 @@ table_comment Unknown storage engine 'ARCHIVE' Warnings: Level Warning Code 1286 -Message Unknown storage engine 'BLACKHOLE' -Level Warning -Code 1286 Message Unknown storage engine 'ARCHIVE' alter table mysql.user drop column default_role, drop column max_statement_time; Phase 1/7: Checking and upgrading mysql database @@ -256,8 +263,8 @@ mysql.user OK Upgrading from a version before MariaDB-10.1 Phase 2/7: Installing used storage engines Checking for tables with unknown storage engine -installing plugin for 'blackhole' storage engine installing plugin for 'archive' storage engine +installing plugin for 'blackhole' storage engine Phase 3/7: Fixing views Phase 4/7: Running 'mysql_fix_privilege_tables' Phase 5/7: Fixing table and database names @@ -273,7 +280,7 @@ test.t1 OK test.t2 OK Phase 7/7: Running 'FLUSH PRIVILEGES' OK -select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; table_catalog def table_schema test table_name t1 @@ -283,6 +290,7 @@ row_format Fixed table_rows 0 data_length 0 table_comment +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t2'; table_catalog def table_schema test table_name t2 diff --git a/mysql-test/r/partition_exchange.result b/mysql-test/r/partition_exchange.result index f37ba183560..3bce5aec8e2 100644 --- a/mysql-test/r/partition_exchange.result +++ b/mysql-test/r/partition_exchange.result @@ -40,7 +40,8 @@ Create Table CREATE TABLE `t2` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2'); +WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') +ORDER BY TABLE_NAME; TABLE_NAME t1 TABLE_TYPE BASE TABLE ENGINE InnoDB @@ -76,7 +77,8 @@ Create Table CREATE TABLE `t2` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2'); +WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') +ORDER BY TABLE_NAME; TABLE_NAME t1 TABLE_TYPE BASE TABLE ENGINE InnoDB @@ -124,7 +126,8 @@ Create Table CREATE TABLE `t2` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2'); +WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') +ORDER BY TABLE_NAME; TABLE_NAME t1 TABLE_TYPE BASE TABLE ENGINE InnoDB @@ -184,7 +187,8 @@ Create Table CREATE TABLE `t2` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2'); +WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') +ORDER BY TABLE_NAME; TABLE_NAME t1 TABLE_TYPE BASE TABLE ENGINE InnoDB @@ -244,7 +248,8 @@ Create Table CREATE TABLE `t2` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2'); +WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') +ORDER BY TABLE_NAME; TABLE_NAME t1 TABLE_TYPE BASE TABLE ENGINE InnoDB @@ -304,7 +309,8 @@ Create Table CREATE TABLE `t2` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2'); +WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') +ORDER BY TABLE_NAME; TABLE_NAME t1 TABLE_TYPE BASE TABLE ENGINE InnoDB @@ -361,7 +367,8 @@ Create Table CREATE TABLE `t2` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2'); +WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') +ORDER BY TABLE_NAME; TABLE_NAME t1 TABLE_TYPE BASE TABLE ENGINE InnoDB @@ -418,7 +425,8 @@ Create Table CREATE TABLE `t2` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2'); +WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') +ORDER BY TABLE_NAME; TABLE_NAME t1 TABLE_TYPE BASE TABLE ENGINE InnoDB diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index cb2237699fb..e46f9b840ed 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -4810,8 +4810,8 @@ select routine_name,routine_schema from information_schema.routines where routine_schema like 'bug18344%'| routine_name routine_schema bug18344 bug18344_012345678901 -bug18344_2 bug18344_012345678901 bug18344 bug18344_0123456789012 +bug18344_2 bug18344_012345678901 bug18344_2 bug18344_0123456789012 drop database bug18344_012345678901| drop database bug18344_0123456789012| diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index a601dac5337..9631192da33 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -2800,8 +2800,8 @@ CREATE TABLE t1 (db VARCHAR(64) DEFAULT NULL); INSERT INTO t1 VALUES ('mysql'),('information_schema'); SELECT * FROM t1 WHERE db IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA); db -mysql information_schema +mysql DROP TABLE t1; # # MDEV-5581: Server crashes in in JOIN::prepare on 2nd execution of PS with materialization+semijoin diff --git a/mysql-test/suite/funcs_1/datadict/is_key_column_usage.inc b/mysql-test/suite/funcs_1/datadict/is_key_column_usage.inc index 71030a2b1d8..ba390dfb4f5 100644 --- a/mysql-test/suite/funcs_1/datadict/is_key_column_usage.inc +++ b/mysql-test/suite/funcs_1/datadict/is_key_column_usage.inc @@ -75,7 +75,8 @@ eval SHOW COLUMNS FROM information_schema.$is_table; SELECT constraint_catalog, constraint_schema, constraint_name, table_catalog, table_schema, table_name, column_name FROM information_schema.key_column_usage -WHERE constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL; +WHERE constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL +ORDER BY BINARY table_schema, BINARY table_name, BINARY column_name, BINARY constraint_name; --echo ######################################################################################## diff --git a/mysql-test/suite/funcs_1/datadict/is_schemata.inc b/mysql-test/suite/funcs_1/datadict/is_schemata.inc index 18c759b91d1..0a3f0e726b3 100644 --- a/mysql-test/suite/funcs_1/datadict/is_schemata.inc +++ b/mysql-test/suite/funcs_1/datadict/is_schemata.inc @@ -59,7 +59,8 @@ eval SHOW COLUMNS FROM information_schema.$is_table; # Show that CATALOG_NAME and SQL_PATH are always NULL. SELECT catalog_name, schema_name, sql_path FROM information_schema.schemata -WHERE catalog_name IS NOT NULL or sql_path IS NOT NULL; +WHERE catalog_name IS NOT NULL or sql_path IS NOT NULL +ORDER BY schema_name; --echo ############################################################################### diff --git a/mysql-test/suite/funcs_1/datadict/is_triggers.inc b/mysql-test/suite/funcs_1/datadict/is_triggers.inc index b2ce1e2de00..3a449c93c4b 100644 --- a/mysql-test/suite/funcs_1/datadict/is_triggers.inc +++ b/mysql-test/suite/funcs_1/datadict/is_triggers.inc @@ -85,7 +85,8 @@ eval SHOW COLUMNS FROM information_schema.$is_table; SELECT * FROM information_schema.triggers WHERE trigger_catalog IS NOT NULL OR event_object_catalog IS NOT NULL OR action_condition IS NOT NULL OR action_reference_old_table IS NOT NULL - OR action_reference_new_table IS NOT NULL; + OR action_reference_new_table IS NOT NULL +ORDER BY trigger_schema, trigger_name; --echo ################################################################################## diff --git a/mysql-test/suite/funcs_1/datadict/is_views.inc b/mysql-test/suite/funcs_1/datadict/is_views.inc index bdba03f3632..cb3444e308a 100644 --- a/mysql-test/suite/funcs_1/datadict/is_views.inc +++ b/mysql-test/suite/funcs_1/datadict/is_views.inc @@ -159,12 +159,14 @@ CREATE USER 'testuser1'@'localhost'; # Check just created VIEW SELECT * FROM information_schema.views -WHERE table_name LIKE 't1_%'; +WHERE table_name LIKE 't1_%' +ORDER BY table_schema, table_name; CREATE VIEW test.t1_view AS SELECT DISTINCT f1 FROM test.t1_table; CREATE ALGORITHM=MERGE VIEW test.t1_view1 AS SELECT f1 FROM test.t1_table; CREATE ALGORITHM=TEMPTABLE VIEW test.t1_view2 AS SELECT f1 FROM test.t1_table; SELECT * FROM information_schema.views -WHERE table_name LIKE 't1_%'; +WHERE table_name LIKE 't1_%' +ORDER BY table_schema, table_name; DROP VIEW test.t1_view1; DROP VIEW test.t1_view2; diff --git a/mysql-test/suite/funcs_1/r/is_basics_mixed.result b/mysql-test/suite/funcs_1/r/is_basics_mixed.result index 2d14ada4f89..f9468648c61 100644 --- a/mysql-test/suite/funcs_1/r/is_basics_mixed.result +++ b/mysql-test/suite/funcs_1/r/is_basics_mixed.result @@ -341,12 +341,14 @@ CREATE VIEW db_datadict.v2 AS SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE FROM information_schema.tables WHERE table_schema = 'db_datadict'; SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE -FROM db_datadict.v2; +FROM db_datadict.v2 +ORDER BY TABLE_NAME; TABLE_SCHEMA TABLE_NAME TABLE_TYPE db_datadict t1 BASE TABLE db_datadict v2 VIEW SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE -FROM information_schema.tables WHERE table_schema = 'db_datadict'; +FROM information_schema.tables WHERE table_schema = 'db_datadict' +ORDER BY TABLE_NAME; TABLE_SCHEMA TABLE_NAME TABLE_TYPE db_datadict t1 BASE TABLE db_datadict v2 VIEW diff --git a/mysql-test/suite/funcs_1/r/is_key_column_usage.result b/mysql-test/suite/funcs_1/r/is_key_column_usage.result index fa2a738788e..933a8e537f2 100644 --- a/mysql-test/suite/funcs_1/r/is_key_column_usage.result +++ b/mysql-test/suite/funcs_1/r/is_key_column_usage.result @@ -73,18 +73,19 @@ REFERENCED_COLUMN_NAME varchar(64) YES NULL SELECT constraint_catalog, constraint_schema, constraint_name, table_catalog, table_schema, table_name, column_name FROM information_schema.key_column_usage -WHERE constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL; +WHERE constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL +ORDER BY BINARY table_schema, BINARY table_name, BINARY column_name, BINARY constraint_name; constraint_catalog constraint_schema constraint_name table_catalog table_schema table_name column_name +def mysql PRIMARY def mysql column_stats column_name def mysql PRIMARY def mysql column_stats db_name def mysql PRIMARY def mysql column_stats table_name -def mysql PRIMARY def mysql column_stats column_name -def mysql PRIMARY def mysql columns_priv Host +def mysql PRIMARY def mysql columns_priv Column_name def mysql PRIMARY def mysql columns_priv Db -def mysql PRIMARY def mysql columns_priv User +def mysql PRIMARY def mysql columns_priv Host def mysql PRIMARY def mysql columns_priv Table_name -def mysql PRIMARY def mysql columns_priv Column_name -def mysql PRIMARY def mysql db Host +def mysql PRIMARY def mysql columns_priv User def mysql PRIMARY def mysql db Db +def mysql PRIMARY def mysql db Host def mysql PRIMARY def mysql db User def mysql PRIMARY def mysql event db def mysql PRIMARY def mysql event name @@ -99,41 +100,41 @@ def mysql PRIMARY def mysql help_relation help_keyword_id def mysql PRIMARY def mysql help_relation help_topic_id def mysql PRIMARY def mysql help_topic help_topic_id def mysql name def mysql help_topic name -def mysql PRIMARY def mysql host Host def mysql PRIMARY def mysql host Db +def mysql PRIMARY def mysql host Host def mysql PRIMARY def mysql index_stats db_name -def mysql PRIMARY def mysql index_stats table_name def mysql PRIMARY def mysql index_stats index_name def mysql PRIMARY def mysql index_stats prefix_arity +def mysql PRIMARY def mysql index_stats table_name def mysql PRIMARY def mysql innodb_index_stats database_name -def mysql PRIMARY def mysql innodb_index_stats table_name def mysql PRIMARY def mysql innodb_index_stats index_name def mysql PRIMARY def mysql innodb_index_stats stat_name +def mysql PRIMARY def mysql innodb_index_stats table_name def mysql PRIMARY def mysql innodb_table_stats database_name def mysql PRIMARY def mysql innodb_table_stats table_name def mysql PRIMARY def mysql plugin name def mysql PRIMARY def mysql proc db def mysql PRIMARY def mysql proc name def mysql PRIMARY def mysql proc type -def mysql PRIMARY def mysql procs_priv Host def mysql PRIMARY def mysql procs_priv Db -def mysql PRIMARY def mysql procs_priv User +def mysql PRIMARY def mysql procs_priv Host def mysql PRIMARY def mysql procs_priv Routine_name def mysql PRIMARY def mysql procs_priv Routine_type +def mysql PRIMARY def mysql procs_priv User def mysql PRIMARY def mysql proxies_priv Host -def mysql PRIMARY def mysql proxies_priv User def mysql PRIMARY def mysql proxies_priv Proxied_host def mysql PRIMARY def mysql proxies_priv Proxied_user +def mysql PRIMARY def mysql proxies_priv User def mysql Host def mysql roles_mapping Host -def mysql Host def mysql roles_mapping User def mysql Host def mysql roles_mapping Role +def mysql Host def mysql roles_mapping User def mysql PRIMARY def mysql servers Server_name def mysql PRIMARY def mysql table_stats db_name def mysql PRIMARY def mysql table_stats table_name -def mysql PRIMARY def mysql tables_priv Host def mysql PRIMARY def mysql tables_priv Db -def mysql PRIMARY def mysql tables_priv User +def mysql PRIMARY def mysql tables_priv Host def mysql PRIMARY def mysql tables_priv Table_name +def mysql PRIMARY def mysql tables_priv User def mysql PRIMARY def mysql time_zone Time_zone_id def mysql PRIMARY def mysql time_zone_leap_second Transition_time def mysql PRIMARY def mysql time_zone_name Name diff --git a/mysql-test/suite/funcs_1/r/is_key_column_usage_embedded.result b/mysql-test/suite/funcs_1/r/is_key_column_usage_embedded.result index 4a6114f3da3..62e566ca89f 100644 --- a/mysql-test/suite/funcs_1/r/is_key_column_usage_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_key_column_usage_embedded.result @@ -73,18 +73,19 @@ REFERENCED_COLUMN_NAME varchar(64) YES NULL SELECT constraint_catalog, constraint_schema, constraint_name, table_catalog, table_schema, table_name, column_name FROM information_schema.key_column_usage -WHERE constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL; +WHERE constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL +ORDER BY BINARY table_schema, BINARY table_name, BINARY column_name, BINARY constraint_name; constraint_catalog constraint_schema constraint_name table_catalog table_schema table_name column_name +def mysql PRIMARY def mysql column_stats column_name def mysql PRIMARY def mysql column_stats db_name def mysql PRIMARY def mysql column_stats table_name -def mysql PRIMARY def mysql column_stats column_name -def mysql PRIMARY def mysql columns_priv Host +def mysql PRIMARY def mysql columns_priv Column_name def mysql PRIMARY def mysql columns_priv Db -def mysql PRIMARY def mysql columns_priv User +def mysql PRIMARY def mysql columns_priv Host def mysql PRIMARY def mysql columns_priv Table_name -def mysql PRIMARY def mysql columns_priv Column_name -def mysql PRIMARY def mysql db Host +def mysql PRIMARY def mysql columns_priv User def mysql PRIMARY def mysql db Db +def mysql PRIMARY def mysql db Host def mysql PRIMARY def mysql db User def mysql PRIMARY def mysql event db def mysql PRIMARY def mysql event name @@ -99,41 +100,41 @@ def mysql PRIMARY def mysql help_relation help_keyword_id def mysql PRIMARY def mysql help_relation help_topic_id def mysql PRIMARY def mysql help_topic help_topic_id def mysql name def mysql help_topic name -def mysql PRIMARY def mysql host Host def mysql PRIMARY def mysql host Db +def mysql PRIMARY def mysql host Host def mysql PRIMARY def mysql index_stats db_name -def mysql PRIMARY def mysql index_stats table_name def mysql PRIMARY def mysql index_stats index_name def mysql PRIMARY def mysql index_stats prefix_arity +def mysql PRIMARY def mysql index_stats table_name def mysql PRIMARY def mysql innodb_index_stats database_name -def mysql PRIMARY def mysql innodb_index_stats table_name def mysql PRIMARY def mysql innodb_index_stats index_name def mysql PRIMARY def mysql innodb_index_stats stat_name +def mysql PRIMARY def mysql innodb_index_stats table_name def mysql PRIMARY def mysql innodb_table_stats database_name def mysql PRIMARY def mysql innodb_table_stats table_name def mysql PRIMARY def mysql plugin name def mysql PRIMARY def mysql proc db def mysql PRIMARY def mysql proc name def mysql PRIMARY def mysql proc type -def mysql PRIMARY def mysql procs_priv Host def mysql PRIMARY def mysql procs_priv Db -def mysql PRIMARY def mysql procs_priv User +def mysql PRIMARY def mysql procs_priv Host def mysql PRIMARY def mysql procs_priv Routine_name def mysql PRIMARY def mysql procs_priv Routine_type +def mysql PRIMARY def mysql procs_priv User def mysql PRIMARY def mysql proxies_priv Host -def mysql PRIMARY def mysql proxies_priv User def mysql PRIMARY def mysql proxies_priv Proxied_host def mysql PRIMARY def mysql proxies_priv Proxied_user +def mysql PRIMARY def mysql proxies_priv User def mysql Host def mysql roles_mapping Host -def mysql Host def mysql roles_mapping User def mysql Host def mysql roles_mapping Role +def mysql Host def mysql roles_mapping User def mysql PRIMARY def mysql servers Server_name def mysql PRIMARY def mysql table_stats db_name def mysql PRIMARY def mysql table_stats table_name -def mysql PRIMARY def mysql tables_priv Host def mysql PRIMARY def mysql tables_priv Db -def mysql PRIMARY def mysql tables_priv User +def mysql PRIMARY def mysql tables_priv Host def mysql PRIMARY def mysql tables_priv Table_name +def mysql PRIMARY def mysql tables_priv User def mysql PRIMARY def mysql time_zone Time_zone_id def mysql PRIMARY def mysql time_zone_leap_second Transition_time def mysql PRIMARY def mysql time_zone_name Name diff --git a/mysql-test/suite/funcs_1/r/is_routines_embedded.result b/mysql-test/suite/funcs_1/r/is_routines_embedded.result index a772ebaebda..8879efb21a5 100644 --- a/mysql-test/suite/funcs_1/r/is_routines_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_routines_embedded.result @@ -197,7 +197,7 @@ sp_6_408002_2 def db_datadict_2 sp_6_408002_2 PROCEDURE NULL NULL NULL NULL NUL SELECT * FROM db_datadict_2.res_6_408002_2; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci latin1_swedish_ci add_suppression def mtr add_suppression PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); FLUSH NO_WRITE_TO_BINLOG TABLE test_suppressions; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci -check_testcase def mtr check_testcase PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name NOT IN ('timestamp') AND variable_name not like "Last_IO_Err*" AND variable_name != 'INNODB_IBUF_MAX_SIZE' AND variable_name != 'INNODB_USE_NATIVE_AIO' AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP' AND variable_name not like 'GTID%POS' AND variable_name != 'GTID_BINLOG_STATE' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema'); SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' ORDER BY columns_in_mysql; SELECT * FROM INFORMATION_SCHEMA.EVENTS; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert'); SELECT * FROM INFORMATION_SCHEMA.ROUTINES; SHOW STATUS LIKE 'slave_open_temp_tables'; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.plugin, mysql.proc, mysql.procs_priv, mysql.roles_mapping, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; SELECT * FROM INFORMATION_SCHEMA.PLUGINS; select * from information_schema.session_variables where variable_name = 'debug_sync'; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci +check_testcase def mtr check_testcase PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name NOT IN ('timestamp') AND variable_name not like "Last_IO_Err*" AND variable_name != 'INNODB_IBUF_MAX_SIZE' AND variable_name != 'INNODB_USE_NATIVE_AIO' AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP' AND variable_name not like 'GTID%POS' AND variable_name != 'GTID_BINLOG_STATE' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY BINARY SCHEMA_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema') ORDER BY BINARY SCHEMA_NAME; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' ORDER BY columns_in_mysql; SELECT * FROM INFORMATION_SCHEMA.EVENTS; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert'); SELECT * FROM INFORMATION_SCHEMA.ROUTINES; SHOW STATUS LIKE 'slave_open_temp_tables'; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.plugin, mysql.proc, mysql.procs_priv, mysql.roles_mapping, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; SELECT * FROM INFORMATION_SCHEMA.PLUGINS; select * from information_schema.session_variables where variable_name = 'debug_sync'; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci check_warnings def mtr check_warnings PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci connect testuser2, localhost, testuser2, , db_datadict; SELECT * FROM information_schema.routines; @@ -209,7 +209,7 @@ sp_6_408002_2 def db_datadict_2 sp_6_408002_2 PROCEDURE NULL NULL NULL NULL NUL SELECT * FROM db_datadict_2.res_6_408002_2; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci latin1_swedish_ci add_suppression def mtr add_suppression PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); FLUSH NO_WRITE_TO_BINLOG TABLE test_suppressions; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci -check_testcase def mtr check_testcase PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name NOT IN ('timestamp') AND variable_name not like "Last_IO_Err*" AND variable_name != 'INNODB_IBUF_MAX_SIZE' AND variable_name != 'INNODB_USE_NATIVE_AIO' AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP' AND variable_name not like 'GTID%POS' AND variable_name != 'GTID_BINLOG_STATE' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema'); SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' ORDER BY columns_in_mysql; SELECT * FROM INFORMATION_SCHEMA.EVENTS; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert'); SELECT * FROM INFORMATION_SCHEMA.ROUTINES; SHOW STATUS LIKE 'slave_open_temp_tables'; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.plugin, mysql.proc, mysql.procs_priv, mysql.roles_mapping, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; SELECT * FROM INFORMATION_SCHEMA.PLUGINS; select * from information_schema.session_variables where variable_name = 'debug_sync'; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci +check_testcase def mtr check_testcase PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name NOT IN ('timestamp') AND variable_name not like "Last_IO_Err*" AND variable_name != 'INNODB_IBUF_MAX_SIZE' AND variable_name != 'INNODB_USE_NATIVE_AIO' AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP' AND variable_name not like 'GTID%POS' AND variable_name != 'GTID_BINLOG_STATE' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY BINARY SCHEMA_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema') ORDER BY BINARY SCHEMA_NAME; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' ORDER BY columns_in_mysql; SELECT * FROM INFORMATION_SCHEMA.EVENTS; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert'); SELECT * FROM INFORMATION_SCHEMA.ROUTINES; SHOW STATUS LIKE 'slave_open_temp_tables'; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.plugin, mysql.proc, mysql.procs_priv, mysql.roles_mapping, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; SELECT * FROM INFORMATION_SCHEMA.PLUGINS; select * from information_schema.session_variables where variable_name = 'debug_sync'; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci check_warnings def mtr check_warnings PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci connect testuser3, localhost, testuser3, , test; SELECT * FROM information_schema.routines; @@ -221,7 +221,7 @@ sp_6_408002_2 def db_datadict_2 sp_6_408002_2 PROCEDURE NULL NULL NULL NULL NUL SELECT * FROM db_datadict_2.res_6_408002_2; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci latin1_swedish_ci add_suppression def mtr add_suppression PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); FLUSH NO_WRITE_TO_BINLOG TABLE test_suppressions; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci -check_testcase def mtr check_testcase PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name NOT IN ('timestamp') AND variable_name not like "Last_IO_Err*" AND variable_name != 'INNODB_IBUF_MAX_SIZE' AND variable_name != 'INNODB_USE_NATIVE_AIO' AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP' AND variable_name not like 'GTID%POS' AND variable_name != 'GTID_BINLOG_STATE' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema'); SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' ORDER BY columns_in_mysql; SELECT * FROM INFORMATION_SCHEMA.EVENTS; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert'); SELECT * FROM INFORMATION_SCHEMA.ROUTINES; SHOW STATUS LIKE 'slave_open_temp_tables'; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.plugin, mysql.proc, mysql.procs_priv, mysql.roles_mapping, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; SELECT * FROM INFORMATION_SCHEMA.PLUGINS; select * from information_schema.session_variables where variable_name = 'debug_sync'; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci +check_testcase def mtr check_testcase PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name NOT IN ('timestamp') AND variable_name not like "Last_IO_Err*" AND variable_name != 'INNODB_IBUF_MAX_SIZE' AND variable_name != 'INNODB_USE_NATIVE_AIO' AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP' AND variable_name not like 'GTID%POS' AND variable_name != 'GTID_BINLOG_STATE' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY BINARY SCHEMA_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema') ORDER BY BINARY SCHEMA_NAME; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' ORDER BY columns_in_mysql; SELECT * FROM INFORMATION_SCHEMA.EVENTS; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert'); SELECT * FROM INFORMATION_SCHEMA.ROUTINES; SHOW STATUS LIKE 'slave_open_temp_tables'; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.host, mysql.plugin, mysql.proc, mysql.procs_priv, mysql.roles_mapping, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.user; SELECT * FROM INFORMATION_SCHEMA.PLUGINS; select * from information_schema.session_variables where variable_name = 'debug_sync'; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci check_warnings def mtr check_warnings PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci connection default; disconnect testuser1; diff --git a/mysql-test/suite/funcs_1/r/is_schemata.result b/mysql-test/suite/funcs_1/r/is_schemata.result index d0f2b734f7e..6db6ac8f150 100644 --- a/mysql-test/suite/funcs_1/r/is_schemata.result +++ b/mysql-test/suite/funcs_1/r/is_schemata.result @@ -51,7 +51,8 @@ DEFAULT_COLLATION_NAME varchar(32) NO SQL_PATH varchar(512) YES NULL SELECT catalog_name, schema_name, sql_path FROM information_schema.schemata -WHERE catalog_name IS NOT NULL or sql_path IS NOT NULL; +WHERE catalog_name IS NOT NULL or sql_path IS NOT NULL +ORDER BY schema_name; catalog_name schema_name sql_path def information_schema NULL def mtr NULL diff --git a/mysql-test/suite/funcs_1/r/is_schemata_embedded.result b/mysql-test/suite/funcs_1/r/is_schemata_embedded.result index 5f48c030c16..bc993e8dc9f 100644 --- a/mysql-test/suite/funcs_1/r/is_schemata_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_schemata_embedded.result @@ -51,7 +51,8 @@ DEFAULT_COLLATION_NAME varchar(32) NO SQL_PATH varchar(512) YES NULL SELECT catalog_name, schema_name, sql_path FROM information_schema.schemata -WHERE catalog_name IS NOT NULL or sql_path IS NOT NULL; +WHERE catalog_name IS NOT NULL or sql_path IS NOT NULL +ORDER BY schema_name; catalog_name schema_name sql_path def information_schema NULL def mtr NULL diff --git a/mysql-test/suite/funcs_1/r/is_statistics.result b/mysql-test/suite/funcs_1/r/is_statistics.result index 749b09fa87d..a07d9d8d3c3 100644 --- a/mysql-test/suite/funcs_1/r/is_statistics.result +++ b/mysql-test/suite/funcs_1/r/is_statistics.result @@ -83,16 +83,17 @@ INDEX_TYPE varchar(16) NO COMMENT varchar(16) YES NULL INDEX_COMMENT varchar(1024) NO SELECT table_catalog, table_schema, table_name, index_schema, index_name -FROM information_schema.statistics WHERE table_catalog IS NOT NULL; +FROM information_schema.statistics WHERE table_catalog IS NOT NULL +ORDER BY table_schema, table_name, index_schema, index_name; table_catalog table_schema table_name index_schema index_name -def mysql column_stats mysql PRIMARY -def mysql column_stats mysql PRIMARY -def mysql column_stats mysql PRIMARY def mysql columns_priv mysql PRIMARY def mysql columns_priv mysql PRIMARY def mysql columns_priv mysql PRIMARY def mysql columns_priv mysql PRIMARY def mysql columns_priv mysql PRIMARY +def mysql column_stats mysql PRIMARY +def mysql column_stats mysql PRIMARY +def mysql column_stats mysql PRIMARY def mysql db mysql PRIMARY def mysql db mysql PRIMARY def mysql db mysql PRIMARY @@ -102,14 +103,14 @@ def mysql event mysql PRIMARY def mysql func mysql PRIMARY def mysql gtid_slave_pos mysql PRIMARY def mysql gtid_slave_pos mysql PRIMARY -def mysql help_category mysql PRIMARY def mysql help_category mysql name -def mysql help_keyword mysql PRIMARY +def mysql help_category mysql PRIMARY def mysql help_keyword mysql name +def mysql help_keyword mysql PRIMARY def mysql help_relation mysql PRIMARY def mysql help_relation mysql PRIMARY -def mysql help_topic mysql PRIMARY def mysql help_topic mysql name +def mysql help_topic mysql PRIMARY def mysql host mysql PRIMARY def mysql host mysql PRIMARY def mysql index_stats mysql PRIMARY @@ -120,28 +121,28 @@ def mysql plugin mysql PRIMARY def mysql proc mysql PRIMARY def mysql proc mysql PRIMARY def mysql proc mysql PRIMARY +def mysql procs_priv mysql Grantor def mysql procs_priv mysql PRIMARY def mysql procs_priv mysql PRIMARY def mysql procs_priv mysql PRIMARY def mysql procs_priv mysql PRIMARY def mysql procs_priv mysql PRIMARY -def mysql procs_priv mysql Grantor +def mysql proxies_priv mysql Grantor def mysql proxies_priv mysql PRIMARY def mysql proxies_priv mysql PRIMARY def mysql proxies_priv mysql PRIMARY def mysql proxies_priv mysql PRIMARY -def mysql proxies_priv mysql Grantor def mysql roles_mapping mysql Host def mysql roles_mapping mysql Host def mysql roles_mapping mysql Host def mysql servers mysql PRIMARY -def mysql table_stats mysql PRIMARY -def mysql table_stats mysql PRIMARY +def mysql tables_priv mysql Grantor def mysql tables_priv mysql PRIMARY def mysql tables_priv mysql PRIMARY def mysql tables_priv mysql PRIMARY def mysql tables_priv mysql PRIMARY -def mysql tables_priv mysql Grantor +def mysql table_stats mysql PRIMARY +def mysql table_stats mysql PRIMARY def mysql time_zone mysql PRIMARY def mysql time_zone_leap_second mysql PRIMARY def mysql time_zone_name mysql PRIMARY diff --git a/mysql-test/suite/funcs_1/r/is_table_constraints.result b/mysql-test/suite/funcs_1/r/is_table_constraints.result index 0553b4344c8..2f3afaaa0ad 100644 --- a/mysql-test/suite/funcs_1/r/is_table_constraints.result +++ b/mysql-test/suite/funcs_1/r/is_table_constraints.result @@ -55,21 +55,22 @@ CONSTRAINT_TYPE varchar(64) NO SELECT constraint_catalog, constraint_schema, constraint_name, table_schema, table_name FROM information_schema.table_constraints -WHERE constraint_catalog IS NOT NULL; +WHERE constraint_catalog IS NOT NULL +ORDER BY constraint_schema, table_name, constraint_name; constraint_catalog constraint_schema constraint_name table_schema table_name -def mysql PRIMARY mysql column_stats def mysql PRIMARY mysql columns_priv +def mysql PRIMARY mysql column_stats def mysql PRIMARY mysql db def mysql PRIMARY mysql event def mysql PRIMARY mysql func def mysql PRIMARY mysql gtid_slave_pos -def mysql PRIMARY mysql help_category def mysql name mysql help_category -def mysql PRIMARY mysql help_keyword +def mysql PRIMARY mysql help_category def mysql name mysql help_keyword +def mysql PRIMARY mysql help_keyword def mysql PRIMARY mysql help_relation -def mysql PRIMARY mysql help_topic def mysql name mysql help_topic +def mysql PRIMARY mysql help_topic def mysql PRIMARY mysql host def mysql PRIMARY mysql index_stats def mysql PRIMARY mysql innodb_index_stats @@ -80,8 +81,8 @@ def mysql PRIMARY mysql procs_priv def mysql PRIMARY mysql proxies_priv def mysql Host mysql roles_mapping def mysql PRIMARY mysql servers -def mysql PRIMARY mysql table_stats def mysql PRIMARY mysql tables_priv +def mysql PRIMARY mysql table_stats def mysql PRIMARY mysql time_zone def mysql PRIMARY mysql time_zone_leap_second def mysql PRIMARY mysql time_zone_name diff --git a/mysql-test/suite/funcs_1/r/is_triggers.result b/mysql-test/suite/funcs_1/r/is_triggers.result index 8e5842742cb..347bf02238a 100644 --- a/mysql-test/suite/funcs_1/r/is_triggers.result +++ b/mysql-test/suite/funcs_1/r/is_triggers.result @@ -104,7 +104,8 @@ DATABASE_COLLATION varchar(32) NO SELECT * FROM information_schema.triggers WHERE trigger_catalog IS NOT NULL OR event_object_catalog IS NOT NULL OR action_condition IS NOT NULL OR action_reference_old_table IS NOT NULL -OR action_reference_new_table IS NOT NULL; +OR action_reference_new_table IS NOT NULL +ORDER BY trigger_schema, trigger_name; TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION def mtr gs_insert INSERT def mtr global_suppressions 1 NULL BEGIN DECLARE dummy INT; SELECT "" REGEXP NEW.pattern INTO dummy; END ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci def mtr ts_insert INSERT def mtr test_suppressions 1 NULL BEGIN DECLARE dummy INT; SELECT "" REGEXP NEW.pattern INTO dummy; END ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci diff --git a/mysql-test/suite/funcs_1/r/is_triggers_embedded.result b/mysql-test/suite/funcs_1/r/is_triggers_embedded.result index 55dc79e50d2..56c0e22d685 100644 --- a/mysql-test/suite/funcs_1/r/is_triggers_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_triggers_embedded.result @@ -104,7 +104,8 @@ DATABASE_COLLATION varchar(32) NO SELECT * FROM information_schema.triggers WHERE trigger_catalog IS NOT NULL OR event_object_catalog IS NOT NULL OR action_condition IS NOT NULL OR action_reference_old_table IS NOT NULL -OR action_reference_new_table IS NOT NULL; +OR action_reference_new_table IS NOT NULL +ORDER BY trigger_schema, trigger_name; TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION def mtr gs_insert INSERT def mtr global_suppressions 1 NULL BEGIN DECLARE dummy INT; SELECT "" REGEXP NEW.pattern INTO dummy; END ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci def mtr ts_insert INSERT def mtr test_suppressions 1 NULL BEGIN DECLARE dummy INT; SELECT "" REGEXP NEW.pattern INTO dummy; END ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci diff --git a/mysql-test/suite/funcs_1/r/is_views.result b/mysql-test/suite/funcs_1/r/is_views.result index cc50b8033f0..62ec33c8340 100644 --- a/mysql-test/suite/funcs_1/r/is_views.result +++ b/mysql-test/suite/funcs_1/r/is_views.result @@ -128,13 +128,15 @@ ENGINE = <engine_type>; DROP USER 'testuser1'@'localhost'; CREATE USER 'testuser1'@'localhost'; SELECT * FROM information_schema.views -WHERE table_name LIKE 't1_%'; +WHERE table_name LIKE 't1_%' +ORDER BY table_schema, table_name; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM CREATE VIEW test.t1_view AS SELECT DISTINCT f1 FROM test.t1_table; CREATE ALGORITHM=MERGE VIEW test.t1_view1 AS SELECT f1 FROM test.t1_table; CREATE ALGORITHM=TEMPTABLE VIEW test.t1_view2 AS SELECT f1 FROM test.t1_table; SELECT * FROM information_schema.views -WHERE table_name LIKE 't1_%'; +WHERE table_name LIKE 't1_%' +ORDER BY table_schema, table_name; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM def test t1_view select distinct `test`.`t1_table`.`f1` AS `f1` from `test`.`t1_table` NONE NO root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED def test t1_view1 select `test`.`t1_table`.`f1` AS `f1` from `test`.`t1_table` NONE YES root@localhost DEFINER latin1 latin1_swedish_ci MERGE diff --git a/mysql-test/suite/funcs_1/r/is_views_embedded.result b/mysql-test/suite/funcs_1/r/is_views_embedded.result index 1f2e23c3610..c382370e892 100644 --- a/mysql-test/suite/funcs_1/r/is_views_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_views_embedded.result @@ -131,13 +131,15 @@ ENGINE = <engine_type>; DROP USER 'testuser1'@'localhost'; CREATE USER 'testuser1'@'localhost'; SELECT * FROM information_schema.views -WHERE table_name LIKE 't1_%'; +WHERE table_name LIKE 't1_%' +ORDER BY table_schema, table_name; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM CREATE VIEW test.t1_view AS SELECT DISTINCT f1 FROM test.t1_table; CREATE ALGORITHM=MERGE VIEW test.t1_view1 AS SELECT f1 FROM test.t1_table; CREATE ALGORITHM=TEMPTABLE VIEW test.t1_view2 AS SELECT f1 FROM test.t1_table; SELECT * FROM information_schema.views -WHERE table_name LIKE 't1_%'; +WHERE table_name LIKE 't1_%' +ORDER BY table_schema, table_name; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM def test t1_view select distinct `test`.`t1_table`.`f1` AS `f1` from `test`.`t1_table` NONE NO root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED def test t1_view1 select `test`.`t1_table`.`f1` AS `f1` from `test`.`t1_table` NONE YES root@localhost DEFINER latin1 latin1_swedish_ci MERGE diff --git a/mysql-test/suite/funcs_1/t/is_basics_mixed.test b/mysql-test/suite/funcs_1/t/is_basics_mixed.test index c0b03a84478..901441a3557 100644 --- a/mysql-test/suite/funcs_1/t/is_basics_mixed.test +++ b/mysql-test/suite/funcs_1/t/is_basics_mixed.test @@ -286,9 +286,11 @@ CREATE VIEW db_datadict.v2 AS SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE FROM information_schema.tables WHERE table_schema = 'db_datadict'; SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE -FROM db_datadict.v2; +FROM db_datadict.v2 +ORDER BY TABLE_NAME; SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE -FROM information_schema.tables WHERE table_schema = 'db_datadict'; +FROM information_schema.tables WHERE table_schema = 'db_datadict' +ORDER BY TABLE_NAME; GRANT SELECT ON db_datadict.v2 to testuser2@localhost; # --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK diff --git a/mysql-test/suite/funcs_1/t/is_statistics.test b/mysql-test/suite/funcs_1/t/is_statistics.test index 3cdc5568960..3d7f23a7b2c 100644 --- a/mysql-test/suite/funcs_1/t/is_statistics.test +++ b/mysql-test/suite/funcs_1/t/is_statistics.test @@ -78,7 +78,8 @@ eval SHOW COLUMNS FROM information_schema.$is_table; # Show that TABLE_CATALOG is always NULL. SELECT table_catalog, table_schema, table_name, index_schema, index_name -FROM information_schema.statistics WHERE table_catalog IS NOT NULL; +FROM information_schema.statistics WHERE table_catalog IS NOT NULL +ORDER BY table_schema, table_name, index_schema, index_name; --echo #################################################################################### diff --git a/mysql-test/suite/funcs_1/t/is_table_constraints.test b/mysql-test/suite/funcs_1/t/is_table_constraints.test index 96853edac2c..cf8d3fbf2d2 100644 --- a/mysql-test/suite/funcs_1/t/is_table_constraints.test +++ b/mysql-test/suite/funcs_1/t/is_table_constraints.test @@ -68,8 +68,8 @@ eval SHOW COLUMNS FROM information_schema.$is_table; SELECT constraint_catalog, constraint_schema, constraint_name, table_schema, table_name FROM information_schema.table_constraints -WHERE constraint_catalog IS NOT NULL; - +WHERE constraint_catalog IS NOT NULL +ORDER BY constraint_schema, table_name, constraint_name; --echo ######################################################################################### --echo # Testcase 3.2.7.2 + 3.2.7.3: INFORMATION_SCHEMA.TABLE_CONSTRAINTS accessible information diff --git a/mysql-test/suite/innodb_zip/r/innodb-zip.result b/mysql-test/suite/innodb_zip/r/innodb-zip.result index 92bdb863265..0da1d6bbe85 100644 --- a/mysql-test/suite/innodb_zip/r/innodb-zip.result +++ b/mysql-test/suite/innodb_zip/r/innodb-zip.result @@ -62,7 +62,7 @@ row_format=compressed; create table t14(a int primary key) engine=innodb key_block_size=9; Warnings: Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=9. -SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql'; +SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' order by table_name; table_schema table_name row_format data_length index_length mysqltest_innodb_zip t0 Dynamic {valid} 0 mysqltest_innodb_zip t00 Dynamic {valid} 0 @@ -83,15 +83,15 @@ mysqltest_innodb_zip t9 Compact {valid} 0 drop table t0,t00,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14; alter table t1 key_block_size=0; alter table t1 row_format=dynamic; -SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql'; +SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' order by table_name; table_schema table_name row_format data_length index_length mysqltest_innodb_zip t1 Dynamic {valid} 0 alter table t1 row_format=compact; -SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql'; +SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' order by table_name; table_schema table_name row_format data_length index_length mysqltest_innodb_zip t1 Compact {valid} 0 alter table t1 row_format=redundant; -SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql'; +SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' order by table_name; table_schema table_name row_format data_length index_length mysqltest_innodb_zip t1 Redundant {valid} 0 drop table t1; @@ -125,7 +125,7 @@ mysqltest_innodb_zip.t1 analyze status OK analyze table t2; Table Op Msg_type Msg_text mysqltest_innodb_zip.t2 analyze status OK -SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql'; +SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' order by table_name; table_schema table_name row_format data_length index_length mysqltest_innodb_zip t1 Compressed 2048 1024 mysqltest_innodb_zip t2 Dynamic {valid} 0 @@ -169,7 +169,7 @@ create table t8 (id int primary key) engine = innodb row_format = compressed; create table t9 (id int primary key) engine = innodb row_format = dynamic; create table t10(id int primary key) engine = innodb row_format = compact; create table t11(id int primary key) engine = innodb row_format = redundant; -SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql'; +SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' order by table_name; table_schema table_name row_format data_length index_length mysqltest_innodb_zip t1 Dynamic {valid} 0 mysqltest_innodb_zip t10 Compact {valid} 0 @@ -208,7 +208,7 @@ Error 1005 Can't create table `mysqltest_innodb_zip`.`t4` (errno: 140 "Wrong cre Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB create table t5 (id int primary key) engine = innodb key_block_size = 4 row_format = default; -SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql'; +SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' order by table_name; table_schema table_name row_format data_length index_length mysqltest_innodb_zip t1 Compressed 4096 0 mysqltest_innodb_zip t5 Compressed 4096 0 @@ -240,7 +240,7 @@ Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 1 Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. Error 1005 Can't create table `mysqltest_innodb_zip`.`t2` (errno: 140 "Wrong create options") Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB -SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql'; +SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' order by table_name; table_schema table_name row_format data_length index_length set global innodb_file_per_table = off; create table t1 (id int primary key) engine = innodb key_block_size = 1; @@ -276,7 +276,7 @@ show warnings; Level Code Message create table t8 (id int primary key) engine = innodb row_format = compact; create table t9 (id int primary key) engine = innodb row_format = redundant; -SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql'; +SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' order by table_name; table_schema table_name row_format data_length index_length mysqltest_innodb_zip t7 Dynamic {valid} 0 mysqltest_innodb_zip t8 Compact {valid} 0 diff --git a/mysql-test/suite/innodb_zip/t/innodb-zip.test b/mysql-test/suite/innodb_zip/t/innodb-zip.test index ff73c99f41d..9049b231191 100644 --- a/mysql-test/suite/innodb_zip/t/innodb-zip.test +++ b/mysql-test/suite/innodb_zip/t/innodb-zip.test @@ -9,7 +9,7 @@ SELECT table_name, row_format, data_length, index_length let $per_table=`select @@innodb_file_per_table`; SET @save_innodb_stats_on_metadata=@@global.innodb_stats_on_metadata; ---let $query_i_s = SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' +--let $query_i_s = SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' order by table_name set session innodb_strict_mode=0; set global innodb_file_per_table=off; diff --git a/mysql-test/suite/parts/t/partition_exch_qa_12.test b/mysql-test/suite/parts/t/partition_exch_qa_12.test index 7e048f79017..80fd7ebba82 100644 --- a/mysql-test/suite/parts/t/partition_exch_qa_12.test +++ b/mysql-test/suite/parts/t/partition_exch_qa_12.test @@ -164,6 +164,7 @@ SELECT * FROM tsp_04; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; #--error ER_TABLES_DIFFERENT_METADATA #ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE; +--sorted_result SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('tp', 't_100'); ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100; diff --git a/mysql-test/suite/perfschema/r/dml_handler.result b/mysql-test/suite/perfschema/r/dml_handler.result index 2a1cc7035af..ab850aee933 100644 --- a/mysql-test/suite/perfschema/r/dml_handler.result +++ b/mysql-test/suite/perfschema/r/dml_handler.result @@ -3,7 +3,8 @@ CREATE TEMPORARY TABLE table_list (id INT AUTO_INCREMENT, PRIMARY KEY (id)) AS SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA='performance_schema'; +WHERE TABLE_SCHEMA='performance_schema' + ORDER BY TABLE_NAME; SELECT COUNT(*) FROM table_list INTO @table_count; # For each table in the performance schema, attempt HANDLER...OPEN, @@ -67,11 +68,11 @@ SELECT TABLE_NAME INTO @table_name FROM table_list WHERE id=34; HANDLER performance_schema.mutex_instances OPEN; ERROR HY000: Storage engine PERFORMANCE_SCHEMA of the table `performance_schema`.`mutex_instances` doesn't have this option SELECT TABLE_NAME INTO @table_name FROM table_list WHERE id=33; -HANDLER performance_schema.hosts OPEN; -ERROR HY000: Storage engine PERFORMANCE_SCHEMA of the table `performance_schema`.`hosts` doesn't have this option -SELECT TABLE_NAME INTO @table_name FROM table_list WHERE id=32; HANDLER performance_schema.host_cache OPEN; ERROR HY000: Storage engine PERFORMANCE_SCHEMA of the table `performance_schema`.`host_cache` doesn't have this option +SELECT TABLE_NAME INTO @table_name FROM table_list WHERE id=32; +HANDLER performance_schema.hosts OPEN; +ERROR HY000: Storage engine PERFORMANCE_SCHEMA of the table `performance_schema`.`hosts` doesn't have this option SELECT TABLE_NAME INTO @table_name FROM table_list WHERE id=31; HANDLER performance_schema.file_summary_by_instance OPEN; ERROR HY000: Storage engine PERFORMANCE_SCHEMA of the table `performance_schema`.`file_summary_by_instance` doesn't have this option diff --git a/mysql-test/suite/perfschema/r/information_schema.result b/mysql-test/suite/perfschema/r/information_schema.result index 5e5ce57fb2e..a684fd7b84e 100644 --- a/mysql-test/suite/perfschema/r/information_schema.result +++ b/mysql-test/suite/perfschema/r/information_schema.result @@ -1,6 +1,7 @@ select TABLE_SCHEMA, lower(TABLE_NAME), TABLE_CATALOG from information_schema.tables -where TABLE_SCHEMA='performance_schema'; +where TABLE_SCHEMA='performance_schema' + order by table_name; TABLE_SCHEMA lower(TABLE_NAME) TABLE_CATALOG performance_schema accounts def performance_schema cond_instances def @@ -33,8 +34,8 @@ performance_schema events_waits_summary_global_by_event_name def performance_schema file_instances def performance_schema file_summary_by_event_name def performance_schema file_summary_by_instance def -performance_schema host_cache def performance_schema hosts def +performance_schema host_cache def performance_schema mutex_instances def performance_schema objects_summary_global_by_type def performance_schema performance_timers def @@ -56,7 +57,8 @@ performance_schema threads def performance_schema users def select lower(TABLE_NAME), TABLE_TYPE, ENGINE from information_schema.tables -where TABLE_SCHEMA='performance_schema'; +where TABLE_SCHEMA='performance_schema' + order by table_name; lower(TABLE_NAME) TABLE_TYPE ENGINE accounts BASE TABLE PERFORMANCE_SCHEMA cond_instances BASE TABLE PERFORMANCE_SCHEMA @@ -89,8 +91,8 @@ events_waits_summary_global_by_event_name BASE TABLE PERFORMANCE_SCHEMA file_instances BASE TABLE PERFORMANCE_SCHEMA file_summary_by_event_name BASE TABLE PERFORMANCE_SCHEMA file_summary_by_instance BASE TABLE PERFORMANCE_SCHEMA -host_cache BASE TABLE PERFORMANCE_SCHEMA hosts BASE TABLE PERFORMANCE_SCHEMA +host_cache BASE TABLE PERFORMANCE_SCHEMA mutex_instances BASE TABLE PERFORMANCE_SCHEMA objects_summary_global_by_type BASE TABLE PERFORMANCE_SCHEMA performance_timers BASE TABLE PERFORMANCE_SCHEMA @@ -112,7 +114,8 @@ threads BASE TABLE PERFORMANCE_SCHEMA users BASE TABLE PERFORMANCE_SCHEMA select lower(TABLE_NAME), VERSION, ROW_FORMAT from information_schema.tables -where TABLE_SCHEMA='performance_schema'; +where TABLE_SCHEMA='performance_schema' + order by table_name; lower(TABLE_NAME) VERSION ROW_FORMAT accounts 10 Fixed cond_instances 10 Dynamic @@ -145,8 +148,8 @@ events_waits_summary_global_by_event_name 10 Dynamic file_instances 10 Dynamic file_summary_by_event_name 10 Dynamic file_summary_by_instance 10 Dynamic -host_cache 10 Dynamic hosts 10 Fixed +host_cache 10 Dynamic mutex_instances 10 Dynamic objects_summary_global_by_type 10 Dynamic performance_timers 10 Fixed @@ -168,7 +171,8 @@ threads 10 Dynamic users 10 Fixed select lower(TABLE_NAME), TABLE_ROWS, AVG_ROW_LENGTH from information_schema.tables -where TABLE_SCHEMA='performance_schema'; +where TABLE_SCHEMA='performance_schema' + order by table_name; lower(TABLE_NAME) TABLE_ROWS AVG_ROW_LENGTH accounts 1000 0 cond_instances 1000 0 @@ -201,8 +205,8 @@ events_waits_summary_global_by_event_name 1000 0 file_instances 1000 0 file_summary_by_event_name 1000 0 file_summary_by_instance 1000 0 -host_cache 1000 0 hosts 1000 0 +host_cache 1000 0 mutex_instances 1000 0 objects_summary_global_by_type 1000 0 performance_timers 5 0 @@ -224,7 +228,8 @@ threads 1000 0 users 1000 0 select lower(TABLE_NAME), DATA_LENGTH, MAX_DATA_LENGTH from information_schema.tables -where TABLE_SCHEMA='performance_schema'; +where TABLE_SCHEMA='performance_schema' + order by table_name; lower(TABLE_NAME) DATA_LENGTH MAX_DATA_LENGTH accounts 0 0 cond_instances 0 0 @@ -257,8 +262,8 @@ events_waits_summary_global_by_event_name 0 0 file_instances 0 0 file_summary_by_event_name 0 0 file_summary_by_instance 0 0 -host_cache 0 0 hosts 0 0 +host_cache 0 0 mutex_instances 0 0 objects_summary_global_by_type 0 0 performance_timers 0 0 @@ -280,7 +285,8 @@ threads 0 0 users 0 0 select lower(TABLE_NAME), INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT from information_schema.tables -where TABLE_SCHEMA='performance_schema'; +where TABLE_SCHEMA='performance_schema' + order by table_name; lower(TABLE_NAME) INDEX_LENGTH DATA_FREE AUTO_INCREMENT accounts 0 0 NULL cond_instances 0 0 NULL @@ -313,8 +319,8 @@ events_waits_summary_global_by_event_name 0 0 NULL file_instances 0 0 NULL file_summary_by_event_name 0 0 NULL file_summary_by_instance 0 0 NULL -host_cache 0 0 NULL hosts 0 0 NULL +host_cache 0 0 NULL mutex_instances 0 0 NULL objects_summary_global_by_type 0 0 NULL performance_timers 0 0 NULL @@ -336,7 +342,8 @@ threads 0 0 NULL users 0 0 NULL select lower(TABLE_NAME), CREATE_TIME, UPDATE_TIME, CHECK_TIME from information_schema.tables -where TABLE_SCHEMA='performance_schema'; +where TABLE_SCHEMA='performance_schema' + order by table_name; lower(TABLE_NAME) CREATE_TIME UPDATE_TIME CHECK_TIME accounts NULL NULL NULL cond_instances NULL NULL NULL @@ -369,8 +376,8 @@ events_waits_summary_global_by_event_name NULL NULL NULL file_instances NULL NULL NULL file_summary_by_event_name NULL NULL NULL file_summary_by_instance NULL NULL NULL -host_cache NULL NULL NULL hosts NULL NULL NULL +host_cache NULL NULL NULL mutex_instances NULL NULL NULL objects_summary_global_by_type NULL NULL NULL performance_timers NULL NULL NULL @@ -392,7 +399,8 @@ threads NULL NULL NULL users NULL NULL NULL select lower(TABLE_NAME), TABLE_COLLATION, CHECKSUM from information_schema.tables -where TABLE_SCHEMA='performance_schema'; +where TABLE_SCHEMA='performance_schema' + order by table_name; lower(TABLE_NAME) TABLE_COLLATION CHECKSUM accounts utf8_general_ci NULL cond_instances utf8_general_ci NULL @@ -425,8 +433,8 @@ events_waits_summary_global_by_event_name utf8_general_ci NULL file_instances utf8_general_ci NULL file_summary_by_event_name utf8_general_ci NULL file_summary_by_instance utf8_general_ci NULL -host_cache utf8_general_ci NULL hosts utf8_general_ci NULL +host_cache utf8_general_ci NULL mutex_instances utf8_general_ci NULL objects_summary_global_by_type utf8_general_ci NULL performance_timers utf8_general_ci NULL @@ -448,7 +456,8 @@ threads utf8_general_ci NULL users utf8_general_ci NULL select lower(TABLE_NAME), TABLE_COMMENT from information_schema.tables -where TABLE_SCHEMA='performance_schema'; +where TABLE_SCHEMA='performance_schema' + order by table_name; lower(TABLE_NAME) TABLE_COMMENT accounts cond_instances @@ -481,8 +490,8 @@ events_waits_summary_global_by_event_name file_instances file_summary_by_event_name file_summary_by_instance -host_cache hosts +host_cache mutex_instances objects_summary_global_by_type performance_timers diff --git a/mysql-test/suite/perfschema/r/ortho_iter.result b/mysql-test/suite/perfschema/r/ortho_iter.result index bb572237483..f449aa97b40 100644 --- a/mysql-test/suite/perfschema/r/ortho_iter.result +++ b/mysql-test/suite/perfschema/r/ortho_iter.result @@ -21,7 +21,8 @@ declare debug integer default 0; declare pfs_cursor CURSOR FOR select table_name from information_schema.tables where table_schema= 'performance_schema' - and table_name like "events_%_by_event_name%"; + and table_name like "events_%_by_event_name%" + order by table_name; declare continue handler for sqlstate '02000' set done = 1; select (instr_name like "wait/%") or (instr_name like "idle/%") into is_wait; diff --git a/mysql-test/suite/perfschema/t/dml_handler.test b/mysql-test/suite/perfschema/t/dml_handler.test index db74c85350a..f12cc82f22f 100644 --- a/mysql-test/suite/perfschema/t/dml_handler.test +++ b/mysql-test/suite/perfschema/t/dml_handler.test @@ -18,7 +18,8 @@ CREATE TEMPORARY TABLE table_list (id INT AUTO_INCREMENT, PRIMARY KEY (id)) AS SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES - WHERE TABLE_SCHEMA='performance_schema'; + WHERE TABLE_SCHEMA='performance_schema' + ORDER BY TABLE_NAME; SELECT COUNT(*) FROM table_list INTO @table_count; diff --git a/mysql-test/suite/perfschema/t/information_schema.test b/mysql-test/suite/perfschema/t/information_schema.test index 24325710174..0152ef21905 100644 --- a/mysql-test/suite/perfschema/t/information_schema.test +++ b/mysql-test/suite/perfschema/t/information_schema.test @@ -11,42 +11,52 @@ select TABLE_SCHEMA, lower(TABLE_NAME), TABLE_CATALOG from information_schema.tables - where TABLE_SCHEMA='performance_schema'; + where TABLE_SCHEMA='performance_schema' + order by table_name; select lower(TABLE_NAME), TABLE_TYPE, ENGINE from information_schema.tables - where TABLE_SCHEMA='performance_schema'; + where TABLE_SCHEMA='performance_schema' + order by table_name; select lower(TABLE_NAME), VERSION, ROW_FORMAT from information_schema.tables - where TABLE_SCHEMA='performance_schema'; + where TABLE_SCHEMA='performance_schema' + order by table_name; select lower(TABLE_NAME), TABLE_ROWS, AVG_ROW_LENGTH from information_schema.tables - where TABLE_SCHEMA='performance_schema'; + where TABLE_SCHEMA='performance_schema' + order by table_name; select lower(TABLE_NAME), DATA_LENGTH, MAX_DATA_LENGTH from information_schema.tables - where TABLE_SCHEMA='performance_schema'; + where TABLE_SCHEMA='performance_schema' + order by table_name; select lower(TABLE_NAME), INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT from information_schema.tables - where TABLE_SCHEMA='performance_schema'; + where TABLE_SCHEMA='performance_schema' + order by table_name; select lower(TABLE_NAME), CREATE_TIME, UPDATE_TIME, CHECK_TIME from information_schema.tables - where TABLE_SCHEMA='performance_schema'; + where TABLE_SCHEMA='performance_schema' + order by table_name; select lower(TABLE_NAME), TABLE_COLLATION, CHECKSUM from information_schema.tables - where TABLE_SCHEMA='performance_schema'; + where TABLE_SCHEMA='performance_schema' + order by table_name; # TABLESPACE_NAME does not exist in 5.4 # select lower(TABLE_NAME), CREATE_OPTIONS, TABLESPACE_NAME # from information_schema.tables -# where TABLE_SCHEMA='performance_schema'; +# where TABLE_SCHEMA='performance_schema' +# order by table_name; select lower(TABLE_NAME), TABLE_COMMENT from information_schema.tables - where TABLE_SCHEMA='performance_schema'; + where TABLE_SCHEMA='performance_schema' + order by table_name; diff --git a/mysql-test/suite/perfschema/t/ortho_iter.test b/mysql-test/suite/perfschema/t/ortho_iter.test index 0a90831ae70..f7e68538906 100644 --- a/mysql-test/suite/perfschema/t/ortho_iter.test +++ b/mysql-test/suite/perfschema/t/ortho_iter.test @@ -35,7 +35,8 @@ begin declare pfs_cursor CURSOR FOR select table_name from information_schema.tables where table_schema= 'performance_schema' - and table_name like "events_%_by_event_name%"; + and table_name like "events_%_by_event_name%" + order by table_name; declare continue handler for sqlstate '02000' set done = 1; diff --git a/mysql-test/suite/roles/show_create_database-10463.result b/mysql-test/suite/roles/show_create_database-10463.result index a9b376a891f..7bebc954840 100644 --- a/mysql-test/suite/roles/show_create_database-10463.result +++ b/mysql-test/suite/roles/show_create_database-10463.result @@ -16,7 +16,7 @@ test show create database db; ERROR 42000: Access denied for user 'beep'@'localhost' to database 'db' select table_schema, table_name from information_schema.tables -where table_schema = 'db'; +where table_schema = 'db' order by table_name; table_schema table_name set role r1; show databases; @@ -28,7 +28,7 @@ show create database db; Database Create Database db CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET latin1 */ select table_schema, table_name from information_schema.tables -where table_schema = 'db'; +where table_schema = 'db' order by table_name; table_schema table_name db t1 db t2 @@ -45,7 +45,7 @@ test show create database db; ERROR 42000: Access denied for user 'beep2'@'localhost' to database 'db' select table_schema, table_name from information_schema.tables -where table_schema = 'db'; +where table_schema = 'db' order by table_name; table_schema table_name set role r2; show databases; @@ -57,7 +57,7 @@ show create database db; Database Create Database db CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET latin1 */ select table_schema, table_name from information_schema.tables -where table_schema = 'db'; +where table_schema = 'db' order by table_name; table_schema table_name db t1 db t2 diff --git a/mysql-test/suite/roles/show_create_database-10463.test b/mysql-test/suite/roles/show_create_database-10463.test index 2d921629c10..6278cc71c02 100644 --- a/mysql-test/suite/roles/show_create_database-10463.test +++ b/mysql-test/suite/roles/show_create_database-10463.test @@ -16,13 +16,13 @@ show databases; --error ER_DBACCESS_DENIED_ERROR show create database db; select table_schema, table_name from information_schema.tables -where table_schema = 'db'; +where table_schema = 'db' order by table_name; set role r1; show databases; show create database db; select table_schema, table_name from information_schema.tables -where table_schema = 'db'; +where table_schema = 'db' order by table_name; connection default; @@ -36,14 +36,14 @@ show databases; --error ER_DBACCESS_DENIED_ERROR show create database db; select table_schema, table_name from information_schema.tables -where table_schema = 'db'; +where table_schema = 'db' order by table_name; set role r2; show databases; show create database db; select table_schema, table_name from information_schema.tables -where table_schema = 'db'; +where table_schema = 'db' order by table_name; connection default; diff --git a/mysql-test/suite/sql_sequence/other.result b/mysql-test/suite/sql_sequence/other.result index 190886ab68f..87c36394975 100644 --- a/mysql-test/suite/sql_sequence/other.result +++ b/mysql-test/suite/sql_sequence/other.result @@ -93,7 +93,7 @@ Tables_in_test Table_type s1 SEQUENCE t1 BASE TABLE v1 VIEW -SELECT TABLE_TYPE,ENGINE FROM INFORMATION_SCHEMA.TABLES where table_schema="test"; +SELECT TABLE_TYPE,ENGINE FROM INFORMATION_SCHEMA.TABLES where table_schema="test" ORDER BY TABLE_NAME; TABLE_TYPE ENGINE SEQUENCE MyISAM BASE TABLE MyISAM diff --git a/mysql-test/suite/sql_sequence/other.test b/mysql-test/suite/sql_sequence/other.test index d816ec7c108..edd4cc7acc7 100644 --- a/mysql-test/suite/sql_sequence/other.test +++ b/mysql-test/suite/sql_sequence/other.test @@ -74,7 +74,7 @@ create sequence s1; create table t1 (a int); create view v1 as select * from s1; show full tables; -SELECT TABLE_TYPE,ENGINE FROM INFORMATION_SCHEMA.TABLES where table_schema="test"; +SELECT TABLE_TYPE,ENGINE FROM INFORMATION_SCHEMA.TABLES where table_schema="test" ORDER BY TABLE_NAME; drop table t1,s1; drop view v1; diff --git a/mysql-test/t/compound.test b/mysql-test/t/compound.test index cabdf96e00e..94a6c18b2f5 100644 --- a/mysql-test/t/compound.test +++ b/mysql-test/t/compound.test @@ -106,7 +106,8 @@ select @a| /**/ while (select count(*) from information_schema.tables where table_schema='test') do select concat('drop table ', table_name) into @a - from information_schema.tables where table_schema='test' limit 1; + from information_schema.tables where table_schema='test' + order by table_name limit 1; select @a as 'executing:'; prepare dt from @a; execute dt; diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index bd89f220060..a8c4eff7365 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -1017,6 +1017,7 @@ from имя_таблицы_в_кодировке_утф8_длиной_больш # database, table, field, key, view select * from имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48; +--sorted_result select TABLE_NAME from information_schema.tables where table_schema='test'; diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 94e56774bf4..ca806778f0e 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -1491,6 +1491,7 @@ SHOW CREATE TABLE information_schema.spatial_ref_sys; create table t1(g GEOMETRY, pt POINT); create table t2(g LINESTRING, pl POLYGON); +--sorted_result select * from information_schema.geometry_columns where f_table_schema='test'; drop table t1, t2; diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 417390dcafe..9c306de0e3f 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -32,7 +32,9 @@ create user mysqltest_3@localhost; create user mysqltest_3; +--sorted_result select * from information_schema.SCHEMATA where schema_name > 'm'; +--sorted_result select schema_name from information_schema.schemata; show databases like 't%'; show databases; @@ -71,6 +73,7 @@ select c, v2.table_name from v1 right join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; +--sorted_result select table_name from information_schema.TABLES where table_schema = "mysqltest" and table_name like "t%"; @@ -206,6 +209,7 @@ show create procedure sel2; # Test for views # create view v0 (c) as select schema_name from information_schema.schemata; +--sorted_result select * from v0; --replace_column 3 # explain select * from v0; @@ -222,6 +226,7 @@ create view v4 (c) as select COLLATION_NAME from information_schema.collations where COLLATION_NAME like "latin1%"; select * from v4; show keys from v4; +--sorted_result select * from information_schema.views where TABLE_NAME like "v%"; drop view v0, v1, v2, v3, v4; @@ -273,6 +278,7 @@ create table t1(a int); create view v1 (c) as select a from t1 with check option; create view v2 (c) as select a from t1 WITH LOCAL CHECK OPTION; create view v3 (c) as select a from t1 WITH CASCADED CHECK OPTION; +--sorted_result select * from information_schema.views; grant select (a) on test.t1 to joe@localhost with grant option; select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES; @@ -316,6 +322,7 @@ drop table t1; create table t1 (s1 int); insert into t1 values (0),(9),(0); +--sorted_result select s1 from t1 where s1 in (select version from information_schema.tables) union select version from information_schema.tables; @@ -408,12 +415,18 @@ create view v3 (c) as select sub1(1); create table t4(f1 int, KEY f1_key (f1)); drop table t1; drop function sub1; +--sorted_result select table_name from information_schema.views where table_schema='test'; +--sorted_result select table_name from information_schema.views where table_schema='test'; select column_name from information_schema.columns -where table_schema='test'; +where table_schema='test' and table_name='t4'; +select column_name from information_schema.columns +where table_schema='test' and table_name='v2'; +select column_name from information_schema.columns +where table_schema='test' and table_name='v3'; select index_name from information_schema.statistics where table_schema='test'; select constraint_name from information_schema.table_constraints where table_schema='test'; @@ -503,9 +516,11 @@ drop table t_crashme; # select table_schema,table_name, column_name from information_schema.columns -where data_type = 'longtext' and table_schema != 'performance_schema'; +where data_type = 'longtext' and table_schema != 'performance_schema' +order by binary table_name, ordinal_position; select table_name, column_name, data_type from information_schema.columns -where data_type = 'datetime' and table_name not like 'innodb_%'; +where data_type = 'datetime' and table_name not like 'innodb_%' +order by binary table_name, ordinal_position; # # Bug#8164 subquery with INFORMATION_SCHEMA.COLUMNS, 100 % CPU @@ -870,7 +885,7 @@ connect (con16681,localhost,mysqltest_1,,test); connection con16681; select * from information_schema.views -where table_name='v1' or table_name='v2'; +where table_name='v1' or table_name='v2' order by table_name; connection default; disconnect con16681; drop view v1, v2; @@ -884,7 +899,7 @@ set @a:= '.'; create table t1(f1 char(5)); create table t2(f1 char(5)); select concat(@a, table_name), @a, table_name -from information_schema.tables where table_schema = 'test'; +from information_schema.tables where table_schema = 'test' order by table_name; drop table t1,t2; @@ -996,7 +1011,8 @@ create table t2 (f1 int(11), f2 int(11)); select table_name from information_schema.tables where table_schema = 'test' and table_name not in (select table_name from information_schema.columns - where table_schema = 'test' and column_name = 'f3'); + where table_schema = 'test' and column_name = 'f3') +order by table_name; drop table t1,t2; @@ -1007,7 +1023,7 @@ create table t1(f1 int); create view v1 as select f1+1 as a from t1; create table t2 (f1 int, f2 int); create view v2 as select f1+1 as a, f2 as b from t2; -select table_name, is_updatable from information_schema.views; +select table_name, is_updatable from information_schema.views order by table_name; # # Note: we can perform 'delete' for non updatable view. # @@ -1507,9 +1523,9 @@ let $wait_condition= --source include/wait_condition.inc --echo # These statements should not be blocked by pending lock requests select table_name, column_name, data_type from information_schema.columns - where table_schema = 'test' and table_name in ('t1', 't2'); + where table_schema = 'test' and table_name in ('t1', 't2') order by table_name, column_name; select table_name, auto_increment from information_schema.tables - where table_schema = 'test' and table_name in ('t1', 't2'); + where table_schema = 'test' and table_name in ('t1', 't2') order by table_name; connection con3726_1; unlock tables; connection con3726_2; @@ -1671,14 +1687,14 @@ let $wait_condition= --echo # Issue query to I_S which will open 't0' and get --echo # blocked on 't1' because of RENAME. ---send select table_name, auto_increment from information_schema.tables where table_schema='mysqltest' +--send select table_name, auto_increment from information_schema.tables where table_schema='mysqltest' and table_name='t0' union select table_name, auto_increment from information_schema.tables where table_schema='mysqltest' and table_name<>'t0' order by table_name connect (con12828477_3, localhost, root,,mysqltest); --echo # Wait while the above SELECT is blocked. let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Waiting for table metadata lock" and - info = "select table_name, auto_increment from information_schema.tables where table_schema='mysqltest'"; + info like '%t0%union%t0%'; --source include/wait_condition.inc --echo # diff --git a/mysql-test/t/information_schema2.test b/mysql-test/t/information_schema2.test index 9810c5a0aae..d2fa3da2b5f 100644 --- a/mysql-test/t/information_schema2.test +++ b/mysql-test/t/information_schema2.test @@ -15,5 +15,5 @@ create table t2 (x int); create table t3 (x int); create table t4 AS select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE' ; delete from t4 where table_name not in (select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE'); -select * from t4; +select * from t4 order by table_name; drop table t1, t2, t3, t4; diff --git a/mysql-test/t/information_schema_db.test b/mysql-test/t/information_schema_db.test index 70ad9b5b920..94908570969 100644 --- a/mysql-test/t/information_schema_db.test +++ b/mysql-test/t/information_schema_db.test @@ -100,10 +100,10 @@ create function f2 () returns int return (select max(i) from t2); create view v2 as select f2(); drop table t2; select table_name, table_type, table_comment from information_schema.tables -where table_schema='test'; +where table_schema='test' order by table_name; drop table t1; select table_name, table_type, table_comment from information_schema.tables -where table_schema='test'; +where table_schema='test' order by table_name; drop function f1; drop function f2; drop view v1, v2; diff --git a/mysql-test/t/information_schema_inno.test b/mysql-test/t/information_schema_inno.test index 9a9658e9027..3cdbb8111d9 100644 --- a/mysql-test/t/information_schema_inno.test +++ b/mysql-test/t/information_schema_inno.test @@ -16,8 +16,10 @@ FOREIGN KEY (t1_id) REFERENCES t1(id) ON UPDATE CASCADE) ENGINE=INNODB; CREATE TABLE t3 (id INT PRIMARY KEY, t2_id INT, INDEX par_ind (t2_id), FOREIGN KEY (id, t2_id) REFERENCES t2(t1_id, id) ON DELETE CASCADE) ENGINE=INNODB; +--sorted_result select * from information_schema.TABLE_CONSTRAINTS where TABLE_SCHEMA= "test"; +--sorted_result select * from information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA= "test"; @@ -45,6 +47,7 @@ CREATE TABLE t5(b1 INT, b2 INT, INDEX (b1, b2), FOREIGN KEY (b1, b2) REFERENCES t4(b1, b2) ON UPDATE RESTRICT ON DELETE CASCADE) ENGINE=INNODB; +--sorted_result select a.CONSTRAINT_SCHEMA, b.TABLE_NAME, CONSTRAINT_TYPE, b.CONSTRAINT_NAME, UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME, MATCH_OPTION, UPDATE_RULE, DELETE_RULE, b.REFERENCED_TABLE_NAME @@ -74,7 +77,8 @@ create table `t-1` ( use test; select referenced_table_schema, referenced_table_name from information_schema.key_column_usage -where constraint_schema = 'db-1'; +where constraint_schema = 'db-1' +order by referenced_table_schema, referenced_table_name; drop database `db-1`; # diff --git a/mysql-test/t/information_schema_part.test b/mysql-test/t/information_schema_part.test index ea88f364c07..62fba4f53d8 100644 --- a/mysql-test/t/information_schema_part.test +++ b/mysql-test/t/information_schema_part.test @@ -63,7 +63,7 @@ subpartition by key (a) subpartition x22 tablespace t2) ); --replace_column 16 # 19 # 20 # -select * from information_schema.partitions where table_schema="test"; +select * from information_schema.partitions where table_schema="test" order by table_name, partition_name; drop table t1,t2; create table t1 ( diff --git a/mysql-test/t/mysql_upgrade_noengine.test b/mysql-test/t/mysql_upgrade_noengine.test index cfc3a1dc406..1d65e7ffa1c 100644 --- a/mysql-test/t/mysql_upgrade_noengine.test +++ b/mysql-test/t/mysql_upgrade_noengine.test @@ -19,16 +19,19 @@ install soname 'ha_archive'; vertical_results; create table t1 (a int) engine=blackhole; create table t2 (a int) engine=archive; -select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t2'; flush tables; uninstall plugin blackhole; uninstall plugin archive; -select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t2'; # upgrade from 10.1 - engines aren't enabled exec $MYSQL_UPGRADE 2>&1; -select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t2'; # pretend it's an upgrade from 10.0 alter table mysql.user drop column default_role, drop column max_statement_time; @@ -41,14 +44,16 @@ EOF # still upgrade from 10.1 exec $MYSQL_UPGRADE 2>&1; -select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t2'; alter table mysql.user drop column default_role, drop column max_statement_time; remove_file $datadir/mysql_upgrade_info; # upgrade from 10.0 - engines are enabled exec $MYSQL_UPGRADE 2>&1; -select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t2'; drop table t1, t2; diff --git a/mysql-test/t/partition_exchange.test b/mysql-test/t/partition_exchange.test index e538bee16cd..8dc40da0caa 100644 --- a/mysql-test/t/partition_exchange.test +++ b/mysql-test/t/partition_exchange.test @@ -29,14 +29,16 @@ SHOW CREATE TABLE t2; SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2'); +WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') +ORDER BY TABLE_NAME; ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2; SHOW CREATE TABLE t1; SHOW CREATE TABLE t2; SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2'); +WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') +ORDER BY TABLE_NAME; DROP TABLE t2; @@ -53,7 +55,8 @@ SHOW CREATE TABLE t1; SHOW CREATE TABLE t2; SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2'); +WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') +ORDER BY TABLE_NAME; --echo # Only the non partitioned table have ROW_FORMAT set. DROP TABLE t1, t2; @@ -77,7 +80,8 @@ SHOW CREATE TABLE t1; SHOW CREATE TABLE t2; SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2'); +WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') +ORDER BY TABLE_NAME; --echo # No table have ROW_FORMAT set. DROP TABLE t1, t2; @@ -101,7 +105,8 @@ SHOW CREATE TABLE t1; SHOW CREATE TABLE t2; SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2'); +WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') +ORDER BY TABLE_NAME; --echo # Not same ROW_FORMAT as default (but same). DROP TABLE t1, t2; @@ -125,7 +130,8 @@ SHOW CREATE TABLE t1; SHOW CREATE TABLE t2; SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2'); +WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') +ORDER BY TABLE_NAME; --echo # Not same ROW_FORMAT as default (tables differs). DROP TABLE t1, t2; @@ -150,7 +156,8 @@ SHOW CREATE TABLE t1; SHOW CREATE TABLE t2; SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2'); +WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') +ORDER BY TABLE_NAME; --echo # Different than default (forced ROW_TYPE) DROP TABLE t1, t2; @@ -175,7 +182,8 @@ SHOW CREATE TABLE t1; SHOW CREATE TABLE t2; SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2'); +WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2') +ORDER BY TABLE_NAME; --horizontal_results DROP TABLE t1, t2; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 94d779de203..7d4758403ad 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -5722,8 +5722,10 @@ create procedure bug18344_2() begin end| use test| +--sorted_result select schema_name from information_schema.schemata where schema_name like 'bug18344%'| +--sorted_result select routine_name,routine_schema from information_schema.routines where routine_schema like 'bug18344%'| diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index f90f1e2e927..6fdccee339d 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -2490,6 +2490,7 @@ drop table t1,t2; --echo # CREATE TABLE t1 (db VARCHAR(64) DEFAULT NULL); INSERT INTO t1 VALUES ('mysql'),('information_schema'); +--sorted_result SELECT * FROM t1 WHERE db IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA); DROP TABLE t1; diff --git a/mysql-test/t/type_datetime_hires.test b/mysql-test/t/type_datetime_hires.test index a62c227f563..d220a4601eb 100644 --- a/mysql-test/t/type_datetime_hires.test +++ b/mysql-test/t/type_datetime_hires.test @@ -57,6 +57,7 @@ INSERT INTO t2 VALUES ('2006-10-02 21:50:01.567',8), ('2006-10-02 21:50:01.5678',9); +--sorted_result select table_name,partition_name,partition_method,partition_expression,partition_description,table_rows from information_schema.partitions where table_name in ('t1', 't2'); drop table t1, t2; diff --git a/sql/handler.cc b/sql/handler.cc index ca4f8634091..5e392fa02e6 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -5173,6 +5173,13 @@ static int cmp_table_names(LEX_CSTRING * const *a, LEX_CSTRING * const *b) (uchar*)((*b)->str), (*b)->length); } +#ifndef DBUG_OFF +static int cmp_table_names_desc(LEX_CSTRING * const *a, LEX_CSTRING * const *b) +{ + return -cmp_table_names(a, b); +} +#endif + } Discovered_table_list::Discovered_table_list(THD *thd_arg, @@ -5225,6 +5232,15 @@ void Discovered_table_list::sort() tables->sort(cmp_table_names); } + +#ifndef DBUG_OFF +void Discovered_table_list::sort_desc() +{ + tables->sort(cmp_table_names_desc); +} +#endif + + void Discovered_table_list::remove_duplicates() { LEX_CSTRING **src= tables->front(); diff --git a/sql/handler.h b/sql/handler.h index f681040db39..fc98e53e308 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -4356,6 +4356,13 @@ public: void sort(); void remove_duplicates(); // assumes that the list is sorted +#ifndef DBUG_OFF + /* + Used to find unstable mtr tests querying + INFORMATION_SCHEMA.TABLES without ORDER BY. + */ + void sort_desc(); +#endif }; int ha_discover_table(THD *thd, TABLE_SHARE *share); diff --git a/sql/sql_show.cc b/sql/sql_show.cc index cd08959cc26..f5fd3427fa6 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -1001,7 +1001,7 @@ find_files(THD *thd, Dynamic_array<LEX_CSTRING*> *files, LEX_CSTRING *db, if (ha_discover_table_names(thd, db, dirp, &tl, false)) goto err; } -#if 1 // TODO: MDEV-13049: #if MYSQL_VERSION_ID < 100300 +#if MYSQL_VERSION_ID < 100300 /* incomplete optimization, but a less drastic change in GA version */ if (!thd->lex->select_lex.order_list.elements && !thd->lex->select_lex.group_list.elements) @@ -1009,6 +1009,17 @@ find_files(THD *thd, Dynamic_array<LEX_CSTRING*> *files, LEX_CSTRING *db, if (is_show_command(thd)) #endif tl.sort(); +#ifndef DBUG_OFF + else + { + /* + sort_desc() is used to find easier unstable mtr tests that query + INFORMATION_SCHEMA.{SCHEMATA|TABLES} without a proper ORDER BY. + This can be removed in some release after 10.3 (e.g. in 10.4). + */ + tl.sort_desc(); + } +#endif DBUG_PRINT("info",("found: %zu files", files->elements())); my_dirend(dirp); diff --git a/storage/connect/mysql-test/connect/r/infoschema-9739.result b/storage/connect/mysql-test/connect/r/infoschema-9739.result index 992f4ed0d58..2d54b578521 100644 --- a/storage/connect/mysql-test/connect/r/infoschema-9739.result +++ b/storage/connect/mysql-test/connect/r/infoschema-9739.result @@ -1,10 +1,8 @@ create table t1 (i int) engine=Connect table_type=XML option_list='xmlsup=domdoc'; Warnings: Warning 1105 No file name. Table will use t1.xml -select * from information_schema.tables where create_options like '%table_type=XML%'; +select * from information_schema.tables where table_schema='test' and create_options like '%table_type=XML%'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT Warnings: -Warning 1286 Unknown storage engine 'InnoDB' -Warning 1286 Unknown storage engine 'InnoDB' Warning 1296 Got error 174 'File t1.xml not found' from CONNECT drop table t1; diff --git a/storage/connect/mysql-test/connect/r/infoschema2-9739.result b/storage/connect/mysql-test/connect/r/infoschema2-9739.result index 7d8a6839ea5..0372874862d 100644 --- a/storage/connect/mysql-test/connect/r/infoschema2-9739.result +++ b/storage/connect/mysql-test/connect/r/infoschema2-9739.result @@ -3,10 +3,8 @@ Warning 1105 No file name. Table will use t1.xml create table t1 (i int) engine=Connect table_type=XML option_list='xmlsup=libxml2'; Warnings: Warning 1105 No file name. Table will use t1.xml -select * from information_schema.tables where create_options like '%table_type=XML%'; +select * from information_schema.tables where table_schema='test' and create_options like '%table_type=XML%'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT Warnings: -Warning 1286 Unknown storage engine 'InnoDB' -Warning 1286 Unknown storage engine 'InnoDB' Warning 1296 Got error 174 'File t1.xml not found' from CONNECT drop table t1; diff --git a/storage/connect/mysql-test/connect/t/infoschema-9739.test b/storage/connect/mysql-test/connect/t/infoschema-9739.test index de61164d10d..16f837cafcc 100644 --- a/storage/connect/mysql-test/connect/t/infoschema-9739.test +++ b/storage/connect/mysql-test/connect/t/infoschema-9739.test @@ -5,5 +5,5 @@ --source windows.inc create table t1 (i int) engine=Connect table_type=XML option_list='xmlsup=domdoc'; -select * from information_schema.tables where create_options like '%table_type=XML%'; +select * from information_schema.tables where table_schema='test' and create_options like '%table_type=XML%'; drop table t1; diff --git a/storage/connect/mysql-test/connect/t/infoschema2-9739.test b/storage/connect/mysql-test/connect/t/infoschema2-9739.test index 345274b0577..76681bc91ed 100644 --- a/storage/connect/mysql-test/connect/t/infoschema2-9739.test +++ b/storage/connect/mysql-test/connect/t/infoschema2-9739.test @@ -5,5 +5,5 @@ --source have_libxml2.inc create table t1 (i int) engine=Connect table_type=XML option_list='xmlsup=libxml2'; -select * from information_schema.tables where create_options like '%table_type=XML%'; +select * from information_schema.tables where table_schema='test' and create_options like '%table_type=XML%'; drop table t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/statistics.test b/storage/rocksdb/mysql-test/rocksdb/t/statistics.test index 70fc2f72b7e..25a1224c393 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/statistics.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/statistics.test @@ -45,15 +45,19 @@ while ($i<100000) --enable_query_log # should have some statistics before the memtable flush +--sorted_result SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = DATABASE() and table_name <> 't1'; # due to inconsistencies in when the memtable is flushed, just verify t1 has fewer # than the expected number of rows. +--sorted_result SELECT CASE WHEN table_rows < 100000 then 'true' else 'false' end from information_schema.tables where table_name = 't1'; # flush and get even better statistics set global rocksdb_force_flush_memtable_now = true; +--sorted_result SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = DATABASE(); +--sorted_result SELECT table_name, data_length>0, index_length>0 FROM information_schema.tables WHERE table_schema = DATABASE(); # restart the server, check the stats @@ -62,13 +66,17 @@ SELECT table_name, data_length>0, index_length>0 FROM information_schema.tables # give the server a chance to load in statistics --sleep 5 +--sorted_result SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = DATABASE(); +--sorted_result SELECT table_name, data_length>0, index_length>0 FROM information_schema.tables WHERE table_schema = DATABASE(); analyze table t1,t2,t3,t4,t5; # make sure that stats do not change after calling analyze table +--sorted_result SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = DATABASE(); +--sorted_result SELECT table_name, data_length>0, index_length>0 FROM information_schema.tables WHERE table_schema = DATABASE(); drop table t1, t2, t3; |