1SHOW TABLES FROM information_schema LIKE 'ENGINES'; 2Tables_in_information_schema (ENGINES) 3ENGINES 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.ENGINES; 11CREATE PROCEDURE test.p1() SELECT * FROM information_schema.ENGINES; 12CREATE FUNCTION test.f1() returns BIGINT 13BEGIN 14DECLARE counter BIGINT DEFAULT NULL; 15SELECT COUNT(*) INTO counter FROM information_schema.ENGINES; 16RETURN counter; 17END// 18# Attention: The printing of the next result sets is disabled. 19SELECT * FROM information_schema.ENGINES; 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.12.1: INFORMATION_SCHEMA.ENGINES layout 28######################################################################### 29DESCRIBE information_schema.ENGINES; 30Field Type Null Key Default Extra 31ENGINE varchar(64) NO NULL 32SUPPORT varchar(8) NO NULL 33COMMENT varchar(160) NO NULL 34TRANSACTIONS varchar(3) YES NULL 35XA varchar(3) YES NULL 36SAVEPOINTS varchar(3) YES NULL 37SHOW CREATE TABLE information_schema.ENGINES; 38Table Create Table 39ENGINES CREATE TEMPORARY TABLE `ENGINES` ( 40 `ENGINE` varchar(64) NOT NULL, 41 `SUPPORT` varchar(8) NOT NULL, 42 `COMMENT` varchar(160) NOT NULL, 43 `TRANSACTIONS` varchar(3), 44 `XA` varchar(3), 45 `SAVEPOINTS` varchar(3) 46) ENGINE=MEMORY DEFAULT CHARSET=utf8 47SHOW COLUMNS FROM information_schema.ENGINES; 48Field Type Null Key Default Extra 49ENGINE varchar(64) NO NULL 50SUPPORT varchar(8) NO NULL 51COMMENT varchar(160) NO NULL 52TRANSACTIONS varchar(3) YES NULL 53XA varchar(3) YES NULL 54SAVEPOINTS varchar(3) YES NULL 55######################################################################## 56# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and 57# DDL on INFORMATION_SCHEMA tables are not supported 58######################################################################## 59DROP DATABASE IF EXISTS db_datadict; 60CREATE DATABASE db_datadict; 61CREATE TABLE db_datadict.t1 (f1 BIGINT) 62ENGINE = <engine_type>; 63INSERT INTO information_schema.engines 64SELECT * FROM information_schema.engines; 65ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 66UPDATE information_schema.engines SET engine = '1234567'; 67Got one of the listed errors 68DELETE FROM information_schema.engines WHERE support IN ('DEFAULT','YES'); 69ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 70TRUNCATE information_schema.engines; 71ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 72CREATE INDEX my_idx_on_engines ON information_schema.engines(engine); 73ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 74ALTER TABLE information_schema.engines DROP PRIMARY KEY; 75ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 76ALTER TABLE information_schema.engines ADD f1 INT; 77ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 78DROP TABLE information_schema.engines; 79ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 80ALTER TABLE information_schema.engines RENAME db_datadict.engines; 81ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 82ALTER TABLE information_schema.engines RENAME information_schema.xengines; 83ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 84DROP DATABASE db_datadict; 85