diff options
Diffstat (limited to 'mysql-test')
34 files changed, 1585 insertions, 9 deletions
diff --git a/mysql-test/include/system_db_struct.inc b/mysql-test/include/system_db_struct.inc index 123c82484b9..3d97ff67043 100644 --- a/mysql-test/include/system_db_struct.inc +++ b/mysql-test/include/system_db_struct.inc @@ -16,3 +16,6 @@ show create table proc; show create table event; show create table general_log; show create table slow_log; +show create table table_stat; +show create table column_stat; +show create table index_stat; diff --git a/mysql-test/include/world_schema_utf8.inc b/mysql-test/include/world_schema_utf8.inc new file mode 100644 index 00000000000..2a09c5d32d5 --- /dev/null +++ b/mysql-test/include/world_schema_utf8.inc @@ -0,0 +1,25 @@ +CREATE TABLE Country ( + Code char(3) NOT NULL default '', + Name char(52) NOT NULL default '', + SurfaceArea float(10,2) NOT NULL default '0.00', + Population int(11) NOT NULL default '0', + Capital int(11) default NULL, + PRIMARY KEY (Code), + UNIQUE INDEX (Name) +) CHARACTER SET utf8 COLLATE utf8_bin; +CREATE TABLE City ( + ID int(11) NOT NULL auto_increment, + Name char(35) NOT NULL default '', + Country char(3) NOT NULL default '', + Population int(11) NOT NULL default '0', + PRIMARY KEY (ID), + INDEX (Population), + INDEX (Country) +) CHARACTER SET utf8 COLLATE utf8_bin; +CREATE TABLE CountryLanguage ( + Country char(3) NOT NULL default '', + Language char(30) NOT NULL default '', + Percentage float(3,1) NOT NULL default '0.0', + PRIMARY KEY (Country, Language), + INDEX (Percentage) +) CHARACTER SET utf8 COLLATE utf8_bin; diff --git a/mysql-test/r/1st.result b/mysql-test/r/1st.result index 792d9eaf2f1..9c97cd3d2c3 100644 --- a/mysql-test/r/1st.result +++ b/mysql-test/r/1st.result @@ -7,6 +7,7 @@ performance_schema test show tables in mysql; Tables_in_mysql +column_stat columns_priv db event @@ -17,6 +18,7 @@ help_keyword help_relation help_topic host +index_stat ndb_binlog_index plugin proc @@ -24,6 +26,7 @@ procs_priv proxies_priv servers slow_log +table_stat tables_priv time_zone time_zone_leap_second diff --git a/mysql-test/r/connect.result b/mysql-test/r/connect.result index a43a4d383ac..dd31120f5a6 100644 --- a/mysql-test/r/connect.result +++ b/mysql-test/r/connect.result @@ -1,6 +1,7 @@ drop table if exists t1,t2; show tables; Tables_in_mysql +column_stat columns_priv db event @@ -11,6 +12,7 @@ help_keyword help_relation help_topic host +index_stat ndb_binlog_index plugin proc @@ -18,6 +20,7 @@ procs_priv proxies_priv servers slow_log +table_stat tables_priv time_zone time_zone_leap_second @@ -35,6 +38,7 @@ grant ALL on *.* to test@localhost identified by "gambling"; grant ALL on *.* to test@127.0.0.1 identified by "gambling"; show tables; Tables_in_mysql +column_stat columns_priv db event @@ -45,6 +49,7 @@ help_keyword help_relation help_topic host +index_stat ndb_binlog_index plugin proc @@ -52,6 +57,7 @@ procs_priv proxies_priv servers slow_log +table_stat tables_priv time_zone time_zone_leap_second @@ -77,6 +83,7 @@ ERROR HY000: Password hash should be a 41-digit hexadecimal number set password=old_password('gambling3'); show tables; Tables_in_mysql +column_stat columns_priv db event @@ -87,6 +94,7 @@ help_keyword help_relation help_topic host +index_stat ndb_binlog_index plugin proc @@ -94,6 +102,7 @@ procs_priv proxies_priv servers slow_log +table_stat tables_priv time_zone time_zone_leap_second diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index fcb40dae4ff..85729a3ea87 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -81,6 +81,7 @@ TRIGGERS USER_PRIVILEGES USER_STATISTICS VIEWS +column_stat columns_priv db event @@ -91,6 +92,7 @@ help_keyword help_relation help_topic host +index_stat plugin proc procs_priv @@ -102,6 +104,7 @@ t2 t3 t4 t5 +table_stat tables_priv time_zone time_zone_leap_second @@ -120,6 +123,7 @@ TABLE_CONSTRAINTS TABLE_CONSTRAINTS TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_STATISTICS TABLE_STATISTICS TRIGGERS TRIGGERS +table_stat table_stat tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second @@ -141,6 +145,7 @@ TABLE_CONSTRAINTS TABLE_CONSTRAINTS TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_STATISTICS TABLE_STATISTICS TRIGGERS TRIGGERS +table_stat table_stat tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second @@ -162,6 +167,7 @@ TABLE_CONSTRAINTS TABLE_CONSTRAINTS TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_STATISTICS TABLE_STATISTICS TRIGGERS TRIGGERS +table_stat table_stat tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second diff --git a/mysql-test/r/information_schema_all_engines.result b/mysql-test/r/information_schema_all_engines.result index 9304febcc5a..d9f437a29f3 100644 --- a/mysql-test/r/information_schema_all_engines.result +++ b/mysql-test/r/information_schema_all_engines.result @@ -310,4 +310,4 @@ Wildcard: inf_rmation_schema SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status' GROUP BY TABLE_SCHEMA; table_schema count(*) information_schema 40 -mysql 23 +mysql 26 diff --git a/mysql-test/r/log_tables_upgrade.result b/mysql-test/r/log_tables_upgrade.result index 5ed59eecc31..09dd996aa54 100644 --- a/mysql-test/r/log_tables_upgrade.result +++ b/mysql-test/r/log_tables_upgrade.result @@ -19,6 +19,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -28,6 +29,7 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK @@ -35,6 +37,7 @@ mysql.procs_priv OK mysql.proxies_priv OK mysql.renamed_general_log OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result index 247c2b80d62..e1d1153a47a 100644 --- a/mysql-test/r/mysql_upgrade.result +++ b/mysql-test/r/mysql_upgrade.result @@ -7,6 +7,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -16,12 +17,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -44,6 +47,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -53,12 +57,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -81,6 +87,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -90,12 +97,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -121,6 +130,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -130,12 +140,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -164,6 +176,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -173,12 +186,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -210,6 +225,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -219,12 +235,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -259,6 +277,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -268,12 +287,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK diff --git a/mysql-test/r/mysql_upgrade_ssl.result b/mysql-test/r/mysql_upgrade_ssl.result index a08e7c115cc..40a20e38a1d 100644 --- a/mysql-test/r/mysql_upgrade_ssl.result +++ b/mysql-test/r/mysql_upgrade_ssl.result @@ -9,6 +9,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -18,12 +19,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index ab707ceef80..288dc5bd213 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -3,6 +3,7 @@ drop view if exists v1; drop database if exists client_test_db; mtr.global_suppressions OK mtr.test_suppressions OK +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -12,12 +13,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -27,6 +30,7 @@ mysql.time_zone_transition_type OK mysql.user OK mtr.global_suppressions Table is already up to date mtr.test_suppressions Table is already up to date +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -36,12 +40,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -49,6 +55,7 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -58,12 +65,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -71,6 +80,7 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK +mysql.column_stat Table is already up to date mysql.columns_priv Table is already up to date mysql.db Table is already up to date mysql.event Table is already up to date @@ -80,12 +90,14 @@ mysql.help_keyword Table is already up to date mysql.help_relation Table is already up to date mysql.help_topic Table is already up to date mysql.host Table is already up to date +mysql.index_stat Table is already up to date mysql.ndb_binlog_index Table is already up to date mysql.plugin Table is already up to date mysql.proc Table is already up to date mysql.procs_priv Table is already up to date mysql.proxies_priv Table is already up to date mysql.servers Table is already up to date +mysql.table_stat Table is already up to date mysql.tables_priv Table is already up to date mysql.time_zone Table is already up to date mysql.time_zone_leap_second Table is already up to date diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index f9b64e88f68..b1c15bd00b2 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -493,6 +493,9 @@ The following options may be given as the first argument: partial_match_table_scan, semijoin, semijoin_with_cache, subquery_cache, table_elimination, extended_keys } and val is one of {on, off, default} + --optimizer-use-stat-tables=name + Specifies how to use system statistics tables. Possible + values are NEVER, COMPLEMENTARY, PREVERABLY, EXCLUSIVELY --performance-schema Enable the performance schema. --performance-schema-events-waits-history-long-size=# @@ -717,6 +720,9 @@ The following options may be given as the first argument: --skip-show-database Don't allow 'SHOW DATABASE' commands --skip-slave-start If set, slave is not autostarted. + --skip-stat-tables Start without statistical tables. Statistical data on + table cardinalities, columns and indexes from these + tables become unavailable --skip-thread-priority Don't give threads different priorities. This option is deprecated because it has no effect; the implied behavior @@ -990,6 +996,7 @@ old-style-user-limits FALSE optimizer-prune-level 1 optimizer-search-depth 62 optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +optimizer-use-stat-tables NEVER performance-schema FALSE performance-schema-events-waits-history-long-size 10000 performance-schema-events-waits-history-size 10 @@ -1050,6 +1057,7 @@ skip-name-resolve FALSE skip-networking FALSE skip-show-database FALSE skip-slave-start FALSE +skip-stat-tables TRUE slave-compressed-protocol FALSE slave-exec-mode STRICT slave-net-timeout 3600 diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result new file mode 100644 index 00000000000..3a83f9ba66c --- /dev/null +++ b/mysql-test/r/statistics.result @@ -0,0 +1,467 @@ +drop table if exists t1,t2; +set @save_optimizer_use_stat_tables=@@optimizer_use_stat_tables; +CREATE VIEW table_stat AS +SELECT * FROM mysql.table_stat; +CREATE VIEW column_stat AS +SELECT db_name, table_name, column_name, +min_value, max_value, +CAST(nulls_ratio AS decimal(12,4)) AS 'nulls_ratio', +CAST(avg_length AS decimal(12,4)) AS 'avg_length', +CAST(avg_frequency AS decimal(12,4)) AS 'avg_frequency' + FROM mysql.column_stat; +CREATE VIEW index_stat AS +SELECT db_name, table_name, index_name, prefix_arity, +CAST(avg_frequency AS decimal(12,4)) AS 'avg_frequency' + FROM mysql.index_stat; +DELETE FROM mysql.table_stat; +DELETE FROM mysql.column_stat; +DELETE FROM mysql.index_stat; +set optimizer_use_stat_tables='preferably'; +CREATE TABLE t1 ( +a int NOT NULL PRIMARY KEY, +b varchar(32), +c char(16), +d date, +e double, +f bit(3), +INDEX idx1 (b, e), +INDEX idx2(c, d), +INDEX idx3 (d), +INDEX idx4 (e, b, d) +); +INSERT INTO t1 VALUES +(0, NULL, NULL, NULL, NULL, NULL), +(7, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'dddddddd', '1990-05-15', 0.1, b'100'), +(17, 'vvvvvvvvvvvvv', 'aaaa', '1989-03-12', 0.01, b'101'), +(1, 'vvvvvvvvvvvvv', NULL, '1989-03-12', 0.01, b'100'), +(12, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.112, b'001'), +(23, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.1, b'100'), +(8, 'vvvvvvvvvvvvv', 'aaaa', '1999-07-23', 0.1, b'100'), +(22, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'aaaa', '1989-03-12', 0.112, b'001'), +(31, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'aaaa', '1999-07-23', 0.01, b'001'), +(10, NULL, 'aaaa', NULL, 0.01, b'010'), +(5, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.1, b'100'), +(15, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.1, b'010'), +(30, NULL, 'bbbbbb', NULL, NULL, b'100'), +(38, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, NULL, NULL), +(18, 'zzzzzzzzzzzzzzzzzz', 'ccccccccc', '1990-05-15', 0.01, b'010'), +(9, 'yyy', 'bbbbbb', '1998-08-28', 0.01, NULL), +(29, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.012, b'010'), +(3, 'yyy', 'dddddddd', '1990-05-15', 0.112, b'010'), +(39, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, 0.01, b'100'), +(14, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.1, b'100'), +(40, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', '1989-03-12', NULL, NULL), +(44, NULL, 'aaaa', '1989-03-12', NULL, b'010'), +(19, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.012, b'011'), +(21, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.112, b'100'), +(45, NULL, NULL, '1989-03-12', NULL, b'011'), +(2, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'ccccccccc', '1990-05-15', 0.1, b'001'), +(35, 'yyy', 'aaaa', '1990-05-15', 0.05, b'011'), +(4, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.01, b'101'), +(47, NULL, 'aaaa', '1990-05-15', 0.05, b'010'), +(42, NULL, 'ccccccccc', '1989-03-12', 0.01, b'010'), +(32, NULL, 'bbbbbb', '1990-05-15', 0.01, b'011'), +(49, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'aaaa', '1990-05-15', NULL, NULL), +(43, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'bbbbbb', '1990-05-15', NULL, b'100'), +(37, 'yyy', NULL, '1989-03-12', 0.05, b'011'), +(41, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.05, NULL), +(34, 'yyy', NULL, NULL, NULL, NULL), +(33, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.05, b'011'), +(24, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1990-05-15', 0.01, b'101'), +(11, 'yyy', 'ccccccccc', '1999-07-23', 0.1, NULL), +(25, 'zzzzzzzzzzzzzzzzzz', 'bbb', '1989-03-12', 0.01, b'101'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +SELECT * FROM table_stat; +db_name table_name cardinality +test t1 40 +SELECT * FROM column_stat; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency +test t1 a 0 49 0.0000 4.0000 1.0000 +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 +test t1 f 1 5 0.2000 1.0000 6.4000 +SELECT * FROM index_stat; +db_name table_name index_name prefix_arity avg_frequency +test t1 PRIMARY 1 1.0000 +test t1 idx1 1 6.4000 +test t1 idx1 2 1.6875 +test t1 idx2 1 7.0000 +test t1 idx2 2 2.3846 +test t1 idx3 1 8.5000 +test t1 idx4 1 6.2000 +test t1 idx4 2 1.6875 +test t1 idx4 3 1.1304 +SELECT COUNT(*) FROM t1; +COUNT(*) +40 +SELECT * FROM column_stat +WHERE db_name='test' AND table_name='t1' AND column_name='a'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency +test t1 a 0 49 0.0000 4.0000 1.0000 +SELECT MIN(t1.a), MAX(t1.a), +(SELECT COUNT(*) FROM t1 WHERE t1.b IS NULL) / +(SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.a)", +(SELECT COUNT(t1.a) FROM t1) / +(SELECT COUNT(DISTINCT t1.a) FROM t1) AS "AVG_FREQUENCY(t1.a)" +FROM t1; +MIN(t1.a) MAX(t1.a) NULLS_RATIO(t1.a) AVG_FREQUENCY(t1.a) +0 49 0.2000 1.0000 +SELECT * FROM column_stat +WHERE db_name='test' AND table_name='t1' AND column_name='b'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 +SELECT MIN(t1.b), MAX(t1.b), +(SELECT COUNT(*) FROM t1 WHERE t1.b IS NULL) / +(SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.b)", +(SELECT COUNT(t1.b) FROM t1) / +(SELECT COUNT(DISTINCT t1.b) FROM t1) AS "AVG_FREQUENCY(t1.b)" +FROM t1; +MIN(t1.b) MAX(t1.b) NULLS_RATIO(t1.b) AVG_FREQUENCY(t1.b) +vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 +SELECT * FROM column_stat +WHERE db_name='test' AND table_name='t1' AND column_name='c'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 +SELECT MIN(t1.c), MAX(t1.c), +(SELECT COUNT(*) FROM t1 WHERE t1.c IS NULL) / +(SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.c)", +(SELECT COUNT(t1.c) FROM t1) / +(SELECT COUNT(DISTINCT t1.c) FROM t1) AS "AVG_FREQUENCY(t1.c)" +FROM t1; +MIN(t1.c) MAX(t1.c) NULLS_RATIO(t1.c) AVG_FREQUENCY(t1.c) +aaaa dddddddd 0.1250 7.0000 +SELECT * FROM column_stat +WHERE db_name='test' AND table_name='t1' AND column_name='d'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 +SELECT MIN(t1.d), MAX(t1.d), +(SELECT COUNT(*) FROM t1 WHERE t1.d IS NULL) / +(SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.d)", +(SELECT COUNT(t1.d) FROM t1) / +(SELECT COUNT(DISTINCT t1.d) FROM t1) AS "AVG_FREQUENCY(t1.d)" +FROM t1; +MIN(t1.d) MAX(t1.d) NULLS_RATIO(t1.d) AVG_FREQUENCY(t1.d) +1989-03-12 1999-07-23 0.1500 8.5000 +SELECT * FROM column_stat +WHERE db_name='test' AND table_name='t1' AND column_name='e'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 +SELECT MIN(t1.e), MAX(t1.e), +(SELECT COUNT(*) FROM t1 WHERE t1.e IS NULL) / +(SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.e)", +(SELECT COUNT(t1.e) FROM t1) / +(SELECT COUNT(DISTINCT t1.e) FROM t1) AS "AVG_FREQUENCY(t1.e)" +FROM t1; +MIN(t1.e) MAX(t1.e) NULLS_RATIO(t1.e) AVG_FREQUENCY(t1.e) +0.01 0.112 0.2250 6.2000 +SELECT * FROM index_stat +WHERE db_name='test' AND table_name='t1' AND index_name='idx1'; +db_name table_name index_name prefix_arity avg_frequency +test t1 idx1 1 6.4000 +test t1 idx1 2 1.6875 +SELECT +(SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL) / +(SELECT COUNT(DISTINCT t1.b) FROM t1 WHERE t1.b IS NOT NULL) +AS 'ARITY 1', +(SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) / +(SELECT COUNT(DISTINCT t1.b, t1.e) FROM t1 +WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) +AS 'ARITY 2'; +ARITY 1 ARITY 2 +6.4000 1.6875 +SELECT * FROM index_stat +WHERE db_name='test' AND table_name='t1' AND index_name='idx2'; +db_name table_name index_name prefix_arity avg_frequency +test t1 idx2 1 7.0000 +test t1 idx2 2 2.3846 +SELECT +(SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL) / +(SELECT COUNT(DISTINCT t1.c) FROM t1 WHERE t1.c IS NOT NULL) +AS 'ARITY 1', +(SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL) / +(SELECT COUNT(DISTINCT t1.c, t1.d) FROM t1 +WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL) +AS 'ARITY 2'; +ARITY 1 ARITY 2 +7.0000 2.3846 +SELECT * FROM index_stat +WHERE db_name='test' AND table_name='t1' AND index_name='idx3'; +db_name table_name index_name prefix_arity avg_frequency +test t1 idx3 1 8.5000 +SELECT +(SELECT COUNT(*) FROM t1 WHERE t1.d IS NOT NULL) / +(SELECT COUNT(DISTINCT t1.d) FROM t1 WHERE t1.d IS NOT NULL) +AS 'ARITY 1'; +ARITY 1 +8.5000 +SELECT * FROM index_stat +WHERE db_name='test' AND table_name='t1' AND index_name='idx4'; +db_name table_name index_name prefix_arity avg_frequency +test t1 idx4 1 6.2000 +test t1 idx4 2 1.6875 +test t1 idx4 3 1.1304 +SELECT +(SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL) / +(SELECT COUNT(DISTINCT t1.e) FROM t1 WHERE t1.e IS NOT NULL) +AS 'ARITY 1', +(SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL) / +(SELECT COUNT(DISTINCT t1.e, t1.b) FROM t1 +WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL) +AS 'ARITY 2', +(SELECT COUNT(*) FROM t1 +WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL) / +(SELECT COUNT(DISTINCT t1.e, t1.b, t1.d) FROM t1 +WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL) +AS 'ARITY 3'; +ARITY 1 ARITY 2 ARITY 3 +6.2000 1.6875 1.1304 +CREATE TABLE t2 LIKE t1; +ALTER TABLE t2 ENGINE=InnoDB; +INSERT INTO t2 SELECT * FROM t1; +ANALYZE TABLE t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +SELECT * FROM table_stat; +db_name table_name cardinality +test t1 40 +test t2 40 +SELECT * FROM column_stat ORDER BY column_name; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency +test t1 a 0 49 0.0000 4.0000 1.0000 +test t2 a 0 49 0.0000 4.0000 1.0000 +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 +test t2 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 +test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 +test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 +test t2 e 0.01 0.112 0.2250 8.0000 6.2000 +test t1 f 1 5 0.2000 1.0000 6.4000 +test t2 f 1 5 0.2000 1.0000 6.4000 +SELECT * FROM index_stat ORDER BY index_name, prefix_arity, table_name; +db_name table_name index_name prefix_arity avg_frequency +test t1 PRIMARY 1 1.0000 +test t2 PRIMARY 1 1.0000 +test t1 idx1 1 6.4000 +test t2 idx1 1 6.4000 +test t1 idx1 2 1.6875 +test t2 idx1 2 1.6875 +test t1 idx2 1 7.0000 +test t2 idx2 1 7.0000 +test t1 idx2 2 2.3846 +test t2 idx2 2 2.3846 +test t1 idx3 1 8.5000 +test t2 idx3 1 8.5000 +test t1 idx4 1 6.2000 +test t2 idx4 1 6.2000 +test t1 idx4 2 1.6875 +test t2 idx4 2 1.6875 +test t1 idx4 3 1.1304 +test t2 idx4 3 1.1304 +DELETE FROM mysql.table_stat; +DELETE FROM mysql.column_stat; +DELETE FROM mysql.index_stat; +ALTER TABLE t1 +DROP INDEX idx1, +DROP INDEX idx4; +ALTER TABLE t1 +MODIFY COLUMN b text, +ADD INDEX idx1 (b(4), e), +ADD INDEX idx4 (e, b(4), d); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +SELECT * FROM column_stat; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency +test t1 a 0 49 0.0000 4.0000 1.0000 +test t1 b NULL NULL 0.2000 17.1250 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 +test t1 f 1 5 0.2000 1.0000 6.4000 +SELECT * FROM index_stat; +db_name table_name index_name prefix_arity avg_frequency +test t1 PRIMARY 1 1.0000 +test t1 idx2 1 7.0000 +test t1 idx2 2 2.3846 +test t1 idx3 1 8.5000 +test t1 idx1 1 NULL +test t1 idx1 2 NULL +test t1 idx4 1 6.2000 +test t1 idx4 2 NULL +test t1 idx4 3 NULL +DROP TABLE t1,t2; +DELETE FROM mysql.table_stat; +DELETE FROM mysql.column_stat; +DELETE FROM mysql.index_stat; +set optimizer_use_stat_tables='never'; +set names utf8; +CREATE DATABASE world; +use world; +CREATE TABLE Country ( +Code char(3) NOT NULL default '', +Name char(52) NOT NULL default '', +SurfaceArea float(10,2) NOT NULL default '0.00', +Population int(11) NOT NULL default '0', +Capital int(11) default NULL, +PRIMARY KEY (Code), +UNIQUE INDEX (Name) +) CHARACTER SET utf8 COLLATE utf8_bin; +CREATE TABLE City ( +ID int(11) NOT NULL auto_increment, +Name char(35) NOT NULL default '', +Country char(3) NOT NULL default '', +Population int(11) NOT NULL default '0', +PRIMARY KEY (ID), +INDEX (Population), +INDEX (Country) +) CHARACTER SET utf8 COLLATE utf8_bin; +CREATE TABLE CountryLanguage ( +Country char(3) NOT NULL default '', +Language char(30) NOT NULL default '', +Percentage float(3,1) NOT NULL default '0.0', +PRIMARY KEY (Country, Language), +INDEX (Percentage) +) CHARACTER SET utf8 COLLATE utf8_bin; +set optimizer_use_stat_tables='preferably'; +ANALYZE TABLE Country, City, CountryLanguage; +SELECT UPPER(db_name), UPPER(table_name), cardinality +FROM test.table_stat; +UPPER(db_name) UPPER(table_name) cardinality +WORLD COUNTRY 239 +WORLD CITY 4079 +WORLD COUNTRYLANGUAGE 984 +SELECT UPPER(db_name), UPPER(table_name), +column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency +FROM test.column_stat; +UPPER(db_name) UPPER(table_name) column_name min_value max_value nulls_ratio avg_length avg_frequency +WORLD COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 +WORLD COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 +WORLD COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 +WORLD COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 +WORLD COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 +WORLD CITY ID 1 4079 0.0000 4.0000 1.0000 +WORLD CITY Name A Coruña (La Coruña) Ürgenc 0.0000 8.6416 1.0195 +WORLD CITY Country ABW ZWE 0.0000 3.0000 17.5819 +WORLD CITY Population 42 10500000 0.0000 4.0000 1.0467 +WORLD COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 +WORLD COUNTRYLANGUAGE Language Abhyasi [South]Mande 0.0000 7.1778 2.1532 +WORLD COUNTRYLANGUAGE Percentage 0.0 99.9 0.0000 4.0000 2.7640 +SELECT UPPER(db_name), UPPER(table_name), +index_name, prefix_arity, avg_frequency +FROM test.index_stat; +UPPER(db_name) UPPER(table_name) index_name prefix_arity avg_frequency +WORLD COUNTRY PRIMARY 1 1.0000 +WORLD COUNTRY Name 1 1.0000 +WORLD CITY PRIMARY 1 1.0000 +WORLD CITY Population 1 1.0467 +WORLD CITY Country 1 17.5819 +WORLD COUNTRYLANGUAGE PRIMARY 1 4.2232 +WORLD COUNTRYLANGUAGE PRIMARY 2 1.0000 +WORLD COUNTRYLANGUAGE Percentage 1 2.7640 +use test; +set optimizer_use_stat_tables='never'; +CREATE DATABASE world_innodb; +use world_innodb; +CREATE TABLE Country ( +Code char(3) NOT NULL default '', +Name char(52) NOT NULL default '', +SurfaceArea float(10,2) NOT NULL default '0.00', +Population int(11) NOT NULL default '0', +Capital int(11) default NULL, +PRIMARY KEY (Code), +UNIQUE INDEX (Name) +) CHARACTER SET utf8 COLLATE utf8_bin; +CREATE TABLE City ( +ID int(11) NOT NULL auto_increment, +Name char(35) NOT NULL default '', +Country char(3) NOT NULL default '', +Population int(11) NOT NULL default '0', +PRIMARY KEY (ID), +INDEX (Population), +INDEX (Country) +) CHARACTER SET utf8 COLLATE utf8_bin; +CREATE TABLE CountryLanguage ( +Country char(3) NOT NULL default '', +Language char(30) NOT NULL default '', +Percentage float(3,1) NOT NULL default '0.0', +PRIMARY KEY (Country, Language), +INDEX (Percentage) +) CHARACTER SET utf8 COLLATE utf8_bin; +ALTER TABLE Country ENGINE=InnoDB; +ALTER TABLE City ENGINE=InnoDB; +ALTER TABLE CountryLanguage ENGINE=InnoDB; +set optimizer_use_stat_tables='preferably'; +ANALYZE TABLE Country, City, CountryLanguage; +SELECT UPPER(db_name), UPPER(table_name), cardinality +FROM test.table_stat; +UPPER(db_name) UPPER(table_name) cardinality +WORLD COUNTRY 239 +WORLD CITY 4079 +WORLD COUNTRYLANGUAGE 984 +WORLD_INNODB COUNTRY 239 +WORLD_INNODB CITY 4079 +WORLD_INNODB COUNTRYLANGUAGE 984 +SELECT UPPER(db_name), UPPER(table_name), +column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency +FROM test.column_stat; +UPPER(db_name) UPPER(table_name) column_name min_value max_value nulls_ratio avg_length avg_frequency +WORLD COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 +WORLD COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 +WORLD COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 +WORLD COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 +WORLD COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 +WORLD CITY ID 1 4079 0.0000 4.0000 1.0000 +WORLD CITY Name A Coruña (La Coruña) Ürgenc 0.0000 8.6416 1.0195 +WORLD CITY Country ABW ZWE 0.0000 3.0000 17.5819 +WORLD CITY Population 42 10500000 0.0000 4.0000 1.0467 +WORLD COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 +WORLD COUNTRYLANGUAGE Language Abhyasi [South]Mande 0.0000 7.1778 2.1532 +WORLD COUNTRYLANGUAGE Percentage 0.0 99.9 0.0000 4.0000 2.7640 +WORLD_INNODB COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 +WORLD_INNODB COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 +WORLD_INNODB COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 +WORLD_INNODB COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 +WORLD_INNODB COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 +WORLD_INNODB CITY ID 1 4079 0.0000 4.0000 1.0000 +WORLD_INNODB CITY Name A Coruña (La Coruña) Ürgenc 0.0000 8.6416 1.0195 +WORLD_INNODB CITY Country ABW ZWE 0.0000 3.0000 17.5819 +WORLD_INNODB CITY Population 42 10500000 0.0000 4.0000 1.0467 +WORLD_INNODB COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 +WORLD_INNODB COUNTRYLANGUAGE Language Abhyasi [South]Mande 0.0000 7.1778 2.1532 +WORLD_INNODB COUNTRYLANGUAGE Percentage 0.0 99.9 0.0000 4.0000 2.7640 +SELECT UPPER(db_name), UPPER(table_name), +index_name, prefix_arity, avg_frequency +FROM test.index_stat; +UPPER(db_name) UPPER(table_name) index_name prefix_arity avg_frequency +WORLD COUNTRY PRIMARY 1 1.0000 +WORLD COUNTRY Name 1 1.0000 +WORLD CITY PRIMARY 1 1.0000 +WORLD CITY Population 1 1.0467 +WORLD CITY Country 1 17.5819 +WORLD COUNTRYLANGUAGE PRIMARY 1 4.2232 +WORLD COUNTRYLANGUAGE PRIMARY 2 1.0000 +WORLD COUNTRYLANGUAGE Percentage 1 2.7640 +WORLD_INNODB COUNTRY PRIMARY 1 1.0000 +WORLD_INNODB COUNTRY Name 1 1.0000 +WORLD_INNODB CITY PRIMARY 1 1.0000 +WORLD_INNODB CITY Population 1 1.0467 +WORLD_INNODB CITY Country 1 17.5819 +WORLD_INNODB COUNTRYLANGUAGE PRIMARY 1 4.2232 +WORLD_INNODB COUNTRYLANGUAGE PRIMARY 2 1.0000 +WORLD_INNODB COUNTRYLANGUAGE Percentage 1 2.7640 +use test; +DROP DATABASE world; +DROP DATABASE world_innodb; +DELETE FROM mysql.table_stat; +DELETE FROM mysql.column_stat; +DELETE FROM mysql.index_stat; +DROP VIEW test.table_stat; +DROP VIEW test.column_stat; +DROP VIEW test.index_stat; +set optimizer_use_stat_tables=@save_optimizer_use_stat_tables; diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index d5409136ed4..985fbd0539d 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -1,5 +1,6 @@ show tables; Tables_in_db +column_stat columns_priv db event @@ -10,6 +11,7 @@ help_keyword help_relation help_topic host +index_stat ndb_binlog_index plugin proc @@ -17,6 +19,7 @@ procs_priv proxies_priv servers slow_log +table_stat tables_priv time_zone time_zone_leap_second @@ -264,5 +267,36 @@ slow_log CREATE TABLE `slow_log` ( `server_id` int(10) unsigned NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' +show create table table_stat; +Table Create Table +table_stat CREATE TABLE `table_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `cardinality` bigint(21) unsigned DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Tables' +show create table column_stat; +Table Create Table +column_stat CREATE TABLE `column_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `column_name` varchar(64) COLLATE utf8_bin NOT NULL, + `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `nulls_ratio` double DEFAULT NULL, + `avg_length` double DEFAULT NULL, + `avg_frequency` double DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`,`column_name`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Columns' +show create table index_stat; +Table Create Table +index_stat CREATE TABLE `index_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `index_name` varchar(64) COLLATE utf8_bin NOT NULL, + `prefix_arity` int(11) unsigned NOT NULL, + `avg_frequency` double DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`,`index_name`,`prefix_arity`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Indexes' show tables; Tables_in_test diff --git a/mysql-test/r/system_mysql_db_fix40123.result b/mysql-test/r/system_mysql_db_fix40123.result index d5409136ed4..985fbd0539d 100644 --- a/mysql-test/r/system_mysql_db_fix40123.result +++ b/mysql-test/r/system_mysql_db_fix40123.result @@ -1,5 +1,6 @@ show tables; Tables_in_db +column_stat columns_priv db event @@ -10,6 +11,7 @@ help_keyword help_relation help_topic host +index_stat ndb_binlog_index plugin proc @@ -17,6 +19,7 @@ procs_priv proxies_priv servers slow_log +table_stat tables_priv time_zone time_zone_leap_second @@ -264,5 +267,36 @@ slow_log CREATE TABLE `slow_log` ( `server_id` int(10) unsigned NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' +show create table table_stat; +Table Create Table +table_stat CREATE TABLE `table_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `cardinality` bigint(21) unsigned DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Tables' +show create table column_stat; +Table Create Table +column_stat CREATE TABLE `column_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `column_name` varchar(64) COLLATE utf8_bin NOT NULL, + `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `nulls_ratio` double DEFAULT NULL, + `avg_length` double DEFAULT NULL, + `avg_frequency` double DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`,`column_name`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Columns' +show create table index_stat; +Table Create Table +index_stat CREATE TABLE `index_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `index_name` varchar(64) COLLATE utf8_bin NOT NULL, + `prefix_arity` int(11) unsigned NOT NULL, + `avg_frequency` double DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`,`index_name`,`prefix_arity`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Indexes' show tables; Tables_in_test diff --git a/mysql-test/r/system_mysql_db_fix50030.result b/mysql-test/r/system_mysql_db_fix50030.result index d5409136ed4..985fbd0539d 100644 --- a/mysql-test/r/system_mysql_db_fix50030.result +++ b/mysql-test/r/system_mysql_db_fix50030.result @@ -1,5 +1,6 @@ show tables; Tables_in_db +column_stat columns_priv db event @@ -10,6 +11,7 @@ help_keyword help_relation help_topic host +index_stat ndb_binlog_index plugin proc @@ -17,6 +19,7 @@ procs_priv proxies_priv servers slow_log +table_stat tables_priv time_zone time_zone_leap_second @@ -264,5 +267,36 @@ slow_log CREATE TABLE `slow_log` ( `server_id` int(10) unsigned NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' +show create table table_stat; +Table Create Table +table_stat CREATE TABLE `table_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `cardinality` bigint(21) unsigned DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Tables' +show create table column_stat; +Table Create Table +column_stat CREATE TABLE `column_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `column_name` varchar(64) COLLATE utf8_bin NOT NULL, + `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `nulls_ratio` double DEFAULT NULL, + `avg_length` double DEFAULT NULL, + `avg_frequency` double DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`,`column_name`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Columns' +show create table index_stat; +Table Create Table +index_stat CREATE TABLE `index_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `index_name` varchar(64) COLLATE utf8_bin NOT NULL, + `prefix_arity` int(11) unsigned NOT NULL, + `avg_frequency` double DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`,`index_name`,`prefix_arity`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Indexes' show tables; Tables_in_test diff --git a/mysql-test/r/system_mysql_db_fix50117.result b/mysql-test/r/system_mysql_db_fix50117.result index d5409136ed4..985fbd0539d 100644 --- a/mysql-test/r/system_mysql_db_fix50117.result +++ b/mysql-test/r/system_mysql_db_fix50117.result @@ -1,5 +1,6 @@ show tables; Tables_in_db +column_stat columns_priv db event @@ -10,6 +11,7 @@ help_keyword help_relation help_topic host +index_stat ndb_binlog_index plugin proc @@ -17,6 +19,7 @@ procs_priv proxies_priv servers slow_log +table_stat tables_priv time_zone time_zone_leap_second @@ -264,5 +267,36 @@ slow_log CREATE TABLE `slow_log` ( `server_id` int(10) unsigned NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' +show create table table_stat; +Table Create Table +table_stat CREATE TABLE `table_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `cardinality` bigint(21) unsigned DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Tables' +show create table column_stat; +Table Create Table +column_stat CREATE TABLE `column_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `column_name` varchar(64) COLLATE utf8_bin NOT NULL, + `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `nulls_ratio` double DEFAULT NULL, + `avg_length` double DEFAULT NULL, + `avg_frequency` double DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`,`column_name`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Columns' +show create table index_stat; +Table Create Table +index_stat CREATE TABLE `index_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `index_name` varchar(64) COLLATE utf8_bin NOT NULL, + `prefix_arity` int(11) unsigned NOT NULL, + `avg_frequency` double DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`,`index_name`,`prefix_arity`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Indexes' show tables; Tables_in_test diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql.result b/mysql-test/suite/funcs_1/r/is_columns_mysql.result index 4eff12dab7b..c83f80cd28f 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql.result @@ -9,6 +9,14 @@ def mysql columns_priv Host 1 NO char 60 180 NULL NULL NULL utf8 utf8_bin char( def mysql columns_priv Table_name 4 NO char 64 192 NULL NULL NULL utf8 utf8_bin char(64) PRI select,insert,update,references def mysql columns_priv Timestamp 6 CURRENT_TIMESTAMP NO timestamp NULL NULL NULL NULL 0 NULL NULL timestamp on update CURRENT_TIMESTAMP select,insert,update,references def mysql columns_priv User 3 NO char 16 48 NULL NULL NULL utf8 utf8_bin char(16) PRI select,insert,update,references +def mysql column_stat avg_frequency 8 NULL YES double NULL NULL 22 NULL NULL NULL NULL double select,insert,update,references +def mysql column_stat avg_length 7 NULL YES double NULL NULL 22 NULL NULL NULL NULL double select,insert,update,references +def mysql column_stat column_name 3 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references +def mysql column_stat db_name 1 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references +def mysql column_stat max_value 5 NULL YES varchar 255 765 NULL NULL NULL utf8 utf8_bin varchar(255) select,insert,update,references +def mysql column_stat min_value 4 NULL YES varchar 255 765 NULL NULL NULL utf8 utf8_bin varchar(255) select,insert,update,references +def mysql column_stat nulls_ratio 6 NULL YES double NULL NULL 22 NULL NULL NULL NULL double select,insert,update,references +def mysql column_stat table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references def mysql db Alter_priv 13 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references def mysql db Alter_routine_priv 19 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references def mysql db Create_priv 8 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references @@ -97,6 +105,11 @@ def mysql host Select_priv 3 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci e def mysql host Show_view_priv 16 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references def mysql host Trigger_priv 20 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references def mysql host Update_priv 5 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references +def mysql index_stat avg_frequency 5 NULL YES double NULL NULL 22 NULL NULL NULL NULL double select,insert,update,references +def mysql index_stat db_name 1 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references +def mysql index_stat index_name 3 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references +def mysql index_stat prefix_arity 4 NULL NO int NULL NULL 10 0 NULL NULL NULL int(11) unsigned PRI select,insert,update,references +def mysql index_stat table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references def mysql ndb_binlog_index deletes 6 NULL NO bigint NULL NULL 20 0 NULL NULL NULL bigint(20) unsigned select,insert,update,references def mysql ndb_binlog_index epoch 3 NULL NO bigint NULL NULL 20 0 NULL NULL NULL bigint(20) unsigned PRI select,insert,update,references def mysql ndb_binlog_index File 2 NULL NO varchar 255 255 NULL NULL NULL latin1 latin1_swedish_ci varchar(255) select,insert,update,references @@ -169,6 +182,9 @@ def mysql tables_priv Table_name 4 NO char 64 192 NULL NULL NULL utf8 utf8_bin def mysql tables_priv Table_priv 7 NO set 98 294 NULL NULL NULL utf8 utf8_general_ci set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') select,insert,update,references def mysql tables_priv Timestamp 6 CURRENT_TIMESTAMP NO timestamp NULL NULL NULL NULL 0 NULL NULL timestamp on update CURRENT_TIMESTAMP select,insert,update,references def mysql tables_priv User 3 NO char 16 48 NULL NULL NULL utf8 utf8_bin char(16) PRI select,insert,update,references +def mysql table_stat cardinality 3 NULL YES bigint NULL NULL 20 0 NULL NULL NULL bigint(21) unsigned select,insert,update,references +def mysql table_stat db_name 1 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references +def mysql table_stat table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references def mysql time_zone Time_zone_id 1 NULL NO int NULL NULL 10 0 NULL NULL NULL int(10) unsigned PRI auto_increment select,insert,update,references def mysql time_zone Use_leap_seconds 2 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('Y','N') select,insert,update,references def mysql time_zone_leap_second Correction 2 NULL NO int NULL NULL 10 0 NULL NULL NULL int(11) select,insert,update,references @@ -258,6 +274,7 @@ ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML; COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME 3.0000 char utf8 utf8_bin 3.0000 enum utf8 utf8_bin +3.0000 varchar utf8 utf8_bin 3.0000 char utf8 utf8_general_ci 3.0000 enum utf8 utf8_general_ci 3.0000 set utf8 utf8_general_ci @@ -274,6 +291,7 @@ ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML; COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME NULL bigint NULL NULL NULL datetime NULL NULL +NULL double NULL NULL NULL int NULL NULL NULL smallint NULL NULL NULL time NULL NULL @@ -302,6 +320,14 @@ COL_CML TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH C 3.0000 mysql columns_priv Column_name char 64 192 utf8 utf8_bin char(64) NULL mysql columns_priv Timestamp timestamp NULL NULL NULL NULL timestamp 3.0000 mysql columns_priv Column_priv set 31 93 utf8 utf8_general_ci set('Select','Insert','Update','References') +3.0000 mysql column_stat db_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql column_stat table_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql column_stat column_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql column_stat min_value varchar 255 765 utf8 utf8_bin varchar(255) +3.0000 mysql column_stat max_value varchar 255 765 utf8 utf8_bin varchar(255) +NULL mysql column_stat nulls_ratio double NULL NULL NULL NULL double +NULL mysql column_stat avg_length double NULL NULL NULL NULL double +NULL mysql column_stat avg_frequency double NULL NULL NULL NULL double 3.0000 mysql db Host char 60 180 utf8 utf8_bin char(60) 3.0000 mysql db Db char 64 192 utf8 utf8_bin char(64) 3.0000 mysql db User char 16 48 utf8 utf8_bin char(16) @@ -390,6 +416,11 @@ NULL mysql help_topic help_category_id smallint NULL NULL NULL NULL smallint(5) 3.0000 mysql host Alter_routine_priv enum 1 3 utf8 utf8_general_ci enum('N','Y') 3.0000 mysql host Execute_priv enum 1 3 utf8 utf8_general_ci enum('N','Y') 3.0000 mysql host Trigger_priv enum 1 3 utf8 utf8_general_ci enum('N','Y') +3.0000 mysql index_stat db_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql index_stat table_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql index_stat index_name varchar 64 192 utf8 utf8_bin varchar(64) +NULL mysql index_stat prefix_arity int NULL NULL NULL NULL int(11) unsigned +NULL mysql index_stat avg_frequency double NULL NULL NULL NULL double NULL mysql ndb_binlog_index Position bigint NULL NULL NULL NULL bigint(20) unsigned 1.0000 mysql ndb_binlog_index File varchar 255 255 latin1 latin1_swedish_ci varchar(255) NULL mysql ndb_binlog_index epoch bigint NULL NULL NULL NULL bigint(20) unsigned @@ -462,6 +493,9 @@ NULL mysql slow_log server_id int NULL NULL NULL NULL int(10) unsigned NULL mysql tables_priv Timestamp timestamp NULL NULL NULL NULL timestamp 3.0000 mysql tables_priv Table_priv set 98 294 utf8 utf8_general_ci set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') 3.0000 mysql tables_priv Column_priv set 31 93 utf8 utf8_general_ci set('Select','Insert','Update','References') +3.0000 mysql table_stat db_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql table_stat table_name varchar 64 192 utf8 utf8_bin varchar(64) +NULL mysql table_stat cardinality bigint NULL NULL NULL NULL bigint(21) unsigned NULL mysql time_zone Time_zone_id int NULL NULL NULL NULL int(10) unsigned 3.0000 mysql time_zone Use_leap_seconds enum 1 3 utf8 utf8_general_ci enum('Y','N') NULL mysql time_zone_leap_second Transition_time bigint NULL NULL NULL NULL bigint(20) diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result index d99e7f06295..031d9660e5b 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result @@ -9,6 +9,14 @@ def mysql columns_priv Host 1 NO char 60 180 NULL NULL NULL utf8 utf8_bin char( def mysql columns_priv Table_name 4 NO char 64 192 NULL NULL NULL utf8 utf8_bin char(64) PRI def mysql columns_priv Timestamp 6 CURRENT_TIMESTAMP NO timestamp NULL NULL NULL NULL 0 NULL NULL timestamp on update CURRENT_TIMESTAMP def mysql columns_priv User 3 NO char 16 48 NULL NULL NULL utf8 utf8_bin char(16) PRI +def mysql column_stat avg_frequency 8 NULL YES double NULL NULL 22 NULL NULL NULL NULL double +def mysql column_stat avg_length 7 NULL YES double NULL NULL 22 NULL NULL NULL NULL double +def mysql column_stat column_name 3 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI +def mysql column_stat db_name 1 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI +def mysql column_stat max_value 5 NULL YES varchar 255 765 NULL NULL NULL utf8 utf8_bin varchar(255) +def mysql column_stat min_value 4 NULL YES varchar 255 765 NULL NULL NULL utf8 utf8_bin varchar(255) +def mysql column_stat nulls_ratio 6 NULL YES double NULL NULL 22 NULL NULL NULL NULL double +def mysql column_stat table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI def mysql db Alter_priv 13 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') def mysql db Alter_routine_priv 19 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') def mysql db Create_priv 8 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') @@ -97,6 +105,11 @@ def mysql host Select_priv 3 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci e def mysql host Show_view_priv 16 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') def mysql host Trigger_priv 20 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') def mysql host Update_priv 5 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') +def mysql index_stat avg_frequency 5 NULL YES double NULL NULL 22 NULL NULL NULL NULL double +def mysql index_stat db_name 1 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI +def mysql index_stat index_name 3 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI +def mysql index_stat prefix_arity 4 NULL NO int NULL NULL 10 0 NULL NULL NULL int(11) unsigned PRI +def mysql index_stat table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI def mysql ndb_binlog_index deletes 6 NULL NO bigint NULL NULL 20 0 NULL NULL NULL bigint(20) unsigned def mysql ndb_binlog_index epoch 3 NULL NO bigint NULL NULL 20 0 NULL NULL NULL bigint(20) unsigned PRI def mysql ndb_binlog_index File 2 NULL NO varchar 255 255 NULL NULL NULL latin1 latin1_swedish_ci varchar(255) @@ -169,6 +182,9 @@ def mysql tables_priv Table_name 4 NO char 64 192 NULL NULL NULL utf8 utf8_bin def mysql tables_priv Table_priv 7 NO set 98 294 NULL NULL NULL utf8 utf8_general_ci set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') def mysql tables_priv Timestamp 6 CURRENT_TIMESTAMP NO timestamp NULL NULL NULL NULL 0 NULL NULL timestamp on update CURRENT_TIMESTAMP def mysql tables_priv User 3 NO char 16 48 NULL NULL NULL utf8 utf8_bin char(16) PRI +def mysql table_stat cardinality 3 NULL YES bigint NULL NULL 20 0 NULL NULL NULL bigint(21) unsigned +def mysql table_stat db_name 1 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI +def mysql table_stat table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI def mysql time_zone Time_zone_id 1 NULL NO int NULL NULL 10 0 NULL NULL NULL int(10) unsigned PRI auto_increment def mysql time_zone Use_leap_seconds 2 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('Y','N') def mysql time_zone_leap_second Correction 2 NULL NO int NULL NULL 10 0 NULL NULL NULL int(11) @@ -258,6 +274,7 @@ ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML; COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME 3.0000 char utf8 utf8_bin 3.0000 enum utf8 utf8_bin +3.0000 varchar utf8 utf8_bin 3.0000 char utf8 utf8_general_ci 3.0000 enum utf8 utf8_general_ci 3.0000 set utf8 utf8_general_ci @@ -274,6 +291,7 @@ ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML; COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME NULL bigint NULL NULL NULL datetime NULL NULL +NULL double NULL NULL NULL int NULL NULL NULL smallint NULL NULL NULL time NULL NULL @@ -302,6 +320,14 @@ COL_CML TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH C 3.0000 mysql columns_priv Column_name char 64 192 utf8 utf8_bin char(64) NULL mysql columns_priv Timestamp timestamp NULL NULL NULL NULL timestamp 3.0000 mysql columns_priv Column_priv set 31 93 utf8 utf8_general_ci set('Select','Insert','Update','References') +3.0000 mysql column_stat db_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql column_stat table_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql column_stat column_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql column_stat min_value varchar 255 765 utf8 utf8_bin varchar(255) +3.0000 mysql column_stat max_value varchar 255 765 utf8 utf8_bin varchar(255) +NULL mysql column_stat nulls_ratio double NULL NULL NULL NULL double +NULL mysql column_stat avg_length double NULL NULL NULL NULL double +NULL mysql column_stat avg_frequency double NULL NULL NULL NULL double 3.0000 mysql db Host char 60 180 utf8 utf8_bin char(60) 3.0000 mysql db Db char 64 192 utf8 utf8_bin char(64) 3.0000 mysql db User char 16 48 utf8 utf8_bin char(16) @@ -390,6 +416,11 @@ NULL mysql help_topic help_category_id smallint NULL NULL NULL NULL smallint(5) 3.0000 mysql host Alter_routine_priv enum 1 3 utf8 utf8_general_ci enum('N','Y') 3.0000 mysql host Execute_priv enum 1 3 utf8 utf8_general_ci enum('N','Y') 3.0000 mysql host Trigger_priv enum 1 3 utf8 utf8_general_ci enum('N','Y') +3.0000 mysql index_stat db_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql index_stat table_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql index_stat index_name varchar 64 192 utf8 utf8_bin varchar(64) +NULL mysql index_stat prefix_arity int NULL NULL NULL NULL int(11) unsigned +NULL mysql index_stat avg_frequency double NULL NULL NULL NULL double NULL mysql ndb_binlog_index Position bigint NULL NULL NULL NULL bigint(20) unsigned 1.0000 mysql ndb_binlog_index File varchar 255 255 latin1 latin1_swedish_ci varchar(255) NULL mysql ndb_binlog_index epoch bigint NULL NULL NULL NULL bigint(20) unsigned @@ -462,6 +493,9 @@ NULL mysql slow_log server_id int NULL NULL NULL NULL int(10) unsigned NULL mysql tables_priv Timestamp timestamp NULL NULL NULL NULL timestamp 3.0000 mysql tables_priv Table_priv set 98 294 utf8 utf8_general_ci set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') 3.0000 mysql tables_priv Column_priv set 31 93 utf8 utf8_general_ci set('Select','Insert','Update','References') +3.0000 mysql table_stat db_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql table_stat table_name varchar 64 192 utf8 utf8_bin varchar(64) +NULL mysql table_stat cardinality bigint NULL NULL NULL NULL bigint(21) unsigned NULL mysql time_zone Time_zone_id int NULL NULL NULL NULL int(10) unsigned 3.0000 mysql time_zone Use_leap_seconds enum 1 3 utf8 utf8_general_ci enum('Y','N') NULL mysql time_zone_leap_second Transition_time bigint NULL NULL NULL NULL bigint(20) 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 afd1fe15fed..147e063fe6f 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 @@ -75,6 +75,9 @@ table_schema, table_name, column_name FROM information_schema.key_column_usage WHERE constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL; constraint_catalog constraint_schema constraint_name table_catalog table_schema table_name column_name +def mysql PRIMARY def mysql column_stat db_name +def mysql PRIMARY def mysql column_stat table_name +def mysql PRIMARY def mysql column_stat column_name def mysql PRIMARY def mysql columns_priv Host def mysql PRIMARY def mysql columns_priv Db def mysql PRIMARY def mysql columns_priv User @@ -96,6 +99,10 @@ 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 index_stat db_name +def mysql PRIMARY def mysql index_stat table_name +def mysql PRIMARY def mysql index_stat index_name +def mysql PRIMARY def mysql index_stat prefix_arity def mysql PRIMARY def mysql ndb_binlog_index epoch def mysql PRIMARY def mysql plugin name def mysql PRIMARY def mysql proc db @@ -111,6 +118,8 @@ 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 servers Server_name +def mysql PRIMARY def mysql table_stat db_name +def mysql PRIMARY def mysql table_stat 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 diff --git a/mysql-test/suite/funcs_1/r/is_statistics.result b/mysql-test/suite/funcs_1/r/is_statistics.result index 8543b207728..b0d52779ba5 100644 --- a/mysql-test/suite/funcs_1/r/is_statistics.result +++ b/mysql-test/suite/funcs_1/r/is_statistics.result @@ -85,6 +85,9 @@ 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; table_catalog table_schema table_name index_schema index_name +def mysql column_stat mysql PRIMARY +def mysql column_stat mysql PRIMARY +def mysql column_stat mysql PRIMARY def mysql columns_priv mysql PRIMARY def mysql columns_priv mysql PRIMARY def mysql columns_priv mysql PRIMARY @@ -107,6 +110,10 @@ def mysql help_topic mysql PRIMARY def mysql help_topic mysql name def mysql host mysql PRIMARY def mysql host mysql PRIMARY +def mysql index_stat mysql PRIMARY +def mysql index_stat mysql PRIMARY +def mysql index_stat mysql PRIMARY +def mysql index_stat mysql PRIMARY def mysql ndb_binlog_index mysql PRIMARY def mysql plugin mysql PRIMARY def mysql proc mysql PRIMARY @@ -124,6 +131,8 @@ def mysql proxies_priv mysql PRIMARY def mysql proxies_priv mysql PRIMARY def mysql proxies_priv mysql Grantor def mysql servers mysql PRIMARY +def mysql table_stat mysql PRIMARY +def mysql table_stat mysql PRIMARY def mysql tables_priv mysql PRIMARY def mysql tables_priv mysql PRIMARY def mysql tables_priv mysql PRIMARY diff --git a/mysql-test/suite/funcs_1/r/is_statistics_mysql.result b/mysql-test/suite/funcs_1/r/is_statistics_mysql.result index 4c7d58f96f1..3609fd97232 100644 --- a/mysql-test/suite/funcs_1/r/is_statistics_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_statistics_mysql.result @@ -12,6 +12,9 @@ def mysql columns_priv 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 4 Table_name A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 5 Column_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 3 column_name A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE @@ -29,6 +32,10 @@ def mysql help_topic 0 mysql name 1 name A #CARD# NULL NULL BTREE def mysql help_topic 0 mysql PRIMARY 1 help_topic_id A #CARD# NULL NULL BTREE def mysql host 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql host 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 3 index_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 4 prefix_arity A #CARD# NULL NULL BTREE def mysql ndb_binlog_index 0 mysql PRIMARY 1 epoch A #CARD# NULL NULL BTREE def mysql plugin 0 mysql PRIMARY 1 name A #CARD# NULL NULL BTREE def mysql proc 0 mysql PRIMARY 1 db A #CARD# NULL NULL BTREE @@ -51,6 +58,8 @@ def mysql tables_priv 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 4 Table_name A #CARD# NULL NULL BTREE +def mysql table_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql table_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE def mysql time_zone 0 mysql PRIMARY 1 Time_zone_id A #CARD# NULL NULL BTREE def mysql time_zone_leap_second 0 mysql PRIMARY 1 Transition_time A #CARD# NULL NULL BTREE def mysql time_zone_name 0 mysql PRIMARY 1 Name A #CARD# NULL NULL BTREE diff --git a/mysql-test/suite/funcs_1/r/is_statistics_mysql_embedded.result b/mysql-test/suite/funcs_1/r/is_statistics_mysql_embedded.result index 9137d70b88c..d6b5523a2b2 100644 --- a/mysql-test/suite/funcs_1/r/is_statistics_mysql_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_statistics_mysql_embedded.result @@ -12,6 +12,9 @@ def mysql columns_priv 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 4 Table_name A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 5 Column_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 3 column_name A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE @@ -29,6 +32,10 @@ def mysql help_topic 0 mysql name 1 name A #CARD# NULL NULL BTREE def mysql help_topic 0 mysql PRIMARY 1 help_topic_id A #CARD# NULL NULL BTREE def mysql host 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql host 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 3 index_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 4 prefix_arity A #CARD# NULL NULL BTREE def mysql ndb_binlog_index 0 mysql PRIMARY 1 epoch A #CARD# NULL NULL BTREE def mysql plugin 0 mysql PRIMARY 1 name A #CARD# NULL NULL BTREE def mysql proc 0 mysql PRIMARY 1 db A #CARD# NULL NULL BTREE @@ -51,6 +58,8 @@ def mysql tables_priv 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 4 Table_name A #CARD# NULL NULL BTREE +def mysql table_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql table_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE def mysql time_zone 0 mysql PRIMARY 1 Time_zone_id A #CARD# NULL NULL BTREE def mysql time_zone_leap_second 0 mysql PRIMARY 1 Transition_time A #CARD# NULL NULL BTREE def mysql time_zone_name 0 mysql PRIMARY 1 Name A #CARD# NULL NULL BTREE @@ -70,6 +79,9 @@ def mysql columns_priv 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 4 Table_name A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 5 Column_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 3 column_name A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE @@ -87,6 +99,10 @@ def mysql help_topic 0 mysql name 1 name A #CARD# NULL NULL BTREE def mysql help_topic 0 mysql PRIMARY 1 help_topic_id A #CARD# NULL NULL BTREE def mysql host 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql host 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 3 index_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 4 prefix_arity A #CARD# NULL NULL BTREE def mysql ndb_binlog_index 0 mysql PRIMARY 1 epoch A #CARD# NULL NULL BTREE def mysql plugin 0 mysql PRIMARY 1 name A #CARD# NULL NULL BTREE def mysql proc 0 mysql PRIMARY 1 db A #CARD# NULL NULL BTREE @@ -109,6 +125,8 @@ def mysql tables_priv 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 4 Table_name A #CARD# NULL NULL BTREE +def mysql table_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql table_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE def mysql time_zone 0 mysql PRIMARY 1 Time_zone_id A #CARD# NULL NULL BTREE def mysql time_zone_leap_second 0 mysql PRIMARY 1 Transition_time A #CARD# NULL NULL BTREE def mysql time_zone_name 0 mysql PRIMARY 1 Name A #CARD# NULL NULL BTREE 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 559a1f1f9f5..67a12d1529b 100644 --- a/mysql-test/suite/funcs_1/r/is_table_constraints.result +++ b/mysql-test/suite/funcs_1/r/is_table_constraints.result @@ -57,6 +57,7 @@ table_schema, table_name FROM information_schema.table_constraints WHERE constraint_catalog IS NOT NULL; constraint_catalog constraint_schema constraint_name table_schema table_name +def mysql PRIMARY mysql column_stat def mysql PRIMARY mysql columns_priv def mysql PRIMARY mysql db def mysql PRIMARY mysql event @@ -69,12 +70,14 @@ def mysql PRIMARY mysql help_relation def mysql PRIMARY mysql help_topic def mysql name mysql help_topic def mysql PRIMARY mysql host +def mysql PRIMARY mysql index_stat def mysql PRIMARY mysql ndb_binlog_index def mysql PRIMARY mysql plugin def mysql PRIMARY mysql proc def mysql PRIMARY mysql procs_priv def mysql PRIMARY mysql proxies_priv def mysql PRIMARY mysql servers +def mysql PRIMARY mysql table_stat def mysql PRIMARY mysql tables_priv def mysql PRIMARY mysql time_zone def mysql PRIMARY mysql time_zone_leap_second diff --git a/mysql-test/suite/funcs_1/r/is_table_constraints_mysql.result b/mysql-test/suite/funcs_1/r/is_table_constraints_mysql.result index bca333b6387..2629eca1325 100644 --- a/mysql-test/suite/funcs_1/r/is_table_constraints_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_table_constraints_mysql.result @@ -8,6 +8,7 @@ WHERE table_schema = 'mysql' ORDER BY table_schema,table_name,constraint_name; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE def mysql PRIMARY mysql columns_priv PRIMARY KEY +def mysql PRIMARY mysql column_stat PRIMARY KEY def mysql PRIMARY mysql db PRIMARY KEY def mysql PRIMARY mysql event PRIMARY KEY def mysql PRIMARY mysql func PRIMARY KEY @@ -19,6 +20,7 @@ def mysql PRIMARY mysql help_relation PRIMARY KEY def mysql name mysql help_topic UNIQUE def mysql PRIMARY mysql help_topic PRIMARY KEY def mysql PRIMARY mysql host PRIMARY KEY +def mysql PRIMARY mysql index_stat PRIMARY KEY def mysql PRIMARY mysql ndb_binlog_index PRIMARY KEY def mysql PRIMARY mysql plugin PRIMARY KEY def mysql PRIMARY mysql proc PRIMARY KEY @@ -26,6 +28,7 @@ def mysql PRIMARY mysql procs_priv PRIMARY KEY def mysql PRIMARY mysql proxies_priv PRIMARY KEY def mysql PRIMARY mysql servers PRIMARY KEY def mysql PRIMARY mysql tables_priv PRIMARY KEY +def mysql PRIMARY mysql table_stat PRIMARY KEY def mysql PRIMARY mysql time_zone PRIMARY KEY def mysql PRIMARY mysql time_zone_leap_second PRIMARY KEY def mysql PRIMARY mysql time_zone_name PRIMARY KEY diff --git a/mysql-test/suite/funcs_1/r/is_table_constraints_mysql_embedded.result b/mysql-test/suite/funcs_1/r/is_table_constraints_mysql_embedded.result index 307357cdd2b..726cebd590b 100644 --- a/mysql-test/suite/funcs_1/r/is_table_constraints_mysql_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_table_constraints_mysql_embedded.result @@ -8,6 +8,7 @@ WHERE table_schema = 'mysql' ORDER BY table_schema,table_name,constraint_name; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE def mysql PRIMARY mysql columns_priv PRIMARY KEY +def mysql PRIMARY mysql column_stat PRIMARY KEY def mysql PRIMARY mysql db PRIMARY KEY def mysql PRIMARY mysql event PRIMARY KEY def mysql PRIMARY mysql func PRIMARY KEY @@ -19,6 +20,7 @@ def mysql PRIMARY mysql help_relation PRIMARY KEY def mysql name mysql help_topic UNIQUE def mysql PRIMARY mysql help_topic PRIMARY KEY def mysql PRIMARY mysql host PRIMARY KEY +def mysql PRIMARY mysql index_stat PRIMARY KEY def mysql PRIMARY mysql ndb_binlog_index PRIMARY KEY def mysql PRIMARY mysql plugin PRIMARY KEY def mysql PRIMARY mysql proc PRIMARY KEY @@ -26,6 +28,7 @@ def mysql PRIMARY mysql procs_priv PRIMARY KEY def mysql PRIMARY mysql proxies_priv PRIMARY KEY def mysql PRIMARY mysql servers PRIMARY KEY def mysql PRIMARY mysql tables_priv PRIMARY KEY +def mysql PRIMARY mysql table_stat PRIMARY KEY def mysql PRIMARY mysql time_zone PRIMARY KEY def mysql PRIMARY mysql time_zone_leap_second PRIMARY KEY def mysql PRIMARY mysql time_zone_name PRIMARY KEY @@ -38,6 +41,7 @@ WHERE table_schema = 'mysql' ORDER BY table_schema,table_name,constraint_name; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE def mysql PRIMARY mysql columns_priv PRIMARY KEY +def mysql PRIMARY mysql column_stat PRIMARY KEY def mysql PRIMARY mysql db PRIMARY KEY def mysql PRIMARY mysql event PRIMARY KEY def mysql PRIMARY mysql func PRIMARY KEY @@ -49,6 +53,7 @@ def mysql PRIMARY mysql help_relation PRIMARY KEY def mysql name mysql help_topic UNIQUE def mysql PRIMARY mysql help_topic PRIMARY KEY def mysql PRIMARY mysql host PRIMARY KEY +def mysql PRIMARY mysql index_stat PRIMARY KEY def mysql PRIMARY mysql ndb_binlog_index PRIMARY KEY def mysql PRIMARY mysql plugin PRIMARY KEY def mysql PRIMARY mysql proc PRIMARY KEY @@ -56,6 +61,7 @@ def mysql PRIMARY mysql procs_priv PRIMARY KEY def mysql PRIMARY mysql proxies_priv PRIMARY KEY def mysql PRIMARY mysql servers PRIMARY KEY def mysql PRIMARY mysql tables_priv PRIMARY KEY +def mysql PRIMARY mysql table_stat PRIMARY KEY def mysql PRIMARY mysql time_zone PRIMARY KEY def mysql PRIMARY mysql time_zone_leap_second PRIMARY KEY def mysql PRIMARY mysql time_zone_name PRIMARY KEY diff --git a/mysql-test/suite/funcs_1/r/is_tables_mysql.result b/mysql-test/suite/funcs_1/r/is_tables_mysql.result index 1b317fa5bba..9b20b05a6d4 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_tables_mysql.result @@ -37,6 +37,29 @@ user_comment Column privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME column_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Columns +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME db TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA @@ -244,6 +267,29 @@ user_comment Host privileges; Merged with database privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME index_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Indexes +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME ndb_binlog_index TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA @@ -428,6 +474,29 @@ user_comment Table privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME table_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Tables +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME time_zone TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA diff --git a/mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result b/mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result index e5afebd0de9..80718c62d85 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result @@ -37,6 +37,29 @@ user_comment Column privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME column_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Columns +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME db TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA @@ -244,6 +267,29 @@ user_comment Host privileges; Merged with database privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME index_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Indexes +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME ndb_binlog_index TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA @@ -428,6 +474,29 @@ user_comment Table privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME table_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Tables +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME time_zone TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA @@ -605,6 +674,29 @@ user_comment Column privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME column_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Columns +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME db TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA @@ -812,6 +904,29 @@ user_comment Host privileges; Merged with database privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME index_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Indexes +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME ndb_binlog_index TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA @@ -996,6 +1111,29 @@ user_comment Table privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME table_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Tables +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME time_zone TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA diff --git a/mysql-test/suite/perfschema/r/pfs_upgrade.result b/mysql-test/suite/perfschema/r/pfs_upgrade.result index 4d7d9e28fe8..b15fb1ad7fa 100644 --- a/mysql-test/suite/perfschema/r/pfs_upgrade.result +++ b/mysql-test/suite/perfschema/r/pfs_upgrade.result @@ -25,7 +25,7 @@ ERROR 1050 (42S01) at line 428: Table 'setup_consumers' already exists ERROR 1050 (42S01) at line 445: Table 'setup_instruments' already exists ERROR 1050 (42S01) at line 461: Table 'setup_timers' already exists ERROR 1050 (42S01) at line 478: Table 'threads' already exists -ERROR 1644 (HY000) at line 1126: Unexpected content found in the performance_schema database. +ERROR 1644 (HY000) at line 1132: Unexpected content found in the performance_schema database. FATAL ERROR: Upgrade failed show tables like "user_table"; Tables_in_performance_schema (user_table) @@ -55,7 +55,7 @@ ERROR 1050 (42S01) at line 428: Table 'setup_consumers' already exists ERROR 1050 (42S01) at line 445: Table 'setup_instruments' already exists ERROR 1050 (42S01) at line 461: Table 'setup_timers' already exists ERROR 1050 (42S01) at line 478: Table 'threads' already exists -ERROR 1644 (HY000) at line 1126: Unexpected content found in the performance_schema database. +ERROR 1644 (HY000) at line 1132: Unexpected content found in the performance_schema database. FATAL ERROR: Upgrade failed show tables like "user_view"; Tables_in_performance_schema (user_view) @@ -83,7 +83,7 @@ ERROR 1050 (42S01) at line 428: Table 'setup_consumers' already exists ERROR 1050 (42S01) at line 445: Table 'setup_instruments' already exists ERROR 1050 (42S01) at line 461: Table 'setup_timers' already exists ERROR 1050 (42S01) at line 478: Table 'threads' already exists -ERROR 1644 (HY000) at line 1126: Unexpected content found in the performance_schema database. +ERROR 1644 (HY000) at line 1132: Unexpected content found in the performance_schema database. FATAL ERROR: Upgrade failed select name from mysql.proc where db='performance_schema'; name @@ -111,7 +111,7 @@ ERROR 1050 (42S01) at line 428: Table 'setup_consumers' already exists ERROR 1050 (42S01) at line 445: Table 'setup_instruments' already exists ERROR 1050 (42S01) at line 461: Table 'setup_timers' already exists ERROR 1050 (42S01) at line 478: Table 'threads' already exists -ERROR 1644 (HY000) at line 1126: Unexpected content found in the performance_schema database. +ERROR 1644 (HY000) at line 1132: Unexpected content found in the performance_schema database. FATAL ERROR: Upgrade failed select name from mysql.proc where db='performance_schema'; name @@ -139,7 +139,7 @@ ERROR 1050 (42S01) at line 428: Table 'setup_consumers' already exists ERROR 1050 (42S01) at line 445: Table 'setup_instruments' already exists ERROR 1050 (42S01) at line 461: Table 'setup_timers' already exists ERROR 1050 (42S01) at line 478: Table 'threads' already exists -ERROR 1644 (HY000) at line 1126: Unexpected content found in the performance_schema database. +ERROR 1644 (HY000) at line 1132: Unexpected content found in the performance_schema database. FATAL ERROR: Upgrade failed select name from mysql.event where db='performance_schema'; name diff --git a/mysql-test/suite/sys_vars/r/optimizer_use_stat_tables_basic.result b/mysql-test/suite/sys_vars/r/optimizer_use_stat_tables_basic.result new file mode 100644 index 00000000000..22c6859af62 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/optimizer_use_stat_tables_basic.result @@ -0,0 +1,116 @@ +SET @start_global_value = @@global.optimizer_use_stat_tables; +SELECT @start_global_value; +@start_global_value +NEVER +SET @start_session_value = @@session.optimizer_use_stat_tables; +SELECT @start_session_value; +@start_session_value +NEVER +SET @@global.optimizer_use_stat_tables = 2; +SET @@global.optimizer_use_stat_tables = DEFAULT; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +NEVER +SET @@session.optimizer_use_stat_tables = 3; +SET @@session.optimizer_use_stat_tables = DEFAULT; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +NEVER +SET @@global.optimizer_use_stat_tables = 0; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +NEVER +SET @@global.optimizer_use_stat_tables = 1; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +COMPLEMENTARY +SET @@global.optimizer_use_stat_tables = 2; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +PREFERABLY +SET @@global.optimizer_use_stat_tables = 3; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +EXCLUSIVELY +SET @@global.optimizer_use_stat_tables = NEVER; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +NEVER +SET @@global.optimizer_use_stat_tables = COMPLEMENTARY; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +COMPLEMENTARY +SET @@global.optimizer_use_stat_tables = PREFERABLY; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +PREFERABLY +SET @@global.optimizer_use_stat_tables = EXCLUSIVELY; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +EXCLUSIVELY +SET @@session.optimizer_use_stat_tables = 0; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +NEVER +SET @@session.optimizer_use_stat_tables = 1; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +COMPLEMENTARY +SET @@session.optimizer_use_stat_tables = 2; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +PREFERABLY +SET @@session.optimizer_use_stat_tables = 3; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +EXCLUSIVELY +SET @@session.optimizer_use_stat_tables = NEVER; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +NEVER +SET @@session.optimizer_use_stat_tables = PREFERABLY; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +PREFERABLY +SET @@session.optimizer_use_stat_tables = EXCLUSIVELY; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +EXCLUSIVELY +SET @@session.optimizer_use_stat_tables = COMPLEMENTARY; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +COMPLEMENTARY +set sql_mode=TRADITIONAL; +SET @@global.optimizer_use_stat_tables = 10; +ERROR 42000: Variable 'optimizer_use_stat_tables' can't be set to the value of '10' +SET @@global.optimizer_use_stat_tables = -1024; +ERROR 42000: Variable 'optimizer_use_stat_tables' can't be set to the value of '-1024' +SET @@global.optimizer_use_stat_tables = 2.4; +ERROR 42000: Incorrect argument type to variable 'optimizer_use_stat_tables' +SET @@global.optimizer_use_stat_tables = OFF; +ERROR 42000: Variable 'optimizer_use_stat_tables' can't be set to the value of 'OFF' +SET @@session.optimizer_use_stat_tables = 10; +ERROR 42000: Variable 'optimizer_use_stat_tables' can't be set to the value of '10' +SET @@session.optimizer_use_stat_tables = -2; +ERROR 42000: Variable 'optimizer_use_stat_tables' can't be set to the value of '-2' +SET @@session.optimizer_use_stat_tables = 1.2; +ERROR 42000: Incorrect argument type to variable 'optimizer_use_stat_tables' +SET @@session.optimizer_use_stat_tables = ON; +ERROR 42000: Variable 'optimizer_use_stat_tables' can't be set to the value of 'ON' +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='optimizer_use_stat_tables'; +VARIABLE_NAME VARIABLE_VALUE +OPTIMIZER_USE_STAT_TABLES EXCLUSIVELY +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES +WHERE VARIABLE_NAME='optimizer_use_stat_tables'; +VARIABLE_NAME VARIABLE_VALUE +OPTIMIZER_USE_STAT_TABLES COMPLEMENTARY +SET @@global.optimizer_use_stat_tables = @start_global_value; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +NEVER +SET @@session.optimizer_use_stat_tables = @start_session_value; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +NEVER +set sql_mode=''; diff --git a/mysql-test/suite/sys_vars/t/optimizer_use_stat_tables_basic.test b/mysql-test/suite/sys_vars/t/optimizer_use_stat_tables_basic.test new file mode 100644 index 00000000000..6e0a96c403e --- /dev/null +++ b/mysql-test/suite/sys_vars/t/optimizer_use_stat_tables_basic.test @@ -0,0 +1,112 @@ +--source include/load_sysvars.inc + +############################################################# +# Save initial value # +############################################################# + +SET @start_global_value = @@global.optimizer_use_stat_tables; +SELECT @start_global_value; +SET @start_session_value = @@session.optimizer_use_stat_tables; +SELECT @start_session_value; + +############################################################### +# Display the DEFAULT value of optimizer_use_stat_tables # +############################################################### + +SET @@global.optimizer_use_stat_tables = 2; +SET @@global.optimizer_use_stat_tables = DEFAULT; +SELECT @@global.optimizer_use_stat_tables; + +SET @@session.optimizer_use_stat_tables = 3; +SET @@session.optimizer_use_stat_tables = DEFAULT; +SELECT @@session.optimizer_use_stat_tables; + + +################################################################################## +# Change the value ofoptimizer_use_stat_tables to a valid value for GLOBAL Scope # +################################################################################## + +SET @@global.optimizer_use_stat_tables = 0; +SELECT @@global.optimizer_use_stat_tables; +SET @@global.optimizer_use_stat_tables = 1; +SELECT @@global.optimizer_use_stat_tables; +SET @@global.optimizer_use_stat_tables = 2; +SELECT @@global.optimizer_use_stat_tables; +SET @@global.optimizer_use_stat_tables = 3; +SELECT @@global.optimizer_use_stat_tables; + +SET @@global.optimizer_use_stat_tables = NEVER; +SELECT @@global.optimizer_use_stat_tables; +SET @@global.optimizer_use_stat_tables = COMPLEMENTARY; +SELECT @@global.optimizer_use_stat_tables; +SET @@global.optimizer_use_stat_tables = PREFERABLY; +SELECT @@global.optimizer_use_stat_tables; +SET @@global.optimizer_use_stat_tables = EXCLUSIVELY; +SELECT @@global.optimizer_use_stat_tables; + +#################################################################################### +# Change the value of optimizer_use_stat_tables to a valid value for SESSION Scope # +#################################################################################### + +SET @@session.optimizer_use_stat_tables = 0; +SELECT @@session.optimizer_use_stat_tables; +SET @@session.optimizer_use_stat_tables = 1; +SELECT @@session.optimizer_use_stat_tables; +SET @@session.optimizer_use_stat_tables = 2; +SELECT @@session.optimizer_use_stat_tables; +SET @@session.optimizer_use_stat_tables = 3; +SELECT @@session.optimizer_use_stat_tables; + +SET @@session.optimizer_use_stat_tables = NEVER; +SELECT @@session.optimizer_use_stat_tables; +SET @@session.optimizer_use_stat_tables = PREFERABLY; +SELECT @@session.optimizer_use_stat_tables; +SET @@session.optimizer_use_stat_tables = EXCLUSIVELY; +SELECT @@session.optimizer_use_stat_tables; +SET @@session.optimizer_use_stat_tables = COMPLEMENTARY; +SELECT @@session.optimizer_use_stat_tables; + +##################################################################### +# Change the value of optimizer_use_stat_tables to an invalid value # +##################################################################### +set sql_mode=TRADITIONAL; +--Error ER_WRONG_VALUE_FOR_VAR +SET @@global.optimizer_use_stat_tables = 10; +--Error ER_WRONG_VALUE_FOR_VAR +SET @@global.optimizer_use_stat_tables = -1024; +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.optimizer_use_stat_tables = 2.4; +--Error ER_WRONG_VALUE_FOR_VAR +SET @@global.optimizer_use_stat_tables = OFF; +--Error ER_WRONG_VALUE_FOR_VAR +SET @@session.optimizer_use_stat_tables = 10; +--Error ER_WRONG_VALUE_FOR_VAR +SET @@session.optimizer_use_stat_tables = -2; +--Error ER_WRONG_TYPE_FOR_VAR +SET @@session.optimizer_use_stat_tables = 1.2; +--Error ER_WRONG_VALUE_FOR_VAR +SET @@session.optimizer_use_stat_tables = ON; + +############################################################################### +# Check if the value in GLOBAL & SESSION Tables matches value in variable # +############################################################################### + +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='optimizer_use_stat_tables'; + +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES +WHERE VARIABLE_NAME='optimizer_use_stat_tables'; + +#################################### +# Restore initial value # +#################################### + +SET @@global.optimizer_use_stat_tables = @start_global_value; +SELECT @@global.optimizer_use_stat_tables; +SET @@session.optimizer_use_stat_tables = @start_session_value; +SELECT @@session.optimizer_use_stat_tables; +set sql_mode=''; + +###################################################### +# END OF optimizer_use_stat_tables TESTS # +######################################################
\ No newline at end of file diff --git a/mysql-test/t/statistics.test b/mysql-test/t/statistics.test new file mode 100644 index 00000000000..81108c9846d --- /dev/null +++ b/mysql-test/t/statistics.test @@ -0,0 +1,303 @@ +--source include/have_innodb.inc +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +set @save_optimizer_use_stat_tables=@@optimizer_use_stat_tables; + +CREATE VIEW table_stat AS +SELECT * FROM mysql.table_stat; + +CREATE VIEW column_stat AS + SELECT db_name, table_name, column_name, + min_value, max_value, + CAST(nulls_ratio AS decimal(12,4)) AS 'nulls_ratio', + CAST(avg_length AS decimal(12,4)) AS 'avg_length', + CAST(avg_frequency AS decimal(12,4)) AS 'avg_frequency' + FROM mysql.column_stat; + +CREATE VIEW index_stat AS + SELECT db_name, table_name, index_name, prefix_arity, + CAST(avg_frequency AS decimal(12,4)) AS 'avg_frequency' + FROM mysql.index_stat; + +DELETE FROM mysql.table_stat; +DELETE FROM mysql.column_stat; +DELETE FROM mysql.index_stat; + +set optimizer_use_stat_tables='preferably'; + +CREATE TABLE t1 ( + a int NOT NULL PRIMARY KEY, + b varchar(32), + c char(16), + d date, + e double, + f bit(3), + INDEX idx1 (b, e), + INDEX idx2(c, d), + INDEX idx3 (d), + INDEX idx4 (e, b, d) +); + +INSERT INTO t1 VALUES + (0, NULL, NULL, NULL, NULL, NULL), + (7, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'dddddddd', '1990-05-15', 0.1, b'100'), + (17, 'vvvvvvvvvvvvv', 'aaaa', '1989-03-12', 0.01, b'101'), + (1, 'vvvvvvvvvvvvv', NULL, '1989-03-12', 0.01, b'100'), + (12, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.112, b'001'), + (23, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.1, b'100'), + (8, 'vvvvvvvvvvvvv', 'aaaa', '1999-07-23', 0.1, b'100'), + (22, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'aaaa', '1989-03-12', 0.112, b'001'), + (31, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'aaaa', '1999-07-23', 0.01, b'001'), + (10, NULL, 'aaaa', NULL, 0.01, b'010'), + (5, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.1, b'100'), + (15, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.1, b'010'), + (30, NULL, 'bbbbbb', NULL, NULL, b'100'), + (38, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, NULL, NULL), + (18, 'zzzzzzzzzzzzzzzzzz', 'ccccccccc', '1990-05-15', 0.01, b'010'), + (9, 'yyy', 'bbbbbb', '1998-08-28', 0.01, NULL), + (29, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.012, b'010'), + (3, 'yyy', 'dddddddd', '1990-05-15', 0.112, b'010'), + (39, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, 0.01, b'100'), + (14, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.1, b'100'), + (40, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', '1989-03-12', NULL, NULL), + (44, NULL, 'aaaa', '1989-03-12', NULL, b'010'), + (19, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.012, b'011'), + (21, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.112, b'100'), + (45, NULL, NULL, '1989-03-12', NULL, b'011'), + (2, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'ccccccccc', '1990-05-15', 0.1, b'001'), + (35, 'yyy', 'aaaa', '1990-05-15', 0.05, b'011'), + (4, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.01, b'101'), + (47, NULL, 'aaaa', '1990-05-15', 0.05, b'010'), + (42, NULL, 'ccccccccc', '1989-03-12', 0.01, b'010'), + (32, NULL, 'bbbbbb', '1990-05-15', 0.01, b'011'), + (49, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'aaaa', '1990-05-15', NULL, NULL), + (43, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'bbbbbb', '1990-05-15', NULL, b'100'), + (37, 'yyy', NULL, '1989-03-12', 0.05, b'011'), + (41, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.05, NULL), + (34, 'yyy', NULL, NULL, NULL, NULL), + (33, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.05, b'011'), + (24, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1990-05-15', 0.01, b'101'), + (11, 'yyy', 'ccccccccc', '1999-07-23', 0.1, NULL), + (25, 'zzzzzzzzzzzzzzzzzz', 'bbb', '1989-03-12', 0.01, b'101'); + +ANALYZE TABLE t1; + +SELECT * FROM table_stat; +SELECT * FROM column_stat; +SELECT * FROM index_stat; + +SELECT COUNT(*) FROM t1; + +SELECT * FROM column_stat + WHERE db_name='test' AND table_name='t1' AND column_name='a'; +SELECT MIN(t1.a), MAX(t1.a), + (SELECT COUNT(*) FROM t1 WHERE t1.b IS NULL) / + (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.a)", + (SELECT COUNT(t1.a) FROM t1) / + (SELECT COUNT(DISTINCT t1.a) FROM t1) AS "AVG_FREQUENCY(t1.a)" +FROM t1; + +SELECT * FROM column_stat + WHERE db_name='test' AND table_name='t1' AND column_name='b'; +SELECT MIN(t1.b), MAX(t1.b), + (SELECT COUNT(*) FROM t1 WHERE t1.b IS NULL) / + (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.b)", + (SELECT COUNT(t1.b) FROM t1) / + (SELECT COUNT(DISTINCT t1.b) FROM t1) AS "AVG_FREQUENCY(t1.b)" +FROM t1; + +SELECT * FROM column_stat + WHERE db_name='test' AND table_name='t1' AND column_name='c'; +SELECT MIN(t1.c), MAX(t1.c), + (SELECT COUNT(*) FROM t1 WHERE t1.c IS NULL) / + (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.c)", + (SELECT COUNT(t1.c) FROM t1) / + (SELECT COUNT(DISTINCT t1.c) FROM t1) AS "AVG_FREQUENCY(t1.c)" +FROM t1; + +SELECT * FROM column_stat + WHERE db_name='test' AND table_name='t1' AND column_name='d'; +SELECT MIN(t1.d), MAX(t1.d), + (SELECT COUNT(*) FROM t1 WHERE t1.d IS NULL) / + (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.d)", + (SELECT COUNT(t1.d) FROM t1) / + (SELECT COUNT(DISTINCT t1.d) FROM t1) AS "AVG_FREQUENCY(t1.d)" +FROM t1; + +SELECT * FROM column_stat + WHERE db_name='test' AND table_name='t1' AND column_name='e'; +SELECT MIN(t1.e), MAX(t1.e), + (SELECT COUNT(*) FROM t1 WHERE t1.e IS NULL) / + (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.e)", + (SELECT COUNT(t1.e) FROM t1) / + (SELECT COUNT(DISTINCT t1.e) FROM t1) AS "AVG_FREQUENCY(t1.e)" +FROM t1; + +SELECT * FROM index_stat + WHERE db_name='test' AND table_name='t1' AND index_name='idx1'; +SELECT + (SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL) / + (SELECT COUNT(DISTINCT t1.b) FROM t1 WHERE t1.b IS NOT NULL) + AS 'ARITY 1', + (SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) / + (SELECT COUNT(DISTINCT t1.b, t1.e) FROM t1 + WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) + AS 'ARITY 2'; + +SELECT * FROM index_stat + WHERE db_name='test' AND table_name='t1' AND index_name='idx2'; +SELECT + (SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL) / + (SELECT COUNT(DISTINCT t1.c) FROM t1 WHERE t1.c IS NOT NULL) + AS 'ARITY 1', + (SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL) / + (SELECT COUNT(DISTINCT t1.c, t1.d) FROM t1 + WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL) + AS 'ARITY 2'; + +SELECT * FROM index_stat + WHERE db_name='test' AND table_name='t1' AND index_name='idx3'; +SELECT + (SELECT COUNT(*) FROM t1 WHERE t1.d IS NOT NULL) / + (SELECT COUNT(DISTINCT t1.d) FROM t1 WHERE t1.d IS NOT NULL) + AS 'ARITY 1'; + +SELECT * FROM index_stat + WHERE db_name='test' AND table_name='t1' AND index_name='idx4'; +SELECT + (SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL) / + (SELECT COUNT(DISTINCT t1.e) FROM t1 WHERE t1.e IS NOT NULL) + AS 'ARITY 1', + (SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL) / + (SELECT COUNT(DISTINCT t1.e, t1.b) FROM t1 + WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL) + AS 'ARITY 2', + (SELECT COUNT(*) FROM t1 + WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL) / + (SELECT COUNT(DISTINCT t1.e, t1.b, t1.d) FROM t1 + WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL) + AS 'ARITY 3'; + + +CREATE TABLE t2 LIKE t1; +ALTER TABLE t2 ENGINE=InnoDB; +INSERT INTO t2 SELECT * FROM t1; + +ANALYZE TABLE t2; + +SELECT * FROM table_stat; +SELECT * FROM column_stat ORDER BY column_name; +SELECT * FROM index_stat ORDER BY index_name, prefix_arity, table_name; + + +DELETE FROM mysql.table_stat; +DELETE FROM mysql.column_stat; +DELETE FROM mysql.index_stat; + +ALTER TABLE t1 + DROP INDEX idx1, + DROP INDEX idx4; +ALTER TABLE t1 + MODIFY COLUMN b text, + ADD INDEX idx1 (b(4), e), + ADD INDEX idx4 (e, b(4), d); + +ANALYZE TABLE t1; + +SELECT * FROM column_stat; +SELECT * FROM index_stat; + +DROP TABLE t1,t2; + +DELETE FROM mysql.table_stat; +DELETE FROM mysql.column_stat; +DELETE FROM mysql.index_stat; + +set optimizer_use_stat_tables='never'; + +set names utf8; + +CREATE DATABASE world; + +use world; + +--source include/world_schema_utf8.inc + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/world.inc +--enable_warnings +--enable_result_log +--enable_query_log + +set optimizer_use_stat_tables='preferably'; + +--disable_result_log +ANALYZE TABLE Country, City, CountryLanguage; +--enable_result_log + +SELECT UPPER(db_name), UPPER(table_name), cardinality + FROM test.table_stat; +SELECT UPPER(db_name), UPPER(table_name), + column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency + FROM test.column_stat; +SELECT UPPER(db_name), UPPER(table_name), + index_name, prefix_arity, avg_frequency + FROM test.index_stat; + +use test; + +set optimizer_use_stat_tables='never'; + +CREATE DATABASE world_innodb; + +use world_innodb; + +--source include/world_schema_utf8.inc + +ALTER TABLE Country ENGINE=InnoDB; +ALTER TABLE City ENGINE=InnoDB; +ALTER TABLE CountryLanguage ENGINE=InnoDB; + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/world.inc +--enable_warnings +--enable_result_log +--enable_query_log + +set optimizer_use_stat_tables='preferably'; + +--disable_result_log +ANALYZE TABLE Country, City, CountryLanguage; +--enable_result_log + +SELECT UPPER(db_name), UPPER(table_name), cardinality + FROM test.table_stat; +SELECT UPPER(db_name), UPPER(table_name), + column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency + FROM test.column_stat; +SELECT UPPER(db_name), UPPER(table_name), + index_name, prefix_arity, avg_frequency + FROM test.index_stat; + +use test; + +DROP DATABASE world; +DROP DATABASE world_innodb; + +DELETE FROM mysql.table_stat; +DELETE FROM mysql.column_stat; +DELETE FROM mysql.index_stat; + +DROP VIEW test.table_stat; +DROP VIEW test.column_stat; +DROP VIEW test.index_stat; + +set optimizer_use_stat_tables=@save_optimizer_use_stat_tables; + +
\ No newline at end of file diff --git a/mysql-test/t/system_mysql_db_fix40123.test b/mysql-test/t/system_mysql_db_fix40123.test index 5c82f0022b8..ae5b86262eb 100644 --- a/mysql-test/t/system_mysql_db_fix40123.test +++ b/mysql-test/t/system_mysql_db_fix40123.test @@ -59,6 +59,11 @@ CREATE TABLE time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, CREATE TABLE time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; +CREATE TABLE table_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, cardinality bigint(21) unsigned DEFAULT NULL, PRIMARY KEY (db_name,table_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Tables'; + +CREATE TABLE column_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, column_name varchar(64) NOT NULL, min_value varchar(255) DEFAULT NULL, max_value varchar(255) DEFAULT NULL, nulls_ratio double DEFAULT NULL, avg_length double DEFAULT NULL, avg_frequency double DEFAULT NULL, PRIMARY KEY (db_name,table_name,column_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns'; + +CREATE TABLE index_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, index_name varchar(64) NOT NULL, prefix_arity int(11) unsigned NOT NULL, avg_frequency double DEFAULT NULL, PRIMARY KEY (db_name,table_name,index_name,prefix_arity) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Indexes'; # Run the mysql_fix_privilege_tables.sql using "mysql --force" --exec $MYSQL --force test < $MYSQL_FIX_PRIVILEGE_TABLES @@ -72,7 +77,7 @@ CREATE TABLE time_zone_leap_second ( Transition_time bigint signed NOT NULL, -- disable_query_log # Drop all tables created by this test -DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, proxies_priv; +DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, proxies_priv, table_stat, column_stat, index_stat; -- enable_query_log diff --git a/mysql-test/t/system_mysql_db_fix50030.test b/mysql-test/t/system_mysql_db_fix50030.test index e8cfe6a6cf3..35938b6f7fa 100644 --- a/mysql-test/t/system_mysql_db_fix50030.test +++ b/mysql-test/t/system_mysql_db_fix50030.test @@ -66,6 +66,12 @@ CREATE TABLE servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) N INSERT INTO servers VALUES ('test','localhost','test','root','', 0,'','mysql','root'); +CREATE TABLE table_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, cardinality bigint(21) unsigned DEFAULT NULL, PRIMARY KEY (db_name,table_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Tables'; + +CREATE TABLE column_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, column_name varchar(64) NOT NULL, min_value varchar(255) DEFAULT NULL, max_value varchar(255) DEFAULT NULL, nulls_ratio double DEFAULT NULL, avg_length double DEFAULT NULL, avg_frequency double DEFAULT NULL, PRIMARY KEY (db_name,table_name,column_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns'; + +CREATE TABLE index_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, index_name varchar(64) NOT NULL, prefix_arity int(11) unsigned NOT NULL, avg_frequency double DEFAULT NULL, PRIMARY KEY (db_name,table_name,index_name,prefix_arity) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Indexes'; + # Run the mysql_fix_privilege_tables.sql using "mysql --force" --exec $MYSQL --force test < $MYSQL_FIX_PRIVILEGE_TABLES @@ -78,7 +84,7 @@ INSERT INTO servers VALUES ('test','localhost','test','root','', 0,'','mysql','r -- disable_query_log # Drop all tables created by this test -DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, proxies_priv; +DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, proxies_priv, table_stat, column_stat, index_stat; -- enable_query_log diff --git a/mysql-test/t/system_mysql_db_fix50117.test b/mysql-test/t/system_mysql_db_fix50117.test index 69ad68faaa8..bf19b70e77d 100644 --- a/mysql-test/t/system_mysql_db_fix50117.test +++ b/mysql-test/t/system_mysql_db_fix50117.test @@ -85,6 +85,12 @@ CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_b CREATE TABLE IF NOT EXISTS ndb_binlog_index (Position BIGINT UNSIGNED NOT NULL, File VARCHAR(255) NOT NULL, epoch BIGINT UNSIGNED NOT NULL, inserts BIGINT UNSIGNED NOT NULL, updates BIGINT UNSIGNED NOT NULL, deletes BIGINT UNSIGNED NOT NULL, schemaops BIGINT UNSIGNED NOT NULL, PRIMARY KEY(epoch)) ENGINE=MYISAM; +CREATE TABLE table_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, cardinality bigint(21) unsigned DEFAULT NULL, PRIMARY KEY (db_name,table_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Tables'; + +CREATE TABLE column_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, column_name varchar(64) NOT NULL, min_value varchar(255) DEFAULT NULL, max_value varchar(255) DEFAULT NULL, nulls_ratio double DEFAULT NULL, avg_length double DEFAULT NULL, avg_frequency double DEFAULT NULL, PRIMARY KEY (db_name,table_name,column_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns'; + +CREATE TABLE index_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, index_name varchar(64) NOT NULL, prefix_arity int(11) unsigned NOT NULL, avg_frequency double DEFAULT NULL, PRIMARY KEY (db_name,table_name,index_name,prefix_arity) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Indexes'; + # Run the mysql_fix_privilege_tables.sql using "mysql --force" --exec $MYSQL --force test < $MYSQL_FIX_PRIVILEGE_TABLES @@ -97,7 +103,7 @@ CREATE TABLE IF NOT EXISTS ndb_binlog_index (Position BIGINT UNSIGNED NOT NULL, -- disable_query_log # Drop all tables created by this test -DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, proxies_priv; +DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, proxies_priv, table_stat, column_stat, index_stat; -- enable_query_log |