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 End of 5.1 tests.
1451
# Wait till all disconnects are completed
1452
--source include/wait_until_count_sessions.inc
added
removed
mysql-test/t/information_schema.test
