diff options
author | Michael Widenius <monty@mariadb.org> | 2018-03-09 14:05:35 +0200 |
---|---|---|
committer | Monty <monty@mariadb.org> | 2018-03-29 13:59:44 +0300 |
commit | a7abddeffa6a760ce948c2dfb007cdf3f1a369d5 (patch) | |
tree | 70eb743fa965a17380bbc0ac88ae79ca1075b896 /mysql-test/main/information_schema.result | |
parent | ab1941266c59a19703a74b5593cf3f508a5752d7 (diff) | |
download | mariadb-git-a7abddeffa6a760ce948c2dfb007cdf3f1a369d5.tar.gz |
Create 'main' test directory and move 't' and 'r' there
Diffstat (limited to 'mysql-test/main/information_schema.result')
-rw-r--r-- | mysql-test/main/information_schema.result | 2191 |
1 files changed, 2191 insertions, 0 deletions
diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result new file mode 100644 index 00000000000..5fcda217d01 --- /dev/null +++ b/mysql-test/main/information_schema.result @@ -0,0 +1,2191 @@ +set global sql_mode=""; +set local sql_mode=""; +DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5; +DROP VIEW IF EXISTS v1; +show variables where variable_name like "skip_show_database"; +Variable_name Value +skip_show_database OFF +grant select, update, execute on test.* to mysqltest_2@localhost; +grant select, update on test.* to mysqltest_1@localhost; +create user mysqltest_3@localhost; +create user mysqltest_3; +select * from information_schema.SCHEMATA where schema_name > 'm'; +CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH +def mtr latin1 latin1_swedish_ci NULL +def mysql latin1 latin1_swedish_ci NULL +def performance_schema utf8 utf8_general_ci NULL +def test latin1 latin1_swedish_ci NULL +select schema_name from information_schema.schemata; +schema_name +information_schema +mtr +mysql +performance_schema +test +show databases like 't%'; +Database (t%) +test +show databases; +Database +information_schema +mtr +mysql +performance_schema +test +show databases where `database` = 't%'; +Database +create database mysqltest; +create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b)); +create table test.t2(a int); +create table t3(a int, KEY a_data (a)); +create table mysqltest.t4(a int); +create table t5 (id int auto_increment primary key); +insert into t5 values (10); +create view v1 (c) as +SELECT table_name FROM information_schema.TABLES +WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND +table_name not like 'innodb_%' AND +table_name not like 'xtradb_%'; +select * from v1; +c +ALL_PLUGINS +APPLICABLE_ROLES +CHARACTER_SETS +CLIENT_STATISTICS +COLLATIONS +COLLATION_CHARACTER_SET_APPLICABILITY +COLUMNS +COLUMN_PRIVILEGES +ENABLED_ROLES +ENGINES +EVENTS +FILES +GEOMETRY_COLUMNS +GLOBAL_STATUS +GLOBAL_VARIABLES +INDEX_STATISTICS +KEY_CACHES +KEY_COLUMN_USAGE +PARAMETERS +PARTITIONS +PLUGINS +PROCESSLIST +PROFILING +REFERENTIAL_CONSTRAINTS +ROUTINES +SCHEMATA +SCHEMA_PRIVILEGES +SESSION_STATUS +SESSION_VARIABLES +SPATIAL_REF_SYS +STATISTICS +SYSTEM_VARIABLES +TABLES +TABLESPACES +TABLE_CONSTRAINTS +TABLE_PRIVILEGES +TABLE_STATISTICS +TRIGGERS +USER_PRIVILEGES +USER_STATISTICS +VIEWS +column_stats +columns_priv +db +event +func +general_log +gtid_slave_pos +help_category +help_keyword +help_relation +help_topic +host +index_stats +plugin +proc +procs_priv +proxies_priv +roles_mapping +servers +slow_log +t1 +t2 +t3 +t4 +t5 +table_stats +tables_priv +time_zone +time_zone_leap_second +time_zone_name +time_zone_transition +time_zone_transition_type +transaction_registry +user +v1 +select c,table_name from v1 +inner join information_schema.TABLES v2 on (v1.c=v2.table_name) +where v1.c like "t%"; +c table_name +TABLES TABLES +TABLESPACES TABLESPACES +TABLE_CONSTRAINTS TABLE_CONSTRAINTS +TABLE_PRIVILEGES TABLE_PRIVILEGES +TABLE_STATISTICS TABLE_STATISTICS +TRIGGERS TRIGGERS +t1 t1 +t2 t2 +t3 t3 +t4 t4 +t5 t5 +table_stats table_stats +tables_priv tables_priv +time_zone time_zone +time_zone_leap_second time_zone_leap_second +time_zone_name time_zone_name +time_zone_transition time_zone_transition +time_zone_transition_type time_zone_transition_type +transaction_registry transaction_registry +select c,table_name from v1 +left join information_schema.TABLES v2 on (v1.c=v2.table_name) +where v1.c like "t%"; +c table_name +TABLES TABLES +TABLESPACES TABLESPACES +TABLE_CONSTRAINTS TABLE_CONSTRAINTS +TABLE_PRIVILEGES TABLE_PRIVILEGES +TABLE_STATISTICS TABLE_STATISTICS +TRIGGERS TRIGGERS +t1 t1 +t2 t2 +t3 t3 +t4 t4 +t5 t5 +table_stats table_stats +tables_priv tables_priv +time_zone time_zone +time_zone_leap_second time_zone_leap_second +time_zone_name time_zone_name +time_zone_transition time_zone_transition +time_zone_transition_type time_zone_transition_type +transaction_registry transaction_registry +select c, v2.table_name from v1 +right join information_schema.TABLES v2 on (v1.c=v2.table_name) +where v1.c like "t%"; +c table_name +TABLES TABLES +TABLESPACES TABLESPACES +TABLE_CONSTRAINTS TABLE_CONSTRAINTS +TABLE_PRIVILEGES TABLE_PRIVILEGES +TABLE_STATISTICS TABLE_STATISTICS +TRIGGERS TRIGGERS +t1 t1 +t2 t2 +t3 t3 +t4 t4 +t5 t5 +table_stats table_stats +tables_priv tables_priv +time_zone time_zone +time_zone_leap_second time_zone_leap_second +time_zone_name time_zone_name +time_zone_transition time_zone_transition +time_zone_transition_type time_zone_transition_type +transaction_registry transaction_registry +select table_name from information_schema.TABLES +where table_schema = "mysqltest" and table_name like "t%"; +table_name +t1 +t4 +select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest"; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT +def mysqltest t1 1 mysqltest string_data 1 b A NULL NULL NULL YES BTREE +show keys from t3 where Key_name = "a_data"; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +t3 1 a_data 1 a A NULL NULL NULL YES BTREE +show tables like 't%'; +Tables_in_test (t%) +t2 +t3 +t5 +show table status; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary +t2 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL # N +t3 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL # N +t5 MyISAM 10 Fixed 1 7 7 # 2048 0 11 # # NULL latin1_swedish_ci NULL # N +v1 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW # NULL +show full columns from t3 like "a%"; +Field Type Collation Null Key Default Extra Privileges Comment +a int(11) NULL YES MUL NULL select,insert,update,references +show full columns from mysql.db like "Insert%"; +Field Type Collation Null Key Default Extra Privileges Comment +Insert_priv enum('N','Y') utf8_general_ci NO N select,insert,update,references +show full columns from v1; +Field Type Collation Null Key Default Extra Privileges Comment +c varchar(64) utf8_general_ci NO select,insert,update,references +select * from information_schema.COLUMNS where table_name="t1" +and column_name= "a"; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION +def mysqltest t1 a 1 NULL YES int NULL NULL 10 0 NULL NULL NULL int(11) select,insert,update,references NEVER NULL +show columns from mysqltest.t1 where field like "%a%"; +Field Type Null Key Default Extra +a int(11) YES NULL +create view mysqltest.v1 (c) as select a from mysqltest.t1; +grant select (a) on mysqltest.t1 to mysqltest_2@localhost; +grant select on mysqltest.v1 to mysqltest_3; +connect user3,localhost,mysqltest_2,,; +connection user3; +select table_name, column_name, privileges from information_schema.columns +where table_schema = 'mysqltest' and table_name = 't1'; +table_name column_name privileges +t1 a select +show columns from mysqltest.t1; +Field Type Null Key Default Extra +a int(11) YES NULL +connect user4,localhost,mysqltest_3,,mysqltest; +connection user4; +select table_name, column_name, privileges from information_schema.columns +where table_schema = 'mysqltest' and table_name = 'v1'; +table_name column_name privileges +v1 c select +explain select * from v1; +ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +connection default; +disconnect user4; +drop view v1, mysqltest.v1; +drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5; +drop database mysqltest; +select * from information_schema.CHARACTER_SETS +where CHARACTER_SET_NAME like 'latin1%'; +CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN +latin1 latin1_swedish_ci cp1252 West European 1 +SHOW CHARACTER SET LIKE 'latin1%'; +Charset Description Default collation Maxlen +latin1 cp1252 West European latin1_swedish_ci 1 +SHOW CHARACTER SET WHERE charset like 'latin1%'; +Charset Description Default collation Maxlen +latin1 cp1252 West European latin1_swedish_ci 1 +select * from information_schema.COLLATIONS +where COLLATION_NAME like 'latin1%'; +COLLATION_NAME CHARACTER_SET_NAME ID IS_DEFAULT IS_COMPILED SORTLEN +latin1_german1_ci latin1 5 # 1 +latin1_swedish_ci latin1 8 Yes # 1 +latin1_danish_ci latin1 15 # 1 +latin1_german2_ci latin1 31 # 2 +latin1_bin latin1 47 # 1 +latin1_general_ci latin1 48 # 1 +latin1_general_cs latin1 49 # 1 +latin1_spanish_ci latin1 94 # 1 +latin1_swedish_nopad_ci latin1 1032 # 1 +latin1_nopad_bin latin1 1071 # 1 +SHOW COLLATION LIKE 'latin1%'; +Collation Charset Id Default Compiled Sortlen +latin1_german1_ci latin1 5 # 1 +latin1_swedish_ci latin1 8 Yes # 1 +latin1_danish_ci latin1 15 # 1 +latin1_german2_ci latin1 31 # 2 +latin1_bin latin1 47 # 1 +latin1_general_ci latin1 48 # 1 +latin1_general_cs latin1 49 # 1 +latin1_spanish_ci latin1 94 # 1 +latin1_swedish_nopad_ci latin1 1032 # 1 +latin1_nopad_bin latin1 1071 # 1 +SHOW COLLATION WHERE collation like 'latin1%'; +Collation Charset Id Default Compiled Sortlen +latin1_german1_ci latin1 5 # 1 +latin1_swedish_ci latin1 8 Yes # 1 +latin1_danish_ci latin1 15 # 1 +latin1_german2_ci latin1 31 # 2 +latin1_bin latin1 47 # 1 +latin1_general_ci latin1 48 # 1 +latin1_general_cs latin1 49 # 1 +latin1_spanish_ci latin1 94 # 1 +latin1_swedish_nopad_ci latin1 1032 # 1 +latin1_nopad_bin latin1 1071 # 1 +select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY +where COLLATION_NAME like 'latin1%'; +COLLATION_NAME CHARACTER_SET_NAME +latin1_german1_ci latin1 +latin1_swedish_ci latin1 +latin1_danish_ci latin1 +latin1_german2_ci latin1 +latin1_bin latin1 +latin1_general_ci latin1 +latin1_general_cs latin1 +latin1_spanish_ci latin1 +latin1_swedish_nopad_ci latin1 +latin1_nopad_bin latin1 +drop procedure if exists sel2; +drop function if exists sub1; +drop function if exists sub2; +create function sub1(i int) returns int +return i+1; +create procedure sel2() +begin +select * from t1; +select * from t2; +end| +select parameter_style, sql_data_access, dtd_identifier +from information_schema.routines where routine_schema='test'; +parameter_style sql_data_access dtd_identifier +SQL CONTAINS SQL NULL +SQL CONTAINS SQL int(11) +show procedure status where db='test'; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test sel2 PROCEDURE root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +show function status where db='test'; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test sub1 FUNCTION root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +select a.ROUTINE_NAME from information_schema.ROUTINES a, +information_schema.SCHEMATA b where +a.ROUTINE_SCHEMA = b.SCHEMA_NAME AND b.SCHEMA_NAME='test'; +ROUTINE_NAME +sel2 +sub1 +explain select a.ROUTINE_NAME from information_schema.ROUTINES a, +information_schema.SCHEMATA b where +a.ROUTINE_SCHEMA = b.SCHEMA_NAME; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE # ALL NULL NULL NULL NULL NULL +1 SIMPLE # ALL NULL NULL NULL NULL NULL Using where; Using join buffer (flat, BNL join) +select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a, +mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) AND a.ROUTINE_SCHEMA='test' order by 1; +ROUTINE_NAME name +sel2 sel2 +sub1 sub1 +select count(*) from information_schema.ROUTINES where routine_schema='test'; +count(*) +2 +create view v1 as select routine_schema, routine_name from information_schema.routines where routine_schema='test' +order by routine_schema, routine_name; +select * from v1; +routine_schema routine_name +test sel2 +test sub1 +drop view v1; +connect user1,localhost,mysqltest_1,,; +connection user1; +select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; +ROUTINE_NAME ROUTINE_DEFINITION +show create function sub1; +ERROR 42000: FUNCTION sub1 does not exist +connection user3; +select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; +ROUTINE_NAME ROUTINE_DEFINITION +sel2 NULL +sub1 NULL +connection default; +grant all privileges on test.* to mysqltest_1@localhost; +connect user2,localhost,mysqltest_1,,; +connection user2; +select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; +ROUTINE_NAME ROUTINE_DEFINITION +sel2 NULL +sub1 NULL +create function sub2(i int) returns int +return i+1; +select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; +ROUTINE_NAME ROUTINE_DEFINITION +sel2 NULL +sub1 NULL +sub2 return i+1 +show create procedure sel2; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +sel2 NULL latin1 latin1_swedish_ci latin1_swedish_ci +show create function sub1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +sub1 NULL latin1 latin1_swedish_ci latin1_swedish_ci +show create function sub2; +Function sql_mode Create Function character_set_client collation_connection Database Collation +sub2 CREATE DEFINER=`mysqltest_1`@`localhost` FUNCTION `sub2`(i int) RETURNS int(11) +return i+1 latin1 latin1_swedish_ci latin1_swedish_ci +show function status like "sub2"; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test sub2 FUNCTION mysqltest_1@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +connection default; +disconnect user1; +disconnect user3; +drop function sub2; +show create procedure sel2; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +sel2 CREATE DEFINER=`root`@`localhost` PROCEDURE `sel2`() +begin +select * from t1; +select * from t2; +end latin1 latin1_swedish_ci latin1_swedish_ci +create view v0 (c) as select schema_name from information_schema.schemata; +select * from v0; +c +information_schema +mtr +mysql +performance_schema +test +explain select * from v0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE # ALL NULL NULL NULL NULL NULL +create view v1 (c) as select table_name from information_schema.tables +where table_name="v1"; +select * from v1; +c +v1 +create view v2 (c) as select column_name from information_schema.columns +where table_name="v2"; +select * from v2; +c +c +create view v3 (c) as select CHARACTER_SET_NAME from information_schema.character_sets +where CHARACTER_SET_NAME like "latin1%"; +select * from v3; +c +latin1 +create view v4 (c) as select COLLATION_NAME from information_schema.collations +where COLLATION_NAME like "latin1%"; +select * from v4; +c +latin1_german1_ci +latin1_swedish_ci +latin1_danish_ci +latin1_german2_ci +latin1_bin +latin1_general_ci +latin1_general_cs +latin1_spanish_ci +latin1_swedish_nopad_ci +latin1_nopad_bin +show keys from v4; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +select * from information_schema.views where TABLE_NAME like "v%"; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM +def test v0 select `information_schema`.`schemata`.`SCHEMA_NAME` AS `c` from `information_schema`.`schemata` NONE NO root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +def test v1 select `information_schema`.`tables`.`TABLE_NAME` AS `c` from `information_schema`.`tables` where `information_schema`.`tables`.`TABLE_NAME` = 'v1' NONE NO root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +def test v2 select `information_schema`.`columns`.`COLUMN_NAME` AS `c` from `information_schema`.`columns` where `information_schema`.`columns`.`TABLE_NAME` = 'v2' NONE NO root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +def test v3 select `information_schema`.`character_sets`.`CHARACTER_SET_NAME` AS `c` from `information_schema`.`character_sets` where `information_schema`.`character_sets`.`CHARACTER_SET_NAME` like 'latin1%' NONE NO root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +def test v4 select `information_schema`.`collations`.`COLLATION_NAME` AS `c` from `information_schema`.`collations` where `information_schema`.`collations`.`COLLATION_NAME` like 'latin1%' NONE NO root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +drop view v0, v1, v2, v3, v4; +create table t1 (a int); +grant select,update,insert on t1 to mysqltest_1@localhost; +grant select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost; +grant all on test.* to mysqltest_1@localhost with grant option; +select * from information_schema.USER_PRIVILEGES where grantee like '%mysqltest_1%'; +GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE +'mysqltest_1'@'localhost' def USAGE NO +select * from information_schema.SCHEMA_PRIVILEGES where grantee like '%mysqltest_1%'; +GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE +'mysqltest_1'@'localhost' def test SELECT YES +'mysqltest_1'@'localhost' def test INSERT YES +'mysqltest_1'@'localhost' def test UPDATE YES +'mysqltest_1'@'localhost' def test DELETE YES +'mysqltest_1'@'localhost' def test CREATE YES +'mysqltest_1'@'localhost' def test DROP YES +'mysqltest_1'@'localhost' def test REFERENCES YES +'mysqltest_1'@'localhost' def test INDEX YES +'mysqltest_1'@'localhost' def test ALTER YES +'mysqltest_1'@'localhost' def test CREATE TEMPORARY TABLES YES +'mysqltest_1'@'localhost' def test LOCK TABLES YES +'mysqltest_1'@'localhost' def test EXECUTE YES +'mysqltest_1'@'localhost' def test CREATE VIEW YES +'mysqltest_1'@'localhost' def test SHOW VIEW YES +'mysqltest_1'@'localhost' def test CREATE ROUTINE YES +'mysqltest_1'@'localhost' def test ALTER ROUTINE YES +'mysqltest_1'@'localhost' def test EVENT YES +'mysqltest_1'@'localhost' def test TRIGGER YES +'mysqltest_1'@'localhost' def test DELETE VERSIONING ROWS YES +select * from information_schema.TABLE_PRIVILEGES where grantee like '%mysqltest_1%'; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE +'mysqltest_1'@'localhost' def test t1 SELECT NO +'mysqltest_1'@'localhost' def test t1 INSERT NO +'mysqltest_1'@'localhost' def test t1 UPDATE NO +select * from information_schema.COLUMN_PRIVILEGES where grantee like '%mysqltest_1%'; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE +'mysqltest_1'@'localhost' def test t1 a SELECT NO +'mysqltest_1'@'localhost' def test t1 a INSERT NO +'mysqltest_1'@'localhost' def test t1 a UPDATE NO +'mysqltest_1'@'localhost' def test t1 a REFERENCES NO +delete from mysql.user where user like 'mysqltest%'; +delete from mysql.db where user like 'mysqltest%'; +delete from mysql.tables_priv where user like 'mysqltest%'; +delete from mysql.columns_priv where user like 'mysqltest%'; +flush privileges; +drop table t1; +create table t1 (a int null, primary key(a)); +alter table t1 add constraint constraint_1 unique (a); +alter table t1 add constraint unique key_1(a); +Warnings: +Note 1831 Duplicate index `key_1`. This is deprecated and will be disallowed in a future release +alter table t1 add constraint constraint_2 unique key_2(a); +Warnings: +Note 1831 Duplicate index `key_2`. This is deprecated and will be disallowed in a future release +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`), + UNIQUE KEY `constraint_1` (`a`), + UNIQUE KEY `key_1` (`a`), + UNIQUE KEY `key_2` (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from information_schema.TABLE_CONSTRAINTS where +TABLE_SCHEMA= "test"; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE +def test PRIMARY test t1 PRIMARY KEY +def test constraint_1 test t1 UNIQUE +def test key_1 test t1 UNIQUE +def test key_2 test t1 UNIQUE +select * from information_schema.KEY_COLUMN_USAGE where +TABLE_SCHEMA= "test"; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME +def test PRIMARY def test t1 a 1 NULL NULL NULL NULL +def test constraint_1 def test t1 a 1 NULL NULL NULL NULL +def test key_1 def test t1 a 1 NULL NULL NULL NULL +def test key_2 def test t1 a 1 NULL NULL NULL NULL +connection user2; +select table_name from information_schema.TABLES where table_schema like "test%"; +table_name +t1 +select table_name,column_name from information_schema.COLUMNS where table_schema like "test%"; +table_name column_name +t1 a +select ROUTINE_NAME from information_schema.ROUTINES; +ROUTINE_NAME +sel2 +sub1 +disconnect user2; +connection default; +delete from mysql.user where user='mysqltest_1'; +drop table t1; +drop procedure sel2; +drop function sub1; +create table t1(a int); +create view v1 (c) as select a from t1 with check option; +create view v2 (c) as select a from t1 WITH LOCAL CHECK OPTION; +create view v3 (c) as select a from t1 WITH CASCADED CHECK OPTION; +select * from information_schema.views; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM +def test v1 select `test`.`t1`.`a` AS `c` from `test`.`t1` CASCADED YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +def test v2 select `test`.`t1`.`a` AS `c` from `test`.`t1` LOCAL YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +def test v3 select `test`.`t1`.`a` AS `c` from `test`.`t1` CASCADED YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +grant select (a) on test.t1 to joe@localhost with grant option; +select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE +'joe'@'localhost' def test t1 a SELECT YES +select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE +drop view v1, v2, v3; +drop table t1; +delete from mysql.user where user='joe'; +delete from mysql.db where user='joe'; +delete from mysql.tables_priv where user='joe'; +delete from mysql.columns_priv where user='joe'; +flush privileges; +create table t1 (a int not null auto_increment,b int, primary key (a)); +insert into t1 values (1,1),(NULL,3),(NULL,4); +select AUTO_INCREMENT from information_schema.tables where table_name = 't1'; +AUTO_INCREMENT +4 +drop table t1; +create table t1 (s1 int); +insert into t1 values (0),(9),(0); +select s1 from t1 where s1 in (select version from +information_schema.tables) union select version from +information_schema.tables; +s1 +10 +11 +drop table t1; +SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets; +Table Create Table +CHARACTER_SETS CREATE TEMPORARY TABLE `CHARACTER_SETS` ( + `CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '', + `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL DEFAULT '', + `DESCRIPTION` varchar(60) NOT NULL DEFAULT '', + `MAXLEN` bigint(3) NOT NULL DEFAULT 0 +) ENGINE=MEMORY DEFAULT CHARSET=utf8 +set names latin2; +SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets; +Table Create Table +CHARACTER_SETS CREATE TEMPORARY TABLE `CHARACTER_SETS` ( + `CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '', + `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL DEFAULT '', + `DESCRIPTION` varchar(60) NOT NULL DEFAULT '', + `MAXLEN` bigint(3) NOT NULL DEFAULT 0 +) ENGINE=MEMORY DEFAULT CHARSET=utf8 +set names latin1; +create table t1 select * from information_schema.CHARACTER_SETS +where CHARACTER_SET_NAME like "latin1"; +select * from t1; +CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN +latin1 latin1_swedish_ci cp1252 West European 1 +alter table t1 default character set utf8; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '', + `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL DEFAULT '', + `DESCRIPTION` varchar(60) NOT NULL DEFAULT '', + `MAXLEN` bigint(3) NOT NULL DEFAULT 0 +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +drop table t1; +create view v1 as select * from information_schema.TABLES; +drop view v1; +create table t1(a NUMERIC(5,3), b NUMERIC(5,1), c float(5,2), +d NUMERIC(6,4), e float, f DECIMAL(6,3), g int(11), h DOUBLE(10,3), +i DOUBLE); +select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, +CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE +from information_schema.columns where table_name= 't1'; +COLUMN_NAME COLUMN_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE +a decimal(5,3) NULL NULL 5 3 +b decimal(5,1) NULL NULL 5 1 +c float(5,2) NULL NULL 5 2 +d decimal(6,4) NULL NULL 6 4 +e float NULL NULL 12 NULL +f decimal(6,3) NULL NULL 6 3 +g int(11) NULL NULL 10 0 +h double(10,3) NULL NULL 10 3 +i double NULL NULL 22 NULL +drop table t1; +create table t115 as select table_name, column_name, column_type +from information_schema.columns where table_name = 'proc'; +select * from t115; +table_name column_name column_type +proc db char(64) +proc name char(64) +proc type enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') +proc specific_name char(64) +proc language enum('SQL') +proc sql_data_access enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') +proc is_deterministic enum('YES','NO') +proc security_type enum('INVOKER','DEFINER') +proc param_list blob +proc returns longblob +proc body longblob +proc definer char(141) +proc created timestamp +proc modified timestamp +proc sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT') +proc comment text +proc character_set_client char(32) +proc collation_connection char(32) +proc db_collation char(32) +proc body_utf8 longblob +proc aggregate enum('NONE','GROUP') +drop table t115; +create procedure p108 () begin declare c cursor for select data_type +from information_schema.columns; open c; open c; end;// +call p108()// +ERROR 24000: Cursor is already open +drop procedure p108; +create view v1 as select A1.table_name from information_schema.TABLES A1 +where table_name= "user"; +select * from v1; +table_name +user +drop view v1; +create view vo as select 'a' union select 'a'; +show index from vo; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +select * from information_schema.TABLE_CONSTRAINTS where +TABLE_NAME= "vo"; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE +select * from information_schema.KEY_COLUMN_USAGE where +TABLE_NAME= "vo"; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME +drop view vo; +select TABLE_NAME,TABLE_TYPE,ENGINE +from information_schema.tables +where table_schema='information_schema' limit 2; +TABLE_NAME TABLE_TYPE ENGINE +ALL_PLUGINS SYSTEM VIEW Aria +APPLICABLE_ROLES SYSTEM VIEW MEMORY +show tables from information_schema like "T%"; +Tables_in_information_schema (T%) +TABLES +TABLESPACES +TABLE_CONSTRAINTS +TABLE_PRIVILEGES +TABLE_STATISTICS +TRIGGERS +create database information_schema; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +use information_schema; +show full tables like "T%"; +Tables_in_information_schema (T%) Table_type +TABLES SYSTEM VIEW +TABLESPACES SYSTEM VIEW +TABLE_CONSTRAINTS SYSTEM VIEW +TABLE_PRIVILEGES SYSTEM VIEW +TABLE_STATISTICS SYSTEM VIEW +TRIGGERS SYSTEM VIEW +create table t1(a int); +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +use test; +show tables; +Tables_in_test +use information_schema; +show tables like "T%"; +Tables_in_information_schema (T%) +TABLES +TABLESPACES +TABLE_CONSTRAINTS +TABLE_PRIVILEGES +TABLE_STATISTICS +TRIGGERS +select table_name from tables where table_name='user'; +table_name +user +select column_name, privileges from columns +where table_name='user' and column_name like '%o%'; +column_name privileges +Host select,insert,update,references +Password select,insert,update,references +Drop_priv select,insert,update,references +Reload_priv select,insert,update,references +Shutdown_priv select,insert,update,references +Process_priv select,insert,update,references +Show_db_priv select,insert,update,references +Lock_tables_priv select,insert,update,references +Show_view_priv select,insert,update,references +Create_routine_priv select,insert,update,references +Alter_routine_priv select,insert,update,references +Delete_history_priv select,insert,update,references +max_questions select,insert,update,references +max_connections select,insert,update,references +max_user_connections select,insert,update,references +authentication_string select,insert,update,references +password_expired select,insert,update,references +is_role select,insert,update,references +default_role select,insert,update,references +use test; +create function sub1(i int) returns int +return i+1; +create table t1(f1 int); +create view v2 (c) as select f1 from t1; +create view v3 (c) as select sub1(1); +create table t4(f1 int, KEY f1_key (f1)); +drop table t1; +drop function sub1; +select table_name from information_schema.views +where table_schema='test'; +table_name +v2 +v3 +select table_name from information_schema.views +where table_schema='test'; +table_name +v2 +v3 +select column_name from information_schema.columns +where table_schema='test' and table_name='t4'; +column_name +f1 +select column_name from information_schema.columns +where table_schema='test' and table_name='v2'; +column_name +Warnings: +Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +select column_name from information_schema.columns +where table_schema='test' and table_name='v3'; +column_name +Warnings: +Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +select index_name from information_schema.statistics where table_schema='test'; +index_name +f1_key +select constraint_name from information_schema.table_constraints +where table_schema='test'; +constraint_name +show create view v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `test`.`t1`.`f1` AS `c` from `t1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +show create table v3; +View Create View character_set_client collation_connection +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `sub1`(1) AS `c` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +drop view v2; +drop view v3; +drop table t4; +select * from information_schema.table_names; +ERROR 42S02: Unknown table 'table_names' in information_schema +select column_type from information_schema.columns +where table_schema="information_schema" and table_name="COLUMNS" and +(column_name="character_set_name" or column_name="collation_name"); +column_type +varchar(32) +varchar(32) +select TABLE_ROWS from information_schema.tables where +table_schema="information_schema" and table_name="COLUMNS"; +TABLE_ROWS +NULL +select table_type from information_schema.tables +where table_schema="mysql" and table_name="user"; +table_type +BASE TABLE +show open tables where `table` like "user"; +Database Table In_use Name_locked +mysql user 0 0 +show status where variable_name like "%database%"; +Variable_name Value +Acl_database_grants 2 +Com_show_databases 3 +show variables where variable_name like "skip_show_databas"; +Variable_name Value +show global status like "Threads_running"; +Variable_name Value +Threads_running # +create table t1(f1 int); +create table t2(f2 int); +create view v1 as select * from t1, t2; +set @got_val= (select count(*) from information_schema.columns); +drop view v1; +drop table t1, t2; +use test; +CREATE TABLE t_crashme ( f1 BIGINT); +CREATE VIEW a1 (t_CRASHME) AS SELECT f1 FROM t_crashme GROUP BY f1; +CREATE VIEW a2 AS SELECT t_CRASHME FROM a1; +count(*) +68 +drop view a2, a1; +drop table t_crashme; +select table_schema,table_name, column_name from +information_schema.columns +where data_type = 'longtext' and table_schema != 'performance_schema' +order by binary table_name, ordinal_position; +table_schema table_name column_name +information_schema ALL_PLUGINS PLUGIN_DESCRIPTION +information_schema COLUMNS COLUMN_DEFAULT +information_schema COLUMNS COLUMN_TYPE +information_schema COLUMNS GENERATION_EXPRESSION +information_schema EVENTS EVENT_DEFINITION +information_schema PARAMETERS DTD_IDENTIFIER +information_schema PARTITIONS PARTITION_EXPRESSION +information_schema PARTITIONS SUBPARTITION_EXPRESSION +information_schema PARTITIONS PARTITION_DESCRIPTION +information_schema PLUGINS PLUGIN_DESCRIPTION +information_schema PROCESSLIST INFO +information_schema ROUTINES DTD_IDENTIFIER +information_schema ROUTINES ROUTINE_DEFINITION +information_schema ROUTINES ROUTINE_COMMENT +information_schema SYSTEM_VARIABLES ENUM_VALUE_LIST +information_schema TRIGGERS ACTION_CONDITION +information_schema TRIGGERS ACTION_STATEMENT +information_schema VIEWS VIEW_DEFINITION +select table_name, column_name, data_type from information_schema.columns +where data_type = 'datetime' and table_name not like 'innodb_%' +order by binary table_name, ordinal_position; +table_name column_name data_type +EVENTS EXECUTE_AT datetime +EVENTS STARTS datetime +EVENTS ENDS datetime +EVENTS CREATED datetime +EVENTS LAST_ALTERED datetime +EVENTS LAST_EXECUTED datetime +FILES CREATION_TIME datetime +FILES LAST_UPDATE_TIME datetime +FILES LAST_ACCESS_TIME datetime +FILES CREATE_TIME datetime +FILES UPDATE_TIME datetime +FILES CHECK_TIME datetime +PARTITIONS CREATE_TIME datetime +PARTITIONS UPDATE_TIME datetime +PARTITIONS CHECK_TIME datetime +ROUTINES CREATED datetime +ROUTINES LAST_ALTERED datetime +TABLES CREATE_TIME datetime +TABLES UPDATE_TIME datetime +TABLES CHECK_TIME datetime +TRIGGERS CREATED datetime +event execute_at datetime +event last_executed datetime +event starts datetime +event ends datetime +SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A +WHERE NOT EXISTS +(SELECT * FROM INFORMATION_SCHEMA.COLUMNS B +WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA +AND A.TABLE_NAME = B.TABLE_NAME); +COUNT(*) +0 +create table t1 +( x_bigint BIGINT, +x_integer INTEGER, +x_smallint SMALLINT, +x_decimal DECIMAL(5,3), +x_numeric NUMERIC(5,3), +x_real REAL, +x_float FLOAT, +x_double_precision DOUBLE PRECISION ); +SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH +FROM INFORMATION_SCHEMA.COLUMNS +WHERE TABLE_NAME= 't1'; +COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH +x_bigint NULL NULL +x_integer NULL NULL +x_smallint NULL NULL +x_decimal NULL NULL +x_numeric NULL NULL +x_real NULL NULL +x_float NULL NULL +x_double_precision NULL NULL +drop table t1; +grant select on test.* to mysqltest_4@localhost; +connect user10261,localhost,mysqltest_4,,; +connection user10261; +SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS +where COLUMN_NAME='TABLE_NAME' and table_name not like 'innodb%'; +TABLE_NAME COLUMN_NAME PRIVILEGES +COLUMNS TABLE_NAME select +COLUMN_PRIVILEGES TABLE_NAME select +FILES TABLE_NAME select +INDEX_STATISTICS TABLE_NAME select +KEY_COLUMN_USAGE TABLE_NAME select +PARTITIONS TABLE_NAME select +REFERENTIAL_CONSTRAINTS TABLE_NAME select +STATISTICS TABLE_NAME select +TABLES TABLE_NAME select +TABLE_CONSTRAINTS TABLE_NAME select +TABLE_PRIVILEGES TABLE_NAME select +TABLE_STATISTICS TABLE_NAME select +VIEWS TABLE_NAME select +connection default; +disconnect user10261; +delete from mysql.user where user='mysqltest_4'; +delete from mysql.db where user='mysqltest_4'; +flush privileges; +create table t1 (i int, j int); +create trigger trg1 before insert on t1 for each row +begin +if new.j > 10 then +set new.j := 10; +end if; +end| +create trigger trg2 before update on t1 for each row +begin +if old.i % 2 = 0 then +set new.j := -1; +end if; +end| +create trigger trg3 after update on t1 for each row +begin +if new.j = -1 then +set @fired:= "Yes"; +end if; +end| +show triggers; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +trg1 INSERT t1 begin +if new.j > 10 then +set new.j := 10; +end if; +end BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg2 UPDATE t1 begin +if old.i % 2 = 0 then +set new.j := -1; +end if; +end BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg3 UPDATE t1 begin +if new.j = -1 then +set @fired:= "Yes"; +end if; +end AFTER # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +select * from information_schema.triggers where trigger_schema in ('mysql', 'information_schema', 'test', 'mysqltest'); +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +def test trg1 INSERT def test t1 1 NULL begin +if new.j > 10 then +set new.j := 10; +end if; +end ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +def test trg2 UPDATE def test t1 1 NULL begin +if old.i % 2 = 0 then +set new.j := -1; +end if; +end ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +def test trg3 UPDATE def test t1 1 NULL begin +if new.j = -1 then +set @fired:= "Yes"; +end if; +end ROW AFTER NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +drop trigger trg1; +drop trigger trg2; +drop trigger trg3; +drop table t1; +create database mysqltest; +create table mysqltest.t1 (f1 int, f2 int); +create table mysqltest.t2 (f1 int); +grant select (f1) on mysqltest.t1 to user1@localhost; +grant select on mysqltest.t2 to user2@localhost; +grant select on mysqltest.* to user3@localhost; +grant select on *.* to user4@localhost; +connect con1,localhost,user1,,mysqltest; +connect con2,localhost,user2,,mysqltest; +connect con3,localhost,user3,,mysqltest; +connect con4,localhost,user4,,; +connection con1; +select * from information_schema.column_privileges order by grantee; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE +'user1'@'localhost' def mysqltest t1 f1 SELECT NO +select * from information_schema.table_privileges order by grantee; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE +select * from information_schema.schema_privileges order by grantee; +GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE +select * from information_schema.user_privileges order by grantee; +GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE +'user1'@'localhost' def USAGE NO +show grants; +Grants for user1@localhost +GRANT USAGE ON *.* TO 'user1'@'localhost' +GRANT SELECT (f1) ON `mysqltest`.`t1` TO 'user1'@'localhost' +connection con2; +select * from information_schema.column_privileges order by grantee; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE +select * from information_schema.table_privileges order by grantee; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE +'user2'@'localhost' def mysqltest t2 SELECT NO +select * from information_schema.schema_privileges order by grantee; +GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE +select * from information_schema.user_privileges order by grantee; +GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE +'user2'@'localhost' def USAGE NO +show grants; +Grants for user2@localhost +GRANT USAGE ON *.* TO 'user2'@'localhost' +GRANT SELECT ON `mysqltest`.`t2` TO 'user2'@'localhost' +connection con3; +select * from information_schema.column_privileges order by grantee; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE +select * from information_schema.table_privileges order by grantee; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE +select * from information_schema.schema_privileges order by grantee; +GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE +'user3'@'localhost' def mysqltest SELECT NO +select * from information_schema.user_privileges order by grantee; +GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE +'user3'@'localhost' def USAGE NO +show grants; +Grants for user3@localhost +GRANT USAGE ON *.* TO 'user3'@'localhost' +GRANT SELECT ON `mysqltest`.* TO 'user3'@'localhost' +connection con4; +select * from information_schema.column_privileges where grantee like '\'user%' +order by grantee; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE +'user1'@'localhost' def mysqltest t1 f1 SELECT NO +select * from information_schema.table_privileges where grantee like '\'user%' +order by grantee; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE +'user2'@'localhost' def mysqltest t2 SELECT NO +select * from information_schema.schema_privileges where grantee like '\'user%' +order by grantee; +GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE +'user3'@'localhost' def mysqltest SELECT NO +select * from information_schema.user_privileges where grantee like '\'user%' +order by grantee; +GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE +'user1'@'localhost' def USAGE NO +'user2'@'localhost' def USAGE NO +'user3'@'localhost' def USAGE NO +'user4'@'localhost' def SELECT NO +show grants; +Grants for user4@localhost +GRANT SELECT ON *.* TO 'user4'@'localhost' +connection default; +disconnect con1; +disconnect con2; +disconnect con3; +disconnect con4; +drop user user1@localhost, user2@localhost, user3@localhost, user4@localhost; +use test; +drop database mysqltest; +drop procedure if exists p1; +drop procedure if exists p2; +create procedure p1 () modifies sql data set @a = 5; +create procedure p2 () set @a = 5; +select sql_data_access from information_schema.routines +where specific_name like 'p%'; +sql_data_access +MODIFIES SQL DATA +CONTAINS SQL +drop procedure p1; +drop procedure p2; +show create database information_schema; +Database Create Database +information_schema CREATE DATABASE `information_schema` /*!40100 DEFAULT CHARACTER SET utf8 */ +create table t1(f1 LONGBLOB, f2 LONGTEXT); +select column_name,data_type,CHARACTER_OCTET_LENGTH, +CHARACTER_MAXIMUM_LENGTH +from information_schema.columns +where table_name='t1'; +column_name data_type CHARACTER_OCTET_LENGTH CHARACTER_MAXIMUM_LENGTH +f1 longblob 4294967295 4294967295 +f2 longtext 4294967295 4294967295 +drop table t1; +create table t1(f1 tinyint, f2 SMALLINT, f3 mediumint, f4 int, +f5 BIGINT, f6 BIT, f7 bit(64)); +select column_name, NUMERIC_PRECISION, NUMERIC_SCALE +from information_schema.columns +where table_name='t1'; +column_name NUMERIC_PRECISION NUMERIC_SCALE +f1 3 0 +f2 5 0 +f3 7 0 +f4 10 0 +f5 19 0 +f6 1 NULL +f7 64 NULL +drop table t1; +create table t1 (f1 integer); +create trigger tr1 after insert on t1 for each row set @test_var=42; +use information_schema; +select trigger_schema, trigger_name from triggers where +trigger_name='tr1'; +trigger_schema trigger_name +test tr1 +use test; +drop table t1; +create table t1 (a int not null, b int); +use information_schema; +select column_name, column_default from columns +where table_schema='test' and table_name='t1'; +column_name column_default +a NULL +b NULL +use test; +show columns from t1; +Field Type Null Key Default Extra +a int(11) NO NULL +b int(11) YES NULL +drop table t1; +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int); +SHOW TABLE STATUS FROM test +WHERE name IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES +WHERE TABLE_SCHEMA='test' AND TABLE_TYPE='BASE TABLE'); +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary +t1 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL # N +t2 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL # N +DROP TABLE t1,t2; +create table t1(f1 int); +create view v1 (c) as select f1 from t1; +connect con5,localhost,root,,*NO-ONE*; +select database(); +database() +NULL +show fields from test.v1; +Field Type Null Key Default Extra +c int(11) YES NULL +connection default; +disconnect con5; +drop view v1; +drop table t1; +alter database information_schema; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 +drop database information_schema; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +drop table information_schema.tables; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +alter table information_schema.tables; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +use information_schema; +create temporary table schemata(f1 char(10)); +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +CREATE PROCEDURE p1 () +BEGIN +SELECT 'foo' FROM DUAL; +END | +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +select ROUTINE_NAME from routines where ROUTINE_SCHEMA='information_schema'; +ROUTINE_NAME +grant all on information_schema.* to 'user1'@'localhost'; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +grant select on information_schema.* to 'user1'@'localhost'; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +use test; +create table t1(id int); +insert into t1(id) values (1); +select 1 from (select 1 from test.t1) a; +1 +1 +use information_schema; +select 1 from (select 1 from test.t1) a; +1 +1 +use test; +drop table t1; +create table t1 (f1 int(11)); +create view v1 as select * from t1; +drop table t1; +select table_type from information_schema.tables +where table_name="v1"; +table_type +VIEW +drop view v1; +create temporary table t1(f1 int, index(f1)); +show columns from t1; +Field Type Null Key Default Extra +f1 int(11) YES MUL NULL +describe t1; +Field Type Null Key Default Extra +f1 int(11) YES MUL NULL +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +t1 1 f1 1 f1 A NULL NULL NULL YES BTREE +drop table t1; +create table t1(f1 binary(32), f2 varbinary(64)); +select character_maximum_length, character_octet_length +from information_schema.columns where table_name='t1'; +character_maximum_length character_octet_length +32 32 +64 64 +drop table t1; +CREATE TABLE t1 (f1 BIGINT, f2 VARCHAR(20), f3 BIGINT); +INSERT INTO t1 SET f1 = 1, f2 = 'Schoenenbourg', f3 = 1; +CREATE FUNCTION func2() RETURNS BIGINT RETURN 1; +CREATE FUNCTION func1() RETURNS BIGINT +BEGIN +RETURN ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS); +END// +CREATE VIEW v1 AS SELECT 1 FROM t1 +WHERE f3 = (SELECT func2 ()); +SELECT func1(); +func1() +1 +DROP TABLE t1; +DROP VIEW v1; +DROP FUNCTION func1; +DROP FUNCTION func2; +select column_type, group_concat(table_schema, '.', table_name), count(*) as num +from information_schema.columns where +table_schema='information_schema' and +(column_type = 'varchar(7)' or column_type = 'varchar(20)' + or column_type = 'varchar(27)') +group by column_type order by num; +column_type group_concat(table_schema, '.', table_name) num +varchar(7) information_schema.ROUTINES,information_schema.VIEWS 2 +varchar(20) information_schema.ALL_PLUGINS,information_schema.ALL_PLUGINS,information_schema.ALL_PLUGINS,information_schema.FILES,information_schema.FILES,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PROFILING 9 +create table t1(f1 char(1) not null, f2 char(9) not null) +default character set utf8; +select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from +information_schema.columns where table_schema='test' and table_name = 't1'; +CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH +1 3 +9 27 +drop table t1; +use mysql; +INSERT INTO `proc` VALUES ('test','','PROCEDURE','','SQL','CONTAINS_SQL', +'NO','DEFINER','','','BEGIN\r\n \r\nEND','root@%','2006-03-02 18:40:03', +'2006-03-02 18:40:03','','','utf8','utf8_general_ci','utf8_general_ci','n/a', 'NONE'); +select routine_name from information_schema.routines where ROUTINE_SCHEMA='test'; +routine_name + +delete from proc where name=''; +use test; +grant select on test.* to mysqltest_1@localhost; +create table t1 (id int); +create view v1 as select * from t1; +create definer = mysqltest_1@localhost +sql security definer view v2 as select 1; +connect con16681,localhost,mysqltest_1,,test; +connection con16681; +select * from information_schema.views +where table_name='v1' or table_name='v2' order by table_name; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM +def test v1 NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +def test v2 select 1 AS `1` NONE NO mysqltest_1@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +connection default; +disconnect con16681; +drop view v1, v2; +drop table t1; +drop user mysqltest_1@localhost; +set @a:= '.'; +create table t1(f1 char(5)); +create table t2(f1 char(5)); +select concat(@a, table_name), @a, table_name +from information_schema.tables where table_schema = 'test' order by table_name; +concat(@a, table_name) @a table_name +.t1 . t1 +.t2 . t2 +drop table t1,t2; +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +CREATE PROCEDURE p1() SET @a= 1; +CREATE FUNCTION f1() RETURNS INT RETURN @a + 1; +CREATE USER mysql_bug20230@localhost; +GRANT EXECUTE ON PROCEDURE p1 TO mysql_bug20230@localhost; +GRANT EXECUTE ON FUNCTION f1 TO mysql_bug20230@localhost; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test'; +ROUTINE_NAME ROUTINE_DEFINITION +f1 RETURN @a + 1 +p1 SET @a= 1 +SHOW CREATE PROCEDURE p1; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() +SET @a= 1 latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) +RETURN @a + 1 latin1 latin1_swedish_ci latin1_swedish_ci +connect conn1, localhost, mysql_bug20230,,; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test'; +ROUTINE_NAME ROUTINE_DEFINITION +f1 NULL +p1 NULL +SHOW CREATE PROCEDURE p1; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +p1 NULL latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 NULL latin1 latin1_swedish_ci latin1_swedish_ci +CALL p1(); +SELECT f1(); +f1() +2 +disconnect conn1; +connection default; +DROP FUNCTION f1; +DROP PROCEDURE p1; +DROP USER mysql_bug20230@localhost; +SELECT MAX(table_name) FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test') and table_name not like 'xtradb%'; +MAX(table_name) +VIEWS +SELECT table_name from information_schema.tables +WHERE table_name=(SELECT MAX(table_name) +FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test') and table_name not like 'xtradb%'); +table_name +VIEWS +DROP TABLE IF EXISTS bug23037; +DROP FUNCTION IF EXISTS get_value; +SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037'; +COLUMN_NAME MD5(COLUMN_DEFAULT) LENGTH(COLUMN_DEFAULT) +fld1 85ea6a55b8f0058e640b3de141a3a9d9 65534 +SELECT MD5(get_value()); +MD5(get_value()) +76176d2daa20c582375b8dcfc18033cd +SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT), COLUMN_DEFAULT=get_value() FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037'; +COLUMN_NAME MD5(COLUMN_DEFAULT) LENGTH(COLUMN_DEFAULT) COLUMN_DEFAULT=get_value() +fld1 85ea6a55b8f0058e640b3de141a3a9d9 65534 0 +DROP TABLE bug23037; +DROP FUNCTION get_value; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; +create view v1 as +select table_schema as object_schema, +table_name as object_name, +table_type as object_type +from information_schema.tables +order by object_schema; +explain select * from v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE tables ALL NULL NULL NULL NULL NULL Open_frm_only; Scanned all databases; Using filesort +explain select * from (select table_name from information_schema.tables) as a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED tables ALL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases +set optimizer_switch=@tmp_optimizer_switch; +drop view v1; +create table t1 (f1 int(11)); +create table t2 (f1 int(11), f2 int(11)); +select table_name from information_schema.tables +where table_schema = 'test' and table_name not in +(select table_name from information_schema.columns +where table_schema = 'test' and column_name = 'f3') +order by table_name; +table_name +t1 +t2 +drop table t1,t2; +create table t1(f1 int); +create view v1 as select f1+1 as a from t1; +create table t2 (f1 int, f2 int); +create view v2 as select f1+1 as a, f2 as b from t2; +select table_name, is_updatable from information_schema.views order by table_name; +table_name is_updatable +v1 NO +v2 YES +delete from v1; +drop view v1,v2; +drop table t1,t2; +alter database; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 +alter database test; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 +create database mysqltest; +create table mysqltest.t1(a int, b int, c int); +create trigger mysqltest.t1_ai after insert on mysqltest.t1 +for each row set @a = new.a + new.b + new.c; +grant select(b) on mysqltest.t1 to mysqltest_1@localhost; +select trigger_name from information_schema.triggers +where event_object_table='t1'; +trigger_name +t1_ai +show triggers from mysqltest; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +t1_ai INSERT t1 set @a = new.a + new.b + new.c AFTER # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +connect con27629,localhost,mysqltest_1,,mysqltest; +show columns from t1; +Field Type Null Key Default Extra +b int(11) YES NULL +select column_name from information_schema.columns where table_name='t1'; +column_name +b +show triggers; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +select trigger_name from information_schema.triggers +where event_object_table='t1'; +trigger_name +connection default; +disconnect con27629; +drop user mysqltest_1@localhost; +drop database mysqltest; +create table t1 ( +f1 varchar(50), +f2 varchar(50) not null, +f3 varchar(50) default '', +f4 varchar(50) default NULL, +f5 bigint not null, +f6 bigint not null default 10, +f7 datetime not null, +f8 datetime default '2006-01-01' +); +select column_default from information_schema.columns where table_name= 't1'; +column_default +NULL +NULL +'' +NULL +NULL +10 +NULL +'2006-01-01 00:00:00' +show columns from t1; +Field Type Null Key Default Extra +f1 varchar(50) YES NULL +f2 varchar(50) NO NULL +f3 varchar(50) YES +f4 varchar(50) YES NULL +f5 bigint(20) NO NULL +f6 bigint(20) NO 10 +f7 datetime NO NULL +f8 datetime YES 2006-01-01 00:00:00 +drop table t1; +show fields from information_schema.table_names; +ERROR 42S02: Unknown table 'table_names' in information_schema +show keys from information_schema.table_names; +ERROR 42S02: Unknown table 'table_names' in information_schema +USE information_schema; +SET max_heap_table_size = 16384; +CREATE TABLE test.t1( a INT ); +SELECT * +FROM tables ta +JOIN collations co ON ( co.collation_name = ta.table_catalog ) +JOIN character_sets cs ON ( cs.character_set_name = ta.table_catalog ); +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT MAX_INDEX_LENGTH TEMPORARY COLLATION_NAME CHARACTER_SET_NAME ID IS_DEFAULT IS_COMPILED SORTLEN CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN +DROP TABLE test.t1; +SET max_heap_table_size = DEFAULT; +USE test; +End of 5.0 tests. +select * from information_schema.engines WHERE ENGINE="MyISAM"; +ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS +MyISAM DEFAULT MyISAM storage engine NO NO NO +grant select on *.* to user3148@localhost; +connect con3148,localhost,user3148,,test; +connection con3148; +select user,db from information_schema.processlist; +user db +user3148 test +connection default; +disconnect con3148; +drop user user3148@localhost; +connect pslistcon,localhost,root,,test; +SELECT 'other connection here' AS who; +who +other connection here +connection default; +SELECT IF(`time` > 0, 'OK', `time`) AS time_low, +IF(`time` < 1000, 'OK', `time`) AS time_high, +IF(time_ms >= 1000, 'OK', time_ms) AS time_ms_low, +IF(time_ms < 1000000, 'OK', time_ms) AS time_ms_high +FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID=@tid; +time_low time_high time_ms_low time_ms_high +OK OK OK OK +disconnect pslistcon; +DROP TABLE IF EXISTS server_status; +DROP EVENT IF EXISTS event_status; +SET GLOBAL event_scheduler=1; +CREATE EVENT event_status +ON SCHEDULE AT NOW() +ON COMPLETION NOT PRESERVE +DO +BEGIN +CREATE TABLE server_status +SELECT variable_name +FROM information_schema.global_status +WHERE variable_name LIKE 'ABORTED_CONNECTS' OR +variable_name LIKE 'BINLOG_CACHE_DISK_USE'; +END$$ +SELECT variable_name FROM server_status; +variable_name +ABORTED_CONNECTS +BINLOG_CACHE_DISK_USE +DROP TABLE server_status; +SET GLOBAL event_scheduler=0; +explain select table_name from information_schema.views where +table_schema='test' and table_name='v1'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE views ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL Using where; Open_frm_only; Scanned 0 databases +explain select * from information_schema.tables; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE tables ALL NULL NULL NULL NULL NULL Open_full_table; Scanned all databases +explain select * from information_schema.collations; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE collations ALL NULL NULL NULL NULL NULL +explain select * from information_schema.tables where +table_schema='test' and table_name= 't1'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE tables ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL Using where; Open_full_table; Scanned 0 databases +explain select table_name, table_type from information_schema.tables +where table_schema='test'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE tables ALL NULL TABLE_SCHEMA NULL NULL NULL Using where; Open_frm_only; Scanned 1 database +explain select b.table_name +from information_schema.tables a, information_schema.columns b +where a.table_name='t1' and a.table_schema='test' and b.table_name=a.table_name; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE a ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL Using where; Skip_open_table; Scanned 0 databases +1 SIMPLE b ALL NULL NULL NULL NULL NULL Using where; Open_frm_only; Scanned all databases; Using join buffer (flat, BNL join) +SELECT * FROM INFORMATION_SCHEMA.SCHEMATA +WHERE SCHEMA_NAME = 'mysqltest'; +CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH +SELECT * FROM INFORMATION_SCHEMA.SCHEMATA +WHERE SCHEMA_NAME = ''; +CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH +SELECT * FROM INFORMATION_SCHEMA.SCHEMATA +WHERE SCHEMA_NAME = 'test'; +CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH +def test latin1 latin1_swedish_ci NULL +select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME='nonexisting'; +count(*) +0 +select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME=''; +count(*) +0 +select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME=''; +count(*) +0 +select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME='nonexisting'; +count(*) +0 +CREATE VIEW v1 +AS SELECT * +FROM information_schema.tables; +SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'v1'; +VIEW_DEFINITION +select `information_schema`.`tables`.`TABLE_CATALOG` AS `TABLE_CATALOG`,`information_schema`.`tables`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`tables`.`TABLE_NAME` AS `TABLE_NAME`,`information_schema`.`tables`.`TABLE_TYPE` AS `TABLE_TYPE`,`information_schema`.`tables`.`ENGINE` AS `ENGINE`,`information_schema`.`tables`.`VERSION` AS `VERSION`,`information_schema`.`tables`.`ROW_FORMAT` AS `ROW_FORMAT`,`information_schema`.`tables`.`TABLE_ROWS` AS `TABLE_ROWS`,`information_schema`.`tables`.`AVG_ROW_LENGTH` AS `AVG_ROW_LENGTH`,`information_schema`.`tables`.`DATA_LENGTH` AS `DATA_LENGTH`,`information_schema`.`tables`.`MAX_DATA_LENGTH` AS `MAX_DATA_LENGTH`,`information_schema`.`tables`.`INDEX_LENGTH` AS `INDEX_LENGTH`,`information_schema`.`tables`.`DATA_FREE` AS `DATA_FREE`,`information_schema`.`tables`.`AUTO_INCREMENT` AS `AUTO_INCREMENT`,`information_schema`.`tables`.`CREATE_TIME` AS `CREATE_TIME`,`information_schema`.`tables`.`UPDATE_TIME` AS `UPDATE_TIME`,`information_schema`.`tables`.`CHECK_TIME` AS `CHECK_TIME`,`information_schema`.`tables`.`TABLE_COLLATION` AS `TABLE_COLLATION`,`information_schema`.`tables`.`CHECKSUM` AS `CHECKSUM`,`information_schema`.`tables`.`CREATE_OPTIONS` AS `CREATE_OPTIONS`,`information_schema`.`tables`.`TABLE_COMMENT` AS `TABLE_COMMENT`,`information_schema`.`tables`.`MAX_INDEX_LENGTH` AS `MAX_INDEX_LENGTH`,`information_schema`.`tables`.`TEMPORARY` AS `TEMPORARY` from `information_schema`.`tables` +DROP VIEW v1; +SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA +WHERE SCHEMA_NAME ='information_schema'; +SCHEMA_NAME +information_schema +SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES +WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db'; +TABLE_COLLATION +utf8_bin +select * from information_schema.columns where table_schema = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION +select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION +select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME +select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME +select * from `information_schema`.`PARTITIONS` where `TABLE_SCHEMA` = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME +select * from `information_schema`.`PARTITIONS` where `TABLE_NAME` = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME +select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME +select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME +select * from information_schema.schemata where schema_name = NULL; +CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH +select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT +select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT +select * from information_schema.tables where table_schema = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT MAX_INDEX_LENGTH TEMPORARY +select * from information_schema.tables where table_catalog = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT MAX_INDEX_LENGTH TEMPORARY +select * from information_schema.tables where table_name = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT MAX_INDEX_LENGTH TEMPORARY +select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE +select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE +select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_SCHEMA` = NULL; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_TABLE` = NULL; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +select * from `information_schema`.`VIEWS` where `TABLE_SCHEMA` = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM +select * from `information_schema`.`VIEWS` where `TABLE_NAME` = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM +explain extended select 1 from information_schema.tables; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE tables ALL NULL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases +Warnings: +Note 1003 select 1 AS `1` from `information_schema`.`tables` +use information_schema; +show events; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +show events from information_schema; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +show events where Db= 'information_schema'; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +use test; +# +# Bug#34166 Server crash in SHOW OPEN TABLES and prelocking +# +drop table if exists t1; +drop function if exists f1; +create table t1 (a int); +create function f1() returns int +begin +insert into t1 (a) values (1); +return 0; +end| +show open tables where f1()=0; +show open tables where f1()=0; +drop table t1; +drop function f1; +connect conn1, localhost, root,,; +connection conn1; +select * from information_schema.tables where 1=sleep(100000); +connection default; +connection conn1; +Got one of the listed errors +connection default; +disconnect conn1; +connect conn1, localhost, root,,; +connection conn1; +select * from information_schema.columns where 1=sleep(100000); +connection default; +connection conn1; +Got one of the listed errors +connection default; +disconnect conn1; +explain select count(*) from information_schema.tables; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE tables ALL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases +explain select count(*) from information_schema.columns; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE columns ALL NULL NULL NULL NULL NULL Open_frm_only; Scanned all databases +explain select count(*) from information_schema.views; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE views ALL NULL NULL NULL NULL NULL Open_frm_only; Scanned all databases +set global init_connect="drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;"; +select * from information_schema.global_variables where variable_name='init_connect'; +VARIABLE_NAME VARIABLE_VALUE +INIT_CONNECT drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +select * from information_schema.global_variables where variable_name like 'init%' order by variable_name; +VARIABLE_NAME VARIABLE_VALUE +INIT_CONNECT drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +drop table if exists t1;drop table if exists t1; +INIT_FILE +INIT_SLAVE +set global init_connect=""; +create table t0 select * from information_schema.global_status where VARIABLE_NAME='COM_SELECT'; +SELECT 1; +1 +1 +select a.VARIABLE_VALUE - b.VARIABLE_VALUE from t0 b, information_schema.global_status a +where a.VARIABLE_NAME = b.VARIABLE_NAME; +a.VARIABLE_VALUE - b.VARIABLE_VALUE +2 +drop table t0; +CREATE TABLE t1(a INT) KEY_BLOCK_SIZE=1; +SELECT CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; +CREATE_OPTIONS +key_block_size=1 +DROP TABLE t1; +SET TIMESTAMP=@@TIMESTAMP + 10000000; +SELECT 'NOT_OK' AS TEST_RESULT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time < 0; +TEST_RESULT +SET TIMESTAMP=DEFAULT; +# +# Bug #50276: Security flaw in INFORMATION_SCHEMA.TABLES +# +CREATE DATABASE db1; +USE db1; +CREATE TABLE t1 (id INT); +CREATE USER nonpriv; +USE test; +connect nonpriv_con, localhost, nonpriv,,; +connection nonpriv_con; +# connected as nonpriv +# Should return 0 +SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; +COUNT(*) +0 +USE INFORMATION_SCHEMA; +# Should return 0 +SELECT COUNT(*) FROM TABLES WHERE TABLE_NAME='t1'; +COUNT(*) +0 +connection default; +# connected as root +disconnect nonpriv_con; +DROP USER nonpriv; +DROP TABLE db1.t1; +DROP DATABASE db1; + +Bug#54422 query with = 'variables' + +CREATE TABLE variables(f1 INT); +SELECT COLUMN_DEFAULT, TABLE_NAME +FROM INFORMATION_SCHEMA.COLUMNS +WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'variables'; +COLUMN_DEFAULT TABLE_NAME +NULL variables +DROP TABLE variables; +# +# Bug #53814: NUMERIC_PRECISION for unsigned bigint field is 19, +# should be 20 +# +CREATE TABLE ubig (a BIGINT, b BIGINT UNSIGNED); +SELECT TABLE_NAME, COLUMN_NAME, NUMERIC_PRECISION +FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ubig'; +TABLE_NAME COLUMN_NAME NUMERIC_PRECISION +ubig a 19 +ubig b 20 +INSERT INTO ubig VALUES (0xFFFFFFFFFFFFFFFF,0xFFFFFFFFFFFFFFFF); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +SELECT length(CAST(b AS CHAR)) FROM ubig; +length(CAST(b AS CHAR)) +20 +DROP TABLE ubig; +select 1 from information_schema.tables where table_schema=repeat('a', 2000); +1 +grant usage on *.* to mysqltest_1@localhost; +connect con1, localhost, mysqltest_1,,; +connection con1; +select 1 from information_schema.tables where table_schema=repeat('a', 2000); +1 +connection default; +disconnect con1; +drop user mysqltest_1@localhost; +End of 5.1 tests. +# +# Additional test for WL#3726 "DDL locking for all metadata objects" +# To avoid possible deadlocks process of filling of I_S tables should +# use high-priority metadata lock requests when opening tables. +# Below we just test that we really use high-priority lock request +# since reproducing a deadlock will require much more complex test. +# +drop tables if exists t1, t2, t3; +create table t1 (i int); +create table t2 (j int primary key auto_increment); +connect con3726_1,localhost,root,,test; +connection con3726_1; +lock table t2 read; +connect con3726_2,localhost,root,,test; +connection con3726_2; +# RENAME below will be blocked by 'lock table t2 read' above but +# will add two pending requests for exclusive metadata locks. +rename table t2 to t3; +connection default; +# These statements should not be blocked by pending lock requests +select table_name, column_name, data_type from information_schema.columns +where table_schema = 'test' and table_name in ('t1', 't2') order by table_name, column_name; +table_name column_name data_type +t1 i int +t2 j int +select table_name, auto_increment from information_schema.tables +where table_schema = 'test' and table_name in ('t1', 't2') order by table_name; +table_name auto_increment +t1 NULL +t2 1 +connection con3726_1; +unlock tables; +connection con3726_2; +connection default; +disconnect con3726_1; +disconnect con3726_2; +drop tables t1, t3; +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE KEY_COLUMN_USAGE ALL NULL NULL NULL NULL NULL Open_full_table; Scanned all databases +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='t1'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE PARTITIONS ALL NULL TABLE_NAME NULL NULL NULL Using where; Open_full_table; Scanned 1 database +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS +WHERE CONSTRAINT_SCHEMA='test'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE REFERENTIAL_CONSTRAINTS ALL NULL CONSTRAINT_SCHEMA NULL NULL NULL Using where; Open_full_table; Scanned 1 database +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS +WHERE TABLE_NAME='t1' and TABLE_SCHEMA='test'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE TABLE_CONSTRAINTS ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL Using where; Open_full_table; Scanned 0 databases +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TRIGGERS +WHERE EVENT_OBJECT_SCHEMA='test'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE TRIGGERS ALL NULL EVENT_OBJECT_SCHEMA NULL NULL NULL Using where; Open_frm_only; Scanned 1 database +create table information_schema.t1 (f1 INT); +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +drop table information_schema.t1; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +drop temporary table if exists information_schema.t1; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +create temporary table information_schema.t1 (f1 INT); +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +drop view information_schema.v1; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +create view information_schema.v1; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +create trigger mysql.trg1 after insert on information_schema.t1 for each row set @a=1; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +create table t1 select * from information_schema.t1; +ERROR 42S02: Unknown table 't1' in information_schema +CREATE TABLE t1(f1 char(100)); +REPAIR TABLE t1, information_schema.tables; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +CHECKSUM TABLE t1, information_schema.tables; +Table Checksum +test.t1 0 +information_schema.tables 0 +ANALYZE TABLE t1, information_schema.tables; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +CHECK TABLE t1, information_schema.tables; +Table Op Msg_type Msg_text +test.t1 check status OK +information_schema.tables check note The storage engine for the table doesn't support check +OPTIMIZE TABLE t1, information_schema.tables; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +RENAME TABLE v1 to v2, information_schema.tables to t2; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +DROP TABLE t1, information_schema.tables; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +LOCK TABLES t1 READ, information_schema.tables READ; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +DROP TABLE t1; +SELECT * +FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE +LEFT JOIN INFORMATION_SCHEMA.COLUMNS +USING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) +WHERE COLUMNS.TABLE_SCHEMA = 'test' +AND COLUMNS.TABLE_NAME = 't1'; +TABLE_SCHEMA TABLE_NAME COLUMN_NAME CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME TABLE_CATALOG ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION +# +# A test case for Bug#56540 "Exception (crash) in sql_show.cc +# during rqg_info_schema test on Windows" +# Ensure that we never access memory of a closed table, +# in particular, never access table->field[] array. +# Before the fix, the below test case, produced +# valgrind errors. +# +drop table if exists t1; +drop view if exists v1; +create table t1 (a int, b int); +create view v1 as select t1.a, t1.b from t1; +alter table t1 change b c int; +lock table t1 read; +connect con1, localhost, root,,; +connection con1; +flush tables; +connection default; +select * from information_schema.views; +TABLE_CATALOG def +TABLE_SCHEMA test +TABLE_NAME v1 +VIEW_DEFINITION select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` +CHECK_OPTION NONE +IS_UPDATABLE +DEFINER root@localhost +SECURITY_TYPE DEFINER +CHARACTER_SET_CLIENT latin1 +COLLATION_CONNECTION latin1_swedish_ci +ALGORITHM UNDEFINED +Warnings: +Level Warning +Code 1356 +Message View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +unlock tables; +# +# Cleanup. +# +connection con1; +# Reaping 'flush tables' +disconnect con1; +connection default; +drop table t1; +drop view v1; +# +# Test for bug #12828477 - "MDL SUBSYSTEM CREATES BIG OVERHEAD FOR +# CERTAIN QUERIES TO INFORMATION_SCHEMA". +# +# Check that metadata locks which are acquired during the process +# of opening tables/.FRMs/.TRG files while filling I_S table are +# not kept to the end of statement. Keeping the locks has caused +# performance problems in cases when big number of tables (.FRMs +# or .TRG files) were scanned as cost of new lock acquisition has +# increased linearly. +drop database if exists mysqltest; +create database mysqltest; +use mysqltest; +create table t0 (i int); +create table t1 (j int); +create table t2 (k int); +# +# Test that we don't keep locks in case when we to fill +# I_S table we perform full-blown table open. +# +# Acquire lock on 't2' so upcoming RENAME is +# blocked. +lock tables t2 read; +connect con12828477_1, localhost, root,,mysqltest; +# The below RENAME should wait on 't2' while +# keeping X lock on 't1'. +rename table t1 to t3, t2 to t1, t3 to t2; +connect con12828477_2, localhost, root,,mysqltest; +# Wait while the above RENAME is blocked. +# Issue query to I_S which will open 't0' and get +# blocked on 't1' because of RENAME. +select table_name, auto_increment from information_schema.tables where table_schema='mysqltest' and table_name='t0' union select table_name, auto_increment from information_schema.tables where table_schema='mysqltest' and table_name<>'t0' order by table_name; +connect con12828477_3, localhost, root,,mysqltest; +# Wait while the above SELECT is blocked. +# +# Check that it holds no lock on 't0' so it can be renamed. +rename table t0 to t4; +connection default; +# +# Unblock the first RENAME. +unlock tables; +connection con12828477_1; +# Reap the first RENAME +connection con12828477_2; +# Reap SELECT to I_S. +table_name auto_increment +t0 NULL +t1 NULL +t2 NULL +connection default; +# +# Now test that we don't keep locks in case when we to fill +# I_S table we read .FRM or .TRG file only (this was the case +# for which problem existed). +# +rename table t4 to t0; +# Acquire lock on 't2' so upcoming RENAME is +# blocked. +lock tables t2 read; +connection con12828477_1; +# The below RENAME should wait on 't2' while +# keeping X lock on 't1'. +rename table t1 to t3, t2 to t1, t3 to t2; +connection con12828477_2; +# Wait while the above RENAME is blocked. +# Issue query to I_S which will open 't0' and get +# blocked on 't1' because of RENAME. +select event_object_table, trigger_name from information_schema.triggers where event_object_schema='mysqltest'; +connection con12828477_3; +# Wait while the above SELECT is blocked. +# +# Check that it holds no lock on 't0' so it can be renamed. +rename table t0 to t4; +connection default; +# +# Unblock the first RENAME. +unlock tables; +connection con12828477_1; +# Reap the first RENAME +connection con12828477_2; +# Reap SELECT to I_S. +event_object_table trigger_name +connection default; +disconnect con12828477_1; +disconnect con12828477_2; +disconnect con12828477_3; +# +# MDEV-3818: Query against view over IS tables worse than equivalent query without view +# +create view v1 as select table_schema, table_name, column_name from information_schema.columns; +explain extended +select column_name from v1 +where (table_schema = "osm") and (table_name = "test"); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE columns ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL NULL Using where; Open_frm_only; Scanned 0 databases +Warnings: +Note 1003 select `information_schema`.`columns`.`COLUMN_NAME` AS `column_name` from `information_schema`.`columns` where `information_schema`.`columns`.`TABLE_SCHEMA` = 'osm' and `information_schema`.`columns`.`TABLE_NAME` = 'test' +explain extended +select information_schema.columns.column_name as column_name +from information_schema.columns +where (information_schema.columns.table_schema = 'osm') and (information_schema.columns.table_name = 'test'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE columns ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL NULL Using where; Open_frm_only; Scanned 0 databases +Warnings: +Note 1003 select `information_schema`.`columns`.`COLUMN_NAME` AS `column_name` from `information_schema`.`columns` where `information_schema`.`columns`.`TABLE_SCHEMA` = 'osm' and `information_schema`.`columns`.`TABLE_NAME` = 'test' +drop view v1; +# +# Clean-up. +drop database mysqltest; +# +# Test for bug #16869534 - "QUERYING SUBSET OF COLUMNS DOESN'T USE TABLE +# CACHE; OPENED_TABLES INCREASES" +# +SELECT * FROM INFORMATION_SCHEMA.TABLES; +SELECT VARIABLE_VALUE INTO @val1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE +VARIABLE_NAME LIKE 'Opened_tables'; +SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES; +# The below SELECT query should give same output as above SELECT query. +SELECT VARIABLE_VALUE INTO @val2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE +VARIABLE_NAME LIKE 'Opened_tables'; +# The below select should return '1' +SELECT @val1 = @val2; +@val1 = @val2 +1 +# +# End of 5.5 tests +# +# +# MDEV-5723: mysqldump -uroot unusable for multi-database operations, checks all databases +# +drop database if exists db1; +connect con1,localhost,root,,; +connection con1; +create database db1; +use db1; +create table t1 (a int); +create table t2 (a int); +create table t3 (a int); +create database mysqltest; +use mysqltest; +create table t1 (a int); +create table t2 (a int); +create table t3 (a int); +flush tables; +flush status; +SELECT +LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA +FROM +INFORMATION_SCHEMA.FILES +WHERE +FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND +LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME +FROM INFORMATION_SCHEMA.FILES +WHERE +FILE_TYPE = 'DATAFILE' AND +TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME +FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_SCHEMA IN ('db1') +) +) +GROUP BY +LOGFILE_GROUP_NAME, FILE_NAME, ENGINE +ORDER BY +LOGFILE_GROUP_NAME; +LOGFILE_GROUP_NAME FILE_NAME TOTAL_EXTENTS INITIAL_SIZE ENGINE EXTRA +# This must have Opened_tables=3, not 6. +show status like 'Opened_tables'; +Variable_name Value +Opened_tables 3 +drop database mysqltest; +drop database db1; +connection default; +disconnect con1; +set global sql_mode=default; +USE test; +# +# End of 10.0 tests +# +# +# Start of 10.1 tests +# +# +# MDEV-13242 Wrong results for queries with row constructors and information_schema +# +CREATE TABLE tt1(c1 INT); +CREATE TABLE tt2(c2 INT); +SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt1', 'c1')); +count(*) +1 +SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt2', 'c2')); +count(*) +1 +SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt1','c1'),('tt2', 'c2')); +count(*) +2 +SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (SELECT 'tt1','c1' FROM dual UNION SELECT 'tt2', 'c2' FROM dual); +count(*) +2 +SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name='tt1' AND column_name='c1') OR (table_name='tt2' AND column_name='c2'); +count(*) +2 +SELECT column_name FROM information_schema.columns WHERE (table_name, column_name) IN (('tt1','c1'),('tt2', 'c2')) ORDER BY column_name; +column_name +c1 +c2 +DROP TABLE tt1, tt2; |