# Test for information_schema.schemata & # show databases show variables variable_name where variable_name like "skip_show_database"; grant all privileges on test.* to mysqltest_1@localhost; select * from information_schema.SCHEMATA where schema_name > 'm'; select schema_name from information_schema.schemata; show databases *; show databases like 't%'; show databases; show databases * where schema_name like 't%'; show databases * where schema_name = 't%'; # Test for information_schema.tables & # show tables create database testtets; create table testtets.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 testtets.t4(a int); create view v1 (c) as select table_name from information_schema.TABLES; select * from v1; select c,table_name from v1 left join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; select c, v2.table_name from v1 right join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; select table_name from information_schema.TABLES where table_schema = "testtets" and table_name like "t%"; select * from information_schema.STATISTICS where TABLE_SCHEMA = "testtets"; show keys * from t3 where TABLE_SCHEMA Like "test%"; show keys from t3 where INDEX_NAME = "a_data"; show tables like 't%'; --replace_column 15 # 16 # show tables * from test where table_name like 't%'; --replace_column 12 # 13 # --replace_result "2147483647 " "21474836479 " show table status; show full columns from t3 like "a%"; show full columns from mysql.db like "Insert%"; show full columns from v1; select * from information_schema.COLUMNS where table_name="t1" and column_name= "a"; show columns * from testtets.t1 where table_name = "t1"; drop view v1; drop tables testtets.t4, testtets.t1, t2, t3; drop database testtets; # Test for information_schema.CHARACTER_SETS & # SHOW CHARACTER SET select * from information_schema.CHARACTER_SETS where CHARACTER_SET_NAME like 'latin1%'; SHOW CHARACTER SET LIKE 'latin1%'; SHOW CHARACTER SET * LIKE 'latin1%'; SHOW CHARACTER SET WHERE CHARACTER_SET_NAME like 'latin1%'; SHOW CHARACTER SET CHARACTER_SET_NAME WHERE CHARACTER_SET_NAME like 'latin1%'; SHOW CHARACTER SET * WHERE CHARACTER_SET_NAME like 'latin1%'; # Test for information_schema.COLLATIONS & # SHOW COLLATION select * from information_schema.COLLATIONS where COLLATION_NAME like 'latin1%'; SHOW COLLATION LIKE 'latin1%'; SHOW COLLATION * LIKE 'latin1%'; SHOW COLLATION WHERE COLLATION_NAME like 'latin1%'; SHOW COLLATION COLLATION_NAME WHERE COLLATION_NAME like 'latin1%'; SHOW COLLATION * WHERE COLLATION_NAME like 'latin1%'; select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY where COLLATION_NAME like 'latin1%'; # Test for information_schema.ROUTINES & # create function sub1(i int) returns int return i+1; delimiter |; create procedure sel2() begin select * from t1; select * from t2; end| delimiter ;| # # Bug#7222 information_schema: errors in "routines" # select parameter_style, sql_data_access, dtd_identifier from information_schema.routines; --replace_column 5 # 6 # show procedure status; --replace_column 5 # 6 # show function status; select a.ROUTINE_NAME from information_schema.ROUTINES a, information_schema.SCHEMATA b where a.ROUTINE_SCHEMA = b.SCHEMA_NAME; --replace_column 3 # explain select a.ROUTINE_NAME from information_schema.ROUTINES a, information_schema.SCHEMATA b where a.ROUTINE_SCHEMA = b.SCHEMA_NAME; select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a, mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8); select count(*) from information_schema.ROUTINES; # # Test for views # create view v0 (c) as select schema_name from information_schema.schemata; select * from v0; --replace_column 3 # explain select * from v0; create view v1 (c) as select table_name from information_schema.tables where table_name="v1"; select * from v1; create view v2 (c) as select column_name from information_schema.columns where table_name="v2"; select * from v2; create view v3 (c) as select CHARACTER_SET_NAME from information_schema.character_sets where CHARACTER_SET_NAME like "latin1%"; select * from v3; create view v4 (c) as select COLLATION_NAME from information_schema.collations where COLLATION_NAME like "latin1%"; select * from v4; show keys from v4; select * from information_schema.views where TABLE_NAME like "v%"; drop view v0, v1, v2, v3, v4; # # Test for privileges tables # 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%'; select * from information_schema.SCHEMA_PRIVILEGES where grantee like '%mysqltest_1%'; select * from information_schema.TABLE_PRIVILEGES where grantee like '%mysqltest_1%'; select * from information_schema.COLUMN_PRIVILEGES where grantee like '%mysqltest_1%'; delete from mysql.user where user='mysqltest_1'; delete from mysql.db where user='mysqltest_1'; delete from mysql.tables_priv where user='mysqltest_1'; delete from mysql.columns_priv where user='mysqltest_1'; flush privileges; drop table t1; # # Test for KEY_COLUMN_USAGE & TABLE_CONSTRAINTS tables # 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); alter table t1 add constraint constraint_2 unique key_2(a); show create table t1; select * from information_schema.TABLE_CONSTRAINTS where TABLE_SCHEMA= "test"; select * from information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA= "test"; connect (user1,localhost,mysqltest_1,,); connection user1; select table_name from information_schema.TABLES where table_schema like "test%"; select table_name,column_name from information_schema.COLUMNS where table_schema like "test%"; select ROUTINE_NAME from information_schema.ROUTINES; disconnect user1; 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; grant select (a) on test.t1 to joe@localhost with grant option; select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES; select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES; 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; delimiter //; create procedure px5 () begin declare v int; declare c cursor for select version from information_schema.tables where table_schema <> 'information_schema'; open c; fetch c into v; select v; close c; end;// call px5()// call px5()// delimiter ;// select sql_mode from information_schema.ROUTINES; drop procedure px5; 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'; 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; drop table t1; SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets; set names latin2; SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets; set names latin1; create table t1 select * from information_schema.CHARACTER_SETS where CHARACTER_SET_NAME like "latin1"; select * from t1; alter table t1 default character set utf8; show create table t1; 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'; 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; drop table t115; delimiter //; create procedure p108 () begin declare c cursor for select data_type from information_schema.columns; open c; open c; end;// --error 1325 call p108()// delimiter ;// drop procedure p108; create view v1 as select A1.table_name from information_schema.TABLES A1 where table_name= "user"; select * from v1; drop view v1; create view vo as select 'a' union select 'a'; show index from vo; select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME= "vo"; select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME= "vo"; drop view vo; select TABLE_NAME,TABLE_TYPE,ENGINE from information_schema.tables where table_schema='information_schema' limit 2; show tables from information_schema like "T%"; --error 1007 create database information_schema; use information_schema; show full tables like "T%"; --error 1109 create table t1(a int); use test; show tables; use information_schema; show tables like "T%"; # # Bug#7210: information_schema: can't access when table-name = reserved word # select table_name from tables where table_name='user'; select column_name, privileges from columns where table_name='user' and column_name like '%o%'; # # Bug#7212: information_schema: "Can't find file" errors if storage engine gone # Bug#7211: information_schema: crash if bad view # use test; create function sub1(i int) returns int return i+1; create table t1(f1 int); create view t2 (c) as select f1 from t1; create view t3 (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'; select table_name from information_schema.views where table_schema='test'; select column_name from information_schema.columns where table_schema='test'; select index_name from information_schema.statistics where table_schema='test'; select constraint_name from information_schema.table_constraints where table_schema='test'; drop view t2; drop view t3; drop table t4; # # Bug#7213: information_schema: redundant non-standard TABLE_NAMES table # --error 1109 select * from information_schema.table_names; # # Bug#2719 information_schema: errors in "columns" # 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"); # # Bug#2718 information_schema: errors in "tables" # select TABLE_ROWS from information_schema.tables where table_schema="information_schema" and table_name="COLUMNS"; select table_type from information_schema.tables where table_schema="mysql" and table_name="user"; # test for 'show open tables ... where' show open tables where `table` like "user"; # test for 'show status ... where' show status variable_name where variable_name like "%database%"; # test for 'show variables ... where' show variables variable_name where variable_name like "%database%"; # # Bug #7981:SHOW GLOBAL STATUS crashes server # show global status like "Threads_running";