1set @orig_sql_mode= @@sql_mode; 2set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER','')); 3Warnings: 4Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 5DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5; 6DROP VIEW IF EXISTS v1; 7# 8# Bug#11763174 INFORMATION_SCHEMA.PARAMETERS.NUMERIC_PRECISION SHOULD BE BIGINT 9# 10select table_name, data_type, column_type from information_schema.columns where column_name = 'numeric_precision' and table_schema = 'information_schema'; 11table_name data_type column_type 12COLUMNS bigint bigint(21) unsigned 13PARAMETERS bigint bigint(21) unsigned 14ROUTINES bigint bigint(21) unsigned 15show variables where variable_name like "skip_show_database"; 16Variable_name Value 17skip_show_database OFF 18grant select, update, execute on test.* to mysqltest_2@localhost; 19Warnings: 20Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 21grant select, update on test.* to mysqltest_1@localhost; 22Warnings: 23Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 24create user mysqltest_3@localhost; 25create user mysqltest_3; 26select * from information_schema.SCHEMATA where schema_name > 'm'; 27CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 28def mtr latin1 latin1_swedish_ci NULL 29def mysql latin1 latin1_swedish_ci NULL 30def performance_schema utf8 utf8_general_ci NULL 31def sys utf8 utf8_general_ci NULL 32def test latin1 latin1_swedish_ci NULL 33select schema_name from information_schema.schemata; 34schema_name 35information_schema 36mtr 37mysql 38performance_schema 39sys 40test 41show databases like 't%'; 42Database (t%) 43test 44show databases; 45Database 46information_schema 47mtr 48mysql 49performance_schema 50sys 51test 52show databases where `database` = 't%'; 53Database 54create database mysqltest; 55create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b)); 56create table test.t2(a int); 57create table t3(a int, KEY a_data (a)); 58create table mysqltest.t4(a int); 59create table t5 (id int auto_increment primary key); 60insert into t5 values (10); 61create view v1 (c) as 62SELECT table_name FROM information_schema.TABLES 63WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND 64table_name not like 'ndb_%' AND table_name not like 'innodb_%'; 65select * from v1; 66c 67CHARACTER_SETS 68CLIENT_STATISTICS 69COLLATIONS 70COLLATION_CHARACTER_SET_APPLICABILITY 71COLUMNS 72COLUMN_PRIVILEGES 73INDEX_STATISTICS 74ENGINES 75EVENTS 76FILES 77GLOBAL_STATUS 78GLOBAL_TEMPORARY_TABLES 79GLOBAL_VARIABLES 80KEY_COLUMN_USAGE 81OPTIMIZER_TRACE 82PARAMETERS 83PARTITIONS 84PLUGINS 85PROCESSLIST 86PROFILING 87REFERENTIAL_CONSTRAINTS 88ROUTINES 89SCHEMATA 90SCHEMA_PRIVILEGES 91SESSION_STATUS 92SESSION_VARIABLES 93STATISTICS 94TABLES 95TABLESPACES 96TABLE_CONSTRAINTS 97TABLE_PRIVILEGES 98TABLE_STATISTICS 99TEMPORARY_TABLES 100THREAD_STATISTICS 101TRIGGERS 102USER_PRIVILEGES 103USER_STATISTICS 104VIEWS 105XTRADB_READ_VIEW 106XTRADB_RSEG 107XTRADB_INTERNAL_HASH_TABLES 108XTRADB_ZIP_DICT 109XTRADB_ZIP_DICT_COLS 110columns_priv 111db 112engine_cost 113event 114func 115general_log 116gtid_executed 117help_category 118help_keyword 119help_relation 120help_topic 121plugin 122proc 123procs_priv 124proxies_priv 125server_cost 126servers 127slave_master_info 128slave_relay_log_info 129slave_worker_info 130slow_log 131tables_priv 132time_zone 133time_zone_leap_second 134time_zone_name 135time_zone_transition 136time_zone_transition_type 137user 138t1 139t4 140t2 141t3 142t5 143v1 144select c,table_name from v1 145inner join information_schema.TABLES v2 on (v1.c=v2.table_name) 146where v1.c like "t%"; 147c table_name 148TABLES TABLES 149TABLESPACES TABLESPACES 150TABLE_CONSTRAINTS TABLE_CONSTRAINTS 151TABLE_PRIVILEGES TABLE_PRIVILEGES 152TABLE_STATISTICS TABLE_STATISTICS 153TEMPORARY_TABLES TEMPORARY_TABLES 154THREAD_STATISTICS THREAD_STATISTICS 155TRIGGERS TRIGGERS 156tables_priv tables_priv 157time_zone time_zone 158time_zone_leap_second time_zone_leap_second 159time_zone_name time_zone_name 160time_zone_transition time_zone_transition 161time_zone_transition_type time_zone_transition_type 162t1 t1 163t4 t4 164t2 t2 165t3 t3 166t5 t5 167select c,table_name from v1 168left join information_schema.TABLES v2 on (v1.c=v2.table_name) 169where v1.c like "t%"; 170c table_name 171TABLES TABLES 172TABLESPACES TABLESPACES 173TABLE_CONSTRAINTS TABLE_CONSTRAINTS 174TABLE_PRIVILEGES TABLE_PRIVILEGES 175TABLE_STATISTICS TABLE_STATISTICS 176TEMPORARY_TABLES TEMPORARY_TABLES 177THREAD_STATISTICS THREAD_STATISTICS 178TRIGGERS TRIGGERS 179tables_priv tables_priv 180time_zone time_zone 181time_zone_leap_second time_zone_leap_second 182time_zone_name time_zone_name 183time_zone_transition time_zone_transition 184time_zone_transition_type time_zone_transition_type 185t1 t1 186t4 t4 187t2 t2 188t3 t3 189t5 t5 190select c, v2.table_name from v1 191right join information_schema.TABLES v2 on (v1.c=v2.table_name) 192where v1.c like "t%"; 193c table_name 194TABLES TABLES 195TABLESPACES TABLESPACES 196TABLE_CONSTRAINTS TABLE_CONSTRAINTS 197TABLE_PRIVILEGES TABLE_PRIVILEGES 198TABLE_STATISTICS TABLE_STATISTICS 199TEMPORARY_TABLES TEMPORARY_TABLES 200THREAD_STATISTICS THREAD_STATISTICS 201TRIGGERS TRIGGERS 202tables_priv tables_priv 203time_zone time_zone 204time_zone_leap_second time_zone_leap_second 205time_zone_name time_zone_name 206time_zone_transition time_zone_transition 207time_zone_transition_type time_zone_transition_type 208t1 t1 209t4 t4 210t2 t2 211t3 t3 212t5 t5 213select table_name from information_schema.TABLES 214where table_schema = "mysqltest" and table_name like "t%"; 215table_name 216t1 217t4 218select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest"; 219TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT 220def mysqltest t1 1 mysqltest string_data 1 b A NULL NULL NULL YES BTREE 221show keys from t3 where Key_name = "a_data"; 222Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 223t3 1 a_data 1 a A NULL NULL NULL YES BTREE 224show tables like 't%'; 225Tables_in_test (t%) 226t2 227t3 228t5 229show table status; 230Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment 231t2 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL 232t3 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL 233t5 MyISAM 10 Fixed 1 7 7 # 2048 0 11 # # NULL latin1_swedish_ci NULL 234v1 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW 235show full columns from t3 like "a%"; 236Field Type Collation Null Key Default Extra Privileges Comment 237a int(11) NULL YES MUL NULL select,insert,update,references 238show full columns from mysql.db like "Insert%"; 239Field Type Collation Null Key Default Extra Privileges Comment 240Insert_priv enum('N','Y') utf8_general_ci NO N select,insert,update,references 241show full columns from v1; 242Field Type Collation Null Key Default Extra Privileges Comment 243c varchar(64) utf8_general_ci NO select,insert,update,references 244select * from information_schema.COLUMNS where table_name="t1" 245and column_name= "a"; 246TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT GENERATION_EXPRESSION 247def mysqltest t1 a 1 NULL YES int NULL NULL 10 0 NULL NULL NULL int(11) select,insert,update,references 248show columns from mysqltest.t1 where field like "%a%"; 249Field Type Null Key Default Extra 250a int(11) YES NULL 251create view mysqltest.v1 (c) as select a from mysqltest.t1; 252grant select (a) on mysqltest.t1 to mysqltest_2@localhost; 253grant select on mysqltest.v1 to mysqltest_3; 254select table_name, column_name, privileges from information_schema.columns 255where table_schema = 'mysqltest' and table_name = 't1'; 256table_name column_name privileges 257t1 a select 258show columns from mysqltest.t1; 259Field Type Null Key Default Extra 260a int(11) YES NULL 261select table_name, column_name, privileges from information_schema.columns 262where table_schema = 'mysqltest' and table_name = 'v1'; 263table_name column_name privileges 264v1 c select 265explain select * from v1; 266ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 267drop view v1, mysqltest.v1; 268drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5; 269drop database mysqltest; 270select * from information_schema.CHARACTER_SETS 271where CHARACTER_SET_NAME like 'latin1%'; 272CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN 273latin1 latin1_swedish_ci cp1252 West European 1 274SHOW CHARACTER SET LIKE 'latin1%'; 275Charset Description Default collation Maxlen 276latin1 cp1252 West European latin1_swedish_ci 1 277SHOW CHARACTER SET WHERE charset like 'latin1%'; 278Charset Description Default collation Maxlen 279latin1 cp1252 West European latin1_swedish_ci 1 280select * from information_schema.COLLATIONS 281where COLLATION_NAME like 'latin1%'; 282COLLATION_NAME CHARACTER_SET_NAME ID IS_DEFAULT IS_COMPILED SORTLEN 283latin1_german1_ci latin1 5 # 1 284latin1_swedish_ci latin1 8 Yes # 1 285latin1_danish_ci latin1 15 # 1 286latin1_german2_ci latin1 31 # 2 287latin1_bin latin1 47 # 1 288latin1_general_ci latin1 48 # 1 289latin1_general_cs latin1 49 # 1 290latin1_spanish_ci latin1 94 # 1 291SHOW COLLATION LIKE 'latin1%'; 292Collation Charset Id Default Compiled Sortlen 293latin1_german1_ci latin1 5 # 1 294latin1_swedish_ci latin1 8 Yes # 1 295latin1_danish_ci latin1 15 # 1 296latin1_german2_ci latin1 31 # 2 297latin1_bin latin1 47 # 1 298latin1_general_ci latin1 48 # 1 299latin1_general_cs latin1 49 # 1 300latin1_spanish_ci latin1 94 # 1 301SHOW COLLATION WHERE collation like 'latin1%'; 302Collation Charset Id Default Compiled Sortlen 303latin1_german1_ci latin1 5 # 1 304latin1_swedish_ci latin1 8 Yes # 1 305latin1_danish_ci latin1 15 # 1 306latin1_german2_ci latin1 31 # 2 307latin1_bin latin1 47 # 1 308latin1_general_ci latin1 48 # 1 309latin1_general_cs latin1 49 # 1 310latin1_spanish_ci latin1 94 # 1 311select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY 312where COLLATION_NAME like 'latin1%'; 313COLLATION_NAME CHARACTER_SET_NAME 314latin1_german1_ci latin1 315latin1_swedish_ci latin1 316latin1_danish_ci latin1 317latin1_german2_ci latin1 318latin1_bin latin1 319latin1_general_ci latin1 320latin1_general_cs latin1 321latin1_spanish_ci latin1 322drop procedure if exists sel2; 323drop function if exists sub1; 324drop function if exists sub2; 325create function sub1(i int) returns int 326return i+1; 327create procedure sel2() 328begin 329select * from t1; 330select * from t2; 331end| 332select parameter_style, sql_data_access, dtd_identifier 333from information_schema.routines where routine_schema='test'; 334parameter_style sql_data_access dtd_identifier 335SQL CONTAINS SQL NULL 336SQL CONTAINS SQL int(11) 337show procedure status where db='test'; 338Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 339test sel2 PROCEDURE root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 340show function status where db='test'; 341Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 342test sub1 FUNCTION root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 343select a.ROUTINE_NAME from information_schema.ROUTINES a, 344information_schema.SCHEMATA b where 345a.ROUTINE_SCHEMA = b.SCHEMA_NAME AND b.SCHEMA_NAME='test'; 346ROUTINE_NAME 347sel2 348sub1 349explain select a.ROUTINE_NAME from information_schema.ROUTINES a, 350information_schema.SCHEMATA b where 351a.ROUTINE_SCHEMA = b.SCHEMA_NAME; 352id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3531 SIMPLE # NULL ALL NULL NULL NULL NULL NULL NULL NULL 3541 SIMPLE # NULL ALL NULL NULL NULL NULL NULL NULL Using where; Using join buffer (Block Nested Loop) 355Warnings: 356Note 1003 # 357select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a, 358mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) AND a.ROUTINE_SCHEMA='test' order by 1; 359ROUTINE_NAME name 360sel2 sel2 361sub1 sub1 362select count(*) from information_schema.ROUTINES where routine_schema='test'; 363count(*) 3642 365create view v1 as select routine_schema, routine_name from information_schema.routines where routine_schema='test' 366order by routine_schema, routine_name; 367select * from v1; 368routine_schema routine_name 369test sel2 370test sub1 371drop view v1; 372select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; 373ROUTINE_NAME ROUTINE_DEFINITION 374show create function sub1; 375ERROR 42000: FUNCTION sub1 does not exist 376select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; 377ROUTINE_NAME ROUTINE_DEFINITION 378sel2 NULL 379sub1 NULL 380grant all privileges on test.* to mysqltest_1@localhost; 381select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; 382ROUTINE_NAME ROUTINE_DEFINITION 383sel2 NULL 384sub1 NULL 385create function sub2(i int) returns int 386return i+1; 387select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; 388ROUTINE_NAME ROUTINE_DEFINITION 389sel2 NULL 390sub1 NULL 391sub2 return i+1 392show create procedure sel2; 393Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 394sel2 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION NULL latin1 latin1_swedish_ci latin1_swedish_ci 395show create function sub1; 396Function sql_mode Create Function character_set_client collation_connection Database Collation 397sub1 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION NULL latin1 latin1_swedish_ci latin1_swedish_ci 398show create function sub2; 399Function sql_mode Create Function character_set_client collation_connection Database Collation 400sub2 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`mysqltest_1`@`localhost` FUNCTION `sub2`(i int) RETURNS int(11) 401return i+1 latin1 latin1_swedish_ci latin1_swedish_ci 402show function status like "sub2"; 403Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 404test sub2 FUNCTION mysqltest_1@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 405drop function sub2; 406show create procedure sel2; 407Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 408sel2 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `sel2`() 409begin 410select * from t1; 411select * from t2; 412end latin1 latin1_swedish_ci latin1_swedish_ci 413create view v0 (c) as select schema_name from information_schema.schemata; 414select * from v0; 415c 416information_schema 417mtr 418mysql 419performance_schema 420sys 421test 422explain select * from v0; 423id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4241 SIMPLE # NULL ALL NULL NULL NULL NULL NULL NULL NULL 425Warnings: 426Note 1003 # 427create view v1 (c) as select table_name from information_schema.tables 428where table_name="v1"; 429select * from v1; 430c 431v1 432create view v2 (c) as select column_name from information_schema.columns 433where table_name="v2"; 434select * from v2; 435c 436c 437create view v3 (c) as select CHARACTER_SET_NAME from information_schema.character_sets 438where CHARACTER_SET_NAME like "latin1%"; 439select * from v3; 440c 441latin1 442create view v4 (c) as select COLLATION_NAME from information_schema.collations 443where COLLATION_NAME like "latin1%"; 444select * from v4; 445c 446latin1_german1_ci 447latin1_swedish_ci 448latin1_danish_ci 449latin1_german2_ci 450latin1_bin 451latin1_general_ci 452latin1_general_cs 453latin1_spanish_ci 454show keys from v4; 455Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 456select * from information_schema.views where TABLE_SCHEMA != 'sys' and TABLE_NAME like "v%"; 457TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION 458def test v0 select `information_schema`.`schemata`.`SCHEMA_NAME` AS `c` from `information_schema`.`schemata` NONE NO root@localhost DEFINER latin1 latin1_swedish_ci 459def test v1 select `information_schema`.`tables`.`TABLE_NAME` AS `c` from `information_schema`.`tables` where (`information_schema`.`tables`.`TABLE_NAME` = 'v1') NONE NO root@localhost DEFINER latin1 latin1_swedish_ci 460def test v2 select `information_schema`.`columns`.`COLUMN_NAME` AS `c` from `information_schema`.`columns` where (`information_schema`.`columns`.`TABLE_NAME` = 'v2') NONE NO root@localhost DEFINER latin1 latin1_swedish_ci 461def test v3 select `information_schema`.`character_sets`.`CHARACTER_SET_NAME` AS `c` from `information_schema`.`character_sets` where (`information_schema`.`character_sets`.`CHARACTER_SET_NAME` like 'latin1%') NONE NO root@localhost DEFINER latin1 latin1_swedish_ci 462def test v4 select `information_schema`.`collations`.`COLLATION_NAME` AS `c` from `information_schema`.`collations` where (`information_schema`.`collations`.`COLLATION_NAME` like 'latin1%') NONE NO root@localhost DEFINER latin1 latin1_swedish_ci 463drop view v0, v1, v2, v3, v4; 464create table t1 (a int); 465grant select,update,insert on t1 to mysqltest_1@localhost; 466grant select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost; 467grant all on test.* to mysqltest_1@localhost with grant option; 468select * from information_schema.USER_PRIVILEGES where grantee like '%mysqltest_1%'; 469GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 470'mysqltest_1'@'localhost' def USAGE NO 471select * from information_schema.SCHEMA_PRIVILEGES where grantee like '%mysqltest_1%'; 472GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE 473'mysqltest_1'@'localhost' def test SELECT YES 474'mysqltest_1'@'localhost' def test INSERT YES 475'mysqltest_1'@'localhost' def test UPDATE YES 476'mysqltest_1'@'localhost' def test DELETE YES 477'mysqltest_1'@'localhost' def test CREATE YES 478'mysqltest_1'@'localhost' def test DROP YES 479'mysqltest_1'@'localhost' def test REFERENCES YES 480'mysqltest_1'@'localhost' def test INDEX YES 481'mysqltest_1'@'localhost' def test ALTER YES 482'mysqltest_1'@'localhost' def test CREATE TEMPORARY TABLES YES 483'mysqltest_1'@'localhost' def test LOCK TABLES YES 484'mysqltest_1'@'localhost' def test EXECUTE YES 485'mysqltest_1'@'localhost' def test CREATE VIEW YES 486'mysqltest_1'@'localhost' def test SHOW VIEW YES 487'mysqltest_1'@'localhost' def test CREATE ROUTINE YES 488'mysqltest_1'@'localhost' def test ALTER ROUTINE YES 489'mysqltest_1'@'localhost' def test EVENT YES 490'mysqltest_1'@'localhost' def test TRIGGER YES 491select * from information_schema.TABLE_PRIVILEGES where grantee like '%mysqltest_1%'; 492GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 493'mysqltest_1'@'localhost' def test t1 SELECT NO 494'mysqltest_1'@'localhost' def test t1 INSERT NO 495'mysqltest_1'@'localhost' def test t1 UPDATE NO 496select * from information_schema.COLUMN_PRIVILEGES where grantee like '%mysqltest_1%'; 497GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 498'mysqltest_1'@'localhost' def test t1 a SELECT NO 499'mysqltest_1'@'localhost' def test t1 a INSERT NO 500'mysqltest_1'@'localhost' def test t1 a UPDATE NO 501'mysqltest_1'@'localhost' def test t1 a REFERENCES NO 502delete from mysql.user where user like 'mysqltest%'; 503delete from mysql.db where user like 'mysqltest%'; 504delete from mysql.tables_priv where user like 'mysqltest%'; 505delete from mysql.columns_priv where user like 'mysqltest%'; 506flush privileges; 507drop table t1; 508create table t1 (a int not null, primary key(a)); 509alter table t1 add constraint constraint_1 unique (a); 510alter table t1 add constraint unique key_1(a); 511Warnings: 512Warning 1831 Duplicate index 'key_1' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. 513alter table t1 add constraint constraint_2 unique key_2(a); 514Warnings: 515Warning 1831 Duplicate index 'key_2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. 516show create table t1; 517Table Create Table 518t1 CREATE TABLE `t1` ( 519 `a` int(11) NOT NULL, 520 PRIMARY KEY (`a`), 521 UNIQUE KEY `constraint_1` (`a`), 522 UNIQUE KEY `key_1` (`a`), 523 UNIQUE KEY `key_2` (`a`) 524) ENGINE=MyISAM DEFAULT CHARSET=latin1 525select * from information_schema.TABLE_CONSTRAINTS where 526TABLE_SCHEMA= "test"; 527CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE 528def test PRIMARY test t1 PRIMARY KEY 529def test constraint_1 test t1 UNIQUE 530def test key_1 test t1 UNIQUE 531def test key_2 test t1 UNIQUE 532select * from information_schema.key_column_usage where 533TABLE_SCHEMA= "test"; 534CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME 535def test PRIMARY def test t1 a 1 NULL NULL NULL NULL 536def test constraint_1 def test t1 a 1 NULL NULL NULL NULL 537def test key_1 def test t1 a 1 NULL NULL NULL NULL 538def test key_2 def test t1 a 1 NULL NULL NULL NULL 539select table_name from information_schema.TABLES where table_schema like "test%"; 540table_name 541t1 542select table_name,column_name from information_schema.COLUMNS where table_schema like "test%"; 543table_name column_name 544t1 a 545select ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_SCHEMA != 'sys'; 546ROUTINE_NAME 547sel2 548sub1 549delete from mysql.user where user='mysqltest_1'; 550drop table t1; 551drop procedure sel2; 552drop function sub1; 553create table t1(a int); 554create view v1 (c) as select a from t1 with check option; 555create view v2 (c) as select a from t1 WITH LOCAL CHECK OPTION; 556create view v3 (c) as select a from t1 WITH CASCADED CHECK OPTION; 557select * from information_schema.views where table_schema != 'sys'; 558TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION 559def test v1 select `test`.`t1`.`a` AS `c` from `test`.`t1` CASCADED YES root@localhost DEFINER latin1 latin1_swedish_ci 560def test v2 select `test`.`t1`.`a` AS `c` from `test`.`t1` LOCAL YES root@localhost DEFINER latin1 latin1_swedish_ci 561def test v3 select `test`.`t1`.`a` AS `c` from `test`.`t1` CASCADED YES root@localhost DEFINER latin1 latin1_swedish_ci 562grant select (a) on test.t1 to joe@localhost with grant option; 563Warnings: 564Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 565select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE table_schema != 'sys'; 566GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 567'joe'@'localhost' def test t1 a SELECT YES 568select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE table_schema NOT IN ('sys','mysql'); 569GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 570drop view v1, v2, v3; 571drop table t1; 572delete from mysql.user where user='joe'; 573delete from mysql.db where user='joe'; 574delete from mysql.tables_priv where user='joe'; 575delete from mysql.columns_priv where user='joe'; 576flush privileges; 577create table t1 (a int not null auto_increment,b int, primary key (a)); 578insert into t1 values (1,1),(NULL,3),(NULL,4); 579select AUTO_INCREMENT from information_schema.tables where table_name = 't1'; 580AUTO_INCREMENT 5814 582drop table t1; 583create table t1 (s1 int); 584insert into t1 values (0),(9),(0); 585select s1 from t1 where s1 in (select version from 586information_schema.tables) union select version from 587information_schema.tables; 588s1 58910 590NULL 591drop table t1; 592SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets; 593Table Create Table 594CHARACTER_SETS CREATE TEMPORARY TABLE `CHARACTER_SETS` ( 595 `CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '', 596 `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL DEFAULT '', 597 `DESCRIPTION` varchar(60) NOT NULL DEFAULT '', 598 `MAXLEN` bigint(3) NOT NULL DEFAULT '0' 599) ENGINE=MEMORY DEFAULT CHARSET=utf8 600set names latin2; 601SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets; 602Table Create Table 603CHARACTER_SETS CREATE TEMPORARY TABLE `CHARACTER_SETS` ( 604 `CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '', 605 `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL DEFAULT '', 606 `DESCRIPTION` varchar(60) NOT NULL DEFAULT '', 607 `MAXLEN` bigint(3) NOT NULL DEFAULT '0' 608) ENGINE=MEMORY DEFAULT CHARSET=utf8 609set names latin1; 610create table t1 select * from information_schema.CHARACTER_SETS 611where CHARACTER_SET_NAME like "latin1"; 612select * from t1; 613CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN 614latin1 latin1_swedish_ci cp1252 West European 1 615alter table t1 default character set utf8; 616show create table t1; 617Table Create Table 618t1 CREATE TABLE `t1` ( 619 `CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '', 620 `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL DEFAULT '', 621 `DESCRIPTION` varchar(60) NOT NULL DEFAULT '', 622 `MAXLEN` bigint(3) NOT NULL DEFAULT '0' 623) ENGINE=MyISAM DEFAULT CHARSET=utf8 624drop table t1; 625create view v1 as select * from information_schema.TABLES; 626drop view v1; 627create table t1(a NUMERIC(5,3), b NUMERIC(5,1), c float(5,2), 628d NUMERIC(6,4), e float, f DECIMAL(6,3), g int(11), h DOUBLE(10,3), 629i DOUBLE); 630select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, 631CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE 632from information_schema.columns where table_name= 't1'; 633COLUMN_NAME COLUMN_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE 634a decimal(5,3) NULL NULL 5 3 635b decimal(5,1) NULL NULL 5 1 636c float(5,2) NULL NULL 5 2 637d decimal(6,4) NULL NULL 6 4 638e float NULL NULL 12 NULL 639f decimal(6,3) NULL NULL 6 3 640g int(11) NULL NULL 10 0 641h double(10,3) NULL NULL 10 3 642i double NULL NULL 22 NULL 643drop table t1; 644create table t115 as select table_name, column_name, column_type 645from information_schema.columns where table_name = 'proc'; 646select * from t115; 647table_name column_name column_type 648proc db char(64) 649proc name char(64) 650proc type enum('FUNCTION','PROCEDURE') 651proc specific_name char(64) 652proc language enum('SQL') 653proc sql_data_access enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') 654proc is_deterministic enum('YES','NO') 655proc security_type enum('INVOKER','DEFINER') 656proc param_list blob 657proc returns longblob 658proc body longblob 659proc definer char(93) 660proc created timestamp 661proc modified timestamp 662proc sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') 663proc comment text 664proc character_set_client char(32) 665proc collation_connection char(32) 666proc db_collation char(32) 667proc body_utf8 longblob 668drop table t115; 669create procedure p108 () begin declare c cursor for select data_type 670from information_schema.columns; open c; open c; end;// 671call p108()// 672ERROR 24000: Cursor is already open 673drop procedure p108; 674create view v1 as select A1.table_name from information_schema.TABLES A1 675where table_name= "user"; 676select * from v1; 677table_name 678user 679drop view v1; 680create view vo as select 'a' union select 'a'; 681show index from vo; 682Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 683select * from information_schema.TABLE_CONSTRAINTS where 684TABLE_NAME= "vo"; 685CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE 686select * from information_schema.key_column_usage where 687TABLE_NAME= "vo"; 688CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME 689drop view vo; 690select TABLE_NAME,TABLE_TYPE,ENGINE 691from information_schema.tables 692where table_schema='information_schema' limit 2; 693TABLE_NAME TABLE_TYPE ENGINE 694CHARACTER_SETS SYSTEM VIEW MEMORY 695CLIENT_STATISTICS SYSTEM VIEW MEMORY 696show tables from information_schema like "T%"; 697Tables_in_information_schema (T%) 698TABLES 699TABLESPACES 700TABLE_CONSTRAINTS 701TABLE_PRIVILEGES 702TABLE_STATISTICS 703TEMPORARY_TABLES 704THREAD_STATISTICS 705TRIGGERS 706create database information_schema; 707ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 708use information_schema; 709show full tables like "T%"; 710Tables_in_information_schema (T%) Table_type 711TABLES SYSTEM VIEW 712TABLESPACES SYSTEM VIEW 713TABLE_CONSTRAINTS SYSTEM VIEW 714TABLE_PRIVILEGES SYSTEM VIEW 715TABLE_STATISTICS SYSTEM VIEW 716TEMPORARY_TABLES SYSTEM VIEW 717THREAD_STATISTICS SYSTEM VIEW 718TRIGGERS SYSTEM VIEW 719create table t1(a int); 720ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 721use test; 722show tables; 723Tables_in_test 724use information_schema; 725show tables like "T%"; 726Tables_in_information_schema (T%) 727TABLES 728TABLESPACES 729TABLE_CONSTRAINTS 730TABLE_PRIVILEGES 731TABLE_STATISTICS 732TEMPORARY_TABLES 733THREAD_STATISTICS 734TRIGGERS 735select table_name from tables where table_name='user'; 736table_name 737user 738select column_name, privileges from columns 739where table_name='user' and column_name like '%o%'; 740column_name privileges 741Host select,insert,update,references 742Drop_priv select,insert,update,references 743Reload_priv select,insert,update,references 744Shutdown_priv select,insert,update,references 745Process_priv select,insert,update,references 746Show_db_priv select,insert,update,references 747Lock_tables_priv select,insert,update,references 748Show_view_priv select,insert,update,references 749Create_routine_priv select,insert,update,references 750Alter_routine_priv select,insert,update,references 751max_questions select,insert,update,references 752max_connections select,insert,update,references 753max_user_connections select,insert,update,references 754authentication_string select,insert,update,references 755password_expired select,insert,update,references 756password_last_changed select,insert,update,references 757password_lifetime select,insert,update,references 758account_locked select,insert,update,references 759use test; 760create function sub1(i int) returns int 761return i+1; 762create table t1(f1 int); 763create view v2 (c) as select f1 from t1; 764create view v3 (c) as select sub1(1); 765create table t4(f1 int, KEY f1_key (f1)); 766drop table t1; 767drop function sub1; 768select table_name from information_schema.views 769where table_schema='test'; 770table_name 771v2 772v3 773select table_name from information_schema.views 774where table_schema='test'; 775table_name 776v2 777v3 778select column_name from information_schema.columns 779where table_schema='test'; 780column_name 781f1 782Warnings: 783Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 784Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 785select index_name from information_schema.statistics where table_schema='test'; 786index_name 787f1_key 788select constraint_name from information_schema.table_constraints 789where table_schema='test'; 790constraint_name 791show create view v2; 792View Create View character_set_client collation_connection 793v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`f1` AS `c` from `t1` latin1 latin1_swedish_ci 794Warnings: 795Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 796show create table v3; 797View Create View character_set_client collation_connection 798v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `sub1`(1) AS `c` latin1 latin1_swedish_ci 799Warnings: 800Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 801drop view v2; 802drop view v3; 803drop table t4; 804select * from information_schema.table_names; 805ERROR 42S02: Unknown table 'table_names' in information_schema 806select column_type from information_schema.columns 807where table_schema="information_schema" and table_name="COLUMNS" and 808(column_name="character_set_name" or column_name="collation_name"); 809column_type 810varchar(32) 811varchar(32) 812select TABLE_ROWS from information_schema.tables where 813table_schema="information_schema" and table_name="COLUMNS"; 814TABLE_ROWS 815NULL 816select table_type from information_schema.tables 817where table_schema="mysql" and table_name="user"; 818table_type 819BASE TABLE 820show open tables where `table` like "user"; 821Database Table In_use Name_locked 822mysql user 0 0 823show status where variable_name like "%database%"; 824Variable_name Value 825Com_show_databases 3 826show variables where variable_name like "skip_show_databas"; 827Variable_name Value 828show global status like "Threads_running"; 829Variable_name Value 830Threads_running # 831create table t1(f1 int); 832create table t2(f2 int); 833create view v1 as select * from t1, t2; 834set @got_val= (select count(*) from information_schema.columns); 835drop view v1; 836drop table t1, t2; 837use test; 838CREATE TABLE t_crashme ( f1 BIGINT); 839CREATE VIEW a1 (t_CRASHME) AS SELECT f1 FROM t_crashme GROUP BY f1; 840CREATE VIEW a2 AS SELECT t_CRASHME FROM a1; 841count(*) 84268 843drop view a2, a1; 844drop table t_crashme; 845select table_schema, table_name, column_name from information_schema.columns 846where table_schema not in ('performance_schema', 'sys') 847and data_type = 'longtext'; 848table_schema table_name column_name 849information_schema COLUMNS COLUMN_DEFAULT 850information_schema COLUMNS COLUMN_TYPE 851information_schema COLUMNS GENERATION_EXPRESSION 852information_schema EVENTS EVENT_DEFINITION 853information_schema OPTIMIZER_TRACE QUERY 854information_schema OPTIMIZER_TRACE TRACE 855information_schema PARAMETERS DTD_IDENTIFIER 856information_schema PARTITIONS PARTITION_EXPRESSION 857information_schema PARTITIONS SUBPARTITION_EXPRESSION 858information_schema PARTITIONS PARTITION_DESCRIPTION 859information_schema PLUGINS PLUGIN_DESCRIPTION 860information_schema PROCESSLIST INFO 861information_schema ROUTINES DTD_IDENTIFIER 862information_schema ROUTINES ROUTINE_DEFINITION 863information_schema ROUTINES ROUTINE_COMMENT 864information_schema TRIGGERS ACTION_CONDITION 865information_schema TRIGGERS ACTION_STATEMENT 866information_schema VIEWS VIEW_DEFINITION 867select table_name, column_name, data_type from information_schema.columns 868where table_schema not in ('performance_schema', 'sys') 869and data_type = 'datetime' 870 and table_name not like 'innodb_%'; 871table_name column_name data_type 872EVENTS EXECUTE_AT datetime 873EVENTS STARTS datetime 874EVENTS ENDS datetime 875EVENTS CREATED datetime 876EVENTS LAST_ALTERED datetime 877EVENTS LAST_EXECUTED datetime 878FILES CREATION_TIME datetime 879FILES LAST_UPDATE_TIME datetime 880FILES LAST_ACCESS_TIME datetime 881FILES CREATE_TIME datetime 882FILES UPDATE_TIME datetime 883FILES CHECK_TIME datetime 884GLOBAL_TEMPORARY_TABLES CREATE_TIME datetime 885GLOBAL_TEMPORARY_TABLES UPDATE_TIME datetime 886PARTITIONS CREATE_TIME datetime 887PARTITIONS UPDATE_TIME datetime 888PARTITIONS CHECK_TIME datetime 889ROUTINES CREATED datetime 890ROUTINES LAST_ALTERED datetime 891TABLES CREATE_TIME datetime 892TABLES UPDATE_TIME datetime 893TABLES CHECK_TIME datetime 894TEMPORARY_TABLES CREATE_TIME datetime 895TEMPORARY_TABLES UPDATE_TIME datetime 896TRIGGERS CREATED datetime 897event execute_at datetime 898event last_executed datetime 899event starts datetime 900event ends datetime 901SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A 902WHERE NOT EXISTS 903(SELECT * FROM INFORMATION_SCHEMA.COLUMNS B 904WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA 905AND A.TABLE_NAME = B.TABLE_NAME); 906COUNT(*) 9070 908create table t1 909( x_bigint BIGINT, 910x_integer INTEGER, 911x_smallint SMALLINT, 912x_decimal DECIMAL(5,3), 913x_numeric NUMERIC(5,3), 914x_real REAL, 915x_float FLOAT, 916x_double_precision DOUBLE PRECISION ); 917SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH 918FROM INFORMATION_SCHEMA.COLUMNS 919WHERE TABLE_NAME= 't1'; 920COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH 921x_bigint NULL NULL 922x_integer NULL NULL 923x_smallint NULL NULL 924x_decimal NULL NULL 925x_numeric NULL NULL 926x_real NULL NULL 927x_float NULL NULL 928x_double_precision NULL NULL 929drop table t1; 930grant select on test.* to mysqltest_4@localhost; 931Warnings: 932Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 933SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS 934where COLUMN_NAME='TABLE_NAME' ORDER BY TABLE_NAME; 935TABLE_NAME COLUMN_NAME PRIVILEGES 936COLUMNS TABLE_NAME select 937COLUMN_PRIVILEGES TABLE_NAME select 938FILES TABLE_NAME select 939GLOBAL_TEMPORARY_TABLES TABLE_NAME select 940INDEX_STATISTICS TABLE_NAME select 941INNODB_BUFFER_PAGE TABLE_NAME select 942INNODB_BUFFER_PAGE_LRU TABLE_NAME select 943INNODB_CMP_PER_INDEX table_name select 944INNODB_CMP_PER_INDEX_RESET table_name select 945KEY_COLUMN_USAGE TABLE_NAME select 946PARTITIONS TABLE_NAME select 947REFERENTIAL_CONSTRAINTS TABLE_NAME select 948STATISTICS TABLE_NAME select 949TABLES TABLE_NAME select 950TABLE_CONSTRAINTS TABLE_NAME select 951TABLE_PRIVILEGES TABLE_NAME select 952TABLE_STATISTICS TABLE_NAME select 953TEMPORARY_TABLES TABLE_NAME select 954VIEWS TABLE_NAME select 955delete from mysql.user where user='mysqltest_4'; 956delete from mysql.db where user='mysqltest_4'; 957flush privileges; 958SELECT table_schema, count(*) FROM information_schema.TABLES WHERE 959table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') 960AND table_name not like 'ndb%' AND table_name not like 'innodb_%' 961GROUP BY TABLE_SCHEMA; 962table_schema count(*) 963information_schema 43 964mysql 28 965create table t1 (i int, j int); 966create trigger trg1 before insert on t1 for each row 967begin 968if new.j > 10 then 969set new.j := 10; 970end if; 971end| 972create trigger trg2 before update on t1 for each row 973begin 974if old.i % 2 = 0 then 975set new.j := -1; 976end if; 977end| 978create trigger trg3 after update on t1 for each row 979begin 980if new.j = -1 then 981set @fired:= "Yes"; 982end if; 983end| 984show triggers; 985Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation 986trg1 INSERT t1 begin 987if new.j > 10 then 988set new.j := 10; 989end if; 990end BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci 991trg2 UPDATE t1 begin 992if old.i % 2 = 0 then 993set new.j := -1; 994end if; 995end BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci 996trg3 UPDATE t1 begin 997if new.j = -1 then 998set @fired:= "Yes"; 999end if; 1000end AFTER # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci 1001select * from information_schema.triggers where trigger_schema in ('mysql', 'information_schema', 'test', 'mysqltest'); 1002TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION 1003def test trg1 INSERT def test t1 1 NULL begin 1004if new.j > 10 then 1005set new.j := 10; 1006end if; 1007end ROW BEFORE NULL NULL OLD NEW # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci 1008def test trg2 UPDATE def test t1 1 NULL begin 1009if old.i % 2 = 0 then 1010set new.j := -1; 1011end if; 1012end ROW BEFORE NULL NULL OLD NEW # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci 1013def test trg3 UPDATE def test t1 1 NULL begin 1014if new.j = -1 then 1015set @fired:= "Yes"; 1016end if; 1017end ROW AFTER NULL NULL OLD NEW # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci 1018drop trigger trg1; 1019drop trigger trg2; 1020drop trigger trg3; 1021drop table t1; 1022create database mysqltest; 1023create table mysqltest.t1 (f1 int, f2 int); 1024create table mysqltest.t2 (f1 int); 1025grant select (f1) on mysqltest.t1 to user1@localhost; 1026Warnings: 1027Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 1028grant select on mysqltest.t2 to user2@localhost; 1029Warnings: 1030Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 1031grant select on mysqltest.* to user3@localhost; 1032Warnings: 1033Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 1034grant select on *.* to user4@localhost; 1035Warnings: 1036Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 1037select * from information_schema.column_privileges order by grantee; 1038GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 1039'user1'@'localhost' def mysqltest t1 f1 SELECT NO 1040select * from information_schema.table_privileges order by grantee; 1041GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 1042select * from information_schema.schema_privileges order by grantee; 1043GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE 1044select * from information_schema.user_privileges order by grantee; 1045GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 1046'user1'@'localhost' def USAGE NO 1047show grants; 1048Grants for user1@localhost 1049GRANT USAGE ON *.* TO 'user1'@'localhost' 1050GRANT SELECT (f1) ON `mysqltest`.`t1` TO 'user1'@'localhost' 1051select * from information_schema.column_privileges order by grantee; 1052GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 1053select * from information_schema.table_privileges order by grantee; 1054GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 1055'user2'@'localhost' def mysqltest t2 SELECT NO 1056select * from information_schema.schema_privileges order by grantee; 1057GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE 1058select * from information_schema.user_privileges order by grantee; 1059GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 1060'user2'@'localhost' def USAGE NO 1061show grants; 1062Grants for user2@localhost 1063GRANT USAGE ON *.* TO 'user2'@'localhost' 1064GRANT SELECT ON `mysqltest`.`t2` TO 'user2'@'localhost' 1065select * from information_schema.column_privileges order by grantee; 1066GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 1067select * from information_schema.table_privileges order by grantee; 1068GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 1069select * from information_schema.schema_privileges order by grantee; 1070GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE 1071'user3'@'localhost' def mysqltest SELECT NO 1072select * from information_schema.user_privileges order by grantee; 1073GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 1074'user3'@'localhost' def USAGE NO 1075show grants; 1076Grants for user3@localhost 1077GRANT USAGE ON *.* TO 'user3'@'localhost' 1078GRANT SELECT ON `mysqltest`.* TO 'user3'@'localhost' 1079select * from information_schema.column_privileges where grantee like '%user%' 1080order by grantee; 1081GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 1082'user1'@'localhost' def mysqltest t1 f1 SELECT NO 1083select * from information_schema.table_privileges where grantee like '%user%' 1084and table_schema !='mysql' order by grantee; 1085GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 1086'user2'@'localhost' def mysqltest t2 SELECT NO 1087select * from information_schema.schema_privileges where grantee like '%user%' 1088and table_schema !='performance_schema' order by grantee; 1089GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE 1090'user3'@'localhost' def mysqltest SELECT NO 1091select * from information_schema.user_privileges where grantee like '%user%' and grantee not like '%session%' 1092order by grantee; 1093GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 1094'user1'@'localhost' def USAGE NO 1095'user2'@'localhost' def USAGE NO 1096'user3'@'localhost' def USAGE NO 1097'user4'@'localhost' def SELECT NO 1098show grants; 1099Grants for user4@localhost 1100GRANT SELECT ON *.* TO 'user4'@'localhost' 1101drop user user1@localhost, user2@localhost, user3@localhost, user4@localhost; 1102use test; 1103drop database mysqltest; 1104drop procedure if exists p1; 1105drop procedure if exists p2; 1106create procedure p1 () modifies sql data set @a = 5; 1107create procedure p2 () set @a = 5; 1108select sql_data_access from information_schema.routines 1109where specific_name like 'p%' and ROUTINE_SCHEMA != 'sys'; 1110sql_data_access 1111MODIFIES SQL DATA 1112CONTAINS SQL 1113drop procedure p1; 1114drop procedure p2; 1115show create database information_schema; 1116Database Create Database 1117information_schema CREATE DATABASE `information_schema` /*!40100 DEFAULT CHARACTER SET utf8 */ 1118create table t1(f1 LONGBLOB, f2 LONGTEXT); 1119select column_name,data_type,CHARACTER_OCTET_LENGTH, 1120CHARACTER_MAXIMUM_LENGTH 1121from information_schema.columns 1122where table_name='t1'; 1123column_name data_type CHARACTER_OCTET_LENGTH CHARACTER_MAXIMUM_LENGTH 1124f1 longblob 4294967295 4294967295 1125f2 longtext 4294967295 4294967295 1126drop table t1; 1127create table t1(f1 tinyint, f2 SMALLINT, f3 mediumint, f4 int, 1128f5 BIGINT, f6 BIT, f7 bit(64)); 1129select column_name, NUMERIC_PRECISION, NUMERIC_SCALE 1130from information_schema.columns 1131where table_name='t1'; 1132column_name NUMERIC_PRECISION NUMERIC_SCALE 1133f1 3 0 1134f2 5 0 1135f3 7 0 1136f4 10 0 1137f5 19 0 1138f6 1 NULL 1139f7 64 NULL 1140drop table t1; 1141create table t1 (f1 integer); 1142create trigger tr1 after insert on t1 for each row set @test_var=42; 1143use information_schema; 1144select trigger_schema, trigger_name from triggers where 1145trigger_name='tr1'; 1146trigger_schema trigger_name 1147test tr1 1148use test; 1149drop table t1; 1150create table t1 (a int not null, b int); 1151use information_schema; 1152select column_name, column_default from columns 1153where table_schema='test' and table_name='t1'; 1154column_name column_default 1155a NULL 1156b NULL 1157use test; 1158show columns from t1; 1159Field Type Null Key Default Extra 1160a int(11) NO NULL 1161b int(11) YES NULL 1162drop table t1; 1163CREATE TABLE t1 (a int); 1164CREATE TABLE t2 (b int); 1165SHOW TABLE STATUS FROM test 1166WHERE name IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 1167WHERE TABLE_SCHEMA='test' AND TABLE_TYPE='BASE TABLE'); 1168Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment 1169t1 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL 1170t2 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL 1171DROP TABLE t1,t2; 1172create table t1(f1 int); 1173create view v1 (c) as select f1 from t1; 1174select database(); 1175database() 1176NULL 1177show fields from test.v1; 1178Field Type Null Key Default Extra 1179c int(11) YES NULL 1180drop view v1; 1181drop table t1; 1182alter database information_schema; 1183ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 1184drop database information_schema; 1185ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1186drop table information_schema.tables; 1187ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1188alter table information_schema.tables; 1189ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1190use information_schema; 1191create temporary table schemata(f1 char(10)); 1192ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1193CREATE PROCEDURE p1 () 1194BEGIN 1195SELECT 'foo' FROM DUAL; 1196END | 1197ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1198select ROUTINE_NAME from routines where ROUTINE_SCHEMA='information_schema'; 1199ROUTINE_NAME 1200grant all on information_schema.* to 'user1'@'localhost'; 1201ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1202grant select on information_schema.* to 'user1'@'localhost'; 1203ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1204use test; 1205create table t1(id int); 1206insert into t1(id) values (1); 1207select 1 from (select 1 from test.t1) a; 12081 12091 1210use information_schema; 1211select 1 from (select 1 from test.t1) a; 12121 12131 1214use test; 1215drop table t1; 1216create table t1 (f1 int(11)); 1217create view v1 as select * from t1; 1218drop table t1; 1219select table_type from information_schema.tables 1220where table_name="v1"; 1221table_type 1222VIEW 1223drop view v1; 1224create temporary table t1(f1 int, index(f1)); 1225show columns from t1; 1226Field Type Null Key Default Extra 1227f1 int(11) YES MUL NULL 1228describe t1; 1229Field Type Null Key Default Extra 1230f1 int(11) YES MUL NULL 1231show indexes from t1; 1232Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 1233t1 1 f1 1 f1 A NULL NULL NULL YES BTREE 1234drop table t1; 1235create table t1(f1 binary(32), f2 varbinary(64)); 1236select character_maximum_length, character_octet_length 1237from information_schema.columns where table_name='t1'; 1238character_maximum_length character_octet_length 123932 32 124064 64 1241drop table t1; 1242CREATE TABLE t1 (f1 BIGINT, f2 VARCHAR(20), f3 BIGINT); 1243INSERT INTO t1 SET f1 = 1, f2 = 'Schoenenbourg', f3 = 1; 1244CREATE FUNCTION func2() RETURNS BIGINT RETURN 1; 1245CREATE FUNCTION func1() RETURNS BIGINT 1246BEGIN 1247RETURN ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA != 'sys'); 1248END// 1249CREATE VIEW v1 AS SELECT 1 FROM t1 1250WHERE f3 = (SELECT func2 ()); 1251SELECT func1(); 1252func1() 12531 1254DROP TABLE t1; 1255DROP VIEW v1; 1256DROP FUNCTION func1; 1257DROP FUNCTION func2; 1258SELECT column_type, GROUP_CONCAT(table_schema, '.', table_name), COUNT(*) AS num 1259FROM information_schema.columns WHERE 1260table_schema='information_schema' AND 1261(column_type = 'varchar(7)' OR column_type = 'varchar(20)' 1262 OR column_type = 'varchar(30)') 1263GROUP BY column_type ORDER BY num; 1264column_type GROUP_CONCAT(table_schema, '.', table_name) num 1265varchar(7) information_schema.ROUTINES,information_schema.VIEWS 2 1266varchar(30) information_schema.COLUMNS,information_schema.PROFILING,information_schema.PROFILING 3 1267varchar(20) information_schema.FILES,information_schema.FILES,information_schema.FILES,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PROFILING,information_schema.TABLES 8 1268create table t1(f1 char(1) not null, f2 char(9) not null) 1269default character set utf8; 1270select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from 1271information_schema.columns where table_schema='test' and table_name = 't1'; 1272CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH 12731 3 12749 27 1275drop table t1; 1276use mysql; 1277INSERT INTO `proc` VALUES ('test','','PROCEDURE','','SQL','CONTAINS_SQL', 1278'NO','DEFINER','','','BEGIN\r\n \r\nEND','root@%','2006-03-02 18:40:03', 1279'2006-03-02 18:40:03','','','utf8','utf8_general_ci','utf8_general_ci','n/a'); 1280select routine_name from information_schema.routines where ROUTINE_SCHEMA='test'; 1281routine_name 1282 1283delete from proc where name=''; 1284use test; 1285grant select on test.* to mysqltest_1@localhost; 1286Warnings: 1287Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 1288create table t1 (id int); 1289create view v1 as select * from t1; 1290create definer = mysqltest_1@localhost 1291sql security definer view v2 as select 1; 1292select * from information_schema.views 1293where table_name='v1' or table_name='v2'; 1294TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION 1295def test v1 NONE YES root@localhost DEFINER latin1 latin1_swedish_ci 1296def test v2 select 1 AS `1` NONE NO mysqltest_1@localhost DEFINER latin1 latin1_swedish_ci 1297drop view v1, v2; 1298drop table t1; 1299drop user mysqltest_1@localhost; 1300set @a:= '.'; 1301create table t1(f1 char(5)); 1302create table t2(f1 char(5)); 1303select concat(@a, table_name), @a, table_name 1304from information_schema.tables where table_schema = 'test'; 1305concat(@a, table_name) @a table_name 1306.t1 . t1 1307.t2 . t2 1308drop table t1,t2; 1309DROP PROCEDURE IF EXISTS p1; 1310DROP FUNCTION IF EXISTS f1; 1311CREATE PROCEDURE p1() SET @a= 1; 1312CREATE FUNCTION f1() RETURNS INT RETURN @a + 1; 1313CREATE USER mysql_bug20230@localhost; 1314GRANT EXECUTE ON PROCEDURE p1 TO mysql_bug20230@localhost; 1315GRANT EXECUTE ON FUNCTION f1 TO mysql_bug20230@localhost; 1316SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test'; 1317ROUTINE_NAME ROUTINE_DEFINITION 1318f1 RETURN @a + 1 1319p1 SET @a= 1 1320SHOW CREATE PROCEDURE p1; 1321Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 1322p1 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() 1323SET @a= 1 latin1 latin1_swedish_ci latin1_swedish_ci 1324SHOW CREATE FUNCTION f1; 1325Function sql_mode Create Function character_set_client collation_connection Database Collation 1326f1 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) 1327RETURN @a + 1 latin1 latin1_swedish_ci latin1_swedish_ci 1328SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test'; 1329ROUTINE_NAME ROUTINE_DEFINITION 1330f1 NULL 1331p1 NULL 1332SHOW CREATE PROCEDURE p1; 1333Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 1334p1 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION NULL latin1 latin1_swedish_ci latin1_swedish_ci 1335SHOW CREATE FUNCTION f1; 1336Function sql_mode Create Function character_set_client collation_connection Database Collation 1337f1 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION NULL latin1 latin1_swedish_ci latin1_swedish_ci 1338CALL p1(); 1339SELECT f1(); 1340f1() 13412 1342DROP FUNCTION f1; 1343DROP PROCEDURE p1; 1344DROP USER mysql_bug20230@localhost; 1345SELECT MAX(table_name) FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test'); 1346MAX(table_name) 1347XTRADB_ZIP_DICT_COLS 1348SELECT table_name from information_schema.tables 1349WHERE table_name=(SELECT MAX(table_name) 1350FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test')); 1351table_name 1352XTRADB_ZIP_DICT_COLS 1353DROP TABLE IF EXISTS bug23037; 1354DROP FUNCTION IF EXISTS get_value; 1355SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037'; 1356COLUMN_NAME MD5(COLUMN_DEFAULT) LENGTH(COLUMN_DEFAULT) 1357fld1 7cf7a6782be951a1f2464a350da926a5 65532 1358SELECT MD5(get_value()); 1359MD5(get_value()) 13607cf7a6782be951a1f2464a350da926a5 1361SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT), COLUMN_DEFAULT=get_value() FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037'; 1362COLUMN_NAME MD5(COLUMN_DEFAULT) LENGTH(COLUMN_DEFAULT) COLUMN_DEFAULT=get_value() 1363fld1 7cf7a6782be951a1f2464a350da926a5 65532 1 1364DROP TABLE bug23037; 1365DROP FUNCTION get_value; 1366create view v1 as 1367select table_schema as object_schema, 1368table_name as object_name, 1369table_type as object_type 1370from information_schema.tables 1371order by object_schema; 1372explain select * from v1; 1373id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13741 SIMPLE tables NULL ALL NULL NULL NULL NULL NULL NULL Open_frm_only; Scanned all databases; Using filesort 1375Warnings: 1376Note 1003 /* select#1 */ select `information_schema`.`tables`.`TABLE_SCHEMA` AS `object_schema`,`information_schema`.`tables`.`TABLE_NAME` AS `object_name`,`information_schema`.`tables`.`TABLE_TYPE` AS `object_type` from `information_schema`.`tables` order by `information_schema`.`tables`.`TABLE_SCHEMA` 1377explain select * from (select table_name from information_schema.tables) as a; 1378id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13791 SIMPLE tables NULL ALL NULL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases 1380Warnings: 1381Note 1003 /* select#1 */ select `information_schema`.`tables`.`TABLE_NAME` AS `table_name` from `information_schema`.`tables` 1382drop view v1; 1383create table t1 (f1 int(11)); 1384create table t2 (f1 int(11), f2 int(11)); 1385select table_name from information_schema.tables 1386where table_schema = 'test' and table_name not in 1387(select table_name from information_schema.columns 1388where table_schema = 'test' and column_name = 'f3'); 1389table_name 1390t1 1391t2 1392drop table t1,t2; 1393select 1 as f1 from information_schema.tables where "CHARACTER_SETS"= 1394(select cast(table_name as char) from information_schema.tables 1395where table_schema != 'performance_schema' 1396 order by table_name limit 1) limit 1; 1397f1 13981 1399select t.table_name, group_concat(t.table_schema, '.', t.table_name), 1400count(*) as num1 1401from information_schema.tables t 1402inner join information_schema.columns c1 1403on t.table_schema = c1.table_schema AND t.table_name = c1.table_name 1404where t.table_name not like 'ndb%' and 1405t.table_schema = 'information_schema' and 1406c1.ordinal_position = 1407(select isnull(c2.column_type) - 1408isnull(group_concat(c2.table_schema, '.', c2.table_name)) + 1409count(*) as num 1410from information_schema.columns c2 where 1411c2.table_schema='information_schema' and 1412(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)') 1413group by c2.column_type order by num limit 1) 1414and t.table_name not like 'innodb_%' 1415group by t.table_name order by num1, t.table_name; 1416table_name group_concat(t.table_schema, '.', t.table_name) num1 1417CHARACTER_SETS information_schema.CHARACTER_SETS 1 1418CLIENT_STATISTICS information_schema.CLIENT_STATISTICS 1 1419COLLATIONS information_schema.COLLATIONS 1 1420COLLATION_CHARACTER_SET_APPLICABILITY information_schema.COLLATION_CHARACTER_SET_APPLICABILITY 1 1421COLUMNS information_schema.COLUMNS 1 1422COLUMN_PRIVILEGES information_schema.COLUMN_PRIVILEGES 1 1423ENGINES information_schema.ENGINES 1 1424EVENTS information_schema.EVENTS 1 1425FILES information_schema.FILES 1 1426GLOBAL_STATUS information_schema.GLOBAL_STATUS 1 1427GLOBAL_TEMPORARY_TABLES information_schema.GLOBAL_TEMPORARY_TABLES 1 1428GLOBAL_VARIABLES information_schema.GLOBAL_VARIABLES 1 1429INDEX_STATISTICS information_schema.INDEX_STATISTICS 1 1430KEY_COLUMN_USAGE information_schema.KEY_COLUMN_USAGE 1 1431OPTIMIZER_TRACE information_schema.OPTIMIZER_TRACE 1 1432PARAMETERS information_schema.PARAMETERS 1 1433PARTITIONS information_schema.PARTITIONS 1 1434PLUGINS information_schema.PLUGINS 1 1435PROCESSLIST information_schema.PROCESSLIST 1 1436PROFILING information_schema.PROFILING 1 1437REFERENTIAL_CONSTRAINTS information_schema.REFERENTIAL_CONSTRAINTS 1 1438ROUTINES information_schema.ROUTINES 1 1439SCHEMATA information_schema.SCHEMATA 1 1440SCHEMA_PRIVILEGES information_schema.SCHEMA_PRIVILEGES 1 1441SESSION_STATUS information_schema.SESSION_STATUS 1 1442SESSION_VARIABLES information_schema.SESSION_VARIABLES 1 1443STATISTICS information_schema.STATISTICS 1 1444TABLES information_schema.TABLES 1 1445TABLESPACES information_schema.TABLESPACES 1 1446TABLE_CONSTRAINTS information_schema.TABLE_CONSTRAINTS 1 1447TABLE_PRIVILEGES information_schema.TABLE_PRIVILEGES 1 1448TABLE_STATISTICS information_schema.TABLE_STATISTICS 1 1449TEMPORARY_TABLES information_schema.TEMPORARY_TABLES 1 1450THREAD_STATISTICS information_schema.THREAD_STATISTICS 1 1451TRIGGERS information_schema.TRIGGERS 1 1452USER_PRIVILEGES information_schema.USER_PRIVILEGES 1 1453USER_STATISTICS information_schema.USER_STATISTICS 1 1454VIEWS information_schema.VIEWS 1 1455XTRADB_INTERNAL_HASH_TABLES information_schema.XTRADB_INTERNAL_HASH_TABLES 1 1456XTRADB_READ_VIEW information_schema.XTRADB_READ_VIEW 1 1457XTRADB_RSEG information_schema.XTRADB_RSEG 1 1458XTRADB_ZIP_DICT information_schema.XTRADB_ZIP_DICT 1 1459XTRADB_ZIP_DICT_COLS information_schema.XTRADB_ZIP_DICT_COLS 1 1460create table t1(f1 int); 1461create view v1 as select f1+1 as a from t1; 1462create table t2 (f1 int, f2 int); 1463create view v2 as select f1+1 as a, f2 as b from t2; 1464select table_name, is_updatable from information_schema.views where table_schema != 'sys'; 1465table_name is_updatable 1466v1 NO 1467v2 YES 1468delete from v1; 1469drop view v1,v2; 1470drop table t1,t2; 1471alter database; 1472ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 1473alter database test; 1474ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 1475create database mysqltest; 1476create table mysqltest.t1(a int, b int, c int); 1477create trigger mysqltest.t1_ai after insert on mysqltest.t1 1478for each row set @a = new.a + new.b + new.c; 1479grant select(b) on mysqltest.t1 to mysqltest_1@localhost; 1480Warnings: 1481Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 1482select trigger_name from information_schema.triggers 1483where event_object_table='t1'; 1484trigger_name 1485t1_ai 1486show triggers from mysqltest; 1487Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation 1488t1_ai INSERT t1 set @a = new.a + new.b + new.c AFTER # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci 1489show columns from t1; 1490Field Type Null Key Default Extra 1491b int(11) YES NULL 1492select column_name from information_schema.columns where table_name='t1'; 1493column_name 1494b 1495show triggers; 1496Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation 1497select trigger_name from information_schema.triggers 1498where event_object_table='t1'; 1499trigger_name 1500drop user mysqltest_1@localhost; 1501drop database mysqltest; 1502create table t1 ( 1503f1 varchar(50), 1504f2 varchar(50) not null, 1505f3 varchar(50) default '', 1506f4 varchar(50) default NULL, 1507f5 bigint not null, 1508f6 bigint not null default 10, 1509f7 datetime not null, 1510f8 datetime default '2006-01-01' 1511); 1512select column_default from information_schema.columns where table_name= 't1'; 1513column_default 1514NULL 1515NULL 1516 1517NULL 1518NULL 151910 1520NULL 15212006-01-01 00:00:00 1522show columns from t1; 1523Field Type Null Key Default Extra 1524f1 varchar(50) YES NULL 1525f2 varchar(50) NO NULL 1526f3 varchar(50) YES 1527f4 varchar(50) YES NULL 1528f5 bigint(20) NO NULL 1529f6 bigint(20) NO 10 1530f7 datetime NO NULL 1531f8 datetime YES 2006-01-01 00:00:00 1532drop table t1; 1533show fields from information_schema.table_names; 1534ERROR 42S02: Unknown table 'table_names' in information_schema 1535show keys from information_schema.table_names; 1536ERROR 42S02: Unknown table 'table_names' in information_schema 1537USE information_schema; 1538SET max_heap_table_size = 16384; 1539CREATE TABLE test.t1( a INT ); 1540SELECT * 1541FROM tables ta 1542JOIN collations co ON ( co.collation_name = ta.table_catalog ) 1543JOIN character_sets cs ON ( cs.character_set_name = ta.table_catalog ); 1544TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT COLLATION_NAME CHARACTER_SET_NAME ID IS_DEFAULT IS_COMPILED SORTLEN CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN 1545DROP TABLE test.t1; 1546SET max_heap_table_size = DEFAULT; 1547USE test; 1548End of 5.0 tests. 1549select * from information_schema.engines WHERE ENGINE="MyISAM"; 1550ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS 1551MyISAM DEFAULT MyISAM storage engine NO NO NO 1552grant select on *.* to user3148@localhost; 1553Warnings: 1554Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 1555select user,db from information_schema.processlist; 1556user db 1557user3148 test 1558drop user user3148@localhost; 1559DROP TABLE IF EXISTS server_status; 1560DROP EVENT IF EXISTS event_status; 1561SET GLOBAL event_scheduler=1; 1562CREATE EVENT event_status 1563ON SCHEDULE AT NOW() 1564ON COMPLETION NOT PRESERVE 1565DO 1566BEGIN 1567CREATE TABLE server_status 1568SELECT variable_name 1569FROM information_schema.global_status 1570WHERE variable_name LIKE 'ABORTED_CONNECTS' OR 1571variable_name LIKE 'BINLOG_CACHE_DISK_USE'; 1572END$$ 1573SELECT variable_name FROM server_status; 1574variable_name 1575ABORTED_CONNECTS 1576BINLOG_CACHE_DISK_USE 1577DROP TABLE server_status; 1578SET GLOBAL event_scheduler=0; 1579explain select table_name from information_schema.views where 1580table_schema='test' and table_name='v1'; 1581id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15821 SIMPLE views NULL ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL NULL Using where; Open_frm_only; Scanned 0 databases 1583Warnings: 1584Note 1003 /* select#1 */ select `information_schema`.`views`.`TABLE_NAME` AS `table_name` from `information_schema`.`views` where ((`information_schema`.`views`.`TABLE_SCHEMA` = 'test') and (`information_schema`.`views`.`TABLE_NAME` = 'v1')) 1585explain select * from information_schema.tables; 1586id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15871 SIMPLE tables NULL ALL NULL NULL NULL NULL NULL NULL Open_full_table; Scanned all databases 1588Warnings: 1589Note 1003 /* select#1 */ select `information_schema`.`tables`.`TABLE_CATALOG` AS `TABLE_CATALOG`,`information_schema`.`tables`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`tables`.`TABLE_NAME` AS `TABLE_NAME`,`information_schema`.`tables`.`TABLE_TYPE` AS `TABLE_TYPE`,`information_schema`.`tables`.`ENGINE` AS `ENGINE`,`information_schema`.`tables`.`VERSION` AS `VERSION`,`information_schema`.`tables`.`ROW_FORMAT` AS `ROW_FORMAT`,`information_schema`.`tables`.`TABLE_ROWS` AS `TABLE_ROWS`,`information_schema`.`tables`.`AVG_ROW_LENGTH` AS `AVG_ROW_LENGTH`,`information_schema`.`tables`.`DATA_LENGTH` AS `DATA_LENGTH`,`information_schema`.`tables`.`MAX_DATA_LENGTH` AS `MAX_DATA_LENGTH`,`information_schema`.`tables`.`INDEX_LENGTH` AS `INDEX_LENGTH`,`information_schema`.`tables`.`DATA_FREE` AS `DATA_FREE`,`information_schema`.`tables`.`AUTO_INCREMENT` AS `AUTO_INCREMENT`,`information_schema`.`tables`.`CREATE_TIME` AS `CREATE_TIME`,`information_schema`.`tables`.`UPDATE_TIME` AS `UPDATE_TIME`,`information_schema`.`tables`.`CHECK_TIME` AS `CHECK_TIME`,`information_schema`.`tables`.`TABLE_COLLATION` AS `TABLE_COLLATION`,`information_schema`.`tables`.`CHECKSUM` AS `CHECKSUM`,`information_schema`.`tables`.`CREATE_OPTIONS` AS `CREATE_OPTIONS`,`information_schema`.`tables`.`TABLE_COMMENT` AS `TABLE_COMMENT` from `information_schema`.`tables` 1590explain select * from information_schema.collations; 1591id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15921 SIMPLE collations NULL ALL NULL NULL NULL NULL NULL NULL NULL 1593Warnings: 1594Note 1003 /* select#1 */ select `information_schema`.`collations`.`COLLATION_NAME` AS `COLLATION_NAME`,`information_schema`.`collations`.`CHARACTER_SET_NAME` AS `CHARACTER_SET_NAME`,`information_schema`.`collations`.`ID` AS `ID`,`information_schema`.`collations`.`IS_DEFAULT` AS `IS_DEFAULT`,`information_schema`.`collations`.`IS_COMPILED` AS `IS_COMPILED`,`information_schema`.`collations`.`SORTLEN` AS `SORTLEN` from `information_schema`.`collations` 1595explain select * from information_schema.tables where 1596table_schema='test' and table_name= 't1'; 1597id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15981 SIMPLE tables NULL ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL NULL Using where; Open_full_table; Scanned 0 databases 1599Warnings: 1600Note 1003 /* select#1 */ select `information_schema`.`tables`.`TABLE_CATALOG` AS `TABLE_CATALOG`,`information_schema`.`tables`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`tables`.`TABLE_NAME` AS `TABLE_NAME`,`information_schema`.`tables`.`TABLE_TYPE` AS `TABLE_TYPE`,`information_schema`.`tables`.`ENGINE` AS `ENGINE`,`information_schema`.`tables`.`VERSION` AS `VERSION`,`information_schema`.`tables`.`ROW_FORMAT` AS `ROW_FORMAT`,`information_schema`.`tables`.`TABLE_ROWS` AS `TABLE_ROWS`,`information_schema`.`tables`.`AVG_ROW_LENGTH` AS `AVG_ROW_LENGTH`,`information_schema`.`tables`.`DATA_LENGTH` AS `DATA_LENGTH`,`information_schema`.`tables`.`MAX_DATA_LENGTH` AS `MAX_DATA_LENGTH`,`information_schema`.`tables`.`INDEX_LENGTH` AS `INDEX_LENGTH`,`information_schema`.`tables`.`DATA_FREE` AS `DATA_FREE`,`information_schema`.`tables`.`AUTO_INCREMENT` AS `AUTO_INCREMENT`,`information_schema`.`tables`.`CREATE_TIME` AS `CREATE_TIME`,`information_schema`.`tables`.`UPDATE_TIME` AS `UPDATE_TIME`,`information_schema`.`tables`.`CHECK_TIME` AS `CHECK_TIME`,`information_schema`.`tables`.`TABLE_COLLATION` AS `TABLE_COLLATION`,`information_schema`.`tables`.`CHECKSUM` AS `CHECKSUM`,`information_schema`.`tables`.`CREATE_OPTIONS` AS `CREATE_OPTIONS`,`information_schema`.`tables`.`TABLE_COMMENT` AS `TABLE_COMMENT` from `information_schema`.`tables` where ((`information_schema`.`tables`.`TABLE_SCHEMA` = 'test') and (`information_schema`.`tables`.`TABLE_NAME` = 't1')) 1601explain select table_name, table_type from information_schema.tables 1602where table_schema='test'; 1603id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16041 SIMPLE tables NULL ALL NULL TABLE_SCHEMA NULL NULL NULL NULL Using where; Open_frm_only; Scanned 1 database 1605Warnings: 1606Note 1003 /* select#1 */ select `information_schema`.`tables`.`TABLE_NAME` AS `table_name`,`information_schema`.`tables`.`TABLE_TYPE` AS `table_type` from `information_schema`.`tables` where (`information_schema`.`tables`.`TABLE_SCHEMA` = 'test') 1607explain select b.table_name 1608from information_schema.tables a, information_schema.columns b 1609where a.table_name='t1' and a.table_schema='test' and b.table_name=a.table_name; 1610id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16111 SIMPLE a NULL ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL NULL Using where; Skip_open_table; Scanned 0 databases 16121 SIMPLE b NULL ALL NULL NULL NULL NULL NULL NULL Using where; Open_frm_only; Scanned all databases; Using join buffer (Block Nested Loop) 1613Warnings: 1614Note 1003 /* select#1 */ select `b`.`TABLE_NAME` AS `table_name` from `information_schema`.`tables` `a` join `information_schema`.`columns` `b` where ((`b`.`TABLE_NAME` = `a`.`TABLE_NAME`) and (`a`.`TABLE_NAME` = 't1') and (`a`.`TABLE_SCHEMA` = 'test')) 1615SELECT * FROM INFORMATION_SCHEMA.SCHEMATA 1616WHERE SCHEMA_NAME = 'mysqltest'; 1617CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 1618SELECT * FROM INFORMATION_SCHEMA.SCHEMATA 1619WHERE SCHEMA_NAME = ''; 1620CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 1621SELECT * FROM INFORMATION_SCHEMA.SCHEMATA 1622WHERE SCHEMA_NAME = 'test'; 1623CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 1624def test latin1 latin1_swedish_ci NULL 1625select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME='nonexisting'; 1626count(*) 16270 1628select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME=''; 1629count(*) 16300 1631select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME=''; 1632count(*) 16330 1634select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME='nonexisting'; 1635count(*) 16360 1637CREATE VIEW v1 1638AS SELECT * 1639FROM information_schema.tables; 1640SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'v1'; 1641VIEW_DEFINITION 1642select `information_schema`.`tables`.`TABLE_CATALOG` AS `TABLE_CATALOG`,`information_schema`.`tables`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`tables`.`TABLE_NAME` AS `TABLE_NAME`,`information_schema`.`tables`.`TABLE_TYPE` AS `TABLE_TYPE`,`information_schema`.`tables`.`ENGINE` AS `ENGINE`,`information_schema`.`tables`.`VERSION` AS `VERSION`,`information_schema`.`tables`.`ROW_FORMAT` AS `ROW_FORMAT`,`information_schema`.`tables`.`TABLE_ROWS` AS `TABLE_ROWS`,`information_schema`.`tables`.`AVG_ROW_LENGTH` AS `AVG_ROW_LENGTH`,`information_schema`.`tables`.`DATA_LENGTH` AS `DATA_LENGTH`,`information_schema`.`tables`.`MAX_DATA_LENGTH` AS `MAX_DATA_LENGTH`,`information_schema`.`tables`.`INDEX_LENGTH` AS `INDEX_LENGTH`,`information_schema`.`tables`.`DATA_FREE` AS `DATA_FREE`,`information_schema`.`tables`.`AUTO_INCREMENT` AS `AUTO_INCREMENT`,`information_schema`.`tables`.`CREATE_TIME` AS `CREATE_TIME`,`information_schema`.`tables`.`UPDATE_TIME` AS `UPDATE_TIME`,`information_schema`.`tables`.`CHECK_TIME` AS `CHECK_TIME`,`information_schema`.`tables`.`TABLE_COLLATION` AS `TABLE_COLLATION`,`information_schema`.`tables`.`CHECKSUM` AS `CHECKSUM`,`information_schema`.`tables`.`CREATE_OPTIONS` AS `CREATE_OPTIONS`,`information_schema`.`tables`.`TABLE_COMMENT` AS `TABLE_COMMENT` from `information_schema`.`tables` 1643DROP VIEW v1; 1644SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA 1645WHERE SCHEMA_NAME ='information_schema'; 1646SCHEMA_NAME 1647information_schema 1648SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES 1649WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db'; 1650TABLE_COLLATION 1651utf8_bin 1652select * from information_schema.columns where table_schema = NULL; 1653TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT GENERATION_EXPRESSION 1654select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL; 1655TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT GENERATION_EXPRESSION 1656select * from `information_schema`.`key_column_usage` where `TABLE_SCHEMA` = NULL; 1657CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME 1658select * from `information_schema`.`key_column_usage` where `TABLE_NAME` = NULL; 1659CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME 1660select * from `information_schema`.`PARTITIONS` where `TABLE_SCHEMA` = NULL; 1661TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME 1662select * from `information_schema`.`PARTITIONS` where `TABLE_NAME` = NULL; 1663TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME 1664select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL; 1665CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME 1666select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL; 1667CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME 1668select * from information_schema.schemata where schema_name = NULL; 1669CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 1670select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL; 1671TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT 1672select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL; 1673TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT 1674select * from information_schema.tables where table_schema = NULL; 1675TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT 1676select * from information_schema.tables where table_catalog = NULL; 1677TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT 1678select * from information_schema.tables where table_name = NULL; 1679TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT 1680select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL; 1681CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE 1682select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL; 1683CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE 1684select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_SCHEMA` = NULL; 1685TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION 1686select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_TABLE` = NULL; 1687TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION 1688select * from `information_schema`.`VIEWS` where `TABLE_SCHEMA` = NULL; 1689TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION 1690select * from `information_schema`.`VIEWS` where `TABLE_NAME` = NULL; 1691TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION 1692explain extended select 1 from information_schema.tables; 1693id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16941 SIMPLE tables NULL ALL NULL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases 1695Warnings: 1696Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1697Note 1003 /* select#1 */ select 1 AS `1` from `information_schema`.`tables` 1698use information_schema; 1699show events; 1700Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 1701show events from information_schema; 1702Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 1703show events where Db= 'information_schema'; 1704Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 1705use test; 1706# 1707# Bug#34166 Server crash in SHOW OPEN TABLES and prelocking 1708# 1709drop table if exists t1; 1710drop function if exists f1; 1711create table t1 (a int); 1712create function f1() returns int 1713begin 1714insert into t1 (a) values (1); 1715return 0; 1716end| 1717show open tables where f1()=0; 1718show open tables where f1()=0; 1719drop table t1; 1720drop function f1; 1721select * from information_schema.tables where 1=sleep(100000); 1722select * from information_schema.columns where 1=sleep(100000); 1723explain select count(*) from information_schema.tables; 1724id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17251 SIMPLE tables NULL ALL NULL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases 1726Warnings: 1727Note 1003 /* select#1 */ select count(0) AS `count(*)` from `information_schema`.`tables` 1728explain select count(*) from information_schema.columns; 1729id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17301 SIMPLE columns NULL ALL NULL NULL NULL NULL NULL NULL Open_frm_only; Scanned all databases 1731Warnings: 1732Note 1003 /* select#1 */ select count(0) AS `count(*)` from `information_schema`.`columns` 1733explain select count(*) from information_schema.views; 1734id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17351 SIMPLE views NULL ALL NULL NULL NULL NULL NULL NULL Open_frm_only; Scanned all databases 1736Warnings: 1737Note 1003 /* select#1 */ select count(0) AS `count(*)` from `information_schema`.`views` 1738set global init_connect="drop table if exists t1;drop table if exists t1;\ 1739drop table if exists t1;drop table if exists t1;\ 1740drop table if exists t1;drop table if exists t1;\ 1741drop table if exists t1;drop table if exists t1;\ 1742drop table if exists t1;drop table if exists t1;\ 1743drop table if exists t1;drop table if exists t1;\ 1744drop table if exists t1;drop table if exists t1;\ 1745drop table if exists t1;drop table if exists t1;\ 1746drop table if exists t1;drop table if exists t1;\ 1747drop table if exists t1;drop table if exists t1;\ 1748drop table if exists t1;drop table if exists t1;\ 1749drop table if exists t1;drop table if exists t1;\ 1750drop table if exists t1;drop table if exists t1;\ 1751drop table if exists t1;drop table if exists t1;\ 1752drop table if exists t1;drop table if exists t1;\ 1753drop table if exists t1;drop table if exists t1;\ 1754drop table if exists t1;drop table if exists t1;\ 1755drop table if exists t1;drop table if exists t1;\ 1756drop table if exists t1;drop table if exists t1;\ 1757drop table if exists t1;drop table if exists t1;\ 1758drop table if exists t1;drop table if exists t1;"; 1759select * from information_schema.global_variables where variable_name='init_connect'; 1760VARIABLE_NAME VARIABLE_VALUE 1761INIT_CONNECT drop table if exists t1;drop table if exists t1; 1762drop table if exists t1;drop table if exists t1; 1763drop table if exists t1;drop table if exists t1; 1764drop table if exists t1;drop table if exists t1; 1765drop table if exists t1;drop table if exists t1; 1766drop table if exists t1;drop table if exists t1; 1767drop table if exists t1;drop table if exists t1; 1768drop table if exists t1;drop table if exists t1; 1769drop table if exists t1;drop table if exists t1; 1770drop table if exists t1;drop table if exists t1; 1771drop table if exists t1;drop table if exists t1; 1772drop table if exists t1;drop table if exists t1; 1773drop table if exists t1;drop table if exists t1; 1774drop table if exists t1;drop table if exists t1; 1775drop table if exists t1;drop table if exists t1; 1776drop table if exists t1;drop table if exists t1; 1777drop table if exists t1;drop table if exists t1; 1778drop table if exists t1;drop table if exists t1; 1779drop table if exists t1;drop table if exists t1; 1780drop table if exists t1;drop table if exists t1; 1781drop table if exists t1;drop table if exists 1782Warnings: 1783Warning 1287 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' is deprecated and will be removed in a future release. Please use performance_schema.global_variables instead 1784Warning 1406 Data too long for column 'VARIABLE_VALUE' at row 1 1785set global init_connect=""; 1786create table t0 select * from information_schema.global_status where VARIABLE_NAME='COM_SELECT'; 1787SELECT 1; 17881 17891 1790select a.VARIABLE_VALUE - b.VARIABLE_VALUE from t0 b, information_schema.global_status a 1791where a.VARIABLE_NAME = b.VARIABLE_NAME; 1792a.VARIABLE_VALUE - b.VARIABLE_VALUE 17932 1794drop table t0; 1795CREATE TABLE t1(a INT) KEY_BLOCK_SIZE=1; 1796SELECT CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; 1797CREATE_OPTIONS 1798KEY_BLOCK_SIZE=1 1799DROP TABLE t1; 1800SET TIMESTAMP=@@TIMESTAMP + 10000000; 1801SELECT 'OK' AS TEST_RESULT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time < 0; 1802TEST_RESULT 1803OK 1804SET TIMESTAMP=DEFAULT; 1805# 1806# Bug #50276: Security flaw in INFORMATION_SCHEMA.TABLES 1807# 1808CREATE DATABASE db1; 1809USE db1; 1810CREATE TABLE t1 (id INT); 1811CREATE USER nonpriv; 1812USE test; 1813# connected as nonpriv 1814# Should return 0 1815SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; 1816COUNT(*) 18170 1818USE INFORMATION_SCHEMA; 1819# Should return 0 1820SELECT COUNT(*) FROM TABLES WHERE TABLE_NAME='t1'; 1821COUNT(*) 18220 1823# connected as root 1824DROP USER nonpriv; 1825DROP TABLE db1.t1; 1826DROP DATABASE db1; 1827 1828Bug#54422 query with = 'variables' 1829 1830CREATE TABLE variables(f1 INT); 1831SELECT COLUMN_DEFAULT, TABLE_NAME 1832FROM INFORMATION_SCHEMA.COLUMNS 1833WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'variables'; 1834COLUMN_DEFAULT TABLE_NAME 1835NULL variables 1836DROP TABLE variables; 1837# 1838# Bug #53814: NUMERIC_PRECISION for unsigned bigint field is 19, 1839# should be 20 1840# 1841CREATE TABLE ubig (a BIGINT, b BIGINT UNSIGNED); 1842SELECT TABLE_NAME, COLUMN_NAME, NUMERIC_PRECISION 1843FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ubig'; 1844TABLE_NAME COLUMN_NAME NUMERIC_PRECISION 1845ubig a 19 1846ubig b 20 1847INSERT IGNORE INTO ubig VALUES (0xFFFFFFFFFFFFFFFF,0xFFFFFFFFFFFFFFFF); 1848Warnings: 1849Warning 1264 Out of range value for column 'a' at row 1 1850SELECT length(CAST(b AS CHAR)) FROM ubig; 1851length(CAST(b AS CHAR)) 185220 1853DROP TABLE ubig; 1854grant usage on *.* to mysqltest_1@localhost; 1855Warnings: 1856Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 1857select 1 from information_schema.tables where table_schema=repeat('a', 2000); 18581 1859drop user mysqltest_1@localhost; 1860End of 5.1 tests. 1861create function f1 (p1 int, p2 datetime, p3 decimal(10,2)) 1862returns char(10) return null; 1863create procedure p1 (p1 float(8,5), p2 char(32), p3 varchar(10)) begin end; 1864create procedure p2 (p1 enum('c', 's'), p2 blob, p3 text) begin end; 1865select * from information_schema.parameters where specific_schema='test'; 1866SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE 1867def test f1 0 NULL NULL char 10 10 NULL NULL NULL latin1 latin1_swedish_ci char(10) FUNCTION 1868def test f1 1 IN p1 int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION 1869def test f1 2 IN p2 datetime NULL NULL NULL NULL 0 NULL NULL datetime FUNCTION 1870def test f1 3 IN p3 decimal NULL NULL 10 2 NULL NULL NULL decimal(10,2) FUNCTION 1871def test p1 1 IN p1 float NULL NULL 8 5 NULL NULL NULL float(8,5) PROCEDURE 1872def test p1 2 IN p2 char 32 32 NULL NULL NULL latin1 latin1_swedish_ci char(32) PROCEDURE 1873def test p1 3 IN p3 varchar 10 10 NULL NULL NULL latin1 latin1_swedish_ci varchar(10) PROCEDURE 1874def test p2 1 IN p1 enum 1 1 NULL NULL NULL latin1 latin1_swedish_ci enum('c','s') PROCEDURE 1875def test p2 2 IN p2 blob 65535 65535 NULL NULL NULL NULL NULL blob PROCEDURE 1876def test p2 3 IN p3 text 65535 65535 NULL NULL NULL latin1 latin1_swedish_ci text PROCEDURE 1877select data_type, character_maximum_length, 1878character_octet_length, numeric_precision, 1879numeric_scale, character_set_name, 1880collation_name, dtd_identifier 1881from information_schema.routines where routine_schema='test'; 1882data_type character_maximum_length character_octet_length numeric_precision numeric_scale character_set_name collation_name dtd_identifier 1883char 10 10 NULL NULL latin1 latin1_swedish_ci char(10) 1884 NULL NULL NULL NULL NULL NULL NULL 1885 NULL NULL NULL NULL NULL NULL NULL 1886drop procedure p1; 1887drop procedure p2; 1888drop function f1; 1889# 1890# Additional test for WL#3726 "DDL locking for all metadata objects" 1891# To avoid possible deadlocks process of filling of I_S tables should 1892# use high-priority metadata lock requests when opening tables. 1893# Below we just test that we really use high-priority lock request 1894# since reproducing a deadlock will require much more complex test. 1895# 1896drop tables if exists t1, t2, t3; 1897create table t1 (i int); 1898create table t2 (j int primary key auto_increment); 1899# Switching to connection 'con3726_1' 1900lock table t2 read; 1901# Switching to connection 'con3726_2' 1902# RENAME below will be blocked by 'lock table t2 read' above but 1903# will add two pending requests for exclusive metadata locks. 1904rename table t2 to t3; 1905# Switching to connection 'default' 1906# These statements should not be blocked by pending lock requests 1907select table_name, column_name, data_type from information_schema.columns 1908where table_schema = 'test' and table_name in ('t1', 't2'); 1909table_name column_name data_type 1910t1 i int 1911t2 j int 1912select table_name, auto_increment from information_schema.tables 1913where table_schema = 'test' and table_name in ('t1', 't2'); 1914table_name auto_increment 1915t1 NULL 1916t2 1 1917# Switching to connection 'con3726_1' 1918unlock tables; 1919# Switching to connection 'con3726_2' 1920# Switching to connection 'default' 1921drop tables t1, t3; 1922EXPLAIN SELECT * FROM information_schema.key_column_usage; 1923id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19241 SIMPLE key_column_usage NULL ALL NULL NULL NULL NULL NULL NULL Open_full_table; Scanned all databases 1925Warnings: 1926Note 1003 /* select#1 */ select `information_schema`.`key_column_usage`.`CONSTRAINT_CATALOG` AS `CONSTRAINT_CATALOG`,`information_schema`.`key_column_usage`.`CONSTRAINT_SCHEMA` AS `CONSTRAINT_SCHEMA`,`information_schema`.`key_column_usage`.`CONSTRAINT_NAME` AS `CONSTRAINT_NAME`,`information_schema`.`key_column_usage`.`TABLE_CATALOG` AS `TABLE_CATALOG`,`information_schema`.`key_column_usage`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`key_column_usage`.`TABLE_NAME` AS `TABLE_NAME`,`information_schema`.`key_column_usage`.`COLUMN_NAME` AS `COLUMN_NAME`,`information_schema`.`key_column_usage`.`ORDINAL_POSITION` AS `ORDINAL_POSITION`,`information_schema`.`key_column_usage`.`POSITION_IN_UNIQUE_CONSTRAINT` AS `POSITION_IN_UNIQUE_CONSTRAINT`,`information_schema`.`key_column_usage`.`REFERENCED_TABLE_SCHEMA` AS `REFERENCED_TABLE_SCHEMA`,`information_schema`.`key_column_usage`.`REFERENCED_TABLE_NAME` AS `REFERENCED_TABLE_NAME`,`information_schema`.`key_column_usage`.`REFERENCED_COLUMN_NAME` AS `REFERENCED_COLUMN_NAME` from `information_schema`.`key_column_usage` 1927EXPLAIN SELECT * FROM information_schema.partitions WHERE TABLE_NAME='t1'; 1928id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19291 SIMPLE partitions NULL ALL NULL TABLE_NAME NULL NULL NULL NULL Using where; Open_full_table; Scanned 1 database 1930Warnings: 1931Note 1003 /* select#1 */ select `information_schema`.`partitions`.`TABLE_CATALOG` AS `TABLE_CATALOG`,`information_schema`.`partitions`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`partitions`.`TABLE_NAME` AS `TABLE_NAME`,`information_schema`.`partitions`.`PARTITION_NAME` AS `PARTITION_NAME`,`information_schema`.`partitions`.`SUBPARTITION_NAME` AS `SUBPARTITION_NAME`,`information_schema`.`partitions`.`PARTITION_ORDINAL_POSITION` AS `PARTITION_ORDINAL_POSITION`,`information_schema`.`partitions`.`SUBPARTITION_ORDINAL_POSITION` AS `SUBPARTITION_ORDINAL_POSITION`,`information_schema`.`partitions`.`PARTITION_METHOD` AS `PARTITION_METHOD`,`information_schema`.`partitions`.`SUBPARTITION_METHOD` AS `SUBPARTITION_METHOD`,`information_schema`.`partitions`.`PARTITION_EXPRESSION` AS `PARTITION_EXPRESSION`,`information_schema`.`partitions`.`SUBPARTITION_EXPRESSION` AS `SUBPARTITION_EXPRESSION`,`information_schema`.`partitions`.`PARTITION_DESCRIPTION` AS `PARTITION_DESCRIPTION`,`information_schema`.`partitions`.`TABLE_ROWS` AS `TABLE_ROWS`,`information_schema`.`partitions`.`AVG_ROW_LENGTH` AS `AVG_ROW_LENGTH`,`information_schema`.`partitions`.`DATA_LENGTH` AS `DATA_LENGTH`,`information_schema`.`partitions`.`MAX_DATA_LENGTH` AS `MAX_DATA_LENGTH`,`information_schema`.`partitions`.`INDEX_LENGTH` AS `INDEX_LENGTH`,`information_schema`.`partitions`.`DATA_FREE` AS `DATA_FREE`,`information_schema`.`partitions`.`CREATE_TIME` AS `CREATE_TIME`,`information_schema`.`partitions`.`UPDATE_TIME` AS `UPDATE_TIME`,`information_schema`.`partitions`.`CHECK_TIME` AS `CHECK_TIME`,`information_schema`.`partitions`.`CHECKSUM` AS `CHECKSUM`,`information_schema`.`partitions`.`PARTITION_COMMENT` AS `PARTITION_COMMENT`,`information_schema`.`partitions`.`NODEGROUP` AS `NODEGROUP`,`information_schema`.`partitions`.`TABLESPACE_NAME` AS `TABLESPACE_NAME` from `information_schema`.`partitions` where (`information_schema`.`partitions`.`TABLE_NAME` = 't1') 1932EXPLAIN SELECT * FROM information_schema.referential_constraints 1933WHERE CONSTRAINT_SCHEMA='test'; 1934id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19351 SIMPLE referential_constraints NULL ALL NULL CONSTRAINT_SCHEMA NULL NULL NULL NULL Using where; Open_full_table; Scanned 1 database 1936Warnings: 1937Note 1003 /* select#1 */ select `information_schema`.`referential_constraints`.`CONSTRAINT_CATALOG` AS `CONSTRAINT_CATALOG`,`information_schema`.`referential_constraints`.`CONSTRAINT_SCHEMA` AS `CONSTRAINT_SCHEMA`,`information_schema`.`referential_constraints`.`CONSTRAINT_NAME` AS `CONSTRAINT_NAME`,`information_schema`.`referential_constraints`.`UNIQUE_CONSTRAINT_CATALOG` AS `UNIQUE_CONSTRAINT_CATALOG`,`information_schema`.`referential_constraints`.`UNIQUE_CONSTRAINT_SCHEMA` AS `UNIQUE_CONSTRAINT_SCHEMA`,`information_schema`.`referential_constraints`.`UNIQUE_CONSTRAINT_NAME` AS `UNIQUE_CONSTRAINT_NAME`,`information_schema`.`referential_constraints`.`MATCH_OPTION` AS `MATCH_OPTION`,`information_schema`.`referential_constraints`.`UPDATE_RULE` AS `UPDATE_RULE`,`information_schema`.`referential_constraints`.`DELETE_RULE` AS `DELETE_RULE`,`information_schema`.`referential_constraints`.`TABLE_NAME` AS `TABLE_NAME`,`information_schema`.`referential_constraints`.`REFERENCED_TABLE_NAME` AS `REFERENCED_TABLE_NAME` from `information_schema`.`referential_constraints` where (`information_schema`.`referential_constraints`.`CONSTRAINT_SCHEMA` = 'test') 1938EXPLAIN SELECT * FROM information_schema.table_constraints 1939WHERE TABLE_NAME='t1' and TABLE_SCHEMA='test'; 1940id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19411 SIMPLE table_constraints NULL ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL NULL Using where; Open_full_table; Scanned 0 databases 1942Warnings: 1943Note 1003 /* select#1 */ select `information_schema`.`table_constraints`.`CONSTRAINT_CATALOG` AS `CONSTRAINT_CATALOG`,`information_schema`.`table_constraints`.`CONSTRAINT_SCHEMA` AS `CONSTRAINT_SCHEMA`,`information_schema`.`table_constraints`.`CONSTRAINT_NAME` AS `CONSTRAINT_NAME`,`information_schema`.`table_constraints`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`table_constraints`.`TABLE_NAME` AS `TABLE_NAME`,`information_schema`.`table_constraints`.`CONSTRAINT_TYPE` AS `CONSTRAINT_TYPE` from `information_schema`.`table_constraints` where ((`information_schema`.`table_constraints`.`TABLE_NAME` = 't1') and (`information_schema`.`table_constraints`.`TABLE_SCHEMA` = 'test')) 1944EXPLAIN SELECT * FROM information_schema.triggers 1945WHERE EVENT_OBJECT_SCHEMA='test'; 1946id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19471 SIMPLE triggers NULL ALL NULL EVENT_OBJECT_SCHEMA NULL NULL NULL NULL Using where; Open_frm_only; Scanned 1 database 1948Warnings: 1949Note 1003 /* select#1 */ select `information_schema`.`triggers`.`TRIGGER_CATALOG` AS `TRIGGER_CATALOG`,`information_schema`.`triggers`.`TRIGGER_SCHEMA` AS `TRIGGER_SCHEMA`,`information_schema`.`triggers`.`TRIGGER_NAME` AS `TRIGGER_NAME`,`information_schema`.`triggers`.`EVENT_MANIPULATION` AS `EVENT_MANIPULATION`,`information_schema`.`triggers`.`EVENT_OBJECT_CATALOG` AS `EVENT_OBJECT_CATALOG`,`information_schema`.`triggers`.`EVENT_OBJECT_SCHEMA` AS `EVENT_OBJECT_SCHEMA`,`information_schema`.`triggers`.`EVENT_OBJECT_TABLE` AS `EVENT_OBJECT_TABLE`,`information_schema`.`triggers`.`ACTION_ORDER` AS `ACTION_ORDER`,`information_schema`.`triggers`.`ACTION_CONDITION` AS `ACTION_CONDITION`,`information_schema`.`triggers`.`ACTION_STATEMENT` AS `ACTION_STATEMENT`,`information_schema`.`triggers`.`ACTION_ORIENTATION` AS `ACTION_ORIENTATION`,`information_schema`.`triggers`.`ACTION_TIMING` AS `ACTION_TIMING`,`information_schema`.`triggers`.`ACTION_REFERENCE_OLD_TABLE` AS `ACTION_REFERENCE_OLD_TABLE`,`information_schema`.`triggers`.`ACTION_REFERENCE_NEW_TABLE` AS `ACTION_REFERENCE_NEW_TABLE`,`information_schema`.`triggers`.`ACTION_REFERENCE_OLD_ROW` AS `ACTION_REFERENCE_OLD_ROW`,`information_schema`.`triggers`.`ACTION_REFERENCE_NEW_ROW` AS `ACTION_REFERENCE_NEW_ROW`,`information_schema`.`triggers`.`CREATED` AS `CREATED`,`information_schema`.`triggers`.`SQL_MODE` AS `SQL_MODE`,`information_schema`.`triggers`.`DEFINER` AS `DEFINER`,`information_schema`.`triggers`.`CHARACTER_SET_CLIENT` AS `CHARACTER_SET_CLIENT`,`information_schema`.`triggers`.`COLLATION_CONNECTION` AS `COLLATION_CONNECTION`,`information_schema`.`triggers`.`DATABASE_COLLATION` AS `DATABASE_COLLATION` from `information_schema`.`triggers` where (`information_schema`.`triggers`.`EVENT_OBJECT_SCHEMA` = 'test') 1950create table information_schema.t1 (f1 INT); 1951ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1952drop table information_schema.t1; 1953ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1954drop temporary table if exists information_schema.t1; 1955ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1956create temporary table information_schema.t1 (f1 INT); 1957ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1958drop view information_schema.v1; 1959ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1960create view information_schema.v1; 1961ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1962create trigger mysql.trg1 after insert on information_schema.t1 for each row set @a=1; 1963ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1964create table t1 select * from information_schema.t1; 1965ERROR 42S02: Unknown table 't1' in information_schema 1966CREATE TABLE t1(f1 char(100)); 1967REPAIR TABLE t1, information_schema.tables; 1968ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1969CHECKSUM TABLE t1, information_schema.tables; 1970Table Checksum 1971test.t1 0 1972information_schema.tables 0 1973ANALYZE TABLE t1, information_schema.tables; 1974ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1975CHECK TABLE t1, information_schema.tables; 1976Table Op Msg_type Msg_text 1977test.t1 check status OK 1978information_schema.tables check note The storage engine for the table doesn't support check 1979OPTIMIZE TABLE t1, information_schema.tables; 1980ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1981RENAME TABLE v1 to v2, information_schema.tables to t2; 1982ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1983DROP TABLE t1, information_schema.tables; 1984ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1985LOCK TABLES t1 READ, information_schema.tables READ; 1986ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1987DROP TABLE t1; 1988create function f1() returns int return 1; 1989select routine_name, routine_type from information_schema.routines 1990where routine_schema = 'test'; 1991routine_name routine_type 1992f1 FUNCTION 1993drop function f1; 1994SELECT * 1995FROM INFORMATION_SCHEMA.key_column_usage 1996LEFT JOIN INFORMATION_SCHEMA.COLUMNS 1997USING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) 1998WHERE COLUMNS.TABLE_SCHEMA = 'test' 1999AND COLUMNS.TABLE_NAME = 't1'; 2000TABLE_SCHEMA TABLE_NAME COLUMN_NAME CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME TABLE_CATALOG ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT GENERATION_EXPRESSION 2001# 2002# A test case for Bug#56540 "Exception (crash) in sql_show.cc 2003# during rqg_info_schema test on Windows" 2004# Ensure that we never access memory of a closed table, 2005# in particular, never access table->field[] array. 2006# Before the fix, the below test case, produced 2007# valgrind errors. 2008# 2009drop table if exists t1; 2010drop view if exists v1; 2011create table t1 (a int, b int); 2012create view v1 as select t1.a, t1.b from t1; 2013alter table t1 change b c int; 2014lock table t1 read; 2015# --> connection con1 2016flush tables; 2017# --> connection default 2018select * from information_schema.views where table_schema != 'sys'; 2019TABLE_CATALOG def 2020TABLE_SCHEMA test 2021TABLE_NAME v1 2022VIEW_DEFINITION select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` 2023CHECK_OPTION NONE 2024IS_UPDATABLE 2025DEFINER root@localhost 2026SECURITY_TYPE DEFINER 2027CHARACTER_SET_CLIENT latin1 2028COLLATION_CONNECTION latin1_swedish_ci 2029Warnings: 2030Level Warning 2031Code 1356 2032Message View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 2033unlock tables; 2034# 2035# Cleanup. 2036# 2037# --> connection con1 2038# Reaping 'flush tables' 2039# --> connection default 2040drop table t1; 2041drop view v1; 2042# 2043# Test for bug #12828477 - "MDL SUBSYSTEM CREATES BIG OVERHEAD FOR 2044# CERTAIN QUERIES TO INFORMATION_SCHEMA". 2045# 2046# Check that metadata locks which are acquired during the process 2047# of opening tables/.FRMs/.TRG files while filling I_S table are 2048# not kept to the end of statement. Keeping the locks has caused 2049# performance problems in cases when big number of tables (.FRMs 2050# or .TRG files) were scanned as cost of new lock acquisition has 2051# increased linearly. 2052drop database if exists mysqltest; 2053create database mysqltest; 2054use mysqltest; 2055create table t0 (i int); 2056create table t1 (j int); 2057create table t2 (k int); 2058# 2059# Test that we don't keep locks in case when we to fill 2060# I_S table we perform full-blown table open. 2061# 2062# Acquire lock on 't2' so upcoming RENAME is 2063# blocked. 2064lock tables t2 read; 2065# 2066# Switching to connection 'con12828477_1'. 2067# 2068# The below RENAME should wait on 't2' while 2069# keeping X lock on 't1'. 2070rename table t1 to t3, t2 to t1, t3 to t2; 2071# 2072# Switching to connection 'con12828477_2'. 2073# 2074# Wait while the above RENAME is blocked. 2075# Issue query to I_S which will open 't0' and get 2076# blocked on 't1' because of RENAME. 2077select table_name, auto_increment from information_schema.tables where table_schema='mysqltest'; 2078# 2079# Switching to connection 'con12828477_3'. 2080# 2081# Wait while the above SELECT is blocked. 2082# 2083# Check that it holds no lock on 't0' so it can be renamed. 2084rename table t0 to t4; 2085# 2086# Switching to connection 'default'. 2087# 2088# 2089# Unblock the first RENAME. 2090unlock tables; 2091# 2092# Switching to connection 'con12828477_1'. 2093# 2094# Reap the first RENAME 2095# 2096# Switching to connection 'con12828477_2'. 2097# 2098# Reap SELECT to I_S. 2099table_name auto_increment 2100t0 NULL 2101t1 NULL 2102t2 NULL 2103# 2104# Switching to connection 'default'. 2105# 2106# 2107# Now test that we don't keep locks in case when we to fill 2108# I_S table we read .FRM or .TRG file only (this was the case 2109# for which problem existed). 2110# 2111rename table t4 to t0; 2112# Acquire lock on 't2' so upcoming RENAME is 2113# blocked. 2114lock tables t2 read; 2115# 2116# Switching to connection 'con12828477_1'. 2117# 2118# The below RENAME should wait on 't2' while 2119# keeping X lock on 't1'. 2120rename table t1 to t3, t2 to t1, t3 to t2; 2121# 2122# Switching to connection 'con12828477_2'. 2123# 2124# Wait while the above RENAME is blocked. 2125# Issue query to I_S which will open 't0' and get 2126# blocked on 't1' because of RENAME. 2127select event_object_table, trigger_name from information_schema.triggers where event_object_schema='mysqltest'; 2128# 2129# Switching to connection 'con12828477_3'. 2130# 2131# Wait while the above SELECT is blocked. 2132# 2133# Check that it holds no lock on 't0' so it can be renamed. 2134rename table t0 to t4; 2135# 2136# Switching to connection 'default'. 2137# 2138# 2139# Unblock the first RENAME. 2140unlock tables; 2141# 2142# Switching to connection 'con12828477_1'. 2143# 2144# Reap the first RENAME 2145# 2146# Switching to connection 'con12828477_2'. 2147# 2148# Reap SELECT to I_S. 2149event_object_table trigger_name 2150# 2151# Switching to connection 'default'. 2152# 2153# 2154# Test case to test DATETIME_PRECISION of information_schema.columns table 2155# 2156drop database if exists mysqltest; 2157create database mysqltest; 2158use mysqltest; 2159create table mysqltest.t(a int, b date, c time, d datetime, e timestamp); 2160create table mysqltest.t0(a int, b date, c time(0), d datetime(0), e timestamp(0)); 2161create table mysqltest.t1(a int, b date, c time(1), d datetime(1), e timestamp(1)); 2162create table mysqltest.t2(a int, b date, c time(2), d datetime(2), e timestamp(2)); 2163create table mysqltest.t3(a int, b date, c time(3), d datetime(3), e timestamp(3)); 2164create table mysqltest.t4(a int, b date, c time(4), d datetime(4), e timestamp(4)); 2165create table mysqltest.t5(a int, b date, c time(5), d datetime(5), e timestamp(5)); 2166create table mysqltest.t6(a int, b date, c time(6), d datetime(6), e timestamp(6)); 2167select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATETIME_PRECISION from information_schema.columns where TABLE_SCHEMA='mysqltest'; 2168TABLE_NAME COLUMN_NAME DATA_TYPE DATETIME_PRECISION 2169t a int NULL 2170t b date NULL 2171t c time 0 2172t d datetime 0 2173t e timestamp 0 2174t0 a int NULL 2175t0 b date NULL 2176t0 c time 0 2177t0 d datetime 0 2178t0 e timestamp 0 2179t1 a int NULL 2180t1 b date NULL 2181t1 c time 1 2182t1 d datetime 1 2183t1 e timestamp 1 2184t2 a int NULL 2185t2 b date NULL 2186t2 c time 2 2187t2 d datetime 2 2188t2 e timestamp 2 2189t3 a int NULL 2190t3 b date NULL 2191t3 c time 3 2192t3 d datetime 3 2193t3 e timestamp 3 2194t4 a int NULL 2195t4 b date NULL 2196t4 c time 4 2197t4 d datetime 4 2198t4 e timestamp 4 2199t5 a int NULL 2200t5 b date NULL 2201t5 c time 5 2202t5 d datetime 5 2203t5 e timestamp 5 2204t6 a int NULL 2205t6 b date NULL 2206t6 c time 6 2207t6 d datetime 6 2208t6 e timestamp 6 2209# 2210# Clean-up. 2211drop database mysqltest; 2212use test; 2213# 2214# Test for bug #16869534 - "QUERYING SUBSET OF COLUMNS DOESN'T USE TABLE 2215# CACHE; OPENED_TABLES INCREASES" 2216# 2217SELECT * FROM INFORMATION_SCHEMA.TABLES; 2218SELECT VARIABLE_VALUE INTO @val1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE 2219VARIABLE_NAME LIKE 'Opened_tables'; 2220SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES; 2221# The below SELECT query should give same output as above SELECT query. 2222SELECT VARIABLE_VALUE INTO @val2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE 2223VARIABLE_NAME LIKE 'Opened_tables'; 2224# The below select should return '1' 2225SELECT @val1 = @val2; 2226@val1 = @val2 22271 2228# 2229# End of 5.5 tests 2230# 2231# 2232# Bug #13966514 : CRASH IN GET_SCHEMA_TABLES_RESULT WITH MIN/MAX, 2233# LEFT/RIGHT JOIN ON I_S TABLE 2234# 2235CREATE TABLE t1(a INT PRIMARY KEY); 2236INSERT INTO t1 VALUES (1); 2237# must not crash 2238SELECT MAX(a) FROM information_schema.engines RIGHT JOIN t1 ON 1; 2239MAX(a) 22401 2241DROP TABLE t1; 2242# 2243# BUG#13463397 - 63562: UNKNOWN DATABASE INFORMATION_SCHEMA 2244# 2245CREATE PROCEDURE information_schema.is() BEGIN END; 2246ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 2247# 2248# Bug#26877788 SELECT FROM INFORMATION_SCHEMA.FILES RETURNS NO RECORDS WHEN ORDER BY IS USED 2249# 2250SELECT ENGINE, SUPPORT, TRANSACTIONS FROM INFORMATION_SCHEMA.ENGINES 2251WHERE 2252SUPPORT IN ( 2253SELECT DISTINCT SUPPORT 2254FROM INFORMATION_SCHEMA.ENGINES 2255WHERE 2256ENGINE IN ( 2257SELECT DISTINCT ENGINE FROM INFORMATION_SCHEMA.ENGINES 2258WHERE ENGINE IN ('MEMORY'))) 2259ORDER BY ENGINE 2260LIMIT 1; 2261ENGINE SUPPORT TRANSACTIONS 2262ARCHIVE YES NO 2263# 2264# End of 5.6 tests 2265# 2266# 2267# Bug#19307777 ASSERTION `QEP_TAB->CONDITION() == QEP_TAB->CONDITION_OPTIM()' FAILED 2268# 2269EXPLAIN select * from 2270information_schema . innodb_cmp as table1 2271left outer join mysql . procs_priv as table2 2272on ( table2 . routine_name = table1 . compress_time ) 2273where not table1 . compress_time <> '2006-09-03 10:11:37.046313' 2274having table2 . grantor <> '2008-02-28 22:17:05.025739' limit 9; 2275id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22761 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING noticed after reading const tables 2277Warnings: 2278Note 1003 /* select#1 */ select `table1`.`page_size` AS `page_size`,`table1`.`compress_ops` AS `compress_ops`,`table1`.`compress_ops_ok` AS `compress_ops_ok`,`table1`.`compress_time` AS `compress_time`,`table1`.`uncompress_ops` AS `uncompress_ops`,`table1`.`uncompress_time` AS `uncompress_time`,NULL AS `Host`,NULL AS `Db`,NULL AS `User`,NULL AS `Routine_name`,NULL AS `Routine_type`,NULL AS `Grantor`,NULL AS `Proc_priv`,NULL AS `Timestamp` from `information_schema`.`innodb_cmp` `table1` where (`table1`.`compress_time` = '2006-09-03 10:11:37.046313') having 0 limit 9 2279select * from 2280information_schema . innodb_cmp as table1 2281left outer join mysql . procs_priv as table2 2282on ( table2 . routine_name = table1 . compress_time ) 2283where not table1 . compress_time <> '2006-09-03 10:11:37.046313' 2284having table2 . grantor <> '2008-02-28 22:17:05.025739' limit 9; 2285page_size compress_ops compress_ops_ok compress_time uncompress_ops uncompress_time Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp 2286# 2287# Bug#20665051 SQL_SHOW.CC:7764: ASSERTION `QEP_TAB->CONDITION() == QEP_TAB->CONDITION_OPTIM() 2288# 2289EXPLAIN SELECT 1 2290FROM DUAL 2291WHERE (SELECT 1 FROM information_schema.tables 2292WHERE table_schema 2293ORDER BY table_name 2294LIMIT 1); 2295id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22961 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 22972 SUBQUERY tables NULL ALL NULL NULL NULL NULL NULL NULL Using where; Skip_open_table; Scanned all databases; Using filesort 2298Warnings: 2299Note 1003 /* select#1 */ select 1 AS `1` from DUAL where 0 2300SELECT 1 2301FROM DUAL 2302WHERE (SELECT 1 FROM information_schema.tables 2303WHERE table_schema 2304ORDER BY table_name 2305LIMIT 1); 23061 2307EXPLAIN SELECT 1 AS F1 FROM information_schema.tables 2308WHERE "CHARACTER_SETS"= 2309(SELECT CAST(TABLE_NAME AS CHAR) 2310FROM information_schema.tables 2311WHERE table_schema != 'PERFORMANCE_SCHEMA' 2312 ORDER BY table_name 2313LIMIT 1) 2314LIMIT 1; 2315id select_type table partitions type possible_keys key key_len ref rows filtered Extra 23161 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 23172 SUBQUERY tables NULL ALL NULL NULL NULL NULL NULL NULL Using where; Skip_open_table; Scanned all databases; Using filesort 2318Warnings: 2319Note 1003 /* select#1 */ select 1 AS `F1` from `information_schema`.`tables` where 0 limit 1 2320SELECT 1 AS F1 FROM information_schema.tables 2321WHERE "CHARACTER_SETS"= 2322(SELECT CAST(TABLE_NAME AS CHAR) 2323FROM information_schema.tables 2324WHERE table_schema != 'PERFORMANCE_SCHEMA' 2325 ORDER BY table_name 2326LIMIT 1) 2327LIMIT 1; 2328F1 23291 2330# 2331# WL#2284: Increase the length of a user name 2332# 2333set names utf8; 2334CREATE USER user_name_len_22_01234@localhost; 2335GRANT SELECT ON *.* TO user_name_len_22_01234@localhost; 2336SELECT user,db FROM information_schema.processlist; 2337user db 2338user_name_len_22_01234 test 2339CREATE USER очень_очень_очень_длинный_юзер__@localhost; 2340GRANT SELECT ON *.* TO очень_очень_очень_длинный_юзер__@localhost; 2341SELECT user,db FROM information_schema.processlist; 2342user db 2343очень_очень_очень_длинный_юзер__ test 2344DROP USER user_name_len_22_01234@localhost; 2345DROP USER очень_очень_очень_длинный_юзер__@localhost; 2346set names default; 2347set sql_mode= @orig_sql_mode; 2348Warnings: 2349Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 2350