1set global sql_mode=""; 2set local sql_mode=""; 3DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5; 4DROP VIEW IF EXISTS v1; 5show variables where variable_name like "skip_show_database"; 6Variable_name Value 7skip_show_database OFF 8grant select, update, execute on test.* to mysqltest_2@localhost; 9grant select, update on test.* to mysqltest_1@localhost; 10create user mysqltest_3@localhost; 11create user mysqltest_3; 12select * from information_schema.SCHEMATA where schema_name > 'm'; 13CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 14def mtr latin1 latin1_swedish_ci NULL 15def mysql latin1 latin1_swedish_ci NULL 16def performance_schema utf8 utf8_general_ci NULL 17def test latin1 latin1_swedish_ci NULL 18select schema_name from information_schema.schemata; 19schema_name 20information_schema 21mtr 22mysql 23performance_schema 24test 25show databases like 't%'; 26Database (t%) 27test 28show databases; 29Database 30information_schema 31mtr 32mysql 33performance_schema 34test 35show databases where `database` = 't%'; 36Database 37create database mysqltest; 38create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b)); 39create table test.t2(a int); 40create table t3(a int, KEY a_data (a)); 41create table mysqltest.t4(a int); 42create table t5 (id int auto_increment primary key); 43insert into t5 values (10); 44create view v1 (c) as 45SELECT table_name FROM information_schema.TABLES 46WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND 47table_name not like 'innodb_%' AND 48table_name not like 'xtradb_%'; 49select * from v1; 50c 51ALL_PLUGINS 52APPLICABLE_ROLES 53CHARACTER_SETS 54CHECK_CONSTRAINTS 55CLIENT_STATISTICS 56COLLATIONS 57COLLATION_CHARACTER_SET_APPLICABILITY 58COLUMNS 59COLUMN_PRIVILEGES 60ENABLED_ROLES 61ENGINES 62EVENTS 63FILES 64GEOMETRY_COLUMNS 65GLOBAL_STATUS 66GLOBAL_VARIABLES 67INDEX_STATISTICS 68KEYWORDS 69KEY_CACHES 70KEY_COLUMN_USAGE 71OPTIMIZER_TRACE 72PARAMETERS 73PARTITIONS 74PLUGINS 75PROCESSLIST 76PROFILING 77REFERENTIAL_CONSTRAINTS 78ROUTINES 79SCHEMATA 80SCHEMA_PRIVILEGES 81SESSION_STATUS 82SESSION_VARIABLES 83SPATIAL_REF_SYS 84SQL_FUNCTIONS 85STATISTICS 86SYSTEM_VARIABLES 87TABLES 88TABLESPACES 89TABLE_CONSTRAINTS 90TABLE_PRIVILEGES 91TABLE_STATISTICS 92TRIGGERS 93USER_PRIVILEGES 94USER_STATISTICS 95VIEWS 96column_stats 97columns_priv 98db 99event 100func 101general_log 102global_priv 103gtid_slave_pos 104help_category 105help_keyword 106help_relation 107help_topic 108index_stats 109plugin 110proc 111procs_priv 112proxies_priv 113roles_mapping 114servers 115slow_log 116t1 117t2 118t3 119t4 120t5 121table_stats 122tables_priv 123time_zone 124time_zone_leap_second 125time_zone_name 126time_zone_transition 127time_zone_transition_type 128transaction_registry 129user 130v1 131select c,table_name from v1 132inner join information_schema.TABLES v2 on (v1.c=v2.table_name) 133where v1.c like "t%"; 134c table_name 135TABLES TABLES 136TABLESPACES TABLESPACES 137TABLE_CONSTRAINTS TABLE_CONSTRAINTS 138TABLE_PRIVILEGES TABLE_PRIVILEGES 139TABLE_STATISTICS TABLE_STATISTICS 140TRIGGERS TRIGGERS 141t1 t1 142t2 t2 143t3 t3 144t4 t4 145t5 t5 146table_stats table_stats 147tables_priv tables_priv 148time_zone time_zone 149time_zone_leap_second time_zone_leap_second 150time_zone_name time_zone_name 151time_zone_transition time_zone_transition 152time_zone_transition_type time_zone_transition_type 153transaction_registry transaction_registry 154select c,table_name from v1 155left join information_schema.TABLES v2 on (v1.c=v2.table_name) 156where v1.c like "t%"; 157c table_name 158TABLES TABLES 159TABLESPACES TABLESPACES 160TABLE_CONSTRAINTS TABLE_CONSTRAINTS 161TABLE_PRIVILEGES TABLE_PRIVILEGES 162TABLE_STATISTICS TABLE_STATISTICS 163TRIGGERS TRIGGERS 164t1 t1 165t2 t2 166t3 t3 167t4 t4 168t5 t5 169table_stats table_stats 170tables_priv tables_priv 171time_zone time_zone 172time_zone_leap_second time_zone_leap_second 173time_zone_name time_zone_name 174time_zone_transition time_zone_transition 175time_zone_transition_type time_zone_transition_type 176transaction_registry transaction_registry 177select c, v2.table_name from v1 178right join information_schema.TABLES v2 on (v1.c=v2.table_name) 179where v1.c like "t%"; 180c table_name 181TABLES TABLES 182TABLESPACES TABLESPACES 183TABLE_CONSTRAINTS TABLE_CONSTRAINTS 184TABLE_PRIVILEGES TABLE_PRIVILEGES 185TABLE_STATISTICS TABLE_STATISTICS 186TRIGGERS TRIGGERS 187t1 t1 188t2 t2 189t3 t3 190t4 t4 191t5 t5 192table_stats table_stats 193tables_priv tables_priv 194time_zone time_zone 195time_zone_leap_second time_zone_leap_second 196time_zone_name time_zone_name 197time_zone_transition time_zone_transition 198time_zone_transition_type time_zone_transition_type 199transaction_registry transaction_registry 200select table_name from information_schema.TABLES 201where table_schema = "mysqltest" and table_name like "t%"; 202table_name 203t1 204t4 205select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest"; 206TABLE_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 207def mysqltest t1 1 mysqltest string_data 1 b A NULL NULL NULL YES BTREE 208show keys from t3 where Key_name = "a_data"; 209Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 210t3 1 a_data 1 a A NULL NULL NULL YES BTREE 211show tables like 't%'; 212Tables_in_test (t%) 213t2 214t3 215t5 216show table status; 217Name 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 Max_index_length Temporary 218t2 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL # N 219t3 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL # N 220t5 MyISAM 10 Fixed 1 7 7 # 2048 0 11 # # NULL latin1_swedish_ci NULL # N 221v1 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW # NULL 222show full columns from t3 like "a%"; 223Field Type Collation Null Key Default Extra Privileges Comment 224a int(11) NULL YES MUL NULL select,insert,update,references 225show full columns from mysql.db like "Insert%"; 226Field Type Collation Null Key Default Extra Privileges Comment 227Insert_priv enum('N','Y') utf8_general_ci NO N select,insert,update,references 228show full columns from v1; 229Field Type Collation Null Key Default Extra Privileges Comment 230c varchar(64) utf8_general_ci NO NULL select,insert,update,references 231select * from information_schema.COLUMNS where table_name="t1" 232and column_name= "a"; 233TABLE_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 IS_GENERATED GENERATION_EXPRESSION 234def mysqltest t1 a 1 NULL YES int NULL NULL 10 0 NULL NULL NULL int(11) select,insert,update,references NEVER NULL 235show columns from mysqltest.t1 where field like "%a%"; 236Field Type Null Key Default Extra 237a int(11) YES NULL 238create view mysqltest.v1 (c) as select a from mysqltest.t1; 239grant select (a) on mysqltest.t1 to mysqltest_2@localhost; 240grant select on mysqltest.v1 to mysqltest_3; 241connect user3,localhost,mysqltest_2,,; 242connection user3; 243select table_name, column_name, privileges from information_schema.columns 244where table_schema = 'mysqltest' and table_name = 't1'; 245table_name column_name privileges 246t1 a select 247show columns from mysqltest.t1; 248Field Type Null Key Default Extra 249a int(11) YES NULL 250connect user4,localhost,mysqltest_3,,mysqltest; 251connection user4; 252select table_name, column_name, privileges from information_schema.columns 253where table_schema = 'mysqltest' and table_name = 'v1'; 254table_name column_name privileges 255v1 c select 256explain select * from v1; 257ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table 258connection default; 259disconnect user4; 260drop view v1, mysqltest.v1; 261drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5; 262drop database mysqltest; 263select * from information_schema.CHARACTER_SETS 264where CHARACTER_SET_NAME like 'latin1%'; 265CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN 266latin1 latin1_swedish_ci cp1252 West European 1 267SHOW CHARACTER SET LIKE 'latin1%'; 268Charset Description Default collation Maxlen 269latin1 cp1252 West European latin1_swedish_ci 1 270SHOW CHARACTER SET WHERE charset like 'latin1%'; 271Charset Description Default collation Maxlen 272latin1 cp1252 West European latin1_swedish_ci 1 273select * from information_schema.COLLATIONS 274where COLLATION_NAME like 'latin1%'; 275COLLATION_NAME CHARACTER_SET_NAME ID IS_DEFAULT IS_COMPILED SORTLEN 276latin1_german1_ci latin1 5 # 1 277latin1_swedish_ci latin1 8 Yes # 1 278latin1_danish_ci latin1 15 # 1 279latin1_german2_ci latin1 31 # 2 280latin1_bin latin1 47 # 1 281latin1_general_ci latin1 48 # 1 282latin1_general_cs latin1 49 # 1 283latin1_spanish_ci latin1 94 # 1 284latin1_swedish_nopad_ci latin1 1032 # 1 285latin1_nopad_bin latin1 1071 # 1 286SHOW COLLATION LIKE 'latin1%'; 287Collation Charset Id Default Compiled Sortlen 288latin1_german1_ci latin1 5 # 1 289latin1_swedish_ci latin1 8 Yes # 1 290latin1_danish_ci latin1 15 # 1 291latin1_german2_ci latin1 31 # 2 292latin1_bin latin1 47 # 1 293latin1_general_ci latin1 48 # 1 294latin1_general_cs latin1 49 # 1 295latin1_spanish_ci latin1 94 # 1 296latin1_swedish_nopad_ci latin1 1032 # 1 297latin1_nopad_bin latin1 1071 # 1 298SHOW COLLATION WHERE collation like 'latin1%'; 299Collation Charset Id Default Compiled Sortlen 300latin1_german1_ci latin1 5 # 1 301latin1_swedish_ci latin1 8 Yes # 1 302latin1_danish_ci latin1 15 # 1 303latin1_german2_ci latin1 31 # 2 304latin1_bin latin1 47 # 1 305latin1_general_ci latin1 48 # 1 306latin1_general_cs latin1 49 # 1 307latin1_spanish_ci latin1 94 # 1 308latin1_swedish_nopad_ci latin1 1032 # 1 309latin1_nopad_bin latin1 1071 # 1 310select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY 311where COLLATION_NAME like 'latin1%'; 312COLLATION_NAME CHARACTER_SET_NAME 313latin1_german1_ci latin1 314latin1_swedish_ci latin1 315latin1_danish_ci latin1 316latin1_german2_ci latin1 317latin1_bin latin1 318latin1_general_ci latin1 319latin1_general_cs latin1 320latin1_spanish_ci latin1 321latin1_swedish_nopad_ci latin1 322latin1_nopad_bin latin1 323drop procedure if exists sel2; 324drop function if exists sub1; 325drop function if exists sub2; 326create function sub1(i int) returns int 327return i+1; 328create procedure sel2() 329begin 330select * from t1; 331select * from t2; 332end| 333select parameter_style, sql_data_access, dtd_identifier 334from information_schema.routines where routine_schema='test'; 335parameter_style sql_data_access dtd_identifier 336SQL CONTAINS SQL NULL 337SQL CONTAINS SQL int(11) 338show procedure status where db='test'; 339Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 340test sel2 PROCEDURE root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 341show function status where db='test'; 342Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 343test sub1 FUNCTION root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 344select a.ROUTINE_NAME from information_schema.ROUTINES a, 345information_schema.SCHEMATA b where 346a.ROUTINE_SCHEMA = b.SCHEMA_NAME AND b.SCHEMA_NAME='test'; 347ROUTINE_NAME 348sel2 349sub1 350explain select a.ROUTINE_NAME from information_schema.ROUTINES a, 351information_schema.SCHEMATA b where 352a.ROUTINE_SCHEMA = b.SCHEMA_NAME; 353id select_type table type possible_keys key key_len ref rows Extra 3541 SIMPLE # ALL NULL NULL NULL NULL NULL 3551 SIMPLE # ALL NULL NULL NULL NULL NULL Using where; Using join buffer (flat, BNL join) 356select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a, 357mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) AND a.ROUTINE_SCHEMA='test' order by 1; 358ROUTINE_NAME name 359sel2 sel2 360sub1 sub1 361select count(*) from information_schema.ROUTINES where routine_schema='test'; 362count(*) 3632 364create view v1 as select routine_schema, routine_name from information_schema.routines where routine_schema='test' 365order by routine_schema, routine_name; 366select * from v1; 367routine_schema routine_name 368test sel2 369test sub1 370drop view v1; 371connect user1,localhost,mysqltest_1,,; 372connection user1; 373select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; 374ROUTINE_NAME ROUTINE_DEFINITION 375show create function sub1; 376ERROR 42000: FUNCTION sub1 does not exist 377connection user3; 378select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; 379ROUTINE_NAME ROUTINE_DEFINITION 380sel2 NULL 381sub1 NULL 382connection default; 383grant all privileges on test.* to mysqltest_1@localhost; 384connect user2,localhost,mysqltest_1,,; 385connection user2; 386select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; 387ROUTINE_NAME ROUTINE_DEFINITION 388sel2 NULL 389sub1 NULL 390create function sub2(i int) returns int 391return i+1; 392select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; 393ROUTINE_NAME ROUTINE_DEFINITION 394sel2 NULL 395sub1 NULL 396sub2 return i+1 397show create procedure sel2; 398Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 399sel2 NULL latin1 latin1_swedish_ci latin1_swedish_ci 400show create function sub1; 401Function sql_mode Create Function character_set_client collation_connection Database Collation 402sub1 NULL latin1 latin1_swedish_ci latin1_swedish_ci 403show create function sub2; 404Function sql_mode Create Function character_set_client collation_connection Database Collation 405sub2 CREATE DEFINER=`mysqltest_1`@`localhost` FUNCTION `sub2`(i int) RETURNS int(11) 406return i+1 latin1 latin1_swedish_ci latin1_swedish_ci 407show function status like "sub2"; 408Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 409test sub2 FUNCTION mysqltest_1@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 410connection default; 411disconnect user1; 412disconnect user3; 413drop function sub2; 414show create procedure sel2; 415Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 416sel2 CREATE DEFINER=`root`@`localhost` PROCEDURE `sel2`() 417begin 418select * from t1; 419select * from t2; 420end latin1 latin1_swedish_ci latin1_swedish_ci 421create view v0 (c) as select schema_name from information_schema.schemata; 422select * from v0; 423c 424information_schema 425mtr 426mysql 427performance_schema 428test 429explain select * from v0; 430id select_type table type possible_keys key key_len ref rows Extra 4311 SIMPLE # ALL NULL NULL NULL NULL NULL 432create view v1 (c) as select table_name from information_schema.tables 433where table_name="v1"; 434select * from v1; 435c 436v1 437create view v2 (c) as select column_name from information_schema.columns 438where table_name="v2"; 439select * from v2; 440c 441c 442create view v3 (c) as select CHARACTER_SET_NAME from information_schema.character_sets 443where CHARACTER_SET_NAME like "latin1%"; 444select * from v3; 445c 446latin1 447create view v4 (c) as select COLLATION_NAME from information_schema.collations 448where COLLATION_NAME like "latin1%"; 449select * from v4; 450c 451latin1_german1_ci 452latin1_swedish_ci 453latin1_danish_ci 454latin1_german2_ci 455latin1_bin 456latin1_general_ci 457latin1_general_cs 458latin1_spanish_ci 459latin1_swedish_nopad_ci 460latin1_nopad_bin 461show keys from v4; 462Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 463select * from information_schema.views where TABLE_NAME like "v%"; 464TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM 465def test v0 select `information_schema`.`schemata`.`SCHEMA_NAME` AS `c` from `information_schema`.`schemata` NONE NO root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED 466def 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 UNDEFINED 467def 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 UNDEFINED 468def 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 UNDEFINED 469def 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 UNDEFINED 470drop view v0, v1, v2, v3, v4; 471create table t1 (a int); 472grant select,update,insert on t1 to mysqltest_1@localhost; 473grant select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost; 474grant all on test.* to mysqltest_1@localhost with grant option; 475select * from information_schema.USER_PRIVILEGES where grantee like '%mysqltest_1%'; 476GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 477'mysqltest_1'@'localhost' def USAGE NO 478select * from information_schema.SCHEMA_PRIVILEGES where grantee like '%mysqltest_1%'; 479GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE 480'mysqltest_1'@'localhost' def test SELECT YES 481'mysqltest_1'@'localhost' def test INSERT YES 482'mysqltest_1'@'localhost' def test UPDATE YES 483'mysqltest_1'@'localhost' def test DELETE YES 484'mysqltest_1'@'localhost' def test CREATE YES 485'mysqltest_1'@'localhost' def test DROP YES 486'mysqltest_1'@'localhost' def test REFERENCES YES 487'mysqltest_1'@'localhost' def test INDEX YES 488'mysqltest_1'@'localhost' def test ALTER YES 489'mysqltest_1'@'localhost' def test CREATE TEMPORARY TABLES YES 490'mysqltest_1'@'localhost' def test LOCK TABLES YES 491'mysqltest_1'@'localhost' def test EXECUTE YES 492'mysqltest_1'@'localhost' def test CREATE VIEW YES 493'mysqltest_1'@'localhost' def test SHOW VIEW YES 494'mysqltest_1'@'localhost' def test CREATE ROUTINE YES 495'mysqltest_1'@'localhost' def test ALTER ROUTINE YES 496'mysqltest_1'@'localhost' def test EVENT YES 497'mysqltest_1'@'localhost' def test TRIGGER YES 498'mysqltest_1'@'localhost' def test DELETE HISTORY YES 499select * from information_schema.TABLE_PRIVILEGES where grantee like '%mysqltest_1%'; 500GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 501'mysqltest_1'@'localhost' def test t1 SELECT NO 502'mysqltest_1'@'localhost' def test t1 INSERT NO 503'mysqltest_1'@'localhost' def test t1 UPDATE NO 504select * from information_schema.COLUMN_PRIVILEGES where grantee like '%mysqltest_1%'; 505GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 506'mysqltest_1'@'localhost' def test t1 a SELECT NO 507'mysqltest_1'@'localhost' def test t1 a INSERT NO 508'mysqltest_1'@'localhost' def test t1 a UPDATE NO 509'mysqltest_1'@'localhost' def test t1 a REFERENCES NO 510delete from mysql.user where user like 'mysqltest%'; 511delete from mysql.db where user like 'mysqltest%'; 512delete from mysql.tables_priv where user like 'mysqltest%'; 513delete from mysql.columns_priv where user like 'mysqltest%'; 514flush privileges; 515drop table t1; 516create table t1 (a int null, primary key(a)); 517alter table t1 add constraint constraint_1 unique (a); 518alter table t1 add constraint unique key_1(a); 519Warnings: 520Note 1831 Duplicate index `key_1`. This is deprecated and will be disallowed in a future release 521alter table t1 add constraint constraint_2 unique key_2(a); 522Warnings: 523Note 1831 Duplicate index `key_2`. This is deprecated and will be disallowed in a future release 524show create table t1; 525Table Create Table 526t1 CREATE TABLE `t1` ( 527 `a` int(11) NOT NULL, 528 PRIMARY KEY (`a`), 529 UNIQUE KEY `constraint_1` (`a`), 530 UNIQUE KEY `key_1` (`a`), 531 UNIQUE KEY `key_2` (`a`) 532) ENGINE=MyISAM DEFAULT CHARSET=latin1 533select * from information_schema.TABLE_CONSTRAINTS where 534TABLE_SCHEMA= "test"; 535CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE 536def test PRIMARY test t1 PRIMARY KEY 537def test constraint_1 test t1 UNIQUE 538def test key_1 test t1 UNIQUE 539def test key_2 test t1 UNIQUE 540select * from information_schema.KEY_COLUMN_USAGE where 541TABLE_SCHEMA= "test"; 542CONSTRAINT_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 543def test PRIMARY def test t1 a 1 NULL NULL NULL NULL 544def test constraint_1 def test t1 a 1 NULL NULL NULL NULL 545def test key_1 def test t1 a 1 NULL NULL NULL NULL 546def test key_2 def test t1 a 1 NULL NULL NULL NULL 547connection user2; 548select table_name from information_schema.TABLES where table_schema like "test%"; 549table_name 550t1 551select table_name,column_name from information_schema.COLUMNS where table_schema like "test%"; 552table_name column_name 553t1 a 554select ROUTINE_NAME from information_schema.ROUTINES; 555ROUTINE_NAME 556sel2 557sub1 558disconnect user2; 559connection default; 560delete from mysql.user where user='mysqltest_1'; 561drop table t1; 562drop procedure sel2; 563drop function sub1; 564create table t1(a int); 565create view v1 (c) as select a from t1 with check option; 566create view v2 (c) as select a from t1 WITH LOCAL CHECK OPTION; 567create view v3 (c) as select a from t1 WITH CASCADED CHECK OPTION; 568select * from information_schema.views; 569TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM 570def mysql user select `mysql`.`global_priv`.`Host` AS `Host`,`mysql`.`global_priv`.`User` AS `User`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `mysql`.`global_priv` NONE YES mariadb.sys@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED 571def test v1 select `test`.`t1`.`a` AS `c` from `test`.`t1` CASCADED YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED 572def test v2 select `test`.`t1`.`a` AS `c` from `test`.`t1` LOCAL YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED 573def test v3 select `test`.`t1`.`a` AS `c` from `test`.`t1` CASCADED YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED 574grant select (a) on test.t1 to joe@localhost with grant option; 575select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES; 576GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 577'joe'@'localhost' def test t1 a SELECT YES 578select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES; 579GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 580'mariadb.sys'@'localhost' def mysql global_priv SELECT NO 581'mariadb.sys'@'localhost' def mysql global_priv DELETE NO 582drop view v1, v2, v3; 583drop table t1; 584delete from mysql.user where user='joe'; 585delete from mysql.db where user='joe'; 586delete from mysql.tables_priv where user='joe'; 587delete from mysql.columns_priv where user='joe'; 588flush privileges; 589create table t1 (a int not null auto_increment,b int, primary key (a)); 590insert into t1 values (1,1),(NULL,3),(NULL,4); 591select AUTO_INCREMENT from information_schema.tables where table_name = 't1'; 592AUTO_INCREMENT 5934 594drop table t1; 595create table t1 (s1 int); 596insert into t1 values (0),(9),(0); 597select s1 from t1 where s1 in (select version from 598information_schema.tables) union select version from 599information_schema.tables; 600s1 60110 60211 603NULL 604drop table t1; 605SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets; 606Table Create Table 607CHARACTER_SETS CREATE TEMPORARY TABLE `CHARACTER_SETS` ( 608 `CHARACTER_SET_NAME` varchar(32) NOT NULL, 609 `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL, 610 `DESCRIPTION` varchar(60) NOT NULL, 611 `MAXLEN` bigint(3) NOT NULL 612) ENGINE=MEMORY DEFAULT CHARSET=utf8 613set names latin2; 614SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets; 615Table Create Table 616CHARACTER_SETS CREATE TEMPORARY TABLE `CHARACTER_SETS` ( 617 `CHARACTER_SET_NAME` varchar(32) NOT NULL, 618 `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL, 619 `DESCRIPTION` varchar(60) NOT NULL, 620 `MAXLEN` bigint(3) NOT NULL 621) ENGINE=MEMORY DEFAULT CHARSET=utf8 622set names latin1; 623create table t1 select * from information_schema.CHARACTER_SETS 624where CHARACTER_SET_NAME like "latin1"; 625select * from t1; 626CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN 627latin1 latin1_swedish_ci cp1252 West European 1 628alter table t1 default character set utf8; 629show create table t1; 630Table Create Table 631t1 CREATE TABLE `t1` ( 632 `CHARACTER_SET_NAME` varchar(32) NOT NULL, 633 `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL, 634 `DESCRIPTION` varchar(60) NOT NULL, 635 `MAXLEN` bigint(3) NOT NULL 636) ENGINE=MyISAM DEFAULT CHARSET=utf8 637drop table t1; 638create view v1 as select * from information_schema.TABLES; 639drop view v1; 640create table t1(a NUMERIC(5,3), b NUMERIC(5,1), c float(5,2), 641d NUMERIC(6,4), e float, f DECIMAL(6,3), g int(11), h DOUBLE(10,3), 642i DOUBLE); 643select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, 644CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE 645from information_schema.columns where table_name= 't1'; 646COLUMN_NAME COLUMN_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE 647a decimal(5,3) NULL NULL 5 3 648b decimal(5,1) NULL NULL 5 1 649c float(5,2) NULL NULL 5 2 650d decimal(6,4) NULL NULL 6 4 651e float NULL NULL 12 NULL 652f decimal(6,3) NULL NULL 6 3 653g int(11) NULL NULL 10 0 654h double(10,3) NULL NULL 10 3 655i double NULL NULL 22 NULL 656drop table t1; 657create table t115 as select table_name, column_name, column_type 658from information_schema.columns where table_name = 'proc'; 659select * from t115; 660table_name column_name column_type 661proc db char(64) 662proc name char(64) 663proc type enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') 664proc specific_name char(64) 665proc language enum('SQL') 666proc sql_data_access enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') 667proc is_deterministic enum('YES','NO') 668proc security_type enum('INVOKER','DEFINER') 669proc param_list blob 670proc returns longblob 671proc body longblob 672proc definer char(141) 673proc created timestamp 674proc modified timestamp 675proc sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','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','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') 676proc comment text 677proc character_set_client char(32) 678proc collation_connection char(32) 679proc db_collation char(32) 680proc body_utf8 longblob 681proc aggregate enum('NONE','GROUP') 682drop table t115; 683create procedure p108 () begin declare c cursor for select data_type 684from information_schema.columns; open c; open c; end;// 685call p108()// 686ERROR 24000: Cursor is already open 687drop procedure p108; 688create view v1 as select A1.table_name from information_schema.TABLES A1 689where table_name= "user"; 690select * from v1; 691table_name 692user 693drop view v1; 694create view vo as select 'a' union select 'a'; 695show index from vo; 696Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 697select * from information_schema.TABLE_CONSTRAINTS where 698TABLE_NAME= "vo"; 699CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE 700select * from information_schema.KEY_COLUMN_USAGE where 701TABLE_NAME= "vo"; 702CONSTRAINT_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 703drop view vo; 704select TABLE_NAME,TABLE_TYPE,ENGINE 705from information_schema.tables 706where table_schema='information_schema' limit 2; 707TABLE_NAME TABLE_TYPE ENGINE 708ALL_PLUGINS SYSTEM VIEW Aria 709APPLICABLE_ROLES SYSTEM VIEW MEMORY 710show tables from information_schema like "T%"; 711Tables_in_information_schema (T%) 712TABLES 713TABLESPACES 714TABLE_CONSTRAINTS 715TABLE_PRIVILEGES 716TABLE_STATISTICS 717TRIGGERS 718create database information_schema; 719ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 720use information_schema; 721show full tables like "T%"; 722Tables_in_information_schema (T%) Table_type 723TABLES SYSTEM VIEW 724TABLESPACES SYSTEM VIEW 725TABLE_CONSTRAINTS SYSTEM VIEW 726TABLE_PRIVILEGES SYSTEM VIEW 727TABLE_STATISTICS SYSTEM VIEW 728TRIGGERS SYSTEM VIEW 729create table t1(a int); 730ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 731use test; 732show tables; 733Tables_in_test 734use information_schema; 735show tables like "T%"; 736Tables_in_information_schema (T%) 737TABLES 738TABLESPACES 739TABLE_CONSTRAINTS 740TABLE_PRIVILEGES 741TABLE_STATISTICS 742TRIGGERS 743select table_name from tables where table_name='user'; 744table_name 745user 746select column_name, privileges from columns 747where table_name='user' and column_name like '%o%'; 748column_name privileges 749Host select,insert,update,references 750Password select,insert,update,references 751Drop_priv select,insert,update,references 752Reload_priv select,insert,update,references 753Shutdown_priv select,insert,update,references 754Process_priv select,insert,update,references 755Show_db_priv select,insert,update,references 756Lock_tables_priv select,insert,update,references 757Show_view_priv select,insert,update,references 758Create_routine_priv select,insert,update,references 759Alter_routine_priv select,insert,update,references 760Delete_history_priv select,insert,update,references 761max_questions select,insert,update,references 762max_connections select,insert,update,references 763max_user_connections select,insert,update,references 764authentication_string select,insert,update,references 765password_expired select,insert,update,references 766is_role select,insert,update,references 767default_role select,insert,update,references 768use test; 769create function sub1(i int) returns int 770return i+1; 771create table t1(f1 int); 772create view v2 (c) as select f1 from t1; 773create view v3 (c) as select sub1(1); 774create table t4(f1 int, KEY f1_key (f1)); 775drop table t1; 776drop function sub1; 777select table_name from information_schema.views 778where table_schema='test'; 779table_name 780v2 781v3 782select table_name from information_schema.views 783where table_schema='test'; 784table_name 785v2 786v3 787select column_name from information_schema.columns 788where table_schema='test' and table_name='t4'; 789column_name 790f1 791select column_name from information_schema.columns 792where table_schema='test' and table_name='v2'; 793column_name 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 796select column_name from information_schema.columns 797where table_schema='test' and table_name='v3'; 798column_name 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 801select index_name from information_schema.statistics where table_schema='test'; 802index_name 803f1_key 804select constraint_name from information_schema.table_constraints 805where table_schema='test'; 806constraint_name 807show create view v2; 808View Create View character_set_client collation_connection 809v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `test`.`t1`.`f1` AS `c` from `t1` latin1 latin1_swedish_ci 810Warnings: 811Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 812show create table v3; 813View Create View character_set_client collation_connection 814v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `sub1`(1) AS `c` latin1 latin1_swedish_ci 815Warnings: 816Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 817drop view v2; 818drop view v3; 819drop table t4; 820select * from information_schema.table_names; 821ERROR 42S02: Unknown table 'table_names' in information_schema 822select column_type from information_schema.columns 823where table_schema="information_schema" and table_name="COLUMNS" and 824(column_name="character_set_name" or column_name="collation_name"); 825column_type 826varchar(32) 827varchar(32) 828select TABLE_ROWS from information_schema.tables where 829table_schema="information_schema" and table_name="COLUMNS"; 830TABLE_ROWS 831NULL 832select table_type from information_schema.tables 833where table_schema="mysql" and table_name="user"; 834table_type 835VIEW 836show open tables where `table` like "user"; 837Database Table In_use Name_locked 838mysql user 0 0 839show status where variable_name like "%database%"; 840Variable_name Value 841Acl_database_grants 2 842Com_show_databases 3 843show variables where variable_name like "skip_show_databas"; 844Variable_name Value 845show global status like "Threads_running"; 846Variable_name Value 847Threads_running # 848create table t1(f1 int); 849create table t2(f2 int); 850create view v1 as select * from t1, t2; 851set @got_val= (select count(*) from information_schema.columns); 852drop view v1; 853drop table t1, t2; 854use test; 855CREATE TABLE t_crashme ( f1 BIGINT); 856CREATE VIEW a1 (t_CRASHME) AS SELECT f1 FROM t_crashme GROUP BY f1; 857CREATE VIEW a2 AS SELECT t_CRASHME FROM a1; 858count(*) 85968 860drop view a2, a1; 861drop table t_crashme; 862select table_schema,table_name, column_name from 863information_schema.columns 864where data_type = 'longtext' and table_schema != 'performance_schema' 865order by binary table_name, ordinal_position; 866table_schema table_name column_name 867information_schema ALL_PLUGINS PLUGIN_DESCRIPTION 868information_schema CHECK_CONSTRAINTS CHECK_CLAUSE 869information_schema COLUMNS COLUMN_DEFAULT 870information_schema COLUMNS COLUMN_TYPE 871information_schema COLUMNS GENERATION_EXPRESSION 872information_schema EVENTS EVENT_DEFINITION 873information_schema OPTIMIZER_TRACE QUERY 874information_schema OPTIMIZER_TRACE TRACE 875information_schema PARAMETERS DTD_IDENTIFIER 876information_schema PARTITIONS PARTITION_EXPRESSION 877information_schema PARTITIONS SUBPARTITION_EXPRESSION 878information_schema PARTITIONS PARTITION_DESCRIPTION 879information_schema PLUGINS PLUGIN_DESCRIPTION 880information_schema PROCESSLIST INFO 881information_schema ROUTINES DTD_IDENTIFIER 882information_schema ROUTINES ROUTINE_DEFINITION 883information_schema ROUTINES ROUTINE_COMMENT 884information_schema SYSTEM_VARIABLES ENUM_VALUE_LIST 885information_schema TRIGGERS ACTION_CONDITION 886information_schema TRIGGERS ACTION_STATEMENT 887information_schema VIEWS VIEW_DEFINITION 888mysql global_priv Priv 889mysql user Password 890mysql user ssl_cipher 891mysql user x509_issuer 892mysql user x509_subject 893mysql user plugin 894mysql user authentication_string 895mysql user default_role 896select table_name, column_name, data_type from information_schema.columns 897where data_type = 'datetime' and table_name not like 'innodb_%' 898order by binary table_name, ordinal_position; 899table_name column_name data_type 900EVENTS EXECUTE_AT datetime 901EVENTS STARTS datetime 902EVENTS ENDS datetime 903EVENTS CREATED datetime 904EVENTS LAST_ALTERED datetime 905EVENTS LAST_EXECUTED datetime 906FILES CREATION_TIME datetime 907FILES LAST_UPDATE_TIME datetime 908FILES LAST_ACCESS_TIME datetime 909FILES CREATE_TIME datetime 910FILES UPDATE_TIME datetime 911FILES CHECK_TIME datetime 912PARTITIONS CREATE_TIME datetime 913PARTITIONS UPDATE_TIME datetime 914PARTITIONS CHECK_TIME datetime 915ROUTINES CREATED datetime 916ROUTINES LAST_ALTERED datetime 917TABLES CREATE_TIME datetime 918TABLES UPDATE_TIME datetime 919TABLES CHECK_TIME datetime 920TRIGGERS CREATED datetime 921event execute_at datetime 922event last_executed datetime 923event starts datetime 924event ends datetime 925SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A 926WHERE NOT EXISTS 927(SELECT * FROM INFORMATION_SCHEMA.COLUMNS B 928WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA 929AND A.TABLE_NAME = B.TABLE_NAME); 930COUNT(*) 9310 932create table t1 933( x_bigint BIGINT, 934x_integer INTEGER, 935x_smallint SMALLINT, 936x_decimal DECIMAL(5,3), 937x_numeric NUMERIC(5,3), 938x_real REAL, 939x_float FLOAT, 940x_double_precision DOUBLE PRECISION ); 941SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH 942FROM INFORMATION_SCHEMA.COLUMNS 943WHERE TABLE_NAME= 't1'; 944COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH 945x_bigint NULL NULL 946x_integer NULL NULL 947x_smallint NULL NULL 948x_decimal NULL NULL 949x_numeric NULL NULL 950x_real NULL NULL 951x_float NULL NULL 952x_double_precision NULL NULL 953drop table t1; 954grant select on test.* to mysqltest_4@localhost; 955connect user10261,localhost,mysqltest_4,,; 956connection user10261; 957SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS 958where COLUMN_NAME='TABLE_NAME' and table_name not like 'innodb%'; 959TABLE_NAME COLUMN_NAME PRIVILEGES 960CHECK_CONSTRAINTS TABLE_NAME select 961COLUMNS TABLE_NAME select 962COLUMN_PRIVILEGES TABLE_NAME select 963FILES TABLE_NAME select 964INDEX_STATISTICS TABLE_NAME select 965KEY_COLUMN_USAGE TABLE_NAME select 966PARTITIONS TABLE_NAME select 967REFERENTIAL_CONSTRAINTS TABLE_NAME select 968STATISTICS TABLE_NAME select 969TABLES TABLE_NAME select 970TABLE_CONSTRAINTS TABLE_NAME select 971TABLE_PRIVILEGES TABLE_NAME select 972TABLE_STATISTICS TABLE_NAME select 973VIEWS TABLE_NAME select 974connection default; 975disconnect user10261; 976delete from mysql.user where user='mysqltest_4'; 977delete from mysql.db where user='mysqltest_4'; 978flush privileges; 979create table t1 (i int, j int); 980create trigger trg1 before insert on t1 for each row 981begin 982if new.j > 10 then 983set new.j := 10; 984end if; 985end| 986create trigger trg2 before update on t1 for each row 987begin 988if old.i % 2 = 0 then 989set new.j := -1; 990end if; 991end| 992create trigger trg3 after update on t1 for each row 993begin 994if new.j = -1 then 995set @fired:= "Yes"; 996end if; 997end| 998show triggers; 999Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation 1000trg1 INSERT t1 begin 1001if new.j > 10 then 1002set new.j := 10; 1003end if; 1004end BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci 1005trg2 UPDATE t1 begin 1006if old.i % 2 = 0 then 1007set new.j := -1; 1008end if; 1009end BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci 1010trg3 UPDATE t1 begin 1011if new.j = -1 then 1012set @fired:= "Yes"; 1013end if; 1014end AFTER # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci 1015select * from information_schema.triggers where trigger_schema in ('mysql', 'information_schema', 'test', 'mysqltest'); 1016TRIGGER_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 1017def test trg1 INSERT def test t1 1 NULL begin 1018if new.j > 10 then 1019set new.j := 10; 1020end if; 1021end ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci 1022def test trg2 UPDATE def test t1 1 NULL begin 1023if old.i % 2 = 0 then 1024set new.j := -1; 1025end if; 1026end ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci 1027def test trg3 UPDATE def test t1 1 NULL begin 1028if new.j = -1 then 1029set @fired:= "Yes"; 1030end if; 1031end ROW AFTER NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci 1032drop trigger trg1; 1033drop trigger trg2; 1034drop trigger trg3; 1035drop table t1; 1036create database mysqltest; 1037create table mysqltest.t1 (f1 int, f2 int); 1038create table mysqltest.t2 (f1 int); 1039grant select (f1) on mysqltest.t1 to user1@localhost; 1040grant select on mysqltest.t2 to user2@localhost; 1041grant select on mysqltest.* to user3@localhost; 1042grant select on *.* to user4@localhost; 1043connect con1,localhost,user1,,mysqltest; 1044connect con2,localhost,user2,,mysqltest; 1045connect con3,localhost,user3,,mysqltest; 1046connect con4,localhost,user4,,; 1047connection con1; 1048select * from information_schema.column_privileges order by grantee; 1049GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 1050'user1'@'localhost' def mysqltest t1 f1 SELECT NO 1051select * from information_schema.table_privileges order by grantee; 1052GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 1053select * from information_schema.schema_privileges order by grantee; 1054GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE 1055select * from information_schema.user_privileges order by grantee; 1056GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 1057'user1'@'localhost' def USAGE NO 1058show grants; 1059Grants for user1@localhost 1060GRANT USAGE ON *.* TO `user1`@`localhost` 1061GRANT SELECT (f1) ON `mysqltest`.`t1` TO `user1`@`localhost` 1062connection con2; 1063select * from information_schema.column_privileges order by grantee; 1064GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 1065select * from information_schema.table_privileges order by grantee; 1066GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 1067'user2'@'localhost' def mysqltest t2 SELECT NO 1068select * from information_schema.schema_privileges order by grantee; 1069GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE 1070select * from information_schema.user_privileges order by grantee; 1071GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 1072'user2'@'localhost' def USAGE NO 1073show grants; 1074Grants for user2@localhost 1075GRANT USAGE ON *.* TO `user2`@`localhost` 1076GRANT SELECT ON `mysqltest`.`t2` TO `user2`@`localhost` 1077connection con3; 1078select * from information_schema.column_privileges order by grantee; 1079GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 1080select * from information_schema.table_privileges order by grantee; 1081GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 1082select * from information_schema.schema_privileges order by grantee; 1083GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE 1084'user3'@'localhost' def mysqltest SELECT NO 1085select * from information_schema.user_privileges order by grantee; 1086GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 1087'user3'@'localhost' def USAGE NO 1088show grants; 1089Grants for user3@localhost 1090GRANT USAGE ON *.* TO `user3`@`localhost` 1091GRANT SELECT ON `mysqltest`.* TO `user3`@`localhost` 1092connection con4; 1093select * from information_schema.column_privileges where grantee like '\'user%' 1094order by grantee; 1095GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 1096'user1'@'localhost' def mysqltest t1 f1 SELECT NO 1097select * from information_schema.table_privileges where grantee like '\'user%' 1098order by grantee; 1099GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 1100'user2'@'localhost' def mysqltest t2 SELECT NO 1101select * from information_schema.schema_privileges where grantee like '\'user%' 1102order by grantee; 1103GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE 1104'user3'@'localhost' def mysqltest SELECT NO 1105select * from information_schema.user_privileges where grantee like '\'user%' 1106order by grantee; 1107GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 1108'user1'@'localhost' def USAGE NO 1109'user2'@'localhost' def USAGE NO 1110'user3'@'localhost' def USAGE NO 1111'user4'@'localhost' def SELECT NO 1112show grants; 1113Grants for user4@localhost 1114GRANT SELECT ON *.* TO `user4`@`localhost` 1115connection default; 1116disconnect con1; 1117disconnect con2; 1118disconnect con3; 1119disconnect con4; 1120drop user user1@localhost, user2@localhost, user3@localhost, user4@localhost; 1121use test; 1122drop database mysqltest; 1123drop procedure if exists p1; 1124drop procedure if exists p2; 1125create procedure p1 () modifies sql data set @a = 5; 1126create procedure p2 () set @a = 5; 1127select sql_data_access from information_schema.routines 1128where specific_name like 'p%'; 1129sql_data_access 1130MODIFIES SQL DATA 1131CONTAINS SQL 1132drop procedure p1; 1133drop procedure p2; 1134show create database information_schema; 1135Database Create Database 1136information_schema CREATE DATABASE `information_schema` /*!40100 DEFAULT CHARACTER SET utf8 */ 1137create table t1(f1 LONGBLOB, f2 LONGTEXT); 1138select column_name,data_type,CHARACTER_OCTET_LENGTH, 1139CHARACTER_MAXIMUM_LENGTH 1140from information_schema.columns 1141where table_name='t1'; 1142column_name data_type CHARACTER_OCTET_LENGTH CHARACTER_MAXIMUM_LENGTH 1143f1 longblob 4294967295 4294967295 1144f2 longtext 4294967295 4294967295 1145drop table t1; 1146create table t1(f1 tinyint, f2 SMALLINT, f3 mediumint, f4 int, 1147f5 BIGINT, f6 BIT, f7 bit(64)); 1148select column_name, NUMERIC_PRECISION, NUMERIC_SCALE 1149from information_schema.columns 1150where table_name='t1'; 1151column_name NUMERIC_PRECISION NUMERIC_SCALE 1152f1 3 0 1153f2 5 0 1154f3 7 0 1155f4 10 0 1156f5 19 0 1157f6 1 NULL 1158f7 64 NULL 1159drop table t1; 1160create table t1 (f1 integer); 1161create trigger tr1 after insert on t1 for each row set @test_var=42; 1162use information_schema; 1163select trigger_schema, trigger_name from triggers where 1164trigger_name='tr1'; 1165trigger_schema trigger_name 1166test tr1 1167use test; 1168drop table t1; 1169create table t1 (a int not null, b int); 1170use information_schema; 1171select column_name, column_default from columns 1172where table_schema='test' and table_name='t1'; 1173column_name column_default 1174a NULL 1175b NULL 1176use test; 1177show columns from t1; 1178Field Type Null Key Default Extra 1179a int(11) NO NULL 1180b int(11) YES NULL 1181drop table t1; 1182CREATE TABLE t1 (a int); 1183CREATE TABLE t2 (b int); 1184SHOW TABLE STATUS FROM test 1185WHERE name IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 1186WHERE TABLE_SCHEMA='test' AND TABLE_TYPE='BASE TABLE'); 1187Name 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 Max_index_length Temporary 1188t1 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL # N 1189t2 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL # N 1190DROP TABLE t1,t2; 1191create table t1(f1 int); 1192create view v1 (c) as select f1 from t1; 1193connect con5,localhost,root,,*NO-ONE*; 1194select database(); 1195database() 1196NULL 1197show fields from test.v1; 1198Field Type Null Key Default Extra 1199c int(11) YES NULL 1200connection default; 1201disconnect con5; 1202drop view v1; 1203drop table t1; 1204alter database information_schema; 1205ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 1206drop database information_schema; 1207ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1208drop table information_schema.tables; 1209ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1210alter table information_schema.tables; 1211ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1212use information_schema; 1213create temporary table schemata(f1 char(10)); 1214ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1215CREATE PROCEDURE p1 () 1216BEGIN 1217SELECT 'foo' FROM DUAL; 1218END | 1219ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1220select ROUTINE_NAME from routines where ROUTINE_SCHEMA='information_schema'; 1221ROUTINE_NAME 1222grant all on information_schema.* to 'user1'@'localhost'; 1223ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1224grant select on information_schema.* to 'user1'@'localhost'; 1225ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1226use test; 1227create table t1(id int); 1228insert into t1(id) values (1); 1229select 1 from (select 1 from test.t1) a; 12301 12311 1232use information_schema; 1233select 1 from (select 1 from test.t1) a; 12341 12351 1236use test; 1237drop table t1; 1238create table t1 (f1 int(11)); 1239create view v1 as select * from t1; 1240drop table t1; 1241select table_type from information_schema.tables 1242where table_name="v1"; 1243table_type 1244VIEW 1245drop view v1; 1246create temporary table t1(f1 int, index(f1)); 1247show columns from t1; 1248Field Type Null Key Default Extra 1249f1 int(11) YES MUL NULL 1250describe t1; 1251Field Type Null Key Default Extra 1252f1 int(11) YES MUL NULL 1253show indexes from t1; 1254Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 1255t1 1 f1 1 f1 A NULL NULL NULL YES BTREE 1256drop table t1; 1257create table t1(f1 binary(32), f2 varbinary(64)); 1258select character_maximum_length, character_octet_length 1259from information_schema.columns where table_name='t1'; 1260character_maximum_length character_octet_length 126132 32 126264 64 1263drop table t1; 1264CREATE TABLE t1 (f1 BIGINT, f2 VARCHAR(20), f3 BIGINT); 1265INSERT INTO t1 SET f1 = 1, f2 = 'Schoenenbourg', f3 = 1; 1266CREATE FUNCTION func2() RETURNS BIGINT RETURN 1; 1267CREATE FUNCTION func1() RETURNS BIGINT 1268BEGIN 1269RETURN ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS); 1270END// 1271CREATE VIEW v1 AS SELECT 1 FROM t1 1272WHERE f3 = (SELECT func2 ()); 1273SELECT func1(); 1274func1() 12752 1276DROP TABLE t1; 1277DROP VIEW v1; 1278DROP FUNCTION func1; 1279DROP FUNCTION func2; 1280select column_type, group_concat(table_schema, '.', table_name), count(*) as num 1281from information_schema.columns where 1282table_schema='information_schema' and 1283(column_type = 'varchar(7)' or column_type = 'varchar(20)' 1284 or column_type = 'varchar(27)') 1285group by column_type order by num; 1286column_type group_concat(table_schema, '.', table_name) num 1287varchar(7) information_schema.ROUTINES,information_schema.VIEWS 2 1288varchar(20) information_schema.ALL_PLUGINS,information_schema.ALL_PLUGINS,information_schema.ALL_PLUGINS,information_schema.FILES,information_schema.FILES,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PROFILING 9 1289create table t1(f1 char(1) not null, f2 char(9) not null) 1290default character set utf8; 1291select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from 1292information_schema.columns where table_schema='test' and table_name = 't1'; 1293CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH 12941 3 12959 27 1296drop table t1; 1297use mysql; 1298INSERT INTO `proc` VALUES ('test','','PROCEDURE','','SQL','CONTAINS_SQL', 1299'NO','DEFINER','','','BEGIN\r\n \r\nEND','root@%','2006-03-02 18:40:03', 1300'2006-03-02 18:40:03','','','utf8','utf8_general_ci','utf8_general_ci','n/a', 'NONE'); 1301select routine_name from information_schema.routines where ROUTINE_SCHEMA='test'; 1302routine_name 1303 1304delete from proc where name=''; 1305use test; 1306grant select on test.* to mysqltest_1@localhost; 1307create table t1 (id int); 1308create view v1 as select * from t1; 1309create definer = mysqltest_1@localhost 1310sql security definer view v2 as select 1; 1311connect con16681,localhost,mysqltest_1,,test; 1312connection con16681; 1313select * from information_schema.views 1314where table_name='v1' or table_name='v2' order by table_name; 1315TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM 1316def test v1 NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED 1317def test v2 select 1 AS `1` NONE NO mysqltest_1@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED 1318connection default; 1319disconnect con16681; 1320drop view v1, v2; 1321drop table t1; 1322drop user mysqltest_1@localhost; 1323set @a:= '.'; 1324create table t1(f1 char(5)); 1325create table t2(f1 char(5)); 1326select concat(@a, table_name), @a, table_name 1327from information_schema.tables where table_schema = 'test' order by table_name; 1328concat(@a, table_name) @a table_name 1329.t1 . t1 1330.t2 . t2 1331drop table t1,t2; 1332DROP PROCEDURE IF EXISTS p1; 1333DROP FUNCTION IF EXISTS f1; 1334CREATE PROCEDURE p1() SET @a= 1; 1335CREATE FUNCTION f1() RETURNS INT RETURN @a + 1; 1336CREATE USER mysql_bug20230@localhost; 1337GRANT EXECUTE ON PROCEDURE p1 TO mysql_bug20230@localhost; 1338GRANT EXECUTE ON FUNCTION f1 TO mysql_bug20230@localhost; 1339SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test'; 1340ROUTINE_NAME ROUTINE_DEFINITION 1341f1 RETURN @a + 1 1342p1 SET @a= 1 1343SHOW CREATE PROCEDURE p1; 1344Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 1345p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() 1346SET @a= 1 latin1 latin1_swedish_ci latin1_swedish_ci 1347SHOW CREATE FUNCTION f1; 1348Function sql_mode Create Function character_set_client collation_connection Database Collation 1349f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) 1350RETURN @a + 1 latin1 latin1_swedish_ci latin1_swedish_ci 1351connect conn1, localhost, mysql_bug20230,,; 1352SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test'; 1353ROUTINE_NAME ROUTINE_DEFINITION 1354f1 NULL 1355p1 NULL 1356SHOW CREATE PROCEDURE p1; 1357Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 1358p1 NULL latin1 latin1_swedish_ci latin1_swedish_ci 1359SHOW CREATE FUNCTION f1; 1360Function sql_mode Create Function character_set_client collation_connection Database Collation 1361f1 NULL latin1 latin1_swedish_ci latin1_swedish_ci 1362CALL p1(); 1363SELECT f1(); 1364f1() 13652 1366disconnect conn1; 1367connection default; 1368DROP FUNCTION f1; 1369DROP PROCEDURE p1; 1370DROP USER mysql_bug20230@localhost; 1371SELECT MAX(table_name) FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test') and table_name not like 'xtradb%'; 1372MAX(table_name) 1373VIEWS 1374SELECT table_name from information_schema.tables 1375WHERE table_name=(SELECT MAX(table_name) 1376FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test') and table_name not like 'xtradb%'); 1377table_name 1378VIEWS 1379DROP TABLE IF EXISTS bug23037; 1380DROP FUNCTION IF EXISTS get_value; 1381SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037'; 1382COLUMN_NAME MD5(COLUMN_DEFAULT) LENGTH(COLUMN_DEFAULT) 1383fld1 85ea6a55b8f0058e640b3de141a3a9d9 65534 1384SELECT MD5(get_value()); 1385MD5(get_value()) 138676176d2daa20c582375b8dcfc18033cd 1387SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT), COLUMN_DEFAULT=get_value() FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037'; 1388COLUMN_NAME MD5(COLUMN_DEFAULT) LENGTH(COLUMN_DEFAULT) COLUMN_DEFAULT=get_value() 1389fld1 85ea6a55b8f0058e640b3de141a3a9d9 65534 0 1390DROP TABLE bug23037; 1391DROP FUNCTION get_value; 1392set @tmp_optimizer_switch=@@optimizer_switch; 1393set optimizer_switch='derived_merge=off,derived_with_keys=off'; 1394create view v1 as 1395select table_schema as object_schema, 1396table_name as object_name, 1397table_type as object_type 1398from information_schema.tables 1399order by object_schema; 1400explain select * from v1; 1401id select_type table type possible_keys key key_len ref rows Extra 14021 SIMPLE tables ALL NULL NULL NULL NULL NULL Open_frm_only; Scanned all databases; Using filesort 1403explain select * from (select table_name from information_schema.tables) as a; 1404id select_type table type possible_keys key key_len ref rows Extra 14051 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 14062 DERIVED tables ALL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases 1407set optimizer_switch=@tmp_optimizer_switch; 1408drop view v1; 1409create table t1 (f1 int(11)); 1410create table t2 (f1 int(11), f2 int(11)); 1411select table_name from information_schema.tables 1412where table_schema = 'test' and table_name not in 1413(select table_name from information_schema.columns 1414where table_schema = 'test' and column_name = 'f3') 1415order by table_name; 1416table_name 1417t1 1418t2 1419drop table t1,t2; 1420create table t1(f1 int); 1421create view v1 as select f1+1 as a from t1; 1422create table t2 (f1 int, f2 int); 1423create view v2 as select f1+1 as a, f2 as b from t2; 1424select table_name, is_updatable from information_schema.views order by table_name; 1425table_name is_updatable 1426user YES 1427v1 NO 1428v2 YES 1429delete from v1; 1430drop view v1,v2; 1431drop table t1,t2; 1432alter database; 1433ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 1434alter database test; 1435ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 1436create database mysqltest; 1437create table mysqltest.t1(a int, b int, c int); 1438create trigger mysqltest.t1_ai after insert on mysqltest.t1 1439for each row set @a = new.a + new.b + new.c; 1440grant select(b) on mysqltest.t1 to mysqltest_1@localhost; 1441select trigger_name from information_schema.triggers 1442where event_object_table='t1'; 1443trigger_name 1444t1_ai 1445show triggers from mysqltest; 1446Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation 1447t1_ai INSERT t1 set @a = new.a + new.b + new.c AFTER # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci 1448connect con27629,localhost,mysqltest_1,,mysqltest; 1449show columns from t1; 1450Field Type Null Key Default Extra 1451b int(11) YES NULL 1452select column_name from information_schema.columns where table_name='t1'; 1453column_name 1454b 1455show triggers; 1456Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation 1457select trigger_name from information_schema.triggers 1458where event_object_table='t1'; 1459trigger_name 1460connection default; 1461disconnect con27629; 1462drop user mysqltest_1@localhost; 1463drop database mysqltest; 1464create table t1 ( 1465f1 varchar(50), 1466f2 varchar(50) not null, 1467f3 varchar(50) default '', 1468f4 varchar(50) default NULL, 1469f5 bigint not null, 1470f6 bigint not null default 10, 1471f7 datetime not null, 1472f8 datetime default '2006-01-01' 1473); 1474select column_default from information_schema.columns where table_name= 't1'; 1475column_default 1476NULL 1477NULL 1478'' 1479NULL 1480NULL 148110 1482NULL 1483'2006-01-01 00:00:00' 1484show columns from t1; 1485Field Type Null Key Default Extra 1486f1 varchar(50) YES NULL 1487f2 varchar(50) NO NULL 1488f3 varchar(50) YES 1489f4 varchar(50) YES NULL 1490f5 bigint(20) NO NULL 1491f6 bigint(20) NO 10 1492f7 datetime NO NULL 1493f8 datetime YES 2006-01-01 00:00:00 1494drop table t1; 1495show fields from information_schema.table_names; 1496ERROR 42S02: Unknown table 'table_names' in information_schema 1497show keys from information_schema.table_names; 1498ERROR 42S02: Unknown table 'table_names' in information_schema 1499USE information_schema; 1500SET max_heap_table_size = 16384; 1501CREATE TABLE test.t1( a INT ); 1502SELECT * 1503FROM tables ta 1504JOIN collations co ON ( co.collation_name = ta.table_catalog ) 1505JOIN character_sets cs ON ( cs.character_set_name = ta.table_catalog ); 1506TABLE_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 MAX_INDEX_LENGTH TEMPORARY COLLATION_NAME CHARACTER_SET_NAME ID IS_DEFAULT IS_COMPILED SORTLEN CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN 1507DROP TABLE test.t1; 1508SET max_heap_table_size = DEFAULT; 1509USE test; 1510End of 5.0 tests. 1511select * from information_schema.engines WHERE ENGINE="MyISAM"; 1512ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS 1513MyISAM DEFAULT Non-transactional engine with good performance and small data footprint NO NO NO 1514grant select on *.* to user3148@localhost; 1515connect con3148,localhost,user3148,,test; 1516connection con3148; 1517select user,db from information_schema.processlist; 1518user db 1519user3148 test 1520connection default; 1521disconnect con3148; 1522drop user user3148@localhost; 1523connect pslistcon,localhost,root,,test; 1524SELECT 'other connection here' AS who; 1525who 1526other connection here 1527connection default; 1528SELECT IF(`time` > 0, 'OK', `time`) AS time_low, 1529IF(`time` < 1000, 'OK', `time`) AS time_high, 1530IF(time_ms >= 1000, 'OK', time_ms) AS time_ms_low, 1531IF(time_ms < 1000000, 'OK', time_ms) AS time_ms_high 1532FROM INFORMATION_SCHEMA.PROCESSLIST 1533WHERE ID=@tid; 1534time_low time_high time_ms_low time_ms_high 1535OK OK OK OK 1536disconnect pslistcon; 1537DROP TABLE IF EXISTS server_status; 1538DROP EVENT IF EXISTS event_status; 1539SET GLOBAL event_scheduler=1; 1540CREATE EVENT event_status 1541ON SCHEDULE AT NOW() 1542ON COMPLETION NOT PRESERVE 1543DO 1544BEGIN 1545CREATE TABLE server_status 1546SELECT variable_name 1547FROM information_schema.global_status 1548WHERE variable_name LIKE 'ABORTED_CONNECTS' OR 1549variable_name LIKE 'BINLOG_CACHE_DISK_USE'; 1550END$$ 1551SELECT variable_name FROM server_status; 1552variable_name 1553ABORTED_CONNECTS 1554BINLOG_CACHE_DISK_USE 1555DROP TABLE server_status; 1556SET GLOBAL event_scheduler=0; 1557explain select table_name from information_schema.views where 1558table_schema='test' and table_name='v1'; 1559id select_type table type possible_keys key key_len ref rows Extra 15601 SIMPLE views ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL Using where; Open_frm_only; Scanned 0 databases 1561explain select * from information_schema.tables; 1562id select_type table type possible_keys key key_len ref rows Extra 15631 SIMPLE tables ALL NULL NULL NULL NULL NULL Open_full_table; Scanned all databases 1564explain select * from information_schema.collations; 1565id select_type table type possible_keys key key_len ref rows Extra 15661 SIMPLE collations ALL NULL NULL NULL NULL NULL 1567explain select * from information_schema.tables where 1568table_schema='test' and table_name= 't1'; 1569id select_type table type possible_keys key key_len ref rows Extra 15701 SIMPLE tables ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL Using where; Open_full_table; Scanned 0 databases 1571explain select table_name, table_type from information_schema.tables 1572where table_schema='test'; 1573id select_type table type possible_keys key key_len ref rows Extra 15741 SIMPLE tables ALL NULL TABLE_SCHEMA NULL NULL NULL Using where; Open_frm_only; Scanned 1 database 1575explain select b.table_name 1576from information_schema.tables a, information_schema.columns b 1577where a.table_name='t1' and a.table_schema='test' and b.table_name=a.table_name; 1578id select_type table type possible_keys key key_len ref rows Extra 15791 SIMPLE a ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL Using where; Skip_open_table; Scanned 0 databases 15801 SIMPLE b ALL NULL NULL NULL NULL NULL Using where; Open_frm_only; Scanned all databases; Using join buffer (flat, BNL join) 1581SELECT * FROM INFORMATION_SCHEMA.SCHEMATA 1582WHERE SCHEMA_NAME = 'mysqltest'; 1583CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 1584SELECT * FROM INFORMATION_SCHEMA.SCHEMATA 1585WHERE SCHEMA_NAME = ''; 1586CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 1587SELECT * FROM INFORMATION_SCHEMA.SCHEMATA 1588WHERE SCHEMA_NAME = 'test'; 1589CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 1590def test latin1 latin1_swedish_ci NULL 1591select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME='nonexisting'; 1592count(*) 15930 1594select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME=''; 1595count(*) 15960 1597select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME=''; 1598count(*) 15990 1600select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME='nonexisting'; 1601count(*) 16020 1603CREATE VIEW v1 1604AS SELECT * 1605FROM information_schema.tables; 1606SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'v1'; 1607VIEW_DEFINITION 1608select `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`,`information_schema`.`tables`.`MAX_INDEX_LENGTH` AS `MAX_INDEX_LENGTH`,`information_schema`.`tables`.`TEMPORARY` AS `TEMPORARY` from `information_schema`.`tables` 1609DROP VIEW v1; 1610SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA 1611WHERE SCHEMA_NAME ='information_schema'; 1612SCHEMA_NAME 1613information_schema 1614SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES 1615WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db'; 1616TABLE_COLLATION 1617utf8_bin 1618select * from information_schema.columns where table_schema = NULL; 1619TABLE_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 IS_GENERATED GENERATION_EXPRESSION 1620select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL; 1621TABLE_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 IS_GENERATED GENERATION_EXPRESSION 1622select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL; 1623CONSTRAINT_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 1624select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL; 1625CONSTRAINT_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 1626select * from `information_schema`.`PARTITIONS` where `TABLE_SCHEMA` = NULL; 1627TABLE_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 1628select * from `information_schema`.`PARTITIONS` where `TABLE_NAME` = NULL; 1629TABLE_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 1630select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL; 1631CONSTRAINT_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 1632select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL; 1633CONSTRAINT_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 1634select * from information_schema.schemata where schema_name = NULL; 1635CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 1636select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL; 1637TABLE_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 1638select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL; 1639TABLE_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 1640select * from information_schema.tables where table_schema = NULL; 1641TABLE_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 MAX_INDEX_LENGTH TEMPORARY 1642select * from information_schema.tables where table_catalog = NULL; 1643TABLE_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 MAX_INDEX_LENGTH TEMPORARY 1644select * from information_schema.tables where table_name = NULL; 1645TABLE_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 MAX_INDEX_LENGTH TEMPORARY 1646select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL; 1647CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE 1648select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL; 1649CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE 1650select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_SCHEMA` = NULL; 1651TRIGGER_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 1652select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_TABLE` = NULL; 1653TRIGGER_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 1654select * from `information_schema`.`VIEWS` where `TABLE_SCHEMA` = NULL; 1655TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM 1656select * from `information_schema`.`VIEWS` where `TABLE_NAME` = NULL; 1657TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM 1658explain extended select 1 from information_schema.tables; 1659id select_type table type possible_keys key key_len ref rows filtered Extra 16601 SIMPLE tables ALL NULL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases 1661Warnings: 1662Note 1003 select 1 AS `1` from `information_schema`.`tables` 1663use information_schema; 1664show events; 1665Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 1666show events from information_schema; 1667Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 1668show events where Db= 'information_schema'; 1669Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 1670use test; 1671# 1672# Bug#34166 Server crash in SHOW OPEN TABLES and prelocking 1673# 1674drop table if exists t1; 1675drop function if exists f1; 1676create table t1 (a int); 1677create function f1() returns int 1678begin 1679insert into t1 (a) values (1); 1680return 0; 1681end| 1682show open tables where f1()=0; 1683show open tables where f1()=0; 1684drop table t1; 1685drop function f1; 1686connect conn1, localhost, root,,; 1687connection conn1; 1688select * from information_schema.tables where 1=sleep(100000); 1689connection default; 1690connection conn1; 1691Got one of the listed errors 1692connection default; 1693disconnect conn1; 1694connect conn1, localhost, root,,; 1695connection conn1; 1696select * from information_schema.columns where 1=sleep(100000); 1697connection default; 1698connection conn1; 1699Got one of the listed errors 1700connection default; 1701disconnect conn1; 1702explain select count(*) from information_schema.tables; 1703id select_type table type possible_keys key key_len ref rows Extra 17041 SIMPLE tables ALL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases 1705explain select count(*) from information_schema.columns; 1706id select_type table type possible_keys key key_len ref rows Extra 17071 SIMPLE columns ALL NULL NULL NULL NULL NULL Open_frm_only; Scanned all databases 1708explain select count(*) from information_schema.views; 1709id select_type table type possible_keys key key_len ref rows Extra 17101 SIMPLE views ALL NULL NULL NULL NULL NULL Open_frm_only; Scanned all databases 1711set global init_connect="drop table if exists t1;drop table if exists t1;\ 1712drop table if exists t1;drop table if exists t1;\ 1713drop table if exists t1;drop table if exists t1;\ 1714drop table if exists t1;drop table if exists t1;\ 1715drop table if exists t1;drop table if exists t1;\ 1716drop table if exists t1;drop table if exists t1;\ 1717drop table if exists t1;drop table if exists t1;\ 1718drop table if exists t1;drop table if exists t1;\ 1719drop table if exists t1;drop table if exists t1;\ 1720drop table if exists t1;drop table if exists t1;\ 1721drop table if exists t1;drop table if exists t1;\ 1722drop table if exists t1;drop table if exists t1;\ 1723drop table if exists t1;drop table if exists t1;\ 1724drop table if exists t1;drop table if exists t1;\ 1725drop table if exists t1;drop table if exists t1;\ 1726drop table if exists t1;drop table if exists t1;\ 1727drop table if exists t1;drop table if exists t1;\ 1728drop table if exists t1;drop table if exists t1;\ 1729drop table if exists t1;drop table if exists t1;\ 1730drop table if exists t1;drop table if exists t1;\ 1731drop table if exists t1;drop table if exists t1;"; 1732select * from information_schema.global_variables where variable_name='init_connect'; 1733VARIABLE_NAME VARIABLE_VALUE 1734INIT_CONNECT drop table if exists t1;drop table if exists t1; 1735drop table if exists t1;drop table if exists t1; 1736drop table if exists t1;drop table if exists t1; 1737drop table if exists t1;drop table if exists t1; 1738drop 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; 1755select * from information_schema.global_variables where variable_name like 'init%' order by variable_name; 1756VARIABLE_NAME VARIABLE_VALUE 1757INIT_CONNECT drop table if exists t1;drop table if exists t1; 1758drop table if exists t1;drop table if exists t1; 1759drop table if exists t1;drop table if exists t1; 1760drop table if exists t1;drop table if exists t1; 1761drop 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; 1778INIT_FILE 1779INIT_SLAVE 1780set global init_connect=""; 1781create table t0 select * from information_schema.global_status where VARIABLE_NAME='COM_SELECT'; 1782SELECT 1; 17831 17841 1785select a.VARIABLE_VALUE - b.VARIABLE_VALUE from t0 b, information_schema.global_status a 1786where a.VARIABLE_NAME = b.VARIABLE_NAME; 1787a.VARIABLE_VALUE - b.VARIABLE_VALUE 17882 1789drop table t0; 1790CREATE TABLE t1(a INT) KEY_BLOCK_SIZE=1; 1791SELECT CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; 1792CREATE_OPTIONS 1793key_block_size=1 1794DROP TABLE t1; 1795SET TIMESTAMP=@@TIMESTAMP + 10000000; 1796SELECT 'NOT_OK' AS TEST_RESULT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time < 0; 1797TEST_RESULT 1798SET TIMESTAMP=DEFAULT; 1799# 1800# Bug #50276: Security flaw in INFORMATION_SCHEMA.TABLES 1801# 1802CREATE DATABASE db1; 1803USE db1; 1804CREATE TABLE t1 (id INT); 1805CREATE USER nonpriv; 1806USE test; 1807connect nonpriv_con, localhost, nonpriv,,; 1808connection nonpriv_con; 1809# connected as nonpriv 1810# Should return 0 1811SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; 1812COUNT(*) 18130 1814USE INFORMATION_SCHEMA; 1815# Should return 0 1816SELECT COUNT(*) FROM TABLES WHERE TABLE_NAME='t1'; 1817COUNT(*) 18180 1819connection default; 1820# connected as root 1821disconnect nonpriv_con; 1822DROP USER nonpriv; 1823DROP TABLE db1.t1; 1824DROP DATABASE db1; 1825 1826Bug#54422 query with = 'variables' 1827 1828CREATE TABLE variables(f1 INT); 1829SELECT COLUMN_DEFAULT, TABLE_NAME 1830FROM INFORMATION_SCHEMA.COLUMNS 1831WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'variables'; 1832COLUMN_DEFAULT TABLE_NAME 1833NULL variables 1834DROP TABLE variables; 1835# 1836# Bug #53814: NUMERIC_PRECISION for unsigned bigint field is 19, 1837# should be 20 1838# 1839CREATE TABLE ubig (a BIGINT, b BIGINT UNSIGNED); 1840SELECT TABLE_NAME, COLUMN_NAME, NUMERIC_PRECISION 1841FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ubig'; 1842TABLE_NAME COLUMN_NAME NUMERIC_PRECISION 1843ubig a 19 1844ubig b 20 1845INSERT INTO ubig VALUES (0xFFFFFFFFFFFFFFFF,0xFFFFFFFFFFFFFFFF); 1846Warnings: 1847Warning 1264 Out of range value for column 'a' at row 1 1848SELECT length(CAST(b AS CHAR)) FROM ubig; 1849length(CAST(b AS CHAR)) 185020 1851DROP TABLE ubig; 1852select 1 from information_schema.tables where table_schema=repeat('a', 2000); 18531 1854grant usage on *.* to mysqltest_1@localhost; 1855connect con1, localhost, mysqltest_1,,; 1856connection con1; 1857select 1 from information_schema.tables where table_schema=repeat('a', 2000); 18581 1859connection default; 1860disconnect con1; 1861drop user mysqltest_1@localhost; 1862End of 5.1 tests. 1863# 1864# Additional test for WL#3726 "DDL locking for all metadata objects" 1865# To avoid possible deadlocks process of filling of I_S tables should 1866# use high-priority metadata lock requests when opening tables. 1867# Below we just test that we really use high-priority lock request 1868# since reproducing a deadlock will require much more complex test. 1869# 1870drop tables if exists t1, t2, t3; 1871create table t1 (i int); 1872create table t2 (j int primary key auto_increment); 1873connect con3726_1,localhost,root,,test; 1874connection con3726_1; 1875lock table t2 read; 1876connect con3726_2,localhost,root,,test; 1877connection con3726_2; 1878# RENAME below will be blocked by 'lock table t2 read' above but 1879# will add two pending requests for exclusive metadata locks. 1880rename table t2 to t3; 1881connection default; 1882# These statements should not be blocked by pending lock requests 1883select table_name, column_name, data_type from information_schema.columns 1884where table_schema = 'test' and table_name in ('t1', 't2') order by table_name, column_name; 1885table_name column_name data_type 1886t1 i int 1887t2 j int 1888select table_name, auto_increment from information_schema.tables 1889where table_schema = 'test' and table_name in ('t1', 't2') order by table_name; 1890table_name auto_increment 1891t1 NULL 1892t2 1 1893connection con3726_1; 1894unlock tables; 1895connection con3726_2; 1896connection default; 1897disconnect con3726_1; 1898disconnect con3726_2; 1899drop tables t1, t3; 1900EXPLAIN SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE; 1901id select_type table type possible_keys key key_len ref rows Extra 19021 SIMPLE KEY_COLUMN_USAGE ALL NULL NULL NULL NULL NULL Open_full_table; Scanned all databases 1903EXPLAIN SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='t1'; 1904id select_type table type possible_keys key key_len ref rows Extra 19051 SIMPLE PARTITIONS ALL NULL TABLE_NAME NULL NULL NULL Using where; Open_full_table; Scanned 1 database 1906EXPLAIN SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 1907WHERE CONSTRAINT_SCHEMA='test'; 1908id select_type table type possible_keys key key_len ref rows Extra 19091 SIMPLE REFERENTIAL_CONSTRAINTS ALL NULL CONSTRAINT_SCHEMA NULL NULL NULL Using where; Open_full_table; Scanned 1 database 1910EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 1911WHERE TABLE_NAME='t1' and TABLE_SCHEMA='test'; 1912id select_type table type possible_keys key key_len ref rows Extra 19131 SIMPLE TABLE_CONSTRAINTS ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL Using where; Open_full_table; Scanned 0 databases 1914EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TRIGGERS 1915WHERE EVENT_OBJECT_SCHEMA='test'; 1916id select_type table type possible_keys key key_len ref rows Extra 19171 SIMPLE TRIGGERS ALL NULL EVENT_OBJECT_SCHEMA NULL NULL NULL Using where; Open_frm_only; Scanned 1 database 1918create table information_schema.t1 (f1 INT); 1919ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1920drop table information_schema.t1; 1921ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1922drop temporary table if exists information_schema.t1; 1923ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1924create temporary table information_schema.t1 (f1 INT); 1925ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1926drop view information_schema.v1; 1927ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1928create view information_schema.v1; 1929ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1930create trigger mysql.trg1 after insert on information_schema.t1 for each row set @a=1; 1931ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1932create table t1 select * from information_schema.t1; 1933ERROR 42S02: Unknown table 't1' in information_schema 1934CREATE TABLE t1(f1 char(100)); 1935REPAIR TABLE t1, information_schema.tables; 1936ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1937CHECKSUM TABLE t1, information_schema.tables; 1938Table Checksum 1939test.t1 0 1940information_schema.tables 0 1941ANALYZE TABLE t1, information_schema.tables; 1942ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1943CHECK TABLE t1, information_schema.tables; 1944Table Op Msg_type Msg_text 1945test.t1 check status OK 1946information_schema.tables check note The storage engine for the table doesn't support check 1947OPTIMIZE TABLE t1, information_schema.tables; 1948ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1949RENAME TABLE v1 to v2, information_schema.tables to t2; 1950ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1951DROP TABLE t1, information_schema.tables; 1952ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1953LOCK TABLES t1 READ, information_schema.tables READ; 1954ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 1955DROP TABLE t1; 1956SELECT * 1957FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 1958LEFT JOIN INFORMATION_SCHEMA.COLUMNS 1959USING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) 1960WHERE COLUMNS.TABLE_SCHEMA = 'test' 1961AND COLUMNS.TABLE_NAME = 't1'; 1962TABLE_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 IS_GENERATED GENERATION_EXPRESSION 1963# 1964# A test case for Bug#56540 "Exception (crash) in sql_show.cc 1965# during rqg_info_schema test on Windows" 1966# Ensure that we never access memory of a closed table, 1967# in particular, never access table->field[] array. 1968# Before the fix, the below test case, produced 1969# valgrind errors. 1970# 1971drop table if exists t1; 1972drop view if exists v1; 1973create table t1 (a int, b int); 1974create view v1 as select t1.a, t1.b from t1; 1975alter table t1 change b c int; 1976lock table t1 read; 1977connect con1, localhost, root,,; 1978connection con1; 1979flush tables; 1980flush tables t1; 1981connection default; 1982select * from information_schema.views where table_schema='test'; 1983TABLE_CATALOG def 1984TABLE_SCHEMA test 1985TABLE_NAME v1 1986VIEW_DEFINITION select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` 1987CHECK_OPTION NONE 1988IS_UPDATABLE 1989DEFINER root@localhost 1990SECURITY_TYPE DEFINER 1991CHARACTER_SET_CLIENT latin1 1992COLLATION_CONNECTION latin1_swedish_ci 1993ALGORITHM UNDEFINED 1994Warnings: 1995Level Warning 1996Code 1356 1997Message View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 1998unlock tables; 1999# 2000# Cleanup. 2001# 2002connection con1; 2003# Reaping 'flush tables' 2004disconnect con1; 2005connection default; 2006drop table t1; 2007drop view v1; 2008# 2009# Test for bug #12828477 - "MDL SUBSYSTEM CREATES BIG OVERHEAD FOR 2010# CERTAIN QUERIES TO INFORMATION_SCHEMA". 2011# 2012# Check that metadata locks which are acquired during the process 2013# of opening tables/.FRMs/.TRG files while filling I_S table are 2014# not kept to the end of statement. Keeping the locks has caused 2015# performance problems in cases when big number of tables (.FRMs 2016# or .TRG files) were scanned as cost of new lock acquisition has 2017# increased linearly. 2018drop database if exists mysqltest; 2019create database mysqltest; 2020use mysqltest; 2021create table t0 (i int); 2022create table t1 (j int); 2023create table t2 (k int); 2024# 2025# Test that we don't keep locks in case when we to fill 2026# I_S table we perform full-blown table open. 2027# 2028# Acquire lock on 't2' so upcoming RENAME is 2029# blocked. 2030lock tables t2 read; 2031connect con12828477_1, localhost, root,,mysqltest; 2032# The below RENAME should wait on 't2' while 2033# keeping X lock on 't1'. 2034rename table t1 to t3, t2 to t1, t3 to t2; 2035connect con12828477_2, localhost, root,,mysqltest; 2036# Wait while the above RENAME is blocked. 2037# Issue query to I_S which will open 't0' and get 2038# blocked on 't1' because of RENAME. 2039select table_name, auto_increment from information_schema.tables where table_schema='mysqltest' and table_name='t0' union select table_name, auto_increment from information_schema.tables where table_schema='mysqltest' and table_name<>'t0' order by table_name; 2040connect con12828477_3, localhost, root,,mysqltest; 2041# Wait while the above SELECT is blocked. 2042# 2043# Check that it holds no lock on 't0' so it can be renamed. 2044rename table t0 to t4; 2045connection default; 2046# 2047# Unblock the first RENAME. 2048unlock tables; 2049connection con12828477_1; 2050# Reap the first RENAME 2051connection con12828477_2; 2052# Reap SELECT to I_S. 2053table_name auto_increment 2054t0 NULL 2055t1 NULL 2056t2 NULL 2057connection default; 2058# 2059# Now test that we don't keep locks in case when we to fill 2060# I_S table we read .FRM or .TRG file only (this was the case 2061# for which problem existed). 2062# 2063rename table t4 to t0; 2064# Acquire lock on 't2' so upcoming RENAME is 2065# blocked. 2066lock tables t2 read; 2067connection con12828477_1; 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; 2071connection con12828477_2; 2072# Wait while the above RENAME is blocked. 2073# Issue query to I_S which will open 't0' and get 2074# blocked on 't1' because of RENAME. 2075select event_object_table, trigger_name from information_schema.triggers where event_object_schema='mysqltest'; 2076connection con12828477_3; 2077# Wait while the above SELECT is blocked. 2078# 2079# Check that it holds no lock on 't0' so it can be renamed. 2080rename table t0 to t4; 2081connection default; 2082# 2083# Unblock the first RENAME. 2084unlock tables; 2085connection con12828477_1; 2086# Reap the first RENAME 2087connection con12828477_2; 2088# Reap SELECT to I_S. 2089event_object_table trigger_name 2090connection default; 2091disconnect con12828477_1; 2092disconnect con12828477_2; 2093disconnect con12828477_3; 2094# 2095# MDEV-3818: Query against view over IS tables worse than equivalent query without view 2096# 2097create view v1 as select table_schema, table_name, column_name from information_schema.columns; 2098explain extended 2099select column_name from v1 2100where (table_schema = "osm") and (table_name = "test"); 2101id select_type table type possible_keys key key_len ref rows filtered Extra 21021 SIMPLE columns ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL NULL Using where; Open_frm_only; Scanned 0 databases 2103Warnings: 2104Note 1003 select `information_schema`.`columns`.`COLUMN_NAME` AS `column_name` from `information_schema`.`columns` where `information_schema`.`columns`.`TABLE_SCHEMA` = 'osm' and `information_schema`.`columns`.`TABLE_NAME` = 'test' 2105explain extended 2106select information_schema.columns.column_name as column_name 2107from information_schema.columns 2108where (information_schema.columns.table_schema = 'osm') and (information_schema.columns.table_name = 'test'); 2109id select_type table type possible_keys key key_len ref rows filtered Extra 21101 SIMPLE columns ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL NULL Using where; Open_frm_only; Scanned 0 databases 2111Warnings: 2112Note 1003 select `information_schema`.`columns`.`COLUMN_NAME` AS `column_name` from `information_schema`.`columns` where `information_schema`.`columns`.`TABLE_SCHEMA` = 'osm' and `information_schema`.`columns`.`TABLE_NAME` = 'test' 2113drop view v1; 2114# 2115# Clean-up. 2116drop database mysqltest; 2117# 2118# Test for bug #16869534 - "QUERYING SUBSET OF COLUMNS DOESN'T USE TABLE 2119# CACHE; OPENED_TABLES INCREASES" 2120# 2121SELECT * FROM INFORMATION_SCHEMA.TABLES; 2122SELECT VARIABLE_VALUE INTO @val1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE 2123VARIABLE_NAME LIKE 'Opened_tables'; 2124SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES; 2125# The below SELECT query should give same output as above SELECT query. 2126SELECT VARIABLE_VALUE INTO @val2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE 2127VARIABLE_NAME LIKE 'Opened_tables'; 2128# The below select should return '1' 2129SELECT @val1 = @val2; 2130@val1 = @val2 21311 2132# 2133# End of 5.5 tests 2134# 2135# 2136# MDEV-5723: mysqldump -uroot unusable for multi-database operations, checks all databases 2137# 2138drop database if exists db1; 2139connect con1,localhost,root,,; 2140connection con1; 2141create database db1; 2142use db1; 2143create table t1 (a int); 2144create table t2 (a int); 2145create table t3 (a int); 2146create database mysqltest; 2147use mysqltest; 2148create table t1 (a int); 2149create table t2 (a int); 2150create table t3 (a int); 2151flush tables; 2152flush status; 2153SELECT 2154LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA 2155FROM 2156INFORMATION_SCHEMA.FILES 2157WHERE 2158FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND 2159LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME 2160FROM INFORMATION_SCHEMA.FILES 2161WHERE 2162FILE_TYPE = 'DATAFILE' AND 2163TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME 2164FROM INFORMATION_SCHEMA.PARTITIONS 2165WHERE TABLE_SCHEMA IN ('db1') 2166) 2167) 2168GROUP BY 2169LOGFILE_GROUP_NAME, FILE_NAME, ENGINE 2170ORDER BY 2171LOGFILE_GROUP_NAME; 2172LOGFILE_GROUP_NAME FILE_NAME TOTAL_EXTENTS INITIAL_SIZE ENGINE EXTRA 2173# This must have Opened_tables=3, not 6. 2174show status like 'Opened_tables'; 2175Variable_name Value 2176Opened_tables 3 2177drop database mysqltest; 2178drop database db1; 2179connection default; 2180disconnect con1; 2181set global sql_mode=default; 2182USE test; 2183# 2184# End of 10.0 tests 2185# 2186# 2187# Start of 10.1 tests 2188# 2189# 2190# MDEV-13242 Wrong results for queries with row constructors and information_schema 2191# 2192CREATE TABLE tt1(c1 INT); 2193CREATE TABLE tt2(c2 INT); 2194SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt1', 'c1')); 2195count(*) 21961 2197SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt2', 'c2')); 2198count(*) 21991 2200SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt1','c1'),('tt2', 'c2')); 2201count(*) 22022 2203SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (SELECT 'tt1','c1' FROM dual UNION SELECT 'tt2', 'c2' FROM dual); 2204count(*) 22052 2206SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name='tt1' AND column_name='c1') OR (table_name='tt2' AND column_name='c2'); 2207count(*) 22082 2209SELECT column_name FROM information_schema.columns WHERE (table_name, column_name) IN (('tt1','c1'),('tt2', 'c2')) ORDER BY column_name; 2210column_name 2211c1 2212c2 2213DROP TABLE tt1, tt2; 2214# 2215# MDEV-13242 Wrong results for queries with row constructors and information_schema 2216# 2217SELECT SCHEMA_NAME from information_schema.schemata where schema_name='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'; 2218SCHEMA_NAME 2219SELECT SCHEMA_NAME from information_schema.schemata where schema_name=REPEAT('a',193); 2220SCHEMA_NAME 2221# 2222# End of 10.1 tests 2223# 2224# 2225# MDEV-14836: Assertion `m_status == DA_ERROR' failed in 2226# Diagnostics_area::sql_errno upon query from I_S with LIMIT ROWS EXAMINED 2227# 2228SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10; 2229TABLE_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 IS_GENERATED GENERATION_EXPRESSION 2230Warnings: 2231Warning 1931 Query execution was interrupted. The query examined at least ### rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete 2232# 2233# MDEV-24179: AAssertion `m_status == DA_ERROR || m_status == DA_OK || 2234# m_status == DA_OK_BULK' failed in Diagnostics_area::message() 2235# 2236call mtr.add_suppression("Sort aborted.*"); 2237DROP DATABASE test; 2238CREATE DATABASE test; 2239USE test; 2240CREATE VIEW v AS SELECT table_schema AS object_schema, table_name AS object_name, table_type AS object_type FROM information_schema.tables ORDER BY object_schema; 2241SELECT * FROM v LIMIT ROWS EXAMINED 9; 2242ERROR HY000: Sort aborted: 2243DROP VIEW v; 2244# 2245# MDEV-23408 Wrong result upon query from I_S and further Assertion `!alias_arg || strlen(alias_arg->str) == alias_arg->length' failed with certain connection charset 2246# 2247CREATE TABLE t (a INT); 2248SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=CONCAT('t',0x00,'1'); 2249TABLE_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 MAX_INDEX_LENGTH TEMPORARY 2250SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=CONCAT('test',0x00,'1'); 2251TABLE_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 MAX_INDEX_LENGTH TEMPORARY 2252DROP TABLE t; 2253CREATE TABLE `a/~.b` (a INT); 2254SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='a/~.b'; 2255TABLE_SCHEMA TABLE_NAME 2256test a/~.b 2257DROP TABLE `a/~.b`; 2258CREATE DATABASE `a/~.b`; 2259CREATE TABLE `a/~.b`.t1 (a INT); 2260SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='a/~.b'; 2261TABLE_SCHEMA TABLE_NAME 2262a/~.b t1 2263DROP DATABASE `a/~.b`; 2264# 2265# End of 10.2 Test 2266# 2267# 2268# MDEV-21201:No records produced in information_schema query, 2269# depending on projection 2270# 2271create table t (i int, constraint a check (i > 0)); 2272select 2273tc.TABLE_SCHEMA, 2274tc.TABLE_NAME, 2275cc.CONSTRAINT_NAME, 2276cc.CHECK_CLAUSE 2277from information_schema.TABLE_CONSTRAINTS tc 2278join information_schema.CHECK_CONSTRAINTS cc 2279using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) 2280; 2281TABLE_SCHEMA TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE 2282mysql global_priv Priv json_valid(`Priv`) 2283test t a `i` > 0 2284select 2285tc.TABLE_SCHEMA, 2286tc.TABLE_NAME, 2287cc.CONSTRAINT_NAME, 2288cc.CHECK_CLAUSE 2289from information_schema.CHECK_CONSTRAINTS cc 2290join information_schema.TABLE_CONSTRAINTS tc 2291using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) 2292; 2293TABLE_SCHEMA TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE 2294mysql global_priv Priv json_valid(`Priv`) 2295test t a `i` > 0 2296select 2297tc.TABLE_SCHEMA, 2298tc.TABLE_NAME, 2299cc.CONSTRAINT_NAME, 2300cc.CHECK_CLAUSE 2301from information_schema.TABLE_CONSTRAINTS tc 2302NATURAL join information_schema.CHECK_CONSTRAINTS cc 2303; 2304TABLE_SCHEMA TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE 2305mysql global_priv Priv json_valid(`Priv`) 2306test t a `i` > 0 2307select 2308tc.TABLE_SCHEMA, 2309tc.TABLE_NAME, 2310cc.CONSTRAINT_NAME, 2311cc.CHECK_CLAUSE 2312from information_schema.CHECK_CONSTRAINTS cc 2313NATURAL join information_schema.TABLE_CONSTRAINTS tc 2314; 2315TABLE_SCHEMA TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE 2316mysql global_priv Priv json_valid(`Priv`) 2317test t a `i` > 0 2318select 2319tc.TABLE_SCHEMA, 2320tc.TABLE_NAME, 2321cc.CONSTRAINT_NAME, 2322cc.CHECK_CLAUSE, 2323tc.CONSTRAINT_CATALOG, 2324tc.CONSTRAINT_SCHEMA 2325from information_schema.TABLE_CONSTRAINTS tc 2326join information_schema.CHECK_CONSTRAINTS cc 2327using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) 2328; 2329TABLE_SCHEMA TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE CONSTRAINT_CATALOG CONSTRAINT_SCHEMA 2330mysql global_priv Priv json_valid(`Priv`) def mysql 2331test t a `i` > 0 def test 2332drop table t; 2333# 2334# MDEV-24230 subquery on information_schema fails with error message 2335# 2336create table t1 (n int); 2337create table t2 (n int); 2338insert into t1 set n = (select table_rows from information_schema.tables where table_name='t2'); 2339drop table t1, t2; 2340# 2341# MDEV-24593 Signal 11 when group by primary key of table joined to information_schema.columns 2342# 2343create table t1 (f varchar(64) primary key); 2344select f from information_schema.columns i 2345inner join t1 on f=i.column_name 2346group by f; 2347f 2348drop table t1; 2349# 2350# MDEV-24929 Server crash in thr_multi_unlock or in 2351# get_schema_tables_result upon select from I_S with joins 2352# 2353CREATE TABLE t1 (a TIMESTAMP, KEY (a)); 2354INSERT INTO t1 VALUES ('2012-12-12'),('2021-11-11'); 2355SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.ROUTINES) ON (t1b.a IS NULL); 2356count(*) 23572 2358SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.PROFILING) ON (t1b.a IS NULL); 2359count(*) 23602 2361DROP TABLE t1; 2362# 2363# MDEV-24868 Server crashes in optimize_schema_tables_memory_usage after select from information_schema.innodb_sys_columns 2364# 2365create table t1 ( name varchar(64) character set utf8, len int); 2366select * from t1 where (name, len) in (select name, len from information_schema.innodb_sys_columns having len = 8); 2367name len 2368drop table t1; 2369# 2370# MDEV-20254 Problems with EMPTY_STRING_IS_NULL and I_S tables 2371# 2372SET SQL_MODE= 'EMPTY_STRING_IS_NULL'; 2373CREATE OR REPLACE TABLE t1 AS SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 0; 2374SHOW returned: CREATE TABLE `t1` ( 2375 `TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL 2376) ENGINE=MyISAM DEFAULT CHARSET=latin1 2377DROP TABLE t1; 2378CREATE TABLE `t1` ( 2379 `TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL 2380) ENGINE=MyISAM DEFAULT CHARSET=latin1; 2381SHOW CREATE TABLE t1; 2382Table Create Table 2383t1 CREATE TABLE `t1` ( 2384 `TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL 2385) ENGINE=MyISAM DEFAULT CHARSET=latin1 2386DROP TABLE t1; 2387SET SQL_MODE=DEFAULT; 2388SET SQL_MODE= 'EMPTY_STRING_IS_NULL'; 2389CREATE OR REPLACE TABLE t1 AS SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 0; 2390DROP TABLE t1; 2391# Executing the statement returned from SHOW CREATE TABLE 2392DROP TABLE t1; 2393SET SQL_MODE=DEFAULT; 2394# 2395# End of 10.3 tests 2396# 2397