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