1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
SHOW TABLES FROM information_schema LIKE 'EVENTS';
Tables_in_information_schema (EVENTS)
EVENTS
#######################################################################
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
#######################################################################
DROP VIEW IF EXISTS test.v1;
DROP PROCEDURE IF EXISTS test.p1;
DROP FUNCTION IF EXISTS test.f1;
CREATE VIEW test.v1 AS SELECT * FROM information_schema.EVENTS;
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.EVENTS;
CREATE FUNCTION test.f1() returns BIGINT
BEGIN
DECLARE counter BIGINT DEFAULT NULL;
SELECT COUNT(*) INTO counter FROM information_schema.EVENTS;
RETURN counter;
END//
# Attention: The printing of the next result sets is disabled.
SELECT * FROM information_schema.EVENTS;
SELECT * FROM test.v1;
CALL test.p1;
SELECT test.f1();
DROP VIEW test.v1;
DROP PROCEDURE test.p1;
DROP FUNCTION test.f1;
#########################################################################
# Testcase 3.2.12.1: INFORMATION_SCHEMA.EVENTS layout
#########################################################################
DESCRIBE information_schema.EVENTS;
Field Type Null Key Default Extra
EVENT_CATALOG varchar(64) NO NULL
EVENT_SCHEMA varchar(64) NO NULL
EVENT_NAME varchar(64) NO NULL
DEFINER varchar(384) NO NULL
TIME_ZONE varchar(64) NO NULL
EVENT_BODY varchar(8) NO NULL
EVENT_DEFINITION longtext NO NULL
EVENT_TYPE varchar(9) NO NULL
EXECUTE_AT datetime YES NULL
INTERVAL_VALUE varchar(256) YES NULL
INTERVAL_FIELD varchar(18) YES NULL
SQL_MODE varchar(8192) NO NULL
STARTS datetime YES NULL
ENDS datetime YES NULL
STATUS varchar(18) NO NULL
ON_COMPLETION varchar(12) NO NULL
CREATED datetime NO NULL
LAST_ALTERED datetime NO NULL
LAST_EXECUTED datetime YES NULL
EVENT_COMMENT varchar(64) NO NULL
ORIGINATOR bigint(10) NO NULL
CHARACTER_SET_CLIENT varchar(32) NO NULL
COLLATION_CONNECTION varchar(32) NO NULL
DATABASE_COLLATION varchar(32) NO NULL
SHOW CREATE TABLE information_schema.EVENTS;
Table Create Table
EVENTS CREATE TEMPORARY TABLE `EVENTS` (
`EVENT_CATALOG` varchar(64) NOT NULL,
`EVENT_SCHEMA` varchar(64) NOT NULL,
`EVENT_NAME` varchar(64) NOT NULL,
`DEFINER` varchar(384) NOT NULL,
`TIME_ZONE` varchar(64) NOT NULL,
`EVENT_BODY` varchar(8) NOT NULL,
`EVENT_DEFINITION` longtext NOT NULL,
`EVENT_TYPE` varchar(9) NOT NULL,
`EXECUTE_AT` datetime,
`INTERVAL_VALUE` varchar(256),
`INTERVAL_FIELD` varchar(18),
`SQL_MODE` varchar(8192) NOT NULL,
`STARTS` datetime,
`ENDS` datetime,
`STATUS` varchar(18) NOT NULL,
`ON_COMPLETION` varchar(12) NOT NULL,
`CREATED` datetime NOT NULL,
`LAST_ALTERED` datetime NOT NULL,
`LAST_EXECUTED` datetime,
`EVENT_COMMENT` varchar(64) NOT NULL,
`ORIGINATOR` bigint(10) NOT NULL,
`CHARACTER_SET_CLIENT` varchar(32) NOT NULL,
`COLLATION_CONNECTION` varchar(32) NOT NULL,
`DATABASE_COLLATION` varchar(32) NOT NULL
) DEFAULT CHARSET=utf8mb3
SHOW COLUMNS FROM information_schema.EVENTS;
Field Type Null Key Default Extra
EVENT_CATALOG varchar(64) NO NULL
EVENT_SCHEMA varchar(64) NO NULL
EVENT_NAME varchar(64) NO NULL
DEFINER varchar(384) NO NULL
TIME_ZONE varchar(64) NO NULL
EVENT_BODY varchar(8) NO NULL
EVENT_DEFINITION longtext NO NULL
EVENT_TYPE varchar(9) NO NULL
EXECUTE_AT datetime YES NULL
INTERVAL_VALUE varchar(256) YES NULL
INTERVAL_FIELD varchar(18) YES NULL
SQL_MODE varchar(8192) NO NULL
STARTS datetime YES NULL
ENDS datetime YES NULL
STATUS varchar(18) NO NULL
ON_COMPLETION varchar(12) NO NULL
CREATED datetime NO NULL
LAST_ALTERED datetime NO NULL
LAST_EXECUTED datetime YES NULL
EVENT_COMMENT varchar(64) NO NULL
ORIGINATOR bigint(10) NO NULL
CHARACTER_SET_CLIENT varchar(32) NO NULL
COLLATION_CONNECTION varchar(32) NO NULL
DATABASE_COLLATION varchar(32) NO NULL
SELECT event_catalog, event_name, event_body, event_type, event_type,
status, on_completion
FROM information_schema.events
WHERE event_catalog IS NOT NULL or
event_body NOT IN ('SQL') or
event_type NOT IN ('ONE TIME','RECURRING') or
status NOT IN ('ENABLED','DISABLED','SLAVESIDE_DISABLED') or
on_completion NOT IN ('PRESERVE','NOT PRESERVE');
event_catalog event_name event_body event_type event_type status on_completion
########################################################################
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
# DDL on INFORMATION_SCHEMA tables are not supported
########################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
CREATE TABLE db_datadict.t1 (f1 BIGINT)
ENGINE = <engine_type>;
INSERT INTO information_schema.events
SELECT * FROM information_schema.events;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
UPDATE information_schema.events SET event_name = '1234567'
WHERE table_name = 't1';
Got one of the listed errors
DELETE FROM information_schema.events WHERE event_catalog IS NULL;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
TRUNCATE information_schema.events;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE INDEX my_idx_on_events ON information_schema.events(event_name);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.events DROP PRIMARY KEY;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.events ADD f1 INT;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE information_schema.events;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.events RENAME db_datadict.events;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.events RENAME information_schema.xevents;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP DATABASE db_datadict;
|
