mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
bead24b7f3
Remove one of the major sources of race condiitons in mariadb-test. Normally, mariadb_close() sends COM_QUIT to the server and immediately disconnects. In mariadb-test it means the test can switch to another connection and sends queries to the server before the server even started parsing the COM_QUIT packet and these queries can see the connection as fully active, as it didn't reach dispatch_command yet. This is a major source of instability in tests and many - but not all, still less than a half - tests employ workarounds. The correct one is a pair count_sessions.inc/wait_until_count_sessions.inc. Also very popular was wait_until_disconnected.inc, which was completely useless, because it verifies that the connection is closed, and after disconnect it always is, it didn't verify whether the server processed COM_QUIT. Sadly the placebo was as widely used as the real thing. Let's fix this by making mariadb-test `disconnect` command _to wait_ for the server to confirm. This makes almost all workarounds redundant. In some cases count_sessions.inc/wait_until_count_sessions.inc is still needed, though, as only `disconnect` command is changed: * after external tools, like `exec $MYSQL` * after failed `connect` command * replication, after `STOP SLAVE` * Federated/CONNECT/SPIDER/etc after `DROP TABLE` and also in some XA tests, because an XA transaction is dissociated from the THD very late, after the server has closed the client connection. Collateral cleanups: fix comments, remove some redundant statements: * DROP IF EXISTS if nothing is known to exist * DROP table/view before DROP DATABASE * REVOKE privileges before DROP USER etc
2591 lines
110 KiB
Text
2591 lines
110 KiB
Text
set global sql_mode="";
|
|
set local sql_mode="";
|
|
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 PAD_ATTRIBUTE COMMENT
|
|
latin1_german1_ci latin1 5 # 1 PAD SPACE cp1252 West European
|
|
latin1_swedish_ci latin1 8 Yes # 1 PAD SPACE cp1252 West European
|
|
latin1_danish_ci latin1 15 # 1 PAD SPACE cp1252 West European
|
|
latin1_german2_ci latin1 31 # 2 PAD SPACE cp1252 West European
|
|
latin1_bin latin1 47 # 1 PAD SPACE cp1252 West European
|
|
latin1_general_ci latin1 48 # 1 PAD SPACE cp1252 West European
|
|
latin1_general_cs latin1 49 # 1 PAD SPACE cp1252 West European
|
|
latin1_spanish_ci latin1 94 # 1 PAD SPACE cp1252 West European
|
|
latin1_swedish_nopad_ci latin1 1032 # 1 NO PAD
|
|
latin1_nopad_bin latin1 1071 # 1 NO PAD
|
|
SHOW COLLATION LIKE 'latin1%';
|
|
Collation Charset Id Default Compiled Sortlen Pad_attribute
|
|
latin1_german1_ci latin1 5 # 1 PAD SPACE
|
|
latin1_swedish_ci latin1 8 Yes # 1 PAD SPACE
|
|
latin1_danish_ci latin1 15 # 1 PAD SPACE
|
|
latin1_german2_ci latin1 31 # 2 PAD SPACE
|
|
latin1_bin latin1 47 # 1 PAD SPACE
|
|
latin1_general_ci latin1 48 # 1 PAD SPACE
|
|
latin1_general_cs latin1 49 # 1 PAD SPACE
|
|
latin1_spanish_ci latin1 94 # 1 PAD SPACE
|
|
latin1_swedish_nopad_ci latin1 1032 # 1 NO PAD
|
|
latin1_nopad_bin latin1 1071 # 1 NO PAD
|
|
SHOW COLLATION WHERE collation like 'latin1%';
|
|
Collation Charset Id Default Compiled Sortlen Pad_attribute
|
|
latin1_german1_ci latin1 5 # 1 PAD SPACE
|
|
latin1_swedish_ci latin1 8 Yes # 1 PAD SPACE
|
|
latin1_danish_ci latin1 15 # 1 PAD SPACE
|
|
latin1_german2_ci latin1 31 # 2 PAD SPACE
|
|
latin1_bin latin1 47 # 1 PAD SPACE
|
|
latin1_general_ci latin1 48 # 1 PAD SPACE
|
|
latin1_general_cs latin1 49 # 1 PAD SPACE
|
|
latin1_spanish_ci latin1 94 # 1 PAD SPACE
|
|
latin1_swedish_nopad_ci latin1 1032 # 1 NO PAD
|
|
latin1_nopad_bin latin1 1071 # 1 NO PAD
|
|
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,information_schema.TRIGGERS 10
|
|
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 PAD_ATTRIBUTE 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
|
|
#
|
|
# 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
|
|
#
|
|
# 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
|