set global sql_mode="";
set local sql_mode="";
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
DROP VIEW IF EXISTS v1;
show variables where variable_name like "skip_show_database";
Variable_name	Value
skip_show_database	OFF
grant select, update, execute on test.* to mysqltest_2@localhost;
grant select, update on test.* to mysqltest_1@localhost;
create user mysqltest_3@localhost;
create user mysqltest_3;
select * from information_schema.SCHEMATA where schema_name > 'm';
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH	SCHEMA_COMMENT
def	mtr	utf8mb4	utf8mb4_uca1400_ai_ci	NULL	
def	mysql	utf8mb4	utf8mb4_uca1400_ai_ci	NULL	
def	performance_schema	utf8mb3	utf8mb3_general_ci	NULL	
def	sys	utf8mb3	utf8mb3_general_ci	NULL	
def	test	utf8mb4	utf8mb4_uca1400_ai_ci	NULL	
select schema_name from information_schema.schemata;
schema_name
information_schema
mtr
mysql
performance_schema
sys
test
show databases like 't%';
Database (t%)
test
show databases;
Database
information_schema
mtr
mysql
performance_schema
sys
test
show databases where `database` = 't%';
Database
create database mysqltest;
create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
create table test.t2(a int);
create table t3(a int, KEY a_data (a));
create table mysqltest.t4(a int);
create table t5 (id int auto_increment primary key);
insert into t5 values (10);
create view v1 (c) as
SELECT table_name FROM information_schema.TABLES
WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND
table_name not like 'innodb_%' AND
table_name not like 'xtradb_%';
select * from v1;
c
ALL_PLUGINS
APPLICABLE_ROLES
CHARACTER_SETS
CHECK_CONSTRAINTS
CLIENT_STATISTICS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
COLUMN_PRIVILEGES
ENABLED_ROLES
ENGINES
EVENTS
FILES
GEOMETRY_COLUMNS
GLOBAL_STATUS
GLOBAL_VARIABLES
INDEX_STATISTICS
KEYWORDS
KEY_CACHES
KEY_COLUMN_USAGE
KEY_PERIOD_USAGE
OPTIMIZER_COSTS
OPTIMIZER_TRACE
PARAMETERS
PARTITIONS
PERIODS
PLUGINS
PROCESSLIST
PROFILING
REFERENTIAL_CONSTRAINTS
ROUTINES
SCHEMATA
SCHEMA_PRIVILEGES
SEQUENCES
SESSION_STATUS
SESSION_VARIABLES
SLAVE_STATUS
SPATIAL_REF_SYS
SQL_FUNCTIONS
STATISTICS
SYSTEM_VARIABLES
TABLES
TABLESPACES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLE_STATISTICS
TRIGGERS
USERS
USER_PRIVILEGES
USER_STATISTICS
VIEWS
column_stats
columns_priv
db
event
func
general_log
global_priv
gtid_slave_pos
help_category
help_keyword
help_relation
help_topic
index_stats
plugin
proc
procs_priv
proxies_priv
roles_mapping
servers
slow_log
t1
t2
t3
t4
t5
table_stats
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
transaction_registry
user
v1
select c,table_name from v1 
inner join information_schema.TABLES v2 on (v1.c=v2.table_name)
where v1.c like "t%";
c	table_name
TABLES	TABLES
TABLESPACES	TABLESPACES
TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
TABLE_PRIVILEGES	TABLE_PRIVILEGES
TABLE_STATISTICS	TABLE_STATISTICS
TRIGGERS	TRIGGERS
t1	t1
t2	t2
t3	t3
t4	t4
t5	t5
table_stats	table_stats
tables_priv	tables_priv
time_zone	time_zone
time_zone_leap_second	time_zone_leap_second
time_zone_name	time_zone_name
time_zone_transition	time_zone_transition
time_zone_transition_type	time_zone_transition_type
transaction_registry	transaction_registry
select c,table_name from v1 
left join information_schema.TABLES v2 on (v1.c=v2.table_name)
where v1.c like "t%";
c	table_name
TABLES	TABLES
TABLESPACES	TABLESPACES
TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
TABLE_PRIVILEGES	TABLE_PRIVILEGES
TABLE_STATISTICS	TABLE_STATISTICS
TRIGGERS	TRIGGERS
t1	t1
t2	t2
t3	t3
t4	t4
t5	t5
table_stats	table_stats
tables_priv	tables_priv
time_zone	time_zone
time_zone_leap_second	time_zone_leap_second
time_zone_name	time_zone_name
time_zone_transition	time_zone_transition
time_zone_transition_type	time_zone_transition_type
transaction_registry	transaction_registry
select c, v2.table_name from v1
right join information_schema.TABLES v2 on (v1.c=v2.table_name)
where v1.c like "t%";
c	table_name
TABLES	TABLES
TABLESPACES	TABLESPACES
TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
TABLE_PRIVILEGES	TABLE_PRIVILEGES
TABLE_STATISTICS	TABLE_STATISTICS
TRIGGERS	TRIGGERS
t1	t1
t2	t2
t3	t3
t4	t4
t5	t5
table_stats	table_stats
tables_priv	tables_priv
time_zone	time_zone
time_zone_leap_second	time_zone_leap_second
time_zone_name	time_zone_name
time_zone_transition	time_zone_transition
time_zone_transition_type	time_zone_transition_type
transaction_registry	transaction_registry
select table_name from information_schema.TABLES
where table_schema = "mysqltest" and table_name like "t%";
table_name
t1
t4
select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest";
TABLE_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	IGNORED
def	mysqltest	t1	1	mysqltest	string_data	1	b	A	NULL	NULL	NULL	YES	BTREE			NO
show keys from t3 where Key_name = "a_data";
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
t3	1	a_data	1	a	A	NULL	NULL	NULL	YES	BTREE			NO
show tables like 't%';
Tables_in_test (t%)
t2
t3
t5
show table status;
Name	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
t2	MyISAM	10	Fixed	0	0	0	#	1024	0	NULL	#	#	NULL	utf8mb4_uca1400_ai_ci	NULL			#	N
t3	MyISAM	10	Fixed	0	0	0	#	1024	0	NULL	#	#	NULL	utf8mb4_uca1400_ai_ci	NULL			#	N
t5	MyISAM	10	Fixed	1	7	7	#	2048	0	11	#	#	NULL	utf8mb4_uca1400_ai_ci	NULL			#	N
v1	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	NULL	NULL	#	#	NULL	NULL	NULL	NULL	VIEW	#	NULL
show full columns from t3 like "a%";
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
a	int(11)	NULL	YES	MUL	NULL		select,insert,update,references	
show full columns from mysql.db like "Insert%";
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
Insert_priv	enum('N','Y')	utf8mb3_general_ci	NO		N		select,insert,update,references	
show full columns from v1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
c	varchar(64)	utf8mb3_general_ci	NO		NULL		select,insert,update,references	
select * from information_schema.COLUMNS where table_name="t1"
and column_name= "a";
TABLE_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	IS_SYSTEM_TIME_PERIOD_START	IS_SYSTEM_TIME_PERIOD_END
def	mysqltest	t1	a	1	NULL	YES	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)			select,insert,update,references		NEVER	NULL	NO	NO
show columns from mysqltest.t1 where field like "%a%";
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
create view mysqltest.v1 (c) as select a from mysqltest.t1;
grant select (a) on mysqltest.t1 to mysqltest_2@localhost;
grant select on mysqltest.v1 to mysqltest_3;
connect  user3,localhost,mysqltest_2,,;
connection user3;
select table_name, column_name, privileges from information_schema.columns
where table_schema = 'mysqltest' and table_name = 't1';
table_name	column_name	privileges
t1	a	select
show columns from mysqltest.t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
connect  user4,localhost,mysqltest_3,,mysqltest;
connection user4;
select table_name, column_name, privileges from information_schema.columns
where table_schema = 'mysqltest' and table_name = 'v1';
table_name	column_name	privileges
v1	c	select
explain select * from v1;
ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
connection default;
disconnect user4;
drop view v1, mysqltest.v1;
drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
drop database mysqltest;
select * from information_schema.CHARACTER_SETS
where CHARACTER_SET_NAME like 'latin1%';
CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
latin1	latin1_swedish_ci	cp1252 West European	1
SHOW CHARACTER SET LIKE 'latin1%';
Charset	Description	Default collation	Maxlen
latin1	cp1252 West European	latin1_swedish_ci	1
SHOW CHARACTER SET WHERE charset like 'latin1%';
Charset	Description	Default collation	Maxlen
latin1	cp1252 West European	latin1_swedish_ci	1
select * from information_schema.COLLATIONS
where COLLATION_NAME like 'latin1%';
COLLATION_NAME	CHARACTER_SET_NAME	ID	IS_DEFAULT	IS_COMPILED	SORTLEN	COMMENT
latin1_german1_ci	latin1	5		#	1	cp1252 West European
latin1_swedish_ci	latin1	8	Yes	#	1	cp1252 West European
latin1_danish_ci	latin1	15		#	1	cp1252 West European
latin1_german2_ci	latin1	31		#	2	cp1252 West European
latin1_bin	latin1	47		#	1	cp1252 West European
latin1_general_ci	latin1	48		#	1	cp1252 West European
latin1_general_cs	latin1	49		#	1	cp1252 West European
latin1_spanish_ci	latin1	94		#	1	cp1252 West European
latin1_swedish_nopad_ci	latin1	1032		#	1	
latin1_nopad_bin	latin1	1071		#	1	
SHOW COLLATION LIKE 'latin1%';
Collation	Charset	Id	Default	Compiled	Sortlen
latin1_german1_ci	latin1	5		#	1
latin1_swedish_ci	latin1	8	Yes	#	1
latin1_danish_ci	latin1	15		#	1
latin1_german2_ci	latin1	31		#	2
latin1_bin	latin1	47		#	1
latin1_general_ci	latin1	48		#	1
latin1_general_cs	latin1	49		#	1
latin1_spanish_ci	latin1	94		#	1
latin1_swedish_nopad_ci	latin1	1032		#	1
latin1_nopad_bin	latin1	1071		#	1
SHOW COLLATION WHERE collation like 'latin1%';
Collation	Charset	Id	Default	Compiled	Sortlen
latin1_german1_ci	latin1	5		#	1
latin1_swedish_ci	latin1	8	Yes	#	1
latin1_danish_ci	latin1	15		#	1
latin1_german2_ci	latin1	31		#	2
latin1_bin	latin1	47		#	1
latin1_general_ci	latin1	48		#	1
latin1_general_cs	latin1	49		#	1
latin1_spanish_ci	latin1	94		#	1
latin1_swedish_nopad_ci	latin1	1032		#	1
latin1_nopad_bin	latin1	1071		#	1
select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
where COLLATION_NAME like 'latin1%';
COLLATION_NAME	CHARACTER_SET_NAME	FULL_COLLATION_NAME	ID	IS_DEFAULT
latin1_german1_ci	latin1	latin1_german1_ci	5	
latin1_swedish_ci	latin1	latin1_swedish_ci	8	Yes
latin1_danish_ci	latin1	latin1_danish_ci	15	
latin1_german2_ci	latin1	latin1_german2_ci	31	
latin1_bin	latin1	latin1_bin	47	
latin1_general_ci	latin1	latin1_general_ci	48	
latin1_general_cs	latin1	latin1_general_cs	49	
latin1_spanish_ci	latin1	latin1_spanish_ci	94	
latin1_swedish_nopad_ci	latin1	latin1_swedish_nopad_ci	1032	
latin1_nopad_bin	latin1	latin1_nopad_bin	1071	
drop procedure if exists sel2;
drop function if exists sub1;
drop function if exists sub2;
create function sub1(i int) returns int
return i+1;
create procedure sel2()
begin
select * from t1;
select * from t2;
end|
select parameter_style, sql_data_access, dtd_identifier
from information_schema.routines where routine_schema='test';
parameter_style	sql_data_access	dtd_identifier
SQL	CONTAINS SQL	NULL
SQL	CONTAINS SQL	int(11)
show procedure status where db='test';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test	sel2	PROCEDURE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
show function status where db='test';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test	sub1	FUNCTION	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
select a.ROUTINE_NAME from information_schema.ROUTINES a,
information_schema.SCHEMATA b where
a.ROUTINE_SCHEMA = b.SCHEMA_NAME AND b.SCHEMA_NAME='test';
ROUTINE_NAME
sel2
sub1
explain select a.ROUTINE_NAME from information_schema.ROUTINES a,
information_schema.SCHEMATA b where
a.ROUTINE_SCHEMA = b.SCHEMA_NAME;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	#	ALL	NULL	NULL	NULL	NULL	NULL	
1	SIMPLE	#	ALL	NULL	NULL	NULL	NULL	NULL	Using where; Using join buffer (flat, BNL join)
select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a,
mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) AND a.ROUTINE_SCHEMA='test' order by 1;
ROUTINE_NAME	name
sel2	sel2
sub1	sub1
select count(*) from information_schema.ROUTINES where routine_schema='test';
count(*)
2
create view v1 as select routine_schema, routine_name from information_schema.routines where routine_schema='test'
order by routine_schema, routine_name;
select * from v1;
routine_schema	routine_name
test	sel2
test	sub1
drop view v1;
connect  user1,localhost,mysqltest_1,,;
connection user1;
select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES WHERE ROUTINE_SCHEMA <> 'sys';
ROUTINE_NAME	ROUTINE_DEFINITION
show create function sub1;
ERROR 42000: FUNCTION sub1 does not exist
connection user3;
select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES WHERE ROUTINE_SCHEMA <> 'sys';
ROUTINE_NAME	ROUTINE_DEFINITION
sel2	NULL
sub1	NULL
connection default;
grant all privileges on test.* to mysqltest_1@localhost;
connect  user2,localhost,mysqltest_1,,;
connection user2;
select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES WHERE ROUTINE_SCHEMA <> 'sys';
ROUTINE_NAME	ROUTINE_DEFINITION
sel2	begin
select * from t1;
select * from t2;
end
sub1	return i+1
create function sub2(i int) returns int
return i+1;
select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES WHERE ROUTINE_SCHEMA <> 'sys';
ROUTINE_NAME	ROUTINE_DEFINITION
sel2	begin
select * from t1;
select * from t2;
end
sub1	return i+1
sub2	return i+1
show create procedure sel2;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
sel2		CREATE DEFINER=`root`@`localhost` PROCEDURE `sel2`()
begin
select * from t1;
select * from t2;
end	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
show create function sub1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
sub1		CREATE DEFINER=`root`@`localhost` FUNCTION `sub1`(i int) RETURNS int(11)
return i+1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
show create function sub2;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
sub2		CREATE DEFINER=`mysqltest_1`@`localhost` FUNCTION `sub2`(i int) RETURNS int(11)
return i+1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
show function status like "sub2";
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test	sub2	FUNCTION	mysqltest_1@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
connection default;
disconnect user1;
disconnect user3;
drop function sub2;
show create procedure sel2;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
sel2		CREATE DEFINER=`root`@`localhost` PROCEDURE `sel2`()
begin
select * from t1;
select * from t2;
end	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
create view v0 (c) as select schema_name from information_schema.schemata;
select * from v0;
c
information_schema
mtr
mysql
performance_schema
sys
test
explain select * from v0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	#	ALL	NULL	NULL	NULL	NULL	NULL	
create view v1 (c) as select table_name from information_schema.tables
where table_name="v1";
select * from v1;
c
v1
create view v2 (c) as select column_name from information_schema.columns
where table_name="v2";
select * from v2;
c
c
create view v3 (c) as select CHARACTER_SET_NAME from information_schema.character_sets
where CHARACTER_SET_NAME like "latin1%";
select * from v3;
c
latin1
create view v4 (c) as select COLLATION_NAME from information_schema.collations
where COLLATION_NAME like "latin1%";
select * from v4;
c
latin1_german1_ci
latin1_swedish_ci
latin1_danish_ci
latin1_german2_ci
latin1_bin
latin1_general_ci
latin1_general_cs
latin1_spanish_ci
latin1_swedish_nopad_ci
latin1_nopad_bin
show keys from v4;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
select * from information_schema.views where TABLE_NAME like "v%" AND TABLE_SCHEMA <> 'sys';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	ALGORITHM
def	test	v0	select `information_schema`.`schemata`.`SCHEMA_NAME` AS `c` from `information_schema`.`schemata`	NONE	NO	root@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
def	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
def	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
def	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
def	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
drop view v0, v1, v2, v3, v4;
create table t1 (a int);
grant select,update,insert on t1 to mysqltest_1@localhost;
grant select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost;
grant all on test.* to mysqltest_1@localhost with grant option;
select * from information_schema.USER_PRIVILEGES where grantee like '%mysqltest_1%';
GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_1'@'localhost'	def	USAGE	NO
select * from information_schema.SCHEMA_PRIVILEGES where grantee like '%mysqltest_1%';
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_1'@'localhost'	def	test	SELECT	YES
'mysqltest_1'@'localhost'	def	test	INSERT	YES
'mysqltest_1'@'localhost'	def	test	UPDATE	YES
'mysqltest_1'@'localhost'	def	test	DELETE	YES
'mysqltest_1'@'localhost'	def	test	CREATE	YES
'mysqltest_1'@'localhost'	def	test	DROP	YES
'mysqltest_1'@'localhost'	def	test	REFERENCES	YES
'mysqltest_1'@'localhost'	def	test	INDEX	YES
'mysqltest_1'@'localhost'	def	test	ALTER	YES
'mysqltest_1'@'localhost'	def	test	CREATE TEMPORARY TABLES	YES
'mysqltest_1'@'localhost'	def	test	LOCK TABLES	YES
'mysqltest_1'@'localhost'	def	test	EXECUTE	YES
'mysqltest_1'@'localhost'	def	test	CREATE VIEW	YES
'mysqltest_1'@'localhost'	def	test	SHOW VIEW	YES
'mysqltest_1'@'localhost'	def	test	CREATE ROUTINE	YES
'mysqltest_1'@'localhost'	def	test	ALTER ROUTINE	YES
'mysqltest_1'@'localhost'	def	test	EVENT	YES
'mysqltest_1'@'localhost'	def	test	TRIGGER	YES
'mysqltest_1'@'localhost'	def	test	DELETE HISTORY	YES
'mysqltest_1'@'localhost'	def	test	SHOW CREATE ROUTINE	YES
select * from information_schema.TABLE_PRIVILEGES where grantee like '%mysqltest_1%';
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_1'@'localhost'	def	test	t1	SELECT	NO
'mysqltest_1'@'localhost'	def	test	t1	INSERT	NO
'mysqltest_1'@'localhost'	def	test	t1	UPDATE	NO
select * from information_schema.COLUMN_PRIVILEGES where grantee like '%mysqltest_1%';
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_1'@'localhost'	def	test	t1	a	SELECT	NO
'mysqltest_1'@'localhost'	def	test	t1	a	INSERT	NO
'mysqltest_1'@'localhost'	def	test	t1	a	UPDATE	NO
'mysqltest_1'@'localhost'	def	test	t1	a	REFERENCES	NO
delete from mysql.user where user like 'mysqltest%';
delete from mysql.db where user like 'mysqltest%';
delete from mysql.tables_priv where user like 'mysqltest%';
delete from mysql.columns_priv where user like 'mysqltest%';
flush privileges;
drop table t1;
create table t1 (a int null, primary key(a));
alter table t1 add constraint constraint_1 unique (a);
alter table t1 add constraint unique key_1(a);
Warnings:
Note	1831	Duplicate index `key_1`. This is deprecated and will be disallowed in a future release
alter table t1 add constraint constraint_2 unique key_2(a);
Warnings:
Note	1831	Duplicate index `key_2`. This is deprecated and will be disallowed in a future release
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `constraint_1` (`a`),
  UNIQUE KEY `key_1` (`a`),
  UNIQUE KEY `key_2` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
select * from information_schema.TABLE_CONSTRAINTS where
TABLE_SCHEMA= "test";
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
def	test	PRIMARY	test	t1	PRIMARY KEY
def	test	constraint_1	test	t1	UNIQUE
def	test	key_1	test	t1	UNIQUE
def	test	key_2	test	t1	UNIQUE
select * from information_schema.KEY_COLUMN_USAGE where
TABLE_SCHEMA= "test";
CONSTRAINT_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
def	test	PRIMARY	def	test	t1	a	1	NULL	NULL	NULL	NULL
def	test	constraint_1	def	test	t1	a	1	NULL	NULL	NULL	NULL
def	test	key_1	def	test	t1	a	1	NULL	NULL	NULL	NULL
def	test	key_2	def	test	t1	a	1	NULL	NULL	NULL	NULL
connection user2;
select table_name from information_schema.TABLES where table_schema like "test%";
table_name
t1
select table_name,column_name from information_schema.COLUMNS where table_schema like "test%";
table_name	column_name
t1	a
select ROUTINE_NAME from information_schema.ROUTINES;
ROUTINE_NAME
sel2
sub1
disconnect user2;
connection default;
delete from mysql.user where user='mysqltest_1';
drop table t1;
drop procedure sel2;
drop function sub1;
create table t1(a int);
create view v1 (c) as select a from t1 with check option;
create view v2 (c) as select a from t1 WITH LOCAL CHECK OPTION;
create view v3 (c) as select a from t1 WITH CASCADED CHECK OPTION;
select * from information_schema.views where table_schema <> 'sys';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	ALGORITHM
def	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
def	test	v1	select `test`.`t1`.`a` AS `c` from `test`.`t1`	CASCADED	YES	root@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
def	test	v2	select `test`.`t1`.`a` AS `c` from `test`.`t1`	LOCAL	YES	root@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
def	test	v3	select `test`.`t1`.`a` AS `c` from `test`.`t1`	CASCADED	YES	root@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
grant select (a) on test.t1 to joe@localhost with grant option;
select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'joe'@'localhost'	def	test	t1	a	SELECT	YES
select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'mariadb.sys'@'localhost'	def	mysql	global_priv	SELECT	NO
'mariadb.sys'@'localhost'	def	mysql	global_priv	DELETE	NO
drop view v1, v2, v3;
drop table t1;
delete from mysql.user where user='joe';
delete from mysql.db where user='joe';
delete from mysql.tables_priv where user='joe';
delete from mysql.columns_priv where user='joe';
flush privileges;
create table t1 (a int not null auto_increment,b int, primary key (a));
insert into t1 values (1,1),(NULL,3),(NULL,4);
select AUTO_INCREMENT from information_schema.tables where table_name = 't1';
AUTO_INCREMENT
4
drop table t1;
create table t1 (s1 int);
insert into t1 values (0),(9),(0);
select s1 from t1 where s1 in (select version from
information_schema.tables) union select version from
information_schema.tables;
s1
10
11
NULL
drop table t1;
SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
Table	Create Table
CHARACTER_SETS	CREATE TEMPORARY TABLE `CHARACTER_SETS` (
  `CHARACTER_SET_NAME` varchar(32) NOT NULL,
  `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL,
  `DESCRIPTION` varchar(60) NOT NULL,
  `MAXLEN` bigint(3) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
set names latin2;
SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
Table	Create Table
CHARACTER_SETS	CREATE TEMPORARY TABLE `CHARACTER_SETS` (
  `CHARACTER_SET_NAME` varchar(32) NOT NULL,
  `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL,
  `DESCRIPTION` varchar(60) NOT NULL,
  `MAXLEN` bigint(3) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
set names latin1;
create table t1 select * from information_schema.CHARACTER_SETS
where CHARACTER_SET_NAME like "latin1";
select * from t1;
CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
latin1	latin1_swedish_ci	cp1252 West European	1
alter table t1 default character set utf8;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `CHARACTER_SET_NAME` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `DEFAULT_COLLATE_NAME` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `DESCRIPTION` varchar(60) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `MAXLEN` bigint(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
drop table t1;
create view v1 as select * from information_schema.TABLES;
drop view v1;
create table t1(a NUMERIC(5,3), b NUMERIC(5,1), c float(5,2),
d NUMERIC(6,4), e float, f DECIMAL(6,3), g int(11), h DOUBLE(10,3),
i DOUBLE);
select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
from information_schema.columns where table_name= 't1';
COLUMN_NAME	COLUMN_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE
a	decimal(5,3)	NULL	NULL	5	3
b	decimal(5,1)	NULL	NULL	5	1
c	float(5,2)	NULL	NULL	5	2
d	decimal(6,4)	NULL	NULL	6	4
e	float	NULL	NULL	12	NULL
f	decimal(6,3)	NULL	NULL	6	3
g	int(11)	NULL	NULL	10	0
h	double(10,3)	NULL	NULL	10	3
i	double	NULL	NULL	22	NULL
drop table t1;
create table t115 as select table_name, column_name, column_type
from information_schema.columns where table_name = 'proc';
select * from t115;
table_name	column_name	column_type
proc	db	char(64)
proc	name	char(64)
proc	type	enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY')
proc	specific_name	char(64)
proc	language	enum('SQL')
proc	sql_data_access	enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA')
proc	is_deterministic	enum('YES','NO')
proc	security_type	enum('INVOKER','DEFINER')
proc	param_list	blob
proc	returns	longblob
proc	body	longblob
proc	definer	varchar(384)
proc	created	timestamp
proc	modified	timestamp
proc	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')
proc	comment	text
proc	character_set_client	char(32)
proc	collation_connection	char(64)
proc	db_collation	char(64)
proc	body_utf8	longblob
proc	aggregate	enum('NONE','GROUP')
drop table t115;
create procedure p108 () begin declare c cursor for select data_type
from information_schema.columns;  open c; open c; end;//
call p108()//
ERROR 24000: Cursor is already open
drop procedure p108;
create view v1 as select A1.table_name from information_schema.TABLES A1
where table_name= "user";
select * from v1;
table_name
user
drop view v1;
create view vo as select 'a' union select 'a';
show index from vo;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
select * from information_schema.TABLE_CONSTRAINTS where
TABLE_NAME= "vo";
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
select * from information_schema.KEY_COLUMN_USAGE where
TABLE_NAME= "vo";
CONSTRAINT_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
drop view vo;
select TABLE_NAME,TABLE_TYPE,ENGINE
from information_schema.tables
where table_schema='information_schema' limit 2;
TABLE_NAME	TABLE_TYPE	ENGINE
ALL_PLUGINS	SYSTEM VIEW	Aria
APPLICABLE_ROLES	SYSTEM VIEW	MEMORY
show tables from information_schema like "T%";
Tables_in_information_schema (T%)
TABLES
TABLESPACES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLE_STATISTICS
TRIGGERS
create database information_schema;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
use information_schema;
show full tables like "T%";
Tables_in_information_schema (T%)	Table_type
TABLES	SYSTEM VIEW
TABLESPACES	SYSTEM VIEW
TABLE_CONSTRAINTS	SYSTEM VIEW
TABLE_PRIVILEGES	SYSTEM VIEW
TABLE_STATISTICS	SYSTEM VIEW
TRIGGERS	SYSTEM VIEW
create table t1(a int);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
use test;
show tables;
Tables_in_test
use information_schema;
show tables like "T%";
Tables_in_information_schema (T%)
TABLES
TABLESPACES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLE_STATISTICS
TRIGGERS
select table_name from tables where table_name='user';
table_name
user
select column_name, privileges from columns
where table_name='user' and column_name like '%o%';
column_name	privileges
Host	select,insert,update,references
Password	select,insert,update,references
Drop_priv	select,insert,update,references
Reload_priv	select,insert,update,references
Shutdown_priv	select,insert,update,references
Process_priv	select,insert,update,references
Show_db_priv	select,insert,update,references
Lock_tables_priv	select,insert,update,references
Show_view_priv	select,insert,update,references
Create_routine_priv	select,insert,update,references
Alter_routine_priv	select,insert,update,references
Delete_history_priv	select,insert,update,references
max_questions	select,insert,update,references
max_connections	select,insert,update,references
max_user_connections	select,insert,update,references
authentication_string	select,insert,update,references
password_expired	select,insert,update,references
is_role	select,insert,update,references
default_role	select,insert,update,references
use test;
create function sub1(i int) returns int
return i+1;
create table t1(f1 int);
create view v2 (c) as select f1 from t1;
create view v3 (c) as select sub1(1);
create table t4(f1 int, KEY f1_key (f1));
drop table t1;
drop function sub1;
select table_name from information_schema.views
where table_schema='test';
table_name
v2
v3
select table_name from information_schema.views
where table_schema='test';
table_name
v2
v3
select column_name from information_schema.columns
where table_schema='test' and table_name='t4';
column_name
f1
select column_name from information_schema.columns
where table_schema='test' and table_name='v2';
column_name
Warnings:
Warning	1356	View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
select column_name from information_schema.columns
where table_schema='test' and table_name='v3';
column_name
Warnings:
Warning	1356	View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
select index_name from information_schema.statistics where table_schema='test';
index_name
f1_key
select constraint_name from information_schema.table_constraints
where table_schema='test';
constraint_name
show create view v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `test`.`t1`.`f1` AS `c` from `t1`	latin1	latin1_swedish_ci
Warnings:
Warning	1356	View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
show create table v3;
View	Create View	character_set_client	collation_connection
v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `sub1`(1) AS `c`	latin1	latin1_swedish_ci
Warnings:
Warning	1356	View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
drop view v2;
drop view v3;
drop table t4;
select * from information_schema.table_names;
ERROR 42S02: Unknown table 'table_names' in information_schema
select column_type from information_schema.columns
where table_schema="information_schema" and table_name="COLUMNS" and
(column_name="character_set_name" or column_name="collation_name");
column_type
varchar(32)
varchar(64)
select TABLE_ROWS from information_schema.tables where
table_schema="information_schema" and table_name="COLUMNS";
TABLE_ROWS
NULL
select table_type from information_schema.tables
where table_schema="mysql" and table_name="user";
table_type
VIEW
show open tables where `table` like "user";
Database	Table	In_use	Name_locked
mysql	user	0	0
show status where variable_name like "%database%";
Variable_name	Value
Acl_database_grants	0
Com_show_databases	3
show variables where variable_name like "skip_show_databas";
Variable_name	Value
show global status like "Threads_running";
Variable_name	Value
Threads_running	#
create table t1(f1 int);
create table t2(f2 int);
create view v1 as select * from t1, t2;
set @got_val= (select count(*) from information_schema.columns);
drop view v1;
drop table t1, t2;
use test;
CREATE TABLE t_crashme ( f1 BIGINT);
CREATE VIEW a1 (t_CRASHME) AS SELECT f1 FROM t_crashme GROUP BY f1;
CREATE VIEW a2 AS SELECT t_CRASHME FROM a1;
count(*)
68
drop view a2, a1;
drop table t_crashme;
select table_schema,table_name, column_name from
information_schema.columns
where data_type = 'longtext' and table_schema != 'performance_schema'
order by binary table_name, ordinal_position;
table_schema	table_name	column_name
information_schema	ALL_PLUGINS	PLUGIN_DESCRIPTION
information_schema	CHECK_CONSTRAINTS	CHECK_CLAUSE
information_schema	COLUMNS	COLUMN_DEFAULT
information_schema	COLUMNS	COLUMN_TYPE
information_schema	COLUMNS	GENERATION_EXPRESSION
information_schema	EVENTS	EVENT_DEFINITION
information_schema	OPTIMIZER_TRACE	QUERY
information_schema	OPTIMIZER_TRACE	TRACE
information_schema	PARAMETERS	DTD_IDENTIFIER
information_schema	PARTITIONS	PARTITION_EXPRESSION
information_schema	PARTITIONS	SUBPARTITION_EXPRESSION
information_schema	PARTITIONS	PARTITION_DESCRIPTION
information_schema	PLUGINS	PLUGIN_DESCRIPTION
information_schema	PROCESSLIST	INFO
information_schema	ROUTINES	DTD_IDENTIFIER
information_schema	ROUTINES	ROUTINE_DEFINITION
information_schema	ROUTINES	ROUTINE_COMMENT
information_schema	SYSTEM_VARIABLES	ENUM_VALUE_LIST
information_schema	TRIGGERS	ACTION_CONDITION
information_schema	TRIGGERS	ACTION_STATEMENT
information_schema	VIEWS	VIEW_DEFINITION
mysql	global_priv	Priv
sys	innodb_lock_waits	waiting_query
sys	innodb_lock_waits	blocking_query
sys	processlist	current_statement
sys	processlist	last_statement
sys	schema_auto_increment_columns	column_type
sys	schema_table_lock_waits	waiting_query
mysql	servers	Options
sys	session	current_statement
sys	session	last_statement
sys	statement_analysis	query
sys	statements_with_errors_or_warnings	query
sys	statements_with_full_table_scans	query
sys	statements_with_runtimes_in_95th_percentile	query
sys	statements_with_sorting	query
sys	statements_with_temp_tables	query
mysql	user	Password
mysql	user	ssl_cipher
mysql	user	x509_issuer
mysql	user	x509_subject
mysql	user	plugin
mysql	user	authentication_string
mysql	user	default_role
sys	x$processlist	current_statement
sys	x$processlist	last_statement
sys	x$schema_table_lock_waits	waiting_query
sys	x$session	current_statement
sys	x$session	last_statement
sys	x$statement_analysis	query
sys	x$statements_with_errors_or_warnings	query
sys	x$statements_with_full_table_scans	query
sys	x$statements_with_runtimes_in_95th_percentile	query
sys	x$statements_with_sorting	query
sys	x$statements_with_temp_tables	query
select table_name, column_name, data_type from information_schema.columns
where data_type = 'datetime' and table_name not like 'innodb_%'
order by binary table_name, ordinal_position;
table_name	column_name	data_type
EVENTS	EXECUTE_AT	datetime
EVENTS	STARTS	datetime
EVENTS	ENDS	datetime
EVENTS	CREATED	datetime
EVENTS	LAST_ALTERED	datetime
EVENTS	LAST_EXECUTED	datetime
FILES	CREATION_TIME	datetime
FILES	LAST_UPDATE_TIME	datetime
FILES	LAST_ACCESS_TIME	datetime
FILES	CREATE_TIME	datetime
FILES	UPDATE_TIME	datetime
FILES	CHECK_TIME	datetime
PARTITIONS	CREATE_TIME	datetime
PARTITIONS	UPDATE_TIME	datetime
PARTITIONS	CHECK_TIME	datetime
ROUTINES	CREATED	datetime
ROUTINES	LAST_ALTERED	datetime
SLAVE_STATUS	Master_last_event_time	datetime
SLAVE_STATUS	Slave_last_event_time	datetime
TABLES	CREATE_TIME	datetime
TABLES	UPDATE_TIME	datetime
TABLES	CHECK_TIME	datetime
TRIGGERS	CREATED	datetime
USERS	PASSWORD_EXPIRATION_TIME	datetime
event	execute_at	datetime
event	last_executed	datetime
event	starts	datetime
event	ends	datetime
x$innodb_lock_waits	wait_started	datetime
x$innodb_lock_waits	waiting_trx_started	datetime
x$innodb_lock_waits	blocking_trx_started	datetime
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A
WHERE NOT EXISTS
(SELECT * FROM INFORMATION_SCHEMA.COLUMNS B
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
AND A.TABLE_NAME = B.TABLE_NAME);
COUNT(*)
0
create table t1
( x_bigint BIGINT,
x_integer INTEGER,
x_smallint SMALLINT,
x_decimal DECIMAL(5,3),
x_numeric NUMERIC(5,3),
x_real REAL,
x_float FLOAT,
x_double_precision DOUBLE PRECISION );
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME= 't1';
COLUMN_NAME	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH
x_bigint	NULL	NULL
x_integer	NULL	NULL
x_smallint	NULL	NULL
x_decimal	NULL	NULL
x_numeric	NULL	NULL
x_real	NULL	NULL
x_float	NULL	NULL
x_double_precision	NULL	NULL
drop table t1;
grant select on test.* to mysqltest_4@localhost;
connect  user10261,localhost,mysqltest_4,,;
connection user10261;
SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS 
where COLUMN_NAME='TABLE_NAME' and table_name not like 'innodb%';
TABLE_NAME	COLUMN_NAME	PRIVILEGES
CHECK_CONSTRAINTS	TABLE_NAME	select
COLUMNS	TABLE_NAME	select
COLUMN_PRIVILEGES	TABLE_NAME	select
FILES	TABLE_NAME	select
INDEX_STATISTICS	TABLE_NAME	select
KEY_COLUMN_USAGE	TABLE_NAME	select
KEY_PERIOD_USAGE	TABLE_NAME	select
PARTITIONS	TABLE_NAME	select
PERIODS	TABLE_NAME	select
REFERENTIAL_CONSTRAINTS	TABLE_NAME	select
STATISTICS	TABLE_NAME	select
TABLES	TABLE_NAME	select
TABLE_CONSTRAINTS	TABLE_NAME	select
TABLE_PRIVILEGES	TABLE_NAME	select
TABLE_STATISTICS	TABLE_NAME	select
VIEWS	TABLE_NAME	select
connection default;
disconnect user10261;
delete from mysql.user where user='mysqltest_4';
delete from mysql.db where user='mysqltest_4';
flush privileges;
create table t1 (i int, j int);
create trigger trg1 before insert on t1 for each row
begin
if new.j > 10 then
set new.j := 10;
end if;
end|
create trigger trg2 before update on t1 for each row
begin
if old.i % 2 = 0 then
set new.j := -1;
end if;
end|
create trigger trg3 after update on t1 for each row
begin
if new.j = -1 then
set @fired:= "Yes";
end if;
end|
show triggers;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
trg1	INSERT	t1	begin
if new.j > 10 then
set new.j := 10;
end if;
end	BEFORE	#		root@localhost	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
trg2	UPDATE	t1	begin
if old.i % 2 = 0 then
set new.j := -1;
end if;
end	BEFORE	#		root@localhost	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
trg3	UPDATE	t1	begin
if new.j = -1 then
set @fired:= "Yes";
end if;
end	AFTER	#		root@localhost	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
select * from information_schema.triggers where trigger_schema in ('mysql', 'information_schema', 'test', 'mysqltest');
TRIGGER_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
def	test	trg1	INSERT	def	test	t1	1	NULL	begin
if new.j > 10 then
set new.j := 10;
end if;
end	ROW	BEFORE	NULL	NULL	OLD	NEW	#		root@localhost	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
def	test	trg2	UPDATE	def	test	t1	1	NULL	begin
if old.i % 2 = 0 then
set new.j := -1;
end if;
end	ROW	BEFORE	NULL	NULL	OLD	NEW	#		root@localhost	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
def	test	trg3	UPDATE	def	test	t1	1	NULL	begin
if new.j = -1 then
set @fired:= "Yes";
end if;
end	ROW	AFTER	NULL	NULL	OLD	NEW	#		root@localhost	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
drop trigger trg1;
drop trigger trg2;
drop trigger trg3;
drop table t1;
create database mysqltest;
create table mysqltest.t1 (f1 int, f2 int);
create table mysqltest.t2 (f1 int);
grant select (f1) on mysqltest.t1 to user1@localhost;
grant select on mysqltest.t2 to user2@localhost;
grant select on mysqltest.* to user3@localhost;
grant select on *.* to user4@localhost;
connect  con1,localhost,user1,,mysqltest;
connect  con2,localhost,user2,,mysqltest;
connect  con3,localhost,user3,,mysqltest;
connect  con4,localhost,user4,,;
connection con1;
select * from information_schema.column_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'user1'@'localhost'	def	mysqltest	t1	f1	SELECT	NO
select * from information_schema.table_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
select * from information_schema.schema_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
select * from information_schema.user_privileges order by grantee;
GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
'user1'@'localhost'	def	USAGE	NO
show grants;
Grants for user1@localhost
GRANT USAGE ON *.* TO `user1`@`localhost`
GRANT SELECT (`f1`) ON `mysqltest`.`t1` TO `user1`@`localhost`
connection con2;
select * from information_schema.column_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
select * from information_schema.table_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'user2'@'localhost'	def	mysqltest	t2	SELECT	NO
select * from information_schema.schema_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
select * from information_schema.user_privileges order by grantee;
GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
'user2'@'localhost'	def	USAGE	NO
show grants;
Grants for user2@localhost
GRANT USAGE ON *.* TO `user2`@`localhost`
GRANT SELECT ON `mysqltest`.`t2` TO `user2`@`localhost`
connection con3;
select * from information_schema.column_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
select * from information_schema.table_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
select * from information_schema.schema_privileges order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
'user3'@'localhost'	def	mysqltest	SELECT	NO
select * from information_schema.user_privileges order by grantee;
GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
'user3'@'localhost'	def	USAGE	NO
show grants;
Grants for user3@localhost
GRANT USAGE ON *.* TO `user3`@`localhost`
GRANT SELECT ON `mysqltest`.* TO `user3`@`localhost`
connection con4;
select * from information_schema.column_privileges where grantee like '\'user%'
order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'user1'@'localhost'	def	mysqltest	t1	f1	SELECT	NO
select * from information_schema.table_privileges where grantee like '\'user%'
order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'user2'@'localhost'	def	mysqltest	t2	SELECT	NO
select * from information_schema.schema_privileges where grantee like '\'user%'
order by grantee;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
'user3'@'localhost'	def	mysqltest	SELECT	NO
select * from information_schema.user_privileges where grantee like '\'user%'
order by grantee;
GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
'user1'@'localhost'	def	USAGE	NO
'user2'@'localhost'	def	USAGE	NO
'user3'@'localhost'	def	USAGE	NO
'user4'@'localhost'	def	SELECT	NO
show grants;
Grants for user4@localhost
GRANT SELECT ON *.* TO `user4`@`localhost`
connection default;
disconnect con1;
disconnect con2;
disconnect con3;
disconnect con4;
drop user user1@localhost, user2@localhost, user3@localhost, user4@localhost;
use test;
drop database mysqltest;
drop procedure if exists p1;
drop procedure if exists p2;
create procedure p1 () modifies sql data set @a = 5;
create procedure p2 () set @a = 5;
select sql_data_access from information_schema.routines
where specific_name like 'p%' and routine_schema = 'test';
sql_data_access
MODIFIES SQL DATA
CONTAINS SQL
drop procedure p1;
drop procedure p2;
show create database information_schema;
Database	Create Database
information_schema	CREATE DATABASE `information_schema` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci */
create table t1(f1 LONGBLOB, f2 LONGTEXT);
select column_name,data_type,CHARACTER_OCTET_LENGTH,
CHARACTER_MAXIMUM_LENGTH
from information_schema.columns
where table_name='t1';
column_name	data_type	CHARACTER_OCTET_LENGTH	CHARACTER_MAXIMUM_LENGTH
f1	longblob	4294967295	4294967295
f2	longtext	4294967295	4294967295
drop table t1;
create table t1(f1 tinyint, f2 SMALLINT, f3 mediumint, f4 int,
f5 BIGINT, f6 BIT, f7 bit(64));
select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
from information_schema.columns
where table_name='t1';
column_name	NUMERIC_PRECISION	NUMERIC_SCALE
f1	3	0
f2	5	0
f3	7	0
f4	10	0
f5	19	0
f6	1	NULL
f7	64	NULL
drop table t1;
create table t1 (f1 integer);
create trigger tr1 after insert on t1 for each row set @test_var=42;
use information_schema;
select trigger_schema, trigger_name from triggers where
trigger_name='tr1';
trigger_schema	trigger_name
test	tr1
use test;
drop table t1;
create table t1 (a int not null, b int);
use information_schema;
select column_name, column_default from columns
where table_schema='test' and table_name='t1';
column_name	column_default
a	NULL
b	NULL
use test;
show columns from t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	NO		NULL	
b	int(11)	YES		NULL	
drop table t1;
CREATE TABLE t1 (a int);
CREATE TABLE t2 (b int);
SHOW TABLE STATUS FROM test
WHERE name IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='test' AND TABLE_TYPE='BASE TABLE');
Name	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
t1	MyISAM	10	Fixed	0	0	0	#	1024	0	NULL	#	#	NULL	utf8mb4_uca1400_ai_ci	NULL			#	N
t2	MyISAM	10	Fixed	0	0	0	#	1024	0	NULL	#	#	NULL	utf8mb4_uca1400_ai_ci	NULL			#	N
DROP TABLE t1,t2;
create table t1(f1 int);
create view v1 (c) as select f1 from t1;
connect  con5,localhost,root,,*NO-ONE*;
select database();
database()
NULL
show fields from test.v1;
Field	Type	Null	Key	Default	Extra
c	int(11)	YES		NULL	
connection default;
disconnect con5;
drop view v1;
drop table t1;
alter database information_schema;
ERROR 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
drop database information_schema;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
drop table information_schema.tables;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
alter table information_schema.tables;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
use information_schema;
create temporary table schemata(f1 char(10));
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE PROCEDURE p1 ()
BEGIN
SELECT 'foo' FROM DUAL;
END |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
select ROUTINE_NAME from routines where ROUTINE_SCHEMA='information_schema';
ROUTINE_NAME
grant all on information_schema.* to 'user1'@'localhost';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
grant select on information_schema.* to 'user1'@'localhost';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
use test;
create table t1(id int);
insert into t1(id) values (1);
select 1 from (select 1 from test.t1) a;
1
1
use information_schema;
select 1 from (select 1 from test.t1) a;
1
1
use test;
drop table t1;
create table t1 (f1 int(11));
create view v1 as select * from t1;
drop table t1;
select table_type from information_schema.tables
where table_name="v1";
table_type
VIEW
drop view v1;
create temporary table t1(f1 int, index(f1));
show columns from t1;
Field	Type	Null	Key	Default	Extra
f1	int(11)	YES	MUL	NULL	
describe t1;
Field	Type	Null	Key	Default	Extra
f1	int(11)	YES	MUL	NULL	
show indexes from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
t1	1	f1	1	f1	A	NULL	NULL	NULL	YES	BTREE			NO
drop table t1;
create table t1(f1 binary(32), f2 varbinary(64));
select character_maximum_length, character_octet_length
from information_schema.columns where table_name='t1';
character_maximum_length	character_octet_length
32	32
64	64
drop table t1;
CREATE TABLE t1 (f1 BIGINT, f2 VARCHAR(20), f3 BIGINT);
INSERT INTO t1 SET f1 = 1, f2 = 'Schoenenbourg', f3 = 1;
CREATE FUNCTION func2() RETURNS BIGINT RETURN 1;
CREATE FUNCTION func1() RETURNS BIGINT
BEGIN
RETURN ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA <> 'sys');
END//
CREATE VIEW v1 AS SELECT 1 FROM t1
WHERE f3 = (SELECT func2 ());
SELECT func1();
func1()
2
DROP TABLE t1;
DROP VIEW v1;
DROP FUNCTION func1;
DROP FUNCTION func2;
select column_type, group_concat(table_schema, '.', table_name), count(*) as num
from information_schema.columns where
table_schema='information_schema' and
(column_type = 'varchar(7)' or column_type = 'varchar(20)'
 or column_type = 'varchar(27)')
group by column_type order by num;
column_type	group_concat(table_schema, '.', table_name)	num
varchar(7)	information_schema.ROUTINES,information_schema.VIEWS,information_schema.SLAVE_STATUS	3
varchar(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
create table t1(f1 char(1) not null, f2 char(9) not null)
default character set utf8;
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
information_schema.columns where table_schema='test' and table_name = 't1';
CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH
1	3
9	27
drop table t1;
use mysql;
INSERT INTO `proc` VALUES ('test','','PROCEDURE','','SQL','CONTAINS_SQL',
'NO','DEFINER','','','BEGIN\r\n  \r\nEND','root@%','2006-03-02 18:40:03',
'2006-03-02 18:40:03','','','utf8','utf8_general_ci','utf8_general_ci','n/a', 'NONE');
select routine_name from information_schema.routines where ROUTINE_SCHEMA='test';
routine_name

delete from proc where name='';
use test;
grant select on test.* to mysqltest_1@localhost;
create table t1 (id int);
create view v1 as select * from t1;
create definer = mysqltest_1@localhost
sql security definer view v2 as select 1;
connect  con16681,localhost,mysqltest_1,,test;
connection con16681;
select * from information_schema.views
where table_name='v1' or table_name='v2' order by table_name;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	ALGORITHM
def	test	v1		NONE	YES	root@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
def	test	v2	select 1 AS `1`	NONE	NO	mysqltest_1@localhost	DEFINER	latin1	latin1_swedish_ci	UNDEFINED
connection default;
disconnect con16681;
drop view v1, v2;
drop table t1;
drop user mysqltest_1@localhost;
set @a:= '.';
create table t1(f1 char(5));
create table t2(f1 char(5));
select concat(@a, table_name), @a, table_name
from information_schema.tables where table_schema = 'test' order by table_name;
concat(@a, table_name)	@a	table_name
.t1	.	t1
.t2	.	t2
drop table t1,t2;
DROP PROCEDURE IF EXISTS p1;
DROP FUNCTION IF EXISTS f1;
CREATE PROCEDURE p1() SET @a= 1;
CREATE FUNCTION f1() RETURNS INT RETURN @a + 1;
CREATE USER mysql_bug20230@localhost;
GRANT EXECUTE ON PROCEDURE p1 TO mysql_bug20230@localhost;
GRANT EXECUTE ON FUNCTION f1 TO mysql_bug20230@localhost;
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test';
ROUTINE_NAME	ROUTINE_DEFINITION
f1	RETURN @a + 1
p1	SET @a= 1
SHOW CREATE PROCEDURE p1;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
p1		CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
SET @a= 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW CREATE FUNCTION f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1		CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
RETURN @a + 1	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
connect  conn1, localhost, mysql_bug20230,,;
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test';
ROUTINE_NAME	ROUTINE_DEFINITION
f1	NULL
p1	NULL
SHOW CREATE PROCEDURE p1;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
p1		NULL	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SHOW CREATE FUNCTION f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1		NULL	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
CALL p1();
SELECT f1();
f1()
2
disconnect conn1;
connection default;
DROP FUNCTION f1;
DROP PROCEDURE p1;
DROP USER mysql_bug20230@localhost;
SELECT MAX(table_name) FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test') and table_name not like 'xtradb%';
MAX(table_name)
VIEWS
SELECT table_name from information_schema.tables
WHERE table_name=(SELECT MAX(table_name)
FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test') and table_name not like 'xtradb%');
table_name
VIEWS
DROP TABLE IF EXISTS bug23037;
DROP FUNCTION IF EXISTS get_value;
SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';
COLUMN_NAME	MD5(COLUMN_DEFAULT)	LENGTH(COLUMN_DEFAULT)
fld1	85ea6a55b8f0058e640b3de141a3a9d9	65534
SELECT MD5(get_value());
MD5(get_value())
76176d2daa20c582375b8dcfc18033cd
SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT), COLUMN_DEFAULT=get_value() FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';
COLUMN_NAME	MD5(COLUMN_DEFAULT)	LENGTH(COLUMN_DEFAULT)	COLUMN_DEFAULT=get_value()
fld1	85ea6a55b8f0058e640b3de141a3a9d9	65534	0
DROP TABLE bug23037;
DROP FUNCTION get_value;
set @tmp_optimizer_switch=@@optimizer_switch;
set optimizer_switch='derived_merge=off,derived_with_keys=off';
create view v1 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;
explain select * from v1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases; Using filesort
explain select * from (select table_name from information_schema.tables) as a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	100	
2	DERIVED	tables	ALL	NULL	NULL	NULL	NULL	NULL	Skip_open_table; Scanned all databases
set optimizer_switch=@tmp_optimizer_switch;
drop view v1;
create table t1 (f1 int(11));
create table t2 (f1 int(11), f2 int(11));
select table_name from information_schema.tables
where table_schema = 'test' and table_name not in
(select table_name from information_schema.columns
where table_schema = 'test' and column_name = 'f3')
order by table_name;
table_name
t1
t2
drop table t1,t2;
create table t1(f1 int);
create view v1 as select f1+1 as a from t1;
create table t2 (f1 int, f2 int);
create view v2 as select f1+1 as a, f2 as b from t2;
select table_name, is_updatable from information_schema.views order by table_name;
table_name	is_updatable
host_summary	NO
host_summary_by_file_io	NO
host_summary_by_file_io_type	YES
host_summary_by_stages	YES
host_summary_by_statement_latency	NO
host_summary_by_statement_type	YES
innodb_buffer_stats_by_schema	NO
innodb_buffer_stats_by_table	NO
innodb_lock_waits	NO
io_by_thread_by_latency	NO
io_global_by_file_by_bytes	YES
io_global_by_file_by_latency	YES
io_global_by_wait_by_bytes	YES
io_global_by_wait_by_latency	YES
latest_file_io	YES
memory_by_host_by_current_bytes	NO
memory_by_thread_by_current_bytes	NO
memory_by_user_by_current_bytes	NO
memory_global_by_current_bytes	YES
memory_global_total	NO
metrics	NO
privileges_by_table_by_level	NO
processlist	NO
ps_check_lost_instrumentation	YES
schema_auto_increment_columns	NO
schema_index_statistics	YES
schema_object_overview	NO
schema_redundant_indexes	NO
schema_tables_with_full_table_scans	YES
schema_table_lock_waits	NO
schema_table_statistics	NO
schema_table_statistics_with_buffer	NO
schema_unused_indexes	YES
session	YES
session_ssl_status	YES
statements_with_errors_or_warnings	YES
statements_with_full_table_scans	YES
statements_with_runtimes_in_95th_percentile	YES
statements_with_sorting	YES
statements_with_temp_tables	YES
statement_analysis	YES
user	YES
user_summary	NO
user_summary_by_file_io	NO
user_summary_by_file_io_type	YES
user_summary_by_stages	YES
user_summary_by_statement_latency	NO
user_summary_by_statement_type	YES
v1	NO
v2	YES
version	NO
waits_by_host_by_latency	YES
waits_by_user_by_latency	YES
waits_global_by_latency	YES
wait_classes_global_by_avg_latency	NO
wait_classes_global_by_latency	NO
x$host_summary	NO
x$host_summary_by_file_io	NO
x$host_summary_by_file_io_type	YES
x$host_summary_by_stages	YES
x$host_summary_by_statement_latency	NO
x$host_summary_by_statement_type	YES
x$innodb_buffer_stats_by_schema	NO
x$innodb_buffer_stats_by_table	NO
x$innodb_lock_waits	NO
x$io_by_thread_by_latency	NO
x$io_global_by_file_by_bytes	YES
x$io_global_by_file_by_latency	YES
x$io_global_by_wait_by_bytes	YES
x$io_global_by_wait_by_latency	YES
x$latest_file_io	YES
x$memory_by_host_by_current_bytes	NO
x$memory_by_thread_by_current_bytes	NO
x$memory_by_user_by_current_bytes	NO
x$memory_global_by_current_bytes	YES
x$memory_global_total	NO
x$processlist	NO
x$ps_digest_95th_percentile_by_avg_us	NO
x$ps_digest_avg_latency_distribution	NO
x$ps_schema_table_statistics_io	NO
x$schema_flattened_keys	NO
x$schema_index_statistics	YES
x$schema_tables_with_full_table_scans	YES
x$schema_table_lock_waits	NO
x$schema_table_statistics	NO
x$schema_table_statistics_with_buffer	NO
x$session	YES
x$statements_with_errors_or_warnings	YES
x$statements_with_full_table_scans	YES
x$statements_with_runtimes_in_95th_percentile	YES
x$statements_with_sorting	YES
x$statements_with_temp_tables	YES
x$statement_analysis	YES
x$user_summary	NO
x$user_summary_by_file_io	NO
x$user_summary_by_file_io_type	YES
x$user_summary_by_stages	YES
x$user_summary_by_statement_latency	NO
x$user_summary_by_statement_type	YES
x$waits_by_host_by_latency	YES
x$waits_by_user_by_latency	YES
x$waits_global_by_latency	YES
x$wait_classes_global_by_avg_latency	NO
x$wait_classes_global_by_latency	NO
delete from v1;
drop view v1,v2;
drop table t1,t2;
alter database;
ERROR 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
alter database test;
ERROR 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
create database mysqltest;
create table mysqltest.t1(a int, b int, c int);
create trigger mysqltest.t1_ai after insert on mysqltest.t1
for each row set @a = new.a + new.b + new.c;
grant select(b) on mysqltest.t1 to mysqltest_1@localhost;
select trigger_name from information_schema.triggers
where event_object_table='t1';
trigger_name
t1_ai
show triggers from mysqltest;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
t1_ai	INSERT	t1	set @a = new.a + new.b + new.c	AFTER	#		root@localhost	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
connect  con27629,localhost,mysqltest_1,,mysqltest;
show columns from t1;
Field	Type	Null	Key	Default	Extra
b	int(11)	YES		NULL	
select column_name from information_schema.columns where table_name='t1';
column_name
b
show triggers;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
select trigger_name from information_schema.triggers
where event_object_table='t1';
trigger_name
connection default;
disconnect con27629;
drop user mysqltest_1@localhost;
drop database mysqltest;
create table t1 (
f1 varchar(50),
f2 varchar(50) not null,
f3 varchar(50) default '',
f4 varchar(50) default NULL,
f5 bigint not null,
f6 bigint not null default 10,
f7 datetime not null,
f8 datetime default '2006-01-01'
);
select column_default from information_schema.columns where table_name= 't1';
column_default
NULL
NULL
''
NULL
NULL
10
NULL
'2006-01-01 00:00:00'
show columns from t1;
Field	Type	Null	Key	Default	Extra
f1	varchar(50)	YES		NULL	
f2	varchar(50)	NO		NULL	
f3	varchar(50)	YES			
f4	varchar(50)	YES		NULL	
f5	bigint(20)	NO		NULL	
f6	bigint(20)	NO		10	
f7	datetime	NO		NULL	
f8	datetime	YES		2006-01-01 00:00:00	
drop table t1;
show fields from information_schema.table_names;
ERROR 42S02: Unknown table 'table_names' in information_schema
show keys from information_schema.table_names;
ERROR 42S02: Unknown table 'table_names' in information_schema
USE information_schema;
SET max_heap_table_size = 16384;
CREATE TABLE test.t1( a INT );
SELECT *
FROM tables ta
JOIN collations co ON ( co.collation_name = ta.table_catalog )
JOIN character_sets cs ON ( cs.character_set_name = ta.table_catalog );
TABLE_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	COMMENT	CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
DROP TABLE test.t1;
SET max_heap_table_size = DEFAULT;
USE test;
End of 5.0 tests.
select * from information_schema.engines WHERE ENGINE="MyISAM";
ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
MyISAM	DEFAULT	Non-transactional engine with good performance and small data footprint	NO	NO	NO
grant select on *.* to user3148@localhost;
connect  con3148,localhost,user3148,,test;
connection con3148;
select user,db from information_schema.processlist;
user	db
user3148	test
connection default;
disconnect con3148;
drop user user3148@localhost;
connect  pslistcon,localhost,root,,test;
SELECT 'other connection here' AS who;
who
other connection here
connection default;
SELECT IF(`time` > 0, 'OK', `time`) AS time_low,
IF(`time` < 1000, 'OK', `time`) AS time_high,
IF(time_ms >= 1000, 'OK', time_ms) AS time_ms_low,
IF(time_ms < 1000000, 'OK', time_ms) AS time_ms_high
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE ID=@tid;
time_low	time_high	time_ms_low	time_ms_high
OK	OK	OK	OK
disconnect pslistcon;
DROP TABLE IF EXISTS server_status;
DROP EVENT IF EXISTS event_status;
SET GLOBAL event_scheduler=1;
CREATE EVENT event_status
ON SCHEDULE AT NOW()
ON COMPLETION NOT PRESERVE
DO
BEGIN
CREATE TABLE server_status
SELECT variable_name
FROM information_schema.global_status
WHERE variable_name LIKE 'ABORTED_CONNECTS' OR
variable_name LIKE 'BINLOG_CACHE_DISK_USE';
END$$
SELECT variable_name FROM server_status;
variable_name
ABORTED_CONNECTS
BINLOG_CACHE_DISK_USE
DROP TABLE server_status;
SET GLOBAL event_scheduler=0;
explain select table_name from information_schema.views where
table_schema='test' and table_name='v1';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	views	ALL	NULL	TABLE_SCHEMA,TABLE_NAME	NULL	NULL	NULL	Using where; Open_frm_only; Scanned 0 databases
explain select * from information_schema.tables;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	NULL	Open_full_table; Scanned all databases
explain select * from information_schema.collations;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	collations	ALL	NULL	NULL	NULL	NULL	NULL	
explain select * from information_schema.tables where
table_schema='test' and table_name= 't1';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tables	ALL	NULL	TABLE_SCHEMA,TABLE_NAME	NULL	NULL	NULL	Using where; Open_full_table; Scanned 0 databases
explain select table_name, table_type from information_schema.tables
where table_schema='test';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tables	ALL	NULL	TABLE_SCHEMA	NULL	NULL	NULL	Using where; Open_frm_only; Scanned 1 database
explain select b.table_name
from information_schema.tables a, information_schema.columns b
where a.table_name='t1' and a.table_schema='test' and b.table_name=a.table_name;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	a	ALL	NULL	TABLE_SCHEMA,TABLE_NAME	NULL	NULL	NULL	Using where; Skip_open_table; Scanned 0 databases
1	SIMPLE	b	ALL	NULL	NULL	NULL	NULL	NULL	Using where; Open_frm_only; Scanned all databases; Using join buffer (flat, BNL join)
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'mysqltest';
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH	SCHEMA_COMMENT
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = '';
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH	SCHEMA_COMMENT
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'test';
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH	SCHEMA_COMMENT
def	test	utf8mb4	utf8mb4_uca1400_ai_ci	NULL	
select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME='nonexisting';
count(*)
0
select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME='';
count(*)
0
select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME='';
count(*)
0
select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME='nonexisting';
count(*)
0
CREATE VIEW v1
AS SELECT *
FROM information_schema.tables;
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'v1';
VIEW_DEFINITION
select `information_schema`.`tables`.`TABLE_CATALOG` AS `TABLE_CATALOG`,`information_schema`.`tables`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`tables`.`TABLE_NAME` AS `TABLE_NAME`,`information_schema`.`tables`.`TABLE_TYPE` AS `TABLE_TYPE`,`information_schema`.`tables`.`ENGINE` AS `ENGINE`,`information_schema`.`tables`.`VERSION` AS `VERSION`,`information_schema`.`tables`.`ROW_FORMAT` AS `ROW_FORMAT`,`information_schema`.`tables`.`TABLE_ROWS` AS `TABLE_ROWS`,`information_schema`.`tables`.`AVG_ROW_LENGTH` AS `AVG_ROW_LENGTH`,`information_schema`.`tables`.`DATA_LENGTH` AS `DATA_LENGTH`,`information_schema`.`tables`.`MAX_DATA_LENGTH` AS `MAX_DATA_LENGTH`,`information_schema`.`tables`.`INDEX_LENGTH` AS `INDEX_LENGTH`,`information_schema`.`tables`.`DATA_FREE` AS `DATA_FREE`,`information_schema`.`tables`.`AUTO_INCREMENT` AS `AUTO_INCREMENT`,`information_schema`.`tables`.`CREATE_TIME` AS `CREATE_TIME`,`information_schema`.`tables`.`UPDATE_TIME` AS `UPDATE_TIME`,`information_schema`.`tables`.`CHECK_TIME` AS `CHECK_TIME`,`information_schema`.`tables`.`TABLE_COLLATION` AS `TABLE_COLLATION`,`information_schema`.`tables`.`CHECKSUM` AS `CHECKSUM`,`information_schema`.`tables`.`CREATE_OPTIONS` AS `CREATE_OPTIONS`,`information_schema`.`tables`.`TABLE_COMMENT` AS `TABLE_COMMENT`,`information_schema`.`tables`.`MAX_INDEX_LENGTH` AS `MAX_INDEX_LENGTH`,`information_schema`.`tables`.`TEMPORARY` AS `TEMPORARY` from `information_schema`.`tables`
DROP VIEW v1;
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME ='information_schema';
SCHEMA_NAME
information_schema
SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
TABLE_COLLATION
utf8mb3_bin
select * from information_schema.columns where table_schema = NULL;
TABLE_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	IS_SYSTEM_TIME_PERIOD_START	IS_SYSTEM_TIME_PERIOD_END
select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL;
TABLE_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	IS_SYSTEM_TIME_PERIOD_START	IS_SYSTEM_TIME_PERIOD_END
select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;
CONSTRAINT_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
select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;
CONSTRAINT_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
select * from `information_schema`.`PARTITIONS` where `TABLE_SCHEMA` = NULL;
TABLE_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
select * from `information_schema`.`PARTITIONS` where `TABLE_NAME` = NULL;
TABLE_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
select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL;
CONSTRAINT_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
select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL;
CONSTRAINT_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
select * from information_schema.schemata where schema_name = NULL;
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH	SCHEMA_COMMENT
select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL;
TABLE_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	IGNORED
select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL;
TABLE_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	IGNORED
select * from information_schema.tables where table_schema = NULL;
TABLE_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
select * from information_schema.tables where table_catalog = NULL;
TABLE_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
select * from information_schema.tables where table_name = NULL;
TABLE_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
select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_SCHEMA` = NULL;
TRIGGER_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
select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_TABLE` = NULL;
TRIGGER_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
select * from `information_schema`.`VIEWS` where `TABLE_SCHEMA` = NULL;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	ALGORITHM
select * from `information_schema`.`VIEWS` where `TABLE_NAME` = NULL;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	ALGORITHM
explain extended select 1 from information_schema.tables;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Skip_open_table; Scanned all databases
Warnings:
Note	1003	select 1 AS `1` from `information_schema`.`tables`
use information_schema;
show events;
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
show events from information_schema;
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
show events where Db= 'information_schema';
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
use test;
#
# Bug#34166 Server crash in SHOW OPEN TABLES and prelocking
#
drop table if exists t1;
drop function if exists f1;
create table t1 (a int);
create function f1() returns int
begin
insert into t1 (a) values (1);
return 0;
end|
show open tables where f1()=0;
show open tables where f1()=0;
drop table t1;
drop function f1;
connect  conn1, localhost, root,,;
connection conn1;
select * from information_schema.tables where 1=sleep(100000);
connection default;
connection conn1;
Got one of the listed errors
connection default;
disconnect conn1;
connect  conn1, localhost, root,,;
connection conn1;
select * from information_schema.columns where 1=sleep(100000);
connection default;
connection conn1;
Got one of the listed errors
connection default;
disconnect conn1;
explain select count(*) from information_schema.tables;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	NULL	Skip_open_table; Scanned all databases
explain select count(*) from information_schema.columns;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	columns	ALL	NULL	NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases
explain select count(*) from information_schema.views;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	views	ALL	NULL	NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases
set global init_connect="drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;\
drop table if exists t1;drop table if exists t1;";
select * from information_schema.global_variables where variable_name='init_connect';
VARIABLE_NAME	VARIABLE_VALUE
INIT_CONNECT	drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
select * from information_schema.global_variables where variable_name like 'init%' order by variable_name;
VARIABLE_NAME	VARIABLE_VALUE
INIT_CONNECT	drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
drop table if exists t1;drop table if exists t1;
INIT_FILE	
INIT_SLAVE	
set global init_connect="";
create table t0 select * from information_schema.global_status where VARIABLE_NAME='COM_SELECT';
SELECT 1;
1
1
select a.VARIABLE_VALUE - b.VARIABLE_VALUE from t0 b, information_schema.global_status a
where a.VARIABLE_NAME = b.VARIABLE_NAME;
a.VARIABLE_VALUE - b.VARIABLE_VALUE
2
drop table t0;
CREATE TABLE t1(a INT) KEY_BLOCK_SIZE=1;
SELECT CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
CREATE_OPTIONS
key_block_size=1
DROP TABLE t1;
SET TIMESTAMP=@@TIMESTAMP + 10000000;
SELECT 'NOT_OK' AS TEST_RESULT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time < 0;
TEST_RESULT
SET TIMESTAMP=DEFAULT;
#
# Bug #50276: Security flaw in INFORMATION_SCHEMA.TABLES
#
CREATE DATABASE db1;
USE db1;
CREATE TABLE t1 (id INT);
CREATE USER nonpriv;
USE test;
connect  nonpriv_con, localhost, nonpriv,,;
connection nonpriv_con;
# connected as nonpriv
# Should return 0
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
COUNT(*)
0
USE INFORMATION_SCHEMA;
# Should return 0
SELECT COUNT(*) FROM TABLES WHERE TABLE_NAME='t1';
COUNT(*)
0
connection default;
# connected as root
disconnect nonpriv_con;
DROP USER nonpriv;
DROP TABLE db1.t1;
DROP DATABASE db1;

Bug#54422 query with = 'variables'

CREATE TABLE variables(f1 INT);
SELECT COLUMN_DEFAULT, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'variables';
COLUMN_DEFAULT	TABLE_NAME
NULL	variables
DROP TABLE variables;
#
# Bug #53814: NUMERIC_PRECISION for unsigned bigint field is 19, 
# should be 20
#
CREATE TABLE ubig (a BIGINT, b BIGINT UNSIGNED);
SELECT TABLE_NAME, COLUMN_NAME, NUMERIC_PRECISION 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ubig';
TABLE_NAME	COLUMN_NAME	NUMERIC_PRECISION
ubig	a	19
ubig	b	20
INSERT INTO ubig VALUES (0xFFFFFFFFFFFFFFFF,0xFFFFFFFFFFFFFFFF);
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
SELECT length(CAST(b AS CHAR)) FROM ubig;
length(CAST(b AS CHAR))
20
DROP TABLE ubig;
select 1 from information_schema.tables where table_schema=repeat('a', 2000);
1
grant usage on *.* to mysqltest_1@localhost;
connect  con1, localhost, mysqltest_1,,;
connection con1;
select 1 from information_schema.tables where table_schema=repeat('a', 2000);
1
connection default;
disconnect con1;
drop user mysqltest_1@localhost;
End of 5.1 tests.
#
# Additional test for WL#3726 "DDL locking for all metadata objects"
# To avoid possible deadlocks process of filling of I_S tables should
# use high-priority metadata lock requests when opening tables.
# Below we just test that we really use high-priority lock request
# since reproducing a deadlock will require much more complex test.
#
drop tables if exists t1, t2, t3;
create table t1 (i int);
create table t2 (j int primary key auto_increment);
connect  con3726_1,localhost,root,,test;
connection con3726_1;
lock table t2 read;
connect  con3726_2,localhost,root,,test;
connection con3726_2;
# RENAME below will be blocked by 'lock table t2 read' above but
# will add two pending requests for exclusive metadata locks.
rename table t2 to t3;
connection default;
# These statements should not be blocked by pending lock requests
select table_name, column_name, data_type from information_schema.columns
where table_schema = 'test' and table_name in ('t1', 't2') order by table_name, column_name;
table_name	column_name	data_type
t1	i	int
t2	j	int
select table_name, auto_increment from information_schema.tables
where table_schema = 'test' and table_name in ('t1', 't2') order by table_name;
table_name	auto_increment
t1	NULL
t2	1
connection con3726_1;
unlock tables;
connection con3726_2;
connection default;
disconnect con3726_1;
disconnect con3726_2;
drop tables t1, t3;
EXPLAIN SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	KEY_COLUMN_USAGE	ALL	NULL	NULL	NULL	NULL	NULL	Open_full_table; Scanned all databases
EXPLAIN SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='t1';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	PARTITIONS	ALL	NULL	TABLE_NAME	NULL	NULL	NULL	Using where; Open_full_table; Scanned 1 database
EXPLAIN SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA='test';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	REFERENTIAL_CONSTRAINTS	ALL	NULL	CONSTRAINT_SCHEMA	NULL	NULL	NULL	Using where; Open_full_table; Scanned 1 database
EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='t1' and TABLE_SCHEMA='test';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	TABLE_CONSTRAINTS	ALL	NULL	TABLE_SCHEMA,TABLE_NAME	NULL	NULL	NULL	Using where; Open_full_table; Scanned 0 databases
EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
WHERE EVENT_OBJECT_SCHEMA='test';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	TRIGGERS	ALL	NULL	EVENT_OBJECT_SCHEMA	NULL	NULL	NULL	Using where; Open_frm_only; Scanned 1 database
create table information_schema.t1 (f1 INT);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
drop table information_schema.t1;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
drop temporary table if exists information_schema.t1;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
create temporary table information_schema.t1 (f1 INT);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
drop view information_schema.v1;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
create view information_schema.v1;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
create trigger mysql.trg1 after insert on information_schema.t1 for each row set @a=1;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
create table t1 select * from information_schema.t1;
ERROR 42S02: Unknown table 't1' in information_schema
CREATE TABLE t1(f1 char(100));
REPAIR TABLE t1, information_schema.tables;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CHECKSUM TABLE t1, information_schema.tables;
Table	Checksum
test.t1	0
information_schema.tables	0
ANALYZE TABLE t1, information_schema.tables;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CHECK TABLE t1, information_schema.tables;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
information_schema.tables	check	note	The storage engine for the table doesn't support check
OPTIMIZE TABLE t1, information_schema.tables;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
RENAME TABLE v1 to v2, information_schema.tables to t2;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE t1, information_schema.tables;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
LOCK TABLES t1 READ, information_schema.tables READ;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE t1;
SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
LEFT JOIN INFORMATION_SCHEMA.COLUMNS
USING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
WHERE COLUMNS.TABLE_SCHEMA = 'test'
AND COLUMNS.TABLE_NAME = 't1';
TABLE_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	IS_SYSTEM_TIME_PERIOD_START	IS_SYSTEM_TIME_PERIOD_END
#
# A test case for Bug#56540 "Exception (crash) in sql_show.cc
# during rqg_info_schema test on Windows"
# Ensure that we never access memory of a closed table,
# in particular, never access table->field[] array.
# Before the fix, the below test case, produced
# valgrind errors.
#
drop table if exists t1;
drop view if exists v1;
create table t1 (a int, b int);
create view v1 as select t1.a, t1.b from t1;
alter table t1 change b c int;
lock table t1 read;
connect con1, localhost, root,,;
connection con1;
flush tables;
flush tables t1;
connection default;
select * from information_schema.views where table_schema='test';
TABLE_CATALOG	def
TABLE_SCHEMA	test
TABLE_NAME	v1
VIEW_DEFINITION	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
CHECK_OPTION	NONE
IS_UPDATABLE	
DEFINER	root@localhost
SECURITY_TYPE	DEFINER
CHARACTER_SET_CLIENT	latin1
COLLATION_CONNECTION	latin1_swedish_ci
ALGORITHM	UNDEFINED
Warnings:
Level	Warning
Code	1356
Message	View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
unlock tables;
#
# Cleanup.
#
connection con1;
# Reaping 'flush tables'
disconnect con1;
connection default;
drop table t1;
drop view v1;
#
# Test for bug #12828477 - "MDL SUBSYSTEM CREATES BIG OVERHEAD FOR
#                           CERTAIN QUERIES TO INFORMATION_SCHEMA".
#
# Check that metadata locks which are acquired during the process
# of opening tables/.FRMs/.TRG files while filling I_S table are
# not kept to the end of statement. Keeping the locks has caused
# performance problems in cases when big number of tables (.FRMs
# or .TRG files) were scanned as cost of new lock acquisition has
# increased linearly.
drop database if exists mysqltest;
create database mysqltest;
use mysqltest;
create table t0 (i int);
create table t1 (j int);
create table t2 (k int);
#
# Test that we don't keep locks in case when we to fill
# I_S table we perform full-blown table open.
#
# Acquire lock on 't2' so upcoming RENAME is
# blocked.
lock tables t2 read;
connect  con12828477_1, localhost, root,,mysqltest;
# The below RENAME should wait on 't2' while
# keeping X lock on 't1'.
rename table t1 to t3, t2 to t1, t3 to t2;
connect  con12828477_2, localhost, root,,mysqltest;
# Wait while the above RENAME is blocked.
# Issue query to I_S which will open 't0' and get
# blocked on 't1' because of RENAME.
select 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;
connect  con12828477_3, localhost, root,,mysqltest;
# Wait while the above SELECT is blocked.
#
# Check that it holds no lock on 't0' so it can be renamed.
rename table t0 to t4;
connection default;
#
# Unblock the first RENAME.
unlock tables;
connection con12828477_1;
# Reap the first RENAME
connection con12828477_2;
# Reap SELECT to I_S.
table_name	auto_increment
t0	NULL
t1	NULL
t2	NULL
connection default;
#
# Now test that we don't keep locks in case when we to fill
# I_S table we read .FRM or .TRG file only (this was the case
# for which problem existed).
#
rename table t4 to t0;
# Acquire lock on 't2' so upcoming RENAME is
# blocked.
lock tables t2 read;
connection con12828477_1;
# The below RENAME should wait on 't2' while
# keeping X lock on 't1'.
rename table t1 to t3, t2 to t1, t3 to t2;
connection con12828477_2;
# Wait while the above RENAME is blocked.
# Issue query to I_S which will open 't0' and get
# blocked on 't1' because of RENAME.
select event_object_table, trigger_name from information_schema.triggers where event_object_schema='mysqltest';
connection con12828477_3;
# Wait while the above SELECT is blocked.
#
# Check that it holds no lock on 't0' so it can be renamed.
rename table t0 to t4;
connection default;
#
# Unblock the first RENAME.
unlock tables;
connection con12828477_1;
# Reap the first RENAME
connection con12828477_2;
# Reap SELECT to I_S.
event_object_table	trigger_name
connection default;
disconnect con12828477_1;
disconnect con12828477_2;
disconnect con12828477_3;
#
# MDEV-3818: Query against view over IS tables worse than equivalent query without view
#
create view v1 as select table_schema, table_name, column_name from information_schema.columns;
explain extended
select column_name from v1
where (table_schema = "osm") and (table_name = "test");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	columns	ALL	NULL	TABLE_SCHEMA,TABLE_NAME	NULL	NULL	NULL	NULL	Using where; Open_frm_only; Scanned 0 databases
Warnings:
Note	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'
explain extended
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');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	columns	ALL	NULL	TABLE_SCHEMA,TABLE_NAME	NULL	NULL	NULL	NULL	Using where; Open_frm_only; Scanned 0 databases
Warnings:
Note	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'
drop view v1;
#
# Clean-up.
drop database mysqltest;
#
# Test for bug #16869534 - "QUERYING SUBSET OF COLUMNS DOESN'T USE TABLE
#                           CACHE; OPENED_TABLES INCREASES"
#
SELECT * FROM INFORMATION_SCHEMA.TABLES;
SELECT VARIABLE_VALUE INTO @val1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE
VARIABLE_NAME LIKE 'Opened_tables';
SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES;
# The below SELECT query should give same output as above SELECT query.
SELECT VARIABLE_VALUE INTO @val2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE
VARIABLE_NAME LIKE 'Opened_tables';
# The below select should return '1'
SELECT @val1 = @val2;
@val1 = @val2
1
#
# End of 5.5 tests
#
# 
# MDEV-5723: mysqldump -uroot unusable for multi-database operations, checks all databases
# 
drop database if exists db1;
connect  con1,localhost,root,,;
connection con1;
create database db1;
use db1;
create table t1 (a int);
create table t2 (a int);
create table t3 (a int);
create database mysqltest;
use mysqltest;
create table t1 (a int);
create table t2 (a int);
create table t3 (a int);
flush tables;
flush status;
SELECT 
LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA 
FROM 
INFORMATION_SCHEMA.FILES 
WHERE 
FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND 
LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME 
FROM INFORMATION_SCHEMA.FILES 
WHERE 
FILE_TYPE = 'DATAFILE' AND 
TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_SCHEMA IN ('db1')
)
) 
GROUP BY 
LOGFILE_GROUP_NAME, FILE_NAME, ENGINE 
ORDER BY 
LOGFILE_GROUP_NAME;
LOGFILE_GROUP_NAME	FILE_NAME	TOTAL_EXTENTS	INITIAL_SIZE	ENGINE	EXTRA
Warnings:
Warning	1287	'information_schema.FILES' is deprecated and will be removed in a future release
Warning	1287	'information_schema.FILES' is deprecated and will be removed in a future release
# This must have Opened_tables=3, not 6.
show status like 'Opened_tables';
Variable_name	Value
Opened_tables	3
drop database mysqltest;
drop database db1;
connection default;
disconnect con1;
set global sql_mode=default;
USE test;
#
# End of 10.0 tests
#
#
# Start of 10.1 tests
#
#
# MDEV-13242 Wrong results for queries with row constructors and information_schema
#
CREATE TABLE tt1(c1 INT);
CREATE TABLE tt2(c2 INT);
SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt1', 'c1'));
count(*)
1
SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt2', 'c2'));
count(*)
1
SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt1','c1'),('tt2', 'c2'));
count(*)
2
SELECT 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);
count(*)
2
SELECT 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');
count(*)
2
SELECT column_name FROM information_schema.columns WHERE (table_name, column_name) IN (('tt1','c1'),('tt2', 'c2')) ORDER BY column_name;
column_name
c1
c2
DROP TABLE tt1, tt2;
#
# MDEV-13242 Wrong results for queries with row constructors and information_schema
#
SELECT SCHEMA_NAME from information_schema.schemata where schema_name='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
SCHEMA_NAME
SELECT SCHEMA_NAME from information_schema.schemata where schema_name=REPEAT('a',193);
SCHEMA_NAME
#
# End of 10.1 tests
#
#
# MDEV-14836: Assertion `m_status == DA_ERROR' failed in
# Diagnostics_area::sql_errno upon query from I_S with LIMIT ROWS EXAMINED
#
SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10;
TABLE_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	IS_SYSTEM_TIME_PERIOD_START	IS_SYSTEM_TIME_PERIOD_END
Warnings:
Warning	1931	Query execution was interrupted. The query exceeded LIMIT ROWS EXAMINED 10. The query result may be incomplete
#
# MDEV-24179: AAssertion `m_status == DA_ERROR || m_status == DA_OK ||
# m_status == DA_OK_BULK' failed in Diagnostics_area::message()
#
call mtr.add_suppression("Sort aborted.*");
create database dummy;
use dummy;
drop database dummy;
USE test;
CREATE 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;
SELECT * FROM v LIMIT ROWS EXAMINED 9;
ERROR HY000: Sort aborted: LIMIT ROWS EXAMINED
DROP VIEW v;
#
# 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
#
CREATE TABLE t (a INT);
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=CONCAT('t',0x00,'1');
TABLE_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
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=CONCAT('test',0x00,'1');
TABLE_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
DROP TABLE t;
CREATE TABLE `a/~.b` (a INT);
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='a/~.b';
TABLE_SCHEMA	TABLE_NAME
test	a/~.b
DROP TABLE `a/~.b`;
CREATE DATABASE `a/~.b`;
CREATE TABLE `a/~.b`.t1 (a INT);
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='a/~.b';
TABLE_SCHEMA	TABLE_NAME
a/~.b	t1
DROP DATABASE `a/~.b`;
#
# End of 10.2 Test
#
#
# MDEV-21201:No records produced in information_schema query,
# depending on projection
#
create table t (i int, constraint a check (i > 0));
select
tc.TABLE_SCHEMA,
tc.TABLE_NAME,
cc.CONSTRAINT_NAME,
cc.CHECK_CLAUSE
from information_schema.TABLE_CONSTRAINTS tc
join information_schema.CHECK_CONSTRAINTS cc
using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME)
;
TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
mysql	global_priv	Priv	json_valid(`Priv`)
mysql	servers	Options	json_valid(`Options`)
test	t	a	`i` > 0
select
tc.TABLE_SCHEMA,
tc.TABLE_NAME,
cc.CONSTRAINT_NAME,
cc.CHECK_CLAUSE
from information_schema.CHECK_CONSTRAINTS cc
join information_schema.TABLE_CONSTRAINTS tc
using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME)
;
TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
mysql	global_priv	Priv	json_valid(`Priv`)
mysql	servers	Options	json_valid(`Options`)
test	t	a	`i` > 0
select
tc.TABLE_SCHEMA,
tc.TABLE_NAME,
cc.CONSTRAINT_NAME,
cc.CHECK_CLAUSE
from information_schema.TABLE_CONSTRAINTS tc
NATURAL join information_schema.CHECK_CONSTRAINTS cc
;
TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
mysql	global_priv	Priv	json_valid(`Priv`)
mysql	servers	Options	json_valid(`Options`)
test	t	a	`i` > 0
select
tc.TABLE_SCHEMA,
tc.TABLE_NAME,
cc.CONSTRAINT_NAME,
cc.CHECK_CLAUSE
from information_schema.CHECK_CONSTRAINTS cc
NATURAL join information_schema.TABLE_CONSTRAINTS tc
;
TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
mysql	global_priv	Priv	json_valid(`Priv`)
mysql	servers	Options	json_valid(`Options`)
test	t	a	`i` > 0
select
tc.TABLE_SCHEMA,
tc.TABLE_NAME,
cc.CONSTRAINT_NAME,
cc.CHECK_CLAUSE,
tc.CONSTRAINT_CATALOG,
tc.CONSTRAINT_SCHEMA
from information_schema.TABLE_CONSTRAINTS tc
join information_schema.CHECK_CONSTRAINTS cc
using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME)
;
TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE	CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA
mysql	global_priv	Priv	json_valid(`Priv`)	def	mysql
mysql	servers	Options	json_valid(`Options`)	def	mysql
test	t	a	`i` > 0	def	test
drop table t;
#
# MDEV-24230 subquery on information_schema fails with error message
#
create table t1 (n int);
create table t2 (n int);
insert into t1 set n = (select table_rows from information_schema.tables where table_name='t2');
drop table t1, t2;
#
# MDEV-24593 Signal 11 when group by primary key of table joined to information_schema.columns
#
create table t1 (f varchar(64) primary key);
select f from information_schema.columns i
inner join t1 on f=i.column_name
group by f;
f
drop table t1;
#
# MDEV-24929 Server crash in thr_multi_unlock or in
# get_schema_tables_result upon select from I_S with joins
#
CREATE TABLE t1 (a TIMESTAMP, KEY (a));
INSERT INTO t1 VALUES ('2012-12-12'),('2021-11-11');
SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.ROUTINES) ON (t1b.a IS NULL);
count(*)
2
SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.PROFILING) ON (t1b.a IS NULL);
count(*)
2
DROP TABLE t1;
#
# MDEV-24868 Server crashes in optimize_schema_tables_memory_usage after select from information_schema.innodb_sys_columns
#
create table t1 ( name varchar(64) character set utf8 collate utf8_general_ci, len int);
select * from t1 where (name, len) in (select  name, len from information_schema.innodb_sys_columns having len = 8);
name	len
drop table t1;
#
# MDEV-20254 Problems with EMPTY_STRING_IS_NULL and I_S tables
#
SET SQL_MODE= 'EMPTY_STRING_IS_NULL';
CREATE OR REPLACE TABLE t1 AS SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 0;
SHOW returned: CREATE TABLE `t1` (
  `TABLE_NAME` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
CREATE TABLE `t1` (
  `TABLE_NAME` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `TABLE_NAME` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
SET SQL_MODE=DEFAULT;
SET SQL_MODE= 'EMPTY_STRING_IS_NULL';
CREATE OR REPLACE TABLE t1 AS SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 0;
DROP TABLE t1;
# Executing the statement returned from SHOW CREATE TABLE
DROP TABLE t1;
SET SQL_MODE=DEFAULT;
#
# MDEV-27673 Warning after "select progress from information_schema.processlist"
#
select progress from information_schema.processlist limit 1;
progress
0.000
#
# End of 10.3 tests
#
#
# MDEV-MDEV-31064 Changes of the procedure are not immediatly seen in queries to I_S.parameter from other connections
#
CREATE PROCEDURE sp1(IN p1 INT, IN p2 INT)
BEGIN
END;
connect con2, localhost, root,,;
CALL sp1(10, 20);
connection default;
CREATE OR REPLACE PROCEDURE sp1(p1 INT)
BEGIN
END;
connection con2;
SELECT COUNT(*) FROM information_schema.parameters WHERE SPECIFIC_NAME = 'sp1';
COUNT(*)
1
disconnect con2;
connection default;
DROP PROCEDURE sp1;
#
# End of 10.4 tests
#
#
# Start of 10.5 tests
#
#
# MDEV-26507 Assertion `tmp != ((long long) 0x8000000000000000LL)' failed in TIME_from_longlong_datetime_packed
#
CREATE TABLE t1 (a int);
CREATE ALGORITHM=TEMPTABLE VIEW i AS
SELECT a.created
FROM t1 w JOIN INFORMATION_SCHEMA.routines a
WHERE a.routine_name='not existing'
  ORDER BY a.last_altered;
SET SESSION sql_mode='ALLOW_INVALID_DATES';
SELECT * FROM i;
created
SET SESSION sql_mode=DEFAULT;
DROP VIEW i;
DROP TABLE t1;
#
# End of 10.5 tests
#