1SHOW TABLES FROM information_schema LIKE 'SCHEMATA'; 2Tables_in_information_schema (SCHEMATA) 3SCHEMATA 4####################################################################### 5# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT 6####################################################################### 7DROP VIEW IF EXISTS test.v1; 8DROP PROCEDURE IF EXISTS test.p1; 9DROP FUNCTION IF EXISTS test.f1; 10CREATE VIEW test.v1 AS SELECT * FROM information_schema.SCHEMATA; 11CREATE PROCEDURE test.p1() SELECT * FROM information_schema.SCHEMATA; 12CREATE FUNCTION test.f1() returns BIGINT 13BEGIN 14DECLARE counter BIGINT DEFAULT NULL; 15SELECT COUNT(*) INTO counter FROM information_schema.SCHEMATA; 16RETURN counter; 17END// 18# Attention: The printing of the next result sets is disabled. 19SELECT * FROM information_schema.SCHEMATA; 20SELECT * FROM test.v1; 21CALL test.p1; 22SELECT test.f1(); 23DROP VIEW test.v1; 24DROP PROCEDURE test.p1; 25DROP FUNCTION test.f1; 26######################################################################### 27# Testcase 3.2.9.1: INFORMATION_SCHEMA.SCHEMATA layout; 28######################################################################### 29DESCRIBE information_schema.SCHEMATA; 30Field Type Null Key Default Extra 31CATALOG_NAME varchar(512) NO NULL 32SCHEMA_NAME varchar(64) NO NULL 33DEFAULT_CHARACTER_SET_NAME varchar(32) NO NULL 34DEFAULT_COLLATION_NAME varchar(32) NO NULL 35SQL_PATH varchar(512) YES NULL 36SHOW CREATE TABLE information_schema.SCHEMATA; 37Table Create Table 38SCHEMATA CREATE TEMPORARY TABLE `SCHEMATA` ( 39 `CATALOG_NAME` varchar(512) NOT NULL, 40 `SCHEMA_NAME` varchar(64) NOT NULL, 41 `DEFAULT_CHARACTER_SET_NAME` varchar(32) NOT NULL, 42 `DEFAULT_COLLATION_NAME` varchar(32) NOT NULL, 43 `SQL_PATH` varchar(512) 44) ENGINE=MEMORY DEFAULT CHARSET=utf8 45SHOW COLUMNS FROM information_schema.SCHEMATA; 46Field Type Null Key Default Extra 47CATALOG_NAME varchar(512) NO NULL 48SCHEMA_NAME varchar(64) NO NULL 49DEFAULT_CHARACTER_SET_NAME varchar(32) NO NULL 50DEFAULT_COLLATION_NAME varchar(32) NO NULL 51SQL_PATH varchar(512) YES NULL 52SELECT catalog_name, schema_name, sql_path 53FROM information_schema.schemata 54WHERE catalog_name IS NOT NULL or sql_path IS NOT NULL 55ORDER BY schema_name; 56catalog_name schema_name sql_path 57def information_schema NULL 58def mtr NULL 59def mysql NULL 60def performance_schema NULL 61def test NULL 62############################################################################### 63# Testcases 3.2.9.2+3.2.9.3: INFORMATION_SCHEMA.SCHEMATA accessible information 64############################################################################### 65DROP DATABASE IF EXISTS db_datadict_1; 66DROP DATABASE IF EXISTS db_datadict_2; 67CREATE DATABASE db_datadict_1; 68CREATE DATABASE db_datadict_2; 69DROP USER 'testuser1'@'localhost'; 70CREATE USER 'testuser1'@'localhost'; 71DROP USER 'testuser2'@'localhost'; 72CREATE USER 'testuser2'@'localhost'; 73DROP USER 'testuser3'@'localhost'; 74CREATE USER 'testuser3'@'localhost'; 75GRANT SELECT ON db_datadict_1.* to 'testuser1'@'localhost'; 76GRANT SELECT ON db_datadict_1.* to 'testuser2'@'localhost'; 77GRANT SELECT ON db_datadict_2.* to 'testuser2'@'localhost'; 78SELECT * FROM information_schema.schemata 79WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; 80CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 81def db_datadict_1 latin1 latin1_swedish_ci NULL 82def db_datadict_2 latin1 latin1_swedish_ci NULL 83SHOW DATABASES LIKE 'db_datadict_%'; 84Database (db_datadict_%) 85db_datadict_1 86db_datadict_2 87connect testuser1, localhost, testuser1, , db_datadict_1; 88SELECT * FROM information_schema.schemata 89WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; 90CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 91def db_datadict_1 latin1 latin1_swedish_ci NULL 92SHOW DATABASES LIKE 'db_datadict_%'; 93Database (db_datadict_%) 94db_datadict_1 95connect testuser2, localhost, testuser2, , db_datadict_2; 96SELECT * FROM information_schema.schemata 97WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; 98CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 99def db_datadict_1 latin1 latin1_swedish_ci NULL 100def db_datadict_2 latin1 latin1_swedish_ci NULL 101SHOW DATABASES LIKE 'db_datadict_%'; 102Database (db_datadict_%) 103db_datadict_1 104db_datadict_2 105connect testuser3, localhost, testuser3, , test; 106SELECT * FROM information_schema.schemata 107WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; 108CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 109SHOW DATABASES LIKE 'db_datadict_%'; 110Database (db_datadict_%) 111connection default; 112disconnect testuser1; 113disconnect testuser2; 114disconnect testuser3; 115DROP USER 'testuser1'@'localhost'; 116DROP USER 'testuser2'@'localhost'; 117DROP USER 'testuser3'@'localhost'; 118DROP DATABASE db_datadict_1; 119DROP DATABASE db_datadict_2; 120################################################################################# 121# Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.SCHEMATA modifications 122################################################################################# 123DROP DATABASE IF EXISTS db_datadict; 124SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 125CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 126CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'; 127SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 128CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 129def db_datadict latin1 latin1_swedish_ci NULL 130SELECT schema_name, default_character_set_name 131FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 132schema_name default_character_set_name 133db_datadict latin1 134ALTER SCHEMA db_datadict CHARACTER SET 'utf8'; 135SELECT schema_name, default_character_set_name 136FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 137schema_name default_character_set_name 138db_datadict utf8 139ALTER SCHEMA db_datadict CHARACTER SET 'latin1'; 140SELECT schema_name, default_collation_name FROM information_schema.schemata 141WHERE schema_name = 'db_datadict'; 142schema_name default_collation_name 143db_datadict latin1_swedish_ci 144ALTER SCHEMA db_datadict COLLATE 'latin1_general_cs'; 145SELECT schema_name, default_collation_name FROM information_schema.schemata 146WHERE schema_name = 'db_datadict'; 147schema_name default_collation_name 148db_datadict latin1_general_cs 149SELECT schema_name 150FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 151schema_name 152db_datadict 153DROP DATABASE db_datadict; 154SELECT schema_name 155FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 156schema_name 157######################################################################## 158# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and 159# DDL on INFORMATION_SCHEMA tables are not supported 160######################################################################## 161DROP DATABASE IF EXISTS db_datadict; 162CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'; 163INSERT INTO information_schema.schemata 164(catalog_name, schema_name, default_character_set_name, sql_path) 165VALUES (NULL, 'db1', 'latin1', NULL); 166ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 167INSERT INTO information_schema.schemata 168SELECT * FROM information_schema.schemata; 169ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 170UPDATE information_schema.schemata 171SET default_character_set_name = 'utf8' 172WHERE schema_name = 'db_datadict'; 173ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 174UPDATE information_schema.schemata SET catalog_name = 't_4711'; 175ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 176DELETE FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 177ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 178TRUNCATE information_schema.schemata; 179ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 180CREATE INDEX i1 ON information_schema.schemata(schema_name); 181ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 182ALTER TABLE information_schema.schemata ADD f1 INT; 183ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 184DROP TABLE information_schema.schemata; 185ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 186ALTER TABLE information_schema.schemata RENAME db_datadict.schemata; 187ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 188ALTER TABLE information_schema.schemata RENAME information_schema.xschemata; 189ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 190DROP DATABASE db_datadict; 191