1
SHOW TABLES FROM information_schema LIKE 'ENGINES';
2
Tables_in_information_schema (ENGINES)
4
#######################################################################
5
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
6
#######################################################################
7
DROP VIEW IF EXISTS test.v1;
8
DROP PROCEDURE IF EXISTS test.p1;
9
DROP FUNCTION IF EXISTS test.f1;
10
CREATE VIEW test.v1 AS SELECT * FROM information_schema.ENGINES;
11
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.ENGINES;
12
CREATE FUNCTION test.f1() returns BIGINT
14
DECLARE counter BIGINT DEFAULT NULL;
15
SELECT COUNT(*) INTO counter FROM information_schema.ENGINES;
18
# Attention: The printing of the next result sets is disabled.
19
SELECT * FROM information_schema.ENGINES;
20
SELECT * FROM test.v1;
24
DROP PROCEDURE test.p1;
25
DROP FUNCTION test.f1;
26
#########################################################################
27
# Testcase 3.2.12.1: INFORMATION_SCHEMA.ENGINES layout
28
#########################################################################
29
DESCRIBE information_schema.ENGINES;
30
Field Type Null Key Default Extra
33
COMMENT varchar(80) NO
34
TRANSACTIONS varchar(3) NO
36
SAVEPOINTS varchar(3) NO
37
SHOW CREATE TABLE information_schema.ENGINES;
39
ENGINES CREATE TEMPORARY TABLE `ENGINES` (
40
`ENGINE` varchar(64) NOT NULL DEFAULT '',
41
`SUPPORT` varchar(8) NOT NULL DEFAULT '',
42
`COMMENT` varchar(80) NOT NULL DEFAULT '',
43
`TRANSACTIONS` varchar(3) NOT NULL DEFAULT '',
44
`XA` varchar(3) NOT NULL DEFAULT '',
45
`SAVEPOINTS` varchar(3) NOT NULL DEFAULT ''
46
) ENGINE=MEMORY DEFAULT CHARSET=utf8
47
SHOW COLUMNS FROM information_schema.ENGINES;
48
Field Type Null Key Default Extra
51
COMMENT varchar(80) NO
52
TRANSACTIONS varchar(3) NO
54
SAVEPOINTS varchar(3) NO
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
########################################################################
59
DROP DATABASE IF EXISTS db_datadict;
60
CREATE DATABASE db_datadict;
61
CREATE TABLE db_datadict.t1 (f1 BIGINT)
62
ENGINE = <engine_type>;
63
INSERT INTO information_schema.engines
64
SELECT * FROM information_schema.engines;
65
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
66
UPDATE information_schema.engines SET engine = '1234567';
67
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
68
DELETE FROM information_schema.engines WHERE support IN ('DEFAULT','YES');
69
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
70
TRUNCATE information_schema.engines;
71
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
72
CREATE INDEX my_idx_on_engines ON information_schema.engines(engine);
73
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
74
ALTER TABLE information_schema.engines DROP PRIMARY KEY;
75
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
76
ALTER TABLE information_schema.engines ADD f1 INT;
77
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
78
DROP TABLE information_schema.engines;
79
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
80
ALTER TABLE information_schema.engines RENAME db_datadict.engines;
81
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
82
ALTER TABLE information_schema.engines RENAME information_schema.xengines;
83
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
84
DROP DATABASE db_datadict;