1 # This test uses grants, which can't get tested for embedded server
2 -- source include/not_embedded.inc
4 # check that CSV engine was compiled in, as the result of the test depends
5 # on the presence of the log tables (which are CSV-based).
6 --source include/have_csv.inc
8 # Save the initial number of concurrent sessions
9 --source include/count_sessions.inc
12 # Test for information_schema.schemata &
16 DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
17 DROP VIEW IF EXISTS v1;
21 show variables where variable_name like "skip_show_database";
22 grant select, update, execute on test.* to mysqltest_2@localhost;
23 grant select, update on test.* to mysqltest_1@localhost;
24 create user mysqltest_3@localhost;
25 create user mysqltest_3;
28 select * from information_schema.SCHEMATA where schema_name > 'm';
29 select schema_name from information_schema.schemata;
30 show databases like 't%';
32 show databases where `database` = 't%';
34 # Test for information_schema.tables &
37 create database mysqltest;
38 create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
39 create table test.t2(a int);
40 create table t3(a int, KEY a_data (a));
41 create table mysqltest.t4(a int);
42 create table t5 (id int auto_increment primary key);
43 insert into t5 values (10);
45 SELECT table_name FROM information_schema.TABLES
46 WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND
47 table_name<>'ndb_binlog_index' AND
48 table_name<>'ndb_apply_status';
51 select c,table_name from v1
52 inner join information_schema.TABLES v2 on (v1.c=v2.table_name)
55 select c,table_name from v1
56 left join information_schema.TABLES v2 on (v1.c=v2.table_name)
59 select c, v2.table_name from v1
60 right join information_schema.TABLES v2 on (v1.c=v2.table_name)
63 select table_name from information_schema.TABLES
64 where table_schema = "mysqltest" and table_name like "t%";
66 select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest";
67 show keys from t3 where Key_name = "a_data";
69 show tables like 't%';
70 --replace_column 8 # 12 # 13 #
72 show full columns from t3 like "a%";
73 show full columns from mysql.db like "Insert%";
74 show full columns from v1;
75 select * from information_schema.COLUMNS where table_name="t1"
77 show columns from mysqltest.t1 where field like "%a%";
79 create view mysqltest.v1 (c) as select a from mysqltest.t1;
80 grant select (a) on mysqltest.t1 to mysqltest_2@localhost;
81 grant select on mysqltest.v1 to mysqltest_3;
82 connect (user3,localhost,mysqltest_2,,);
84 select table_name, column_name, privileges from information_schema.columns
85 where table_schema = 'mysqltest' and table_name = 't1';
86 show columns from mysqltest.t1;
87 connect (user4,localhost,mysqltest_3,,mysqltest);
89 select table_name, column_name, privileges from information_schema.columns
90 where table_schema = 'mysqltest' and table_name = 'v1';
91 --error ER_VIEW_NO_EXPLAIN
92 explain select * from v1;
96 drop view v1, mysqltest.v1;
97 drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
98 drop database mysqltest;
100 # Test for information_schema.CHARACTER_SETS &
103 select * from information_schema.CHARACTER_SETS
104 where CHARACTER_SET_NAME like 'latin1%';
105 SHOW CHARACTER SET LIKE 'latin1%';
106 SHOW CHARACTER SET WHERE charset like 'latin1%';
108 # Test for information_schema.COLLATIONS &
112 select * from information_schema.COLLATIONS
113 where COLLATION_NAME like 'latin1%';
115 SHOW COLLATION LIKE 'latin1%';
117 SHOW COLLATION WHERE collation like 'latin1%';
119 select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
120 where COLLATION_NAME like 'latin1%';
122 # Test for information_schema.ROUTINES &
126 drop procedure if exists sel2;
127 drop function if exists sub1;
128 drop function if exists sub2;
131 create function sub1(i int) returns int
134 create procedure sel2()
142 # Bug#7222 information_schema: errors in "routines"
144 select parameter_style, sql_data_access, dtd_identifier
145 from information_schema.routines where routine_schema='test';
147 --replace_column 5 # 6 #
148 show procedure status where db='test';
149 --replace_column 5 # 6 #
150 show function status where db='test';
151 select a.ROUTINE_NAME from information_schema.ROUTINES a,
152 information_schema.SCHEMATA b where
153 a.ROUTINE_SCHEMA = b.SCHEMA_NAME AND b.SCHEMA_NAME='test';
155 explain select a.ROUTINE_NAME from information_schema.ROUTINES a,
156 information_schema.SCHEMATA b where
157 a.ROUTINE_SCHEMA = b.SCHEMA_NAME;
159 select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a,
160 mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) AND a.ROUTINE_SCHEMA='test' order by 1;
161 select count(*) from information_schema.ROUTINES where routine_schema='test';
163 create view v1 as select routine_schema, routine_name from information_schema.routines where routine_schema='test'
164 order by routine_schema, routine_name;
168 connect (user1,localhost,mysqltest_1,,);
170 select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
171 --error ER_SP_DOES_NOT_EXIST
172 show create function sub1;
174 select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
176 grant all privileges on test.* to mysqltest_1@localhost;
177 connect (user2,localhost,mysqltest_1,,);
179 select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
180 create function sub2(i int) returns int
182 select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
183 show create procedure sel2;
184 show create function sub1;
185 show create function sub2;
186 --replace_column 5 # 6 #
187 show function status like "sub2";
192 show create procedure sel2;
197 create view v0 (c) as select schema_name from information_schema.schemata;
200 explain select * from v0;
201 create view v1 (c) as select table_name from information_schema.tables
202 where table_name="v1";
204 create view v2 (c) as select column_name from information_schema.columns
205 where table_name="v2";
207 create view v3 (c) as select CHARACTER_SET_NAME from information_schema.character_sets
208 where CHARACTER_SET_NAME like "latin1%";
210 create view v4 (c) as select COLLATION_NAME from information_schema.collations
211 where COLLATION_NAME like "latin1%";
214 select * from information_schema.views where TABLE_NAME like "v%";
215 drop view v0, v1, v2, v3, v4;
218 # Test for privileges tables
220 create table t1 (a int);
221 grant select,update,insert on t1 to mysqltest_1@localhost;
222 grant select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost;
223 grant all on test.* to mysqltest_1@localhost with grant option;
224 select * from information_schema.USER_PRIVILEGES where grantee like '%mysqltest_1%';
225 select * from information_schema.SCHEMA_PRIVILEGES where grantee like '%mysqltest_1%';
226 select * from information_schema.TABLE_PRIVILEGES where grantee like '%mysqltest_1%';
227 select * from information_schema.COLUMN_PRIVILEGES where grantee like '%mysqltest_1%';
228 delete from mysql.user where user like 'mysqltest%';
229 delete from mysql.db where user like 'mysqltest%';
230 delete from mysql.tables_priv where user like 'mysqltest%';
231 delete from mysql.columns_priv where user like 'mysqltest%';
237 # Test for KEY_COLUMN_USAGE & TABLE_CONSTRAINTS tables
240 create table t1 (a int null, primary key(a));
241 alter table t1 add constraint constraint_1 unique (a);
242 alter table t1 add constraint unique key_1(a);
243 alter table t1 add constraint constraint_2 unique key_2(a);
244 show create table t1;
245 select * from information_schema.TABLE_CONSTRAINTS where
246 TABLE_SCHEMA= "test";
247 select * from information_schema.KEY_COLUMN_USAGE where
248 TABLE_SCHEMA= "test";
251 select table_name from information_schema.TABLES where table_schema like "test%";
252 select table_name,column_name from information_schema.COLUMNS where table_schema like "test%";
253 select ROUTINE_NAME from information_schema.ROUTINES;
256 delete from mysql.user where user='mysqltest_1';
261 create table t1(a int);
262 create view v1 (c) as select a from t1 with check option;
263 create view v2 (c) as select a from t1 WITH LOCAL CHECK OPTION;
264 create view v3 (c) as select a from t1 WITH CASCADED CHECK OPTION;
265 select * from information_schema.views;
266 grant select (a) on test.t1 to joe@localhost with grant option;
267 select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES;
268 select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES;
269 drop view v1, v2, v3;
271 delete from mysql.user where user='joe';
272 delete from mysql.db where user='joe';
273 delete from mysql.tables_priv where user='joe';
274 delete from mysql.columns_priv where user='joe';
277 # QQ This results in NULLs instead of the version numbers when
278 # QQ a LOCK TABLES is in effect when selecting from
279 # QQ information_schema.tables.
281 --disable_parsing # until bug is fixed
283 create procedure px5 ()
286 declare c cursor for select version from
287 information_schema.tables where table_schema <> 'information_schema';
297 select sql_mode from information_schema.ROUTINES;
301 create table t1 (a int not null auto_increment,b int, primary key (a));
302 insert into t1 values (1,1),(NULL,3),(NULL,4);
303 select AUTO_INCREMENT from information_schema.tables where table_name = 't1';
306 create table t1 (s1 int);
307 insert into t1 values (0),(9),(0);
308 select s1 from t1 where s1 in (select version from
309 information_schema.tables) union select version from
310 information_schema.tables;
313 SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
315 SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
318 create table t1 select * from information_schema.CHARACTER_SETS
319 where CHARACTER_SET_NAME like "latin1";
321 alter table t1 default character set utf8;
322 show create table t1;
325 create view v1 as select * from information_schema.TABLES;
327 create table t1(a NUMERIC(5,3), b NUMERIC(5,1), c float(5,2),
328 d NUMERIC(6,4), e float, f DECIMAL(6,3), g int(11), h DOUBLE(10,3),
330 select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH,
331 CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
332 from information_schema.columns where table_name= 't1';
335 create table t115 as select table_name, column_name, column_type
336 from information_schema.columns where table_name = 'proc';
341 create procedure p108 () begin declare c cursor for select data_type
342 from information_schema.columns; open c; open c; end;//
343 --error ER_SP_CURSOR_ALREADY_OPEN
348 create view v1 as select A1.table_name from information_schema.TABLES A1
349 where table_name= "user";
353 create view vo as select 'a' union select 'a';
355 select * from information_schema.TABLE_CONSTRAINTS where
357 select * from information_schema.KEY_COLUMN_USAGE where
361 select TABLE_NAME,TABLE_TYPE,ENGINE
362 from information_schema.tables
363 where table_schema='information_schema' limit 2;
364 show tables from information_schema like "T%";
366 --error ER_DBACCESS_DENIED_ERROR
367 create database information_schema;
368 use information_schema;
369 show full tables like "T%";
370 --error ER_UNKNOWN_TABLE
371 create table t1(a int);
374 use information_schema;
375 show tables like "T%";
378 # Bug#7210 information_schema: can't access when table-name = reserved word
380 select table_name from tables where table_name='user';
381 select column_name, privileges from columns
382 where table_name='user' and column_name like '%o%';
385 # Bug#7212 information_schema: "Can't find file" errors if storage engine gone
386 # Bug#7211 information_schema: crash if bad view
389 create function sub1(i int) returns int
391 create table t1(f1 int);
392 create view v2 (c) as select f1 from t1;
393 create view v3 (c) as select sub1(1);
394 create table t4(f1 int, KEY f1_key (f1));
397 select table_name from information_schema.views
398 where table_schema='test';
399 select table_name from information_schema.views
400 where table_schema='test';
401 select column_name from information_schema.columns
402 where table_schema='test';
403 select index_name from information_schema.statistics where table_schema='test';
404 select constraint_name from information_schema.table_constraints
405 where table_schema='test';
407 show create table v3;
413 # Bug#7213 information_schema: redundant non-standard TABLE_NAMES table
415 --error ER_UNKNOWN_TABLE
416 select * from information_schema.table_names;
419 # Bug#2719 information_schema: errors in "columns"
421 select column_type from information_schema.columns
422 where table_schema="information_schema" and table_name="COLUMNS" and
423 (column_name="character_set_name" or column_name="collation_name");
426 # Bug#2718 information_schema: errors in "tables"
428 select TABLE_ROWS from information_schema.tables where
429 table_schema="information_schema" and table_name="COLUMNS";
430 select table_type from information_schema.tables
431 where table_schema="mysql" and table_name="user";
433 # test for 'show open tables ... where'
434 show open tables where `table` like "user";
435 # test for 'show status ... where'
436 show status where variable_name like "%database%";
437 # test for 'show variables ... where'
438 show variables where variable_name like "skip_show_databas";
441 # Bug#7981 SHOW GLOBAL STATUS crashes server
443 # We don't actually care about the value, just that it doesn't crash.
445 show global status like "Threads_running";
448 # Bug#7915 crash,JOIN VIEW, subquery,
449 # SELECT .. FROM INFORMATION_SCHEMA.COLUMNS
451 create table t1(f1 int);
452 create table t2(f2 int);
453 create view v1 as select * from t1, t2;
454 set @got_val= (select count(*) from information_schema.columns);
459 # Bug#7476 crash on SELECT * FROM INFORMATION_SCHEMA.TABLES
462 CREATE TABLE t_crashme ( f1 BIGINT);
463 CREATE VIEW a1 (t_CRASHME) AS SELECT f1 FROM t_crashme GROUP BY f1;
464 CREATE VIEW a2 AS SELECT t_CRASHME FROM a1;
469 EVAL CREATE TABLE t_$tab_count (f1 BIGINT);
473 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;
475 SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test';
479 EVAL DROP TABLE t_$tab_count;
484 drop table t_crashme;
487 # Bug#7215 information_schema: columns are longtext instead of varchar
488 # Bug#7217 information_schema: columns are varbinary() instead of timestamp
490 select table_schema,table_name, column_name from
491 information_schema.columns
492 where data_type = 'longtext';
493 select table_name, column_name, data_type from information_schema.columns
494 where data_type = 'datetime';
497 # Bug#8164 subquery with INFORMATION_SCHEMA.COLUMNS, 100 % CPU
499 SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A
501 (SELECT * FROM INFORMATION_SCHEMA.COLUMNS B
502 WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
503 AND A.TABLE_NAME = B.TABLE_NAME);
506 # Bug#9344 INFORMATION_SCHEMA, wrong content, numeric columns
513 x_decimal DECIMAL(5,3),
514 x_numeric NUMERIC(5,3),
517 x_double_precision DOUBLE PRECISION );
518 SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
519 FROM INFORMATION_SCHEMA.COLUMNS
520 WHERE TABLE_NAME= 't1';
524 # Bug#10261 INFORMATION_SCHEMA.COLUMNS, incomplete result for non root user
527 grant select on test.* to mysqltest_4@localhost;
528 connect (user10261,localhost,mysqltest_4,,);
529 connection user10261;
530 SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS
531 where COLUMN_NAME='TABLE_NAME';
533 disconnect user10261;
534 delete from mysql.user where user='mysqltest_4';
535 delete from mysql.db where user='mysqltest_4';
539 # Bug#9404 information_schema: Weird error messages
540 # with SELECT SUM() ... GROUP BY queries
542 SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status' GROUP BY TABLE_SCHEMA;
546 # TRIGGERS table test
548 create table t1 (i int, j int);
551 create trigger trg1 before insert on t1 for each row
557 create trigger trg2 before update on t1 for each row
559 if old.i % 2 = 0 then
563 create trigger trg3 after update on t1 for each row
571 select * from information_schema.triggers where trigger_schema in ('mysql', 'information_schema', 'test', 'mysqltest');
580 # Bug#10964 Information Schema:Authorization check on privilege tables is improper
583 create database mysqltest;
584 create table mysqltest.t1 (f1 int, f2 int);
585 create table mysqltest.t2 (f1 int);
586 grant select (f1) on mysqltest.t1 to user1@localhost;
587 grant select on mysqltest.t2 to user2@localhost;
588 grant select on mysqltest.* to user3@localhost;
589 grant select on *.* to user4@localhost;
591 connect (con1,localhost,user1,,mysqltest);
592 connect (con2,localhost,user2,,mysqltest);
593 connect (con3,localhost,user3,,mysqltest);
594 connect (con4,localhost,user4,,);
596 select * from information_schema.column_privileges order by grantee;
597 select * from information_schema.table_privileges order by grantee;
598 select * from information_schema.schema_privileges order by grantee;
599 select * from information_schema.user_privileges order by grantee;
602 select * from information_schema.column_privileges order by grantee;
603 select * from information_schema.table_privileges order by grantee;
604 select * from information_schema.schema_privileges order by grantee;
605 select * from information_schema.user_privileges order by grantee;
608 select * from information_schema.column_privileges order by grantee;
609 select * from information_schema.table_privileges order by grantee;
610 select * from information_schema.schema_privileges order by grantee;
611 select * from information_schema.user_privileges order by grantee;
614 select * from information_schema.column_privileges where grantee like '%user%'
616 select * from information_schema.table_privileges where grantee like '%user%'
618 select * from information_schema.schema_privileges where grantee like '%user%'
620 select * from information_schema.user_privileges where grantee like '%user%'
628 drop user user1@localhost, user2@localhost, user3@localhost, user4@localhost;
630 drop database mysqltest;
633 # Bug#11055 information_schema: routines.sql_data_access has wrong value
636 drop procedure if exists p1;
637 drop procedure if exists p2;
640 create procedure p1 () modifies sql data set @a = 5;
641 create procedure p2 () set @a = 5;
642 select sql_data_access from information_schema.routines
643 where specific_name like 'p%';
648 # Bug#9434 SHOW CREATE DATABASE information_schema;
650 show create database information_schema;
653 # Bug#11057 information_schema: columns table has some questionable contents
654 # Bug#12301 information_schema: NUMERIC_SCALE must be 0 for integer columns
656 create table t1(f1 LONGBLOB, f2 LONGTEXT);
657 select column_name,data_type,CHARACTER_OCTET_LENGTH,
658 CHARACTER_MAXIMUM_LENGTH
659 from information_schema.columns
660 where table_name='t1';
662 create table t1(f1 tinyint, f2 SMALLINT, f3 mediumint, f4 int,
663 f5 BIGINT, f6 BIT, f7 bit(64));
664 select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
665 from information_schema.columns
666 where table_name='t1';
670 # Bug#12127 triggers do not show in info_schema before they are used if set to the database
672 create table t1 (f1 integer);
673 create trigger tr1 after insert on t1 for each row set @test_var=42;
674 use information_schema;
675 select trigger_schema, trigger_name from triggers where
681 # Bug#12518 COLUMN_DEFAULT has wrong value if NOT NULL is set
683 create table t1 (a int not null, b int);
684 use information_schema;
685 select column_name, column_default from columns
686 where table_schema='test' and table_name='t1';
688 show columns from t1;
692 # Bug#12636 SHOW TABLE STATUS with where condition containing a subquery
693 # over information schema
696 CREATE TABLE t1 (a int);
697 CREATE TABLE t2 (b int);
699 --replace_column 8 # 12 # 13 #
700 SHOW TABLE STATUS FROM test
701 WHERE name IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
702 WHERE TABLE_SCHEMA='test' AND TABLE_TYPE='BASE TABLE');
707 # Bug#12905 show fields from view behaving erratically with current database
709 create table t1(f1 int);
710 create view v1 (c) as select f1 from t1;
711 connect (con5,localhost,root,,*NO-ONE*);
713 show fields from test.v1;
720 # Bug#9846 Inappropriate error displayed while dropping table from 'INFORMATION_SCHEMA'
722 --error ER_PARSE_ERROR
723 alter database information_schema;
724 --error ER_DBACCESS_DENIED_ERROR
725 drop database information_schema;
726 --error ER_DBACCESS_DENIED_ERROR
727 drop table information_schema.tables;
728 --error ER_DBACCESS_DENIED_ERROR
729 alter table information_schema.tables;
731 # Bug#9683 INFORMATION_SCH: Creation of temporary table allowed in Information_schema DB
733 use information_schema;
734 --error ER_DBACCESS_DENIED_ERROR
735 create temporary table schemata(f1 char(10));
737 # Bug#10708 SP's can use INFORMATION_SCHEMA as ROUTINE_SCHEMA
740 --error ER_BAD_DB_ERROR
741 CREATE PROCEDURE p1 ()
743 SELECT 'foo' FROM DUAL;
746 select ROUTINE_NAME from routines where ROUTINE_SCHEMA='information_schema';
748 # Bug#10734 Grant of privileges other than 'select' and 'create view' should fail on schema
750 --error ER_DBACCESS_DENIED_ERROR
751 grant all on information_schema.* to 'user1'@'localhost';
752 --error ER_DBACCESS_DENIED_ERROR
753 grant select on information_schema.* to 'user1'@'localhost';
756 # Bug#14089 FROM list subquery always fails when information_schema is current database
759 create table t1(id int);
760 insert into t1(id) values (1);
761 select 1 from (select 1 from test.t1) a;
762 use information_schema;
763 select 1 from (select 1 from test.t1) a;
768 # Bug#14476 `information_schema`.`TABLES`.`TABLE_TYPE` with empty value
770 create table t1 (f1 int(11));
771 create view v1 as select * from t1;
773 select table_type from information_schema.tables
774 where table_name="v1";
778 # Bug#14387 SHOW COLUMNS doesn't work on temporary tables
779 # Bug#15224 SHOW INDEX from temporary table doesn't work
780 # Bug#12770 DESC cannot display the info. about temporary table
782 create temporary table t1(f1 int, index(f1));
783 show columns from t1;
785 show indexes from t1;
789 # Bug#14271 I_S: columns has no size for (var)binary columns
791 create table t1(f1 binary(32), f2 varbinary(64));
792 select character_maximum_length, character_octet_length
793 from information_schema.columns where table_name='t1';
797 # Bug#15533 crash, information_schema, function, view
799 CREATE TABLE t1 (f1 BIGINT, f2 VARCHAR(20), f3 BIGINT);
800 INSERT INTO t1 SET f1 = 1, f2 = 'Schoenenbourg', f3 = 1;
802 CREATE FUNCTION func2() RETURNS BIGINT RETURN 1;
805 CREATE FUNCTION func1() RETURNS BIGINT
807 RETURN ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS);
811 CREATE VIEW v1 AS SELECT 1 FROM t1
812 WHERE f3 = (SELECT func2 ());
821 # Bug#15307 GROUP_CONCAT() with ORDER BY returns empty set on information_schema
823 select column_type, group_concat(table_schema, '.', table_name), count(*) as num
824 from information_schema.columns where
825 table_schema='information_schema' and
826 (column_type = 'varchar(7)' or column_type = 'varchar(20)'
827 or column_type = 'varchar(27)')
828 group by column_type order by num;
831 # Bug#19236 bad COLUMNS.CHARACTER_MAXIMUM_LENGHT and CHARACTER_OCTET_LENGTH
833 create table t1(f1 char(1) not null, f2 char(9) not null)
834 default character set utf8;
835 select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
836 information_schema.columns where table_schema='test' and table_name = 't1';
840 # Bug#18177 any access to INFORMATION_SCHEMA.ROUTINES crashes
843 INSERT INTO `proc` VALUES ('test','','PROCEDURE','','SQL','CONTAINS_SQL',
844 'NO','DEFINER','','','BEGIN\r\n \r\nEND','root@%','2006-03-02 18:40:03',
845 '2006-03-02 18:40:03','','','utf8','utf8_general_ci','utf8_general_ci','n/a');
846 select routine_name from information_schema.routines where ROUTINE_SCHEMA='test';
847 delete from proc where name='';
851 # Bug#16681 information_schema shows forbidden VIEW details
853 grant select on test.* to mysqltest_1@localhost;
854 create table t1 (id int);
855 create view v1 as select * from t1;
856 create definer = mysqltest_1@localhost
857 sql security definer view v2 as select 1;
859 connect (con16681,localhost,mysqltest_1,,test);
862 select * from information_schema.views
863 where table_name='v1' or table_name='v2';
868 drop user mysqltest_1@localhost;
871 # Bug#19599 duplication of information_schema column value in a CONCAT expr with user var
874 create table t1(f1 char(5));
875 create table t2(f1 char(5));
876 select concat(@a, table_name), @a, table_name
877 from information_schema.tables where table_schema = 'test';
882 # Bug#20230 routine_definition is not null
885 DROP PROCEDURE IF EXISTS p1;
886 DROP FUNCTION IF EXISTS f1;
889 CREATE PROCEDURE p1() SET @a= 1;
890 CREATE FUNCTION f1() RETURNS INT RETURN @a + 1;
891 CREATE USER mysql_bug20230@localhost;
892 GRANT EXECUTE ON PROCEDURE p1 TO mysql_bug20230@localhost;
893 GRANT EXECUTE ON FUNCTION f1 TO mysql_bug20230@localhost;
895 SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test';
896 SHOW CREATE PROCEDURE p1;
897 SHOW CREATE FUNCTION f1;
899 connect (conn1, localhost, mysql_bug20230,,);
901 SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test';
902 SHOW CREATE PROCEDURE p1;
903 SHOW CREATE FUNCTION f1;
912 DROP USER mysql_bug20230@localhost;
915 # Bug#18925 subqueries with MIN/MAX functions on INFORMARTION_SCHEMA
918 SELECT t.table_name, c1.column_name
919 FROM information_schema.tables t
921 information_schema.columns c1
922 ON t.table_schema = c1.table_schema AND
923 t.table_name = c1.table_name
924 WHERE t.table_schema = 'information_schema' AND
925 c1.ordinal_position =
926 ( SELECT COALESCE(MIN(c2.ordinal_position),1)
927 FROM information_schema.columns c2
928 WHERE c2.table_schema = t.table_schema AND
929 c2.table_name = t.table_name AND
930 c2.column_name LIKE '%SCHEMA%'
932 SELECT t.table_name, c1.column_name
933 FROM information_schema.tables t
935 information_schema.columns c1
936 ON t.table_schema = c1.table_schema AND
937 t.table_name = c1.table_name
938 WHERE t.table_schema = 'information_schema' AND
939 c1.ordinal_position =
940 ( SELECT COALESCE(MIN(c2.ordinal_position),1)
941 FROM information_schema.columns c2
942 WHERE c2.table_schema = 'information_schema' AND
943 c2.table_name = t.table_name AND
944 c2.column_name LIKE '%SCHEMA%'
948 # Bug#2123 query with a simple non-correlated subquery over
949 # INFORMARTION_SCHEMA.TABLES
952 SELECT MAX(table_name) FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test');
953 SELECT table_name from information_schema.tables
954 WHERE table_name=(SELECT MAX(table_name)
955 FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test'));
957 # Bug#23037 Bug in field "Default" of query "SHOW COLUMNS FROM table"
959 # Note, MyISAM/InnoDB can't take more that 65532 chars, because the row
960 # size is limited to 65535 bytes (BLOBs not counted)
963 DROP TABLE IF EXISTS bug23037;
964 DROP FUNCTION IF EXISTS get_value;
968 CREATE FUNCTION get_value()
972 DECLARE col1, col2, col3, col4, col6 CHAR(255);
973 DECLARE default_val VARCHAR(65532);
974 DECLARE done INT DEFAULT 0;
975 DECLARE cur1 CURSOR FOR SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='bug23037';
976 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
978 FETCH cur1 INTO col1, col2, col3, col4, default_val, col6;
984 let $body=`SELECT REPEAT('A', 65532)`;
985 eval CREATE TABLE bug23037(fld1 VARCHAR(65532) CHARACTER SET latin1 DEFAULT "$body");
988 SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';
990 SELECT MD5(get_value());
992 SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT), COLUMN_DEFAULT=get_value() FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';
995 DROP FUNCTION get_value;
998 # Bug#22413 EXPLAIN SELECT FROM view with ORDER BY yield server crash
1001 select table_schema as object_schema,
1002 table_name as object_name,
1003 table_type as object_type
1004 from information_schema.tables
1005 order by object_schema;
1006 explain select * from v1;
1007 explain select * from (select table_name from information_schema.tables) as a;
1011 # Bug#23299 Some queries against INFORMATION_SCHEMA with subqueries fail
1013 create table t1 (f1 int(11));
1014 create table t2 (f1 int(11), f2 int(11));
1016 select table_name from information_schema.tables
1017 where table_schema = 'test' and table_name not in
1018 (select table_name from information_schema.columns
1019 where table_schema = 'test' and column_name = 'f3');
1024 # Bug#24630 Subselect query crashes mysqld
1026 select 1 as f1 from information_schema.tables where "CHARACTER_SETS"=
1027 (select cast(table_name as char) from information_schema.tables
1028 order by table_name limit 1) limit 1;
1030 select t.table_name, group_concat(t.table_schema, '.', t.table_name),
1032 from information_schema.tables t
1033 inner join information_schema.columns c1
1034 on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
1035 where t.table_schema = 'information_schema' and
1036 c1.ordinal_position =
1037 (select isnull(c2.column_type) -
1038 isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
1040 from information_schema.columns c2 where
1041 c2.table_schema='information_schema' and
1042 (c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
1043 group by c2.column_type order by num limit 1)
1044 group by t.table_name order by num1, t.table_name;
1047 # Bug#28266 IS_UPDATABLE field on VIEWS table in I_S database is wrong
1049 create table t1(f1 int);
1050 create view v1 as select f1+1 as a from t1;
1051 create table t2 (f1 int, f2 int);
1052 create view v2 as select f1+1 as a, f2 as b from t2;
1053 select table_name, is_updatable from information_schema.views;
1055 # Note: we can perform 'delete' for non updatable view.
1062 # Bug#25859 ALTER DATABASE works w/o parameters
1064 --error ER_PARSE_ERROR
1066 --error ER_PARSE_ERROR
1067 alter database test;
1070 # Bug#27629 Possible security flaw in INFORMATION_SCHEMA and SHOW statements
1073 create database mysqltest;
1074 create table mysqltest.t1(a int, b int, c int);
1075 create trigger mysqltest.t1_ai after insert on mysqltest.t1
1076 for each row set @a = new.a + new.b + new.c;
1077 grant select(b) on mysqltest.t1 to mysqltest_1@localhost;
1079 select trigger_name from information_schema.triggers
1080 where event_object_table='t1';
1081 show triggers from mysqltest;
1083 connect (con27629,localhost,mysqltest_1,,mysqltest);
1084 show columns from t1;
1085 select column_name from information_schema.columns where table_name='t1';
1088 select trigger_name from information_schema.triggers
1089 where event_object_table='t1';
1091 disconnect con27629;
1092 drop user mysqltest_1@localhost;
1093 drop database mysqltest;
1096 # Bug#27747 database metadata doesn't return sufficient column default info
1100 f2 varchar(50) not null,
1101 f3 varchar(50) default '',
1102 f4 varchar(50) default NULL,
1104 f6 bigint not null default 10,
1105 f7 datetime not null,
1106 f8 datetime default '2006-01-01'
1108 select column_default from information_schema.columns where table_name= 't1';
1109 show columns from t1;
1113 # Bug#30079 A check for "hidden" I_S tables is flawed
1115 --error ER_UNKNOWN_TABLE
1116 show fields from information_schema.table_names;
1117 --error ER_UNKNOWN_TABLE
1118 show keys from information_schema.table_names;
1121 # Bug#34529 Crash on complex Falcon I_S select after ALTER .. PARTITION BY
1123 USE information_schema;
1124 SET max_heap_table_size = 16384;
1126 CREATE TABLE test.t1( a INT );
1128 # What we need to create here is a bit of a corner case:
1129 # We need a star query with information_schema tables, where the first
1130 # branch of the star join produces zero rows, so that reading of the
1131 # second branch never happens. At the same time we have to make sure
1132 # that data for at least the last table is swapped from MEMORY/HEAP to
1133 # MyISAM. This and only this triggers the bug.
1136 JOIN collations co ON ( co.collation_name = ta.table_catalog )
1137 JOIN character_sets cs ON ( cs.character_set_name = ta.table_catalog );
1140 SET max_heap_table_size = DEFAULT;
1143 --echo End of 5.0 tests.
1149 select * from information_schema.engines WHERE ENGINE="MyISAM";
1152 # INFORMATION_SCHEMA.PROCESSLIST
1155 grant select on *.* to user3148@localhost;
1156 connect (con3148,localhost,user3148,,test);
1158 select user,db from information_schema.processlist;
1161 drop user user3148@localhost;
1164 # Bug#26174 Server Crash: INSERT ... SELECT ... FROM I_S.GLOBAL_STATUS
1165 # in Event (see also openssl_1.test)
1168 DROP TABLE IF EXISTS server_status;
1169 DROP EVENT IF EXISTS event_status;
1172 SET GLOBAL event_scheduler=1;
1176 CREATE EVENT event_status
1177 ON SCHEDULE AT NOW()
1178 ON COMPLETION NOT PRESERVE
1181 CREATE TABLE server_status
1182 SELECT variable_name
1183 FROM information_schema.global_status
1184 WHERE variable_name LIKE 'ABORTED_CONNECTS' OR
1185 variable_name LIKE 'BINLOG_CACHE_DISK_USE';
1190 let $wait_timeout= 300;
1191 let $wait_condition=select count(*) = 0 from information_schema.events where event_name='event_status';
1192 --source include/wait_condition.inc
1194 SELECT variable_name FROM server_status;
1196 DROP TABLE server_status;
1197 SET GLOBAL event_scheduler=0;
1201 # WL#3732 Information schema optimization
1204 explain select table_name from information_schema.views where
1205 table_schema='test' and table_name='v1';
1207 explain select * from information_schema.tables;
1208 explain select * from information_schema.collations;
1210 explain select * from information_schema.tables where
1211 table_schema='test' and table_name= 't1';
1212 explain select table_name, table_type from information_schema.tables
1213 where table_schema='test';
1215 explain select b.table_name
1216 from information_schema.tables a, information_schema.columns b
1217 where a.table_name='t1' and a.table_schema='test' and b.table_name=a.table_name;
1220 # Bug#30310 wrong result on SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE ..
1222 SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
1223 WHERE SCHEMA_NAME = 'mysqltest';
1225 SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
1226 WHERE SCHEMA_NAME = '';
1228 SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
1229 WHERE SCHEMA_NAME = 'test';
1231 select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME='nonexisting';
1232 select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME='';
1233 select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME='';
1234 select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME='nonexisting';
1237 # Bug#30689 Wrong content in I_S.VIEWS.VIEW_DEFINITION if VIEW is based on I_S
1241 FROM information_schema.tables;
1242 SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'v1';
1246 # Bug#30795 Query on INFORMATION_SCHEMA.SCHEMATA, wrong result
1248 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
1249 WHERE SCHEMA_NAME ='information_schema';
1252 # Bug#31381 Error in retrieving Data from INFORMATION_SCHEMA
1254 SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES
1255 WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
1258 # Bug#31633 Information schema = NULL queries crash the server
1260 select * from information_schema.columns where table_schema = NULL;
1261 select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL;
1262 select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;
1263 select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;
1264 select * from `information_schema`.`PARTITIONS` where `TABLE_SCHEMA` = NULL;
1265 select * from `information_schema`.`PARTITIONS` where `TABLE_NAME` = NULL;
1266 select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL;
1267 select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL;
1268 select * from information_schema.schemata where schema_name = NULL;
1269 select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL;
1270 select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL;
1271 select * from information_schema.tables where table_schema = NULL;
1272 select * from information_schema.tables where table_catalog = NULL;
1273 select * from information_schema.tables where table_name = NULL;
1274 select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL;
1275 select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL;
1276 select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_SCHEMA` = NULL;
1277 select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_TABLE` = NULL;
1278 select * from `information_schema`.`VIEWS` where `TABLE_SCHEMA` = NULL;
1279 select * from `information_schema`.`VIEWS` where `TABLE_NAME` = NULL;
1282 # Bug#31630 debug assert with explain extended select ... from i_s
1284 explain extended select 1 from information_schema.tables;
1287 # Bug#32775 problems with SHOW EVENTS and Information_Schema
1289 use information_schema;
1291 show events from information_schema;
1292 show events where Db= 'information_schema';
1296 --echo # Bug#34166 Server crash in SHOW OPEN TABLES and prelocking
1299 drop table if exists t1;
1300 drop function if exists f1;
1302 create table t1 (a int);
1304 create function f1() returns int
1306 insert into t1 (a) values (1);
1310 --disable_result_log
1311 show open tables where f1()=0;
1312 show open tables where f1()=0;
1318 # Bug#34656 KILL a query = Assertion failed: m_status == DA_ERROR ||
1321 connect (conn1, localhost, root,,);
1323 let $ID= `select connection_id()`;
1324 send select * from information_schema.tables where 1=sleep(100000);
1326 let $wait_timeout= 10;
1327 let $wait_condition=select count(*)=1 from information_schema.processlist
1328 where state='User sleep' and
1329 info='select * from information_schema.tables where 1=sleep(100000)';
1330 --source include/wait_condition.inc
1335 let $wait_timeout= 10;
1336 let $wait_condition=select count(*)=0 from information_schema.processlist
1337 where state='User sleep' and
1338 info='select * from information_schema.tables where 1=sleep(100000)';
1339 --source include/wait_condition.inc
1341 connect (conn1, localhost, root,,);
1343 let $ID= `select connection_id()`;
1344 send select * from information_schema.columns where 1=sleep(100000);
1346 let $wait_timeout= 10;
1347 let $wait_condition=select count(*)=1 from information_schema.processlist
1348 where state='User sleep' and
1349 info='select * from information_schema.columns where 1=sleep(100000)';
1350 --source include/wait_condition.inc
1355 let $wait_timeout= 10;
1356 let $wait_condition=select count(*)=0 from information_schema.processlist
1357 where state='User sleep' and
1358 info='select * from information_schema.columns where 1=sleep(100000)';
1359 --source include/wait_condition.inc
1363 # Bug#38918 selecting from information_schema.columns is disproportionately slow
1365 explain select count(*) from information_schema.tables;
1366 explain select count(*) from information_schema.columns;
1367 explain select count(*) from information_schema.views;
1370 # Bug#39955 SELECT on INFORMATION_SCHEMA.GLOBAL_VARIABLES takes too long
1372 set global init_connect="drop table if exists t1;drop table if exists t1;\
1373 drop table if exists t1;drop table if exists t1;\
1374 drop table if exists t1;drop table if exists t1;\
1375 drop table if exists t1;drop table if exists t1;\
1376 drop table if exists t1;drop table if exists t1;\
1377 drop table if exists t1;drop table if exists t1;\
1378 drop table if exists t1;drop table if exists t1;\
1379 drop table if exists t1;drop table if exists t1;\
1380 drop table if exists t1;drop table if exists t1;\
1381 drop table if exists t1;drop table if exists t1;\
1382 drop table if exists t1;drop table if exists t1;\
1383 drop table if exists t1;drop table if exists t1;\
1384 drop table if exists t1;drop table if exists t1;\
1385 drop table if exists t1;drop table if exists t1;\
1386 drop table if exists t1;drop table if exists t1;\
1387 drop table if exists t1;drop table if exists t1;\
1388 drop table if exists t1;drop table if exists t1;\
1389 drop table if exists t1;drop table if exists t1;\
1390 drop table if exists t1;drop table if exists t1;\
1391 drop table if exists t1;drop table if exists t1;\
1392 drop table if exists t1;drop table if exists t1;";
1393 select * from information_schema.global_variables where variable_name='init_connect';
1394 set global init_connect="";
1397 # Bug#34517 SHOW GLOBAL STATUS does not work properly in embedded server.
1400 create table t0 select * from information_schema.global_status where VARIABLE_NAME='COM_SELECT';
1402 select a.VARIABLE_VALUE - b.VARIABLE_VALUE from t0 b, information_schema.global_status a
1403 where a.VARIABLE_NAME = b.VARIABLE_NAME;
1407 # Bug#35275 INFORMATION_SCHEMA.TABLES.CREATE_OPTIONS omits KEY_BLOCK_SIZE
1409 CREATE TABLE t1(a INT) KEY_BLOCK_SIZE=1;
1410 SELECT CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
1414 # Bug #22047: Time in SHOW PROCESSLIST for SQL thread in replication seems
1415 # to become negative
1418 SET TIMESTAMP=@@TIMESTAMP + 10000000;
1419 SELECT 'OK' AS TEST_RESULT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time < 0;
1420 SET TIMESTAMP=DEFAULT;
1424 --echo # Bug #50276: Security flaw in INFORMATION_SCHEMA.TABLES
1426 CREATE DATABASE db1;
1428 CREATE TABLE t1 (id INT);
1429 CREATE USER nonpriv;
1432 connect (nonpriv_con, localhost, nonpriv,,);
1433 connection nonpriv_con;
1434 --echo # connected as nonpriv
1435 --echo # Should return 0
1436 SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
1437 USE INFORMATION_SCHEMA;
1438 --echo # Should return 0
1439 SELECT COUNT(*) FROM TABLES WHERE TABLE_NAME='t1';
1442 --echo # connected as root
1443 disconnect nonpriv_con;
1449 --echo Bug#54422 query with = 'variables'
1452 CREATE TABLE variables(f1 INT);
1453 SELECT COLUMN_DEFAULT, TABLE_NAME
1454 FROM INFORMATION_SCHEMA.COLUMNS
1455 WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'variables';
1456 DROP TABLE variables;
1459 --echo # Bug #53814: NUMERIC_PRECISION for unsigned bigint field is 19,
1460 --echo # should be 20
1463 CREATE TABLE ubig (a BIGINT, b BIGINT UNSIGNED);
1465 SELECT TABLE_NAME, COLUMN_NAME, NUMERIC_PRECISION
1466 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ubig';
1468 INSERT INTO ubig VALUES (0xFFFFFFFFFFFFFFFF,0xFFFFFFFFFFFFFFFF);
1469 SELECT length(CAST(b AS CHAR)) FROM ubig;
1474 --echo End of 5.1 tests.
1476 # Wait till all disconnects are completed
1477 --source include/wait_until_count_sessions.inc