# This test uses grants, which can't get tested for embedded server -- source include/not_embedded.inc # Test for information_schema.schemata & # show databases --disable_warnings DROP TABLE IF EXISTS t0,t1,t2; --enable_warnings show variables where variable_name like "skip_show_database"; 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'; select schema_name from information_schema.schemata; show databases like 't%'; show databases; show databases where `database` = 't%'; # Test for information_schema.tables & # show tables 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 view v1 (c) as select table_name from information_schema.TABLES; select * from v1; select c,table_name from v1 inner join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; 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 = "mysqltest" and table_name like "t%"; select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest"; show keys from t3 where Key_name = "a_data"; show tables like 't%'; --replace_column 8 # 12 # 13 # 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 mysqltest.t1 where field like "%a%"; 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'; show columns from mysqltest.t1; 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'; connection default; drop view v1, mysqltest.v1; drop tables mysqltest.t4, mysqltest.t1, t2, t3; drop database mysqltest; # 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 WHERE charset 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 WHERE collation 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; connect (user1,localhost,mysqltest_1,,); connection user1; select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; --error 1305 show create function sub1; connection user3; select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; 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; create function sub2(i int) returns int return i+1; select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; show create procedure sel2; show create function sub1; show create function sub2; connection default; disconnect user1; drop function sub2; show create procedure sel2; # # 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 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; # # 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"; connection user2; 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 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; 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; # QQ This results in NULLs instead of the version numbers when # QQ a LOCK TABLES is in effect when selecting from # QQ information_schema.tables. Until this bug has been fixed, # QQ this test is disabled /pem #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 where variable_name like "%database%"; # test for 'show variables ... where' show variables where variable_name like "skip_show_databas"; # # Bug #7981:SHOW GLOBAL STATUS crashes server # show global status like "Threads_running"; # # Bug #7915 crash,JOIN VIEW, subquery, # SELECT .. FROM INFORMATION_SCHEMA.COLUMNS # 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; # # Bug #7476: crash on SELECT * FROM INFORMATION_SCHEMA.TABLES # 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; let $tab_count= 65; --disable_query_log while ($tab_count) { EVAL CREATE TABLE t_$tab_count (f1 BIGINT); dec $tab_count ; } --disable_result_log SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES; --enable_result_log SELECT count(*) FROM INFORMATION_SCHEMA.TABLES; let $tab_count= 65; while ($tab_count) { EVAL DROP TABLE t_$tab_count; dec $tab_count ; } --enable_query_log drop view a2, a1; drop table t_crashme; # # Bug #7215 information_schema: columns are longtext instead of varchar # Bug #7217 information_schema: columns are varbinary() instead of timestamp # select table_schema,table_name, column_name from information_schema.columns where data_type = 'longtext'; select table_name, column_name, data_type from information_schema.columns where data_type = 'datetime'; # # Bug #8164 subquery with INFORMATION_SCHEMA.COLUMNS, 100 % CPU # 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); # # Bug #9344 INFORMATION_SCHEMA, wrong content, numeric columns # 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'; drop table t1; # # Bug#10261 INFORMATION_SCHEMA.COLUMNS, incomplete result for non root user # create user mysqltest_4@localhost; connect (user4,localhost,mysqltest_4,,); connection user4; SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='TABLE_NAME'; connection default; delete from mysql.user where user='mysqltest_4'; flush privileges; # # Bug #9404 information_schema: Weird error messages # with SELECT SUM() ... GROUP BY queries # SELECT table_schema, count(*) FROM information_schema.TABLES GROUP BY TABLE_SCHEMA; # # Bug #10964 Information Schema:Authorization check on privilege tables is improper # 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,,); connect (con2,localhost,user2,,); connect (con3,localhost,user3,,); connect (con4,localhost,user4,,); connection con1; select * from information_schema.column_privileges; select * from information_schema.table_privileges; select * from information_schema.schema_privileges; select * from information_schema.user_privileges; show grants; connection con2; select * from information_schema.column_privileges; select * from information_schema.table_privileges; select * from information_schema.schema_privileges; select * from information_schema.user_privileges; show grants; connection con3; select * from information_schema.column_privileges; select * from information_schema.table_privileges; select * from information_schema.schema_privileges; select * from information_schema.user_privileges; show grants; connection con4; select * from information_schema.column_privileges where grantee like '%user%'; select * from information_schema.table_privileges where grantee like '%user%'; select * from information_schema.schema_privileges where grantee like '%user%'; select * from information_schema.user_privileges where grantee like '%user%'; show grants; connection default; drop user user1@localhost, user2@localhost, user3@localhost, user4@localhost; use test; drop database mysqltest; # # Bug #11055 information_schema: routines.sql_data_access has wrong value # 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%'; drop procedure p1; drop procedure p2; # # Bug #9434 SHOW CREATE DATABASE information_schema; # show create database information_schema;