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) YES NULL 32SCHEMA_NAME varchar(64) NO 33DEFAULT_CHARACTER_SET_NAME varchar(64) NO 34DEFAULT_COLLATION_NAME varchar(64) NO 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) DEFAULT NULL, 40 `SCHEMA_NAME` varchar(64) NOT NULL DEFAULT '', 41 `DEFAULT_CHARACTER_SET_NAME` varchar(64) NOT NULL DEFAULT '', 42 `DEFAULT_COLLATION_NAME` varchar(64) NOT NULL DEFAULT '', 43 `SQL_PATH` varchar(512) DEFAULT NULL 44) ENGINE=MEMORY DEFAULT CHARSET=utf8 45SHOW COLUMNS FROM information_schema.SCHEMATA; 46Field Type Null Key Default Extra 47CATALOG_NAME varchar(512) YES NULL 48SCHEMA_NAME varchar(64) NO 49DEFAULT_CHARACTER_SET_NAME varchar(64) NO 50DEFAULT_COLLATION_NAME varchar(64) NO 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; 55catalog_name schema_name sql_path 56############################################################################### 57# Testcases 3.2.9.2+3.2.9.3: INFORMATION_SCHEMA.SCHEMATA accessible information 58############################################################################### 59DROP DATABASE IF EXISTS db_datadict_1; 60DROP DATABASE IF EXISTS db_datadict_2; 61CREATE DATABASE db_datadict_1; 62CREATE DATABASE db_datadict_2; 63DROP USER 'testuser1'@'localhost'; 64CREATE USER 'testuser1'@'localhost'; 65DROP USER 'testuser2'@'localhost'; 66CREATE USER 'testuser2'@'localhost'; 67DROP USER 'testuser3'@'localhost'; 68CREATE USER 'testuser3'@'localhost'; 69GRANT SELECT ON db_datadict_1.* to 'testuser1'@'localhost'; 70GRANT SELECT ON db_datadict_1.* to 'testuser2'@'localhost'; 71GRANT SELECT ON db_datadict_2.* to 'testuser2'@'localhost'; 72SELECT * FROM information_schema.schemata 73WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; 74CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 75NULL db_datadict_1 latin1 latin1_swedish_ci NULL 76NULL db_datadict_2 latin1 latin1_swedish_ci NULL 77SHOW DATABASES LIKE 'db_datadict_%'; 78Database (db_datadict_%) 79db_datadict_1 80db_datadict_2 81# Establish connection testuser1 (user=testuser1) 82SELECT * FROM information_schema.schemata 83WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; 84CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 85NULL db_datadict_1 latin1 latin1_swedish_ci NULL 86NULL db_datadict_2 latin1 latin1_swedish_ci NULL 87SHOW DATABASES LIKE 'db_datadict_%'; 88Database (db_datadict_%) 89db_datadict_1 90db_datadict_2 91# Establish connection testuser2 (user=testuser2) 92SELECT * FROM information_schema.schemata 93WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; 94CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 95NULL db_datadict_1 latin1 latin1_swedish_ci NULL 96NULL db_datadict_2 latin1 latin1_swedish_ci NULL 97SHOW DATABASES LIKE 'db_datadict_%'; 98Database (db_datadict_%) 99db_datadict_1 100db_datadict_2 101# Establish connection testuser3 (user=testuser3) 102SELECT * FROM information_schema.schemata 103WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; 104CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 105NULL db_datadict_1 latin1 latin1_swedish_ci NULL 106NULL db_datadict_2 latin1 latin1_swedish_ci NULL 107SHOW DATABASES LIKE 'db_datadict_%'; 108Database (db_datadict_%) 109db_datadict_1 110db_datadict_2 111# Switch to connection default and close connections testuser1,testuser2,testuser3 112DROP USER 'testuser1'@'localhost'; 113DROP USER 'testuser2'@'localhost'; 114DROP USER 'testuser3'@'localhost'; 115DROP DATABASE db_datadict_1; 116DROP DATABASE db_datadict_2; 117################################################################################# 118# Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.SCHEMATA modifications 119################################################################################# 120DROP DATABASE IF EXISTS db_datadict; 121SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 122CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 123CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'; 124SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 125CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH 126NULL db_datadict latin1 latin1_swedish_ci NULL 127SELECT schema_name, default_character_set_name 128FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 129schema_name default_character_set_name 130db_datadict latin1 131ALTER SCHEMA db_datadict CHARACTER SET 'utf8'; 132SELECT schema_name, default_character_set_name 133FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 134schema_name default_character_set_name 135db_datadict utf8 136ALTER SCHEMA db_datadict CHARACTER SET 'latin1'; 137SELECT schema_name, default_collation_name FROM information_schema.schemata 138WHERE schema_name = 'db_datadict'; 139schema_name default_collation_name 140db_datadict latin1_swedish_ci 141ALTER SCHEMA db_datadict COLLATE 'latin1_general_cs'; 142SELECT schema_name, default_collation_name FROM information_schema.schemata 143WHERE schema_name = 'db_datadict'; 144schema_name default_collation_name 145db_datadict latin1_general_cs 146SELECT schema_name 147FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 148schema_name 149db_datadict 150DROP DATABASE db_datadict; 151SELECT schema_name 152FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 153schema_name 154######################################################################## 155# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and 156# DDL on INFORMATION_SCHEMA tables are not supported 157######################################################################## 158DROP DATABASE IF EXISTS db_datadict; 159CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'; 160INSERT INTO information_schema.schemata 161(catalog_name, schema_name, default_character_set_name, sql_path) 162VALUES (NULL, 'db1', 'latin1', NULL); 163ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 164INSERT INTO information_schema.schemata 165SELECT * FROM information_schema.schemata; 166ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 167UPDATE information_schema.schemata 168SET default_character_set_name = 'utf8' 169WHERE schema_name = 'db_datadict'; 170ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 171UPDATE information_schema.schemata SET catalog_name = 't_4711'; 172ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 173DELETE FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 174ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 175TRUNCATE information_schema.schemata; 176ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 177CREATE INDEX i1 ON information_schema.schemata(schema_name); 178ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 179ALTER TABLE information_schema.schemata ADD f1 INT; 180ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 181DROP TABLE information_schema.schemata; 182ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 183ALTER TABLE information_schema.schemata RENAME db_datadict.schemata; 184ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 185ALTER TABLE information_schema.schemata RENAME information_schema.xschemata; 186ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 187DROP DATABASE db_datadict; 188