Debian Codegraph @

mysql-test/suite/funcs_1/t/is_events.test

raw
1 # Result differences depending on FS case sensitivity.
2 if (!$require_case_insensitive_file_system)
3 {
4 --source include/have_case_sensitive_file_system.inc
5 }
6
7 # suite/funcs_1/t/is_events.test
8 #
9 # Check the layout of information_schema.events and some functionality of it.
10 #
11 # This test is not intended for checking storage engine properties
12 # Therefore please do not alter $engine_type and $other_engine_type.
13 #
14 # Author:
15 # 2008-02-29 mleich WL#4203 Reorganize and fix the data dictionary tests of
16 # testsuite funcs_1
17 #
18
19
20 let $engine_type = InnoDB;
21
22 let $is_table = EVENTS;
23
24 # The table INFORMATION_SCHEMA.EVENTS must exist
25 eval SHOW TABLES FROM information_schema LIKE '$is_table';
26
27 --echo #######################################################################
28 --echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
29 --echo #######################################################################
30 # Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
31 # statement, just as if it were an ordinary user-defined table.
32 #
33 --source suite/funcs_1/datadict/is_table_query.inc
34
35
36 --echo #########################################################################
37 --echo # Testcase 3.2.12.1: INFORMATION_SCHEMA.EVENTS layout
38 --echo #########################################################################
39 # Ensure that the INFORMATION_SCHEMA.EVENTS table has the following columns,
40 # in the following order:
41 #
42 # EVENT_CATALOG always NULL
43 # EVENT_SCHEMA The name of the schema (database) to which this
44 # event belongs.
45 # EVENT_NAME The name of the event.
46 # DEFINER The user who created the event.
47 # 'user_name'@'host_name' format!
48 # TIME_ZONE The time zone in effect when schedule for the event was
49 # last modified
50 # EVENT_BODY The language used for the statements in the event's
51 # DO clause. (always SQL)
52 # EVENT_DEFINITION The text of the SQL statement making up the event's
53 # DO clause.
54 # EVENT_TYPE One of the two values ONE TIME or RECURRING.
55 # EXECUTE_AT one-time event: DATETIME value specified in the AT clause
56 # of the CREATE EVENT statement used to
57 # create the event
58 # or of the last ALTER EVENT statement that modified
59 # the event.
60 # INTERVAL_VALUE recurring events; numeric portion of the event's
61 # EVERY clause.
62 # INTERVAL_FIELD recurring events: units portion of the EVERY clause
63 # governing the timing of the event, prefixed with
64 # 'INTERVAL_'. Example: 'INTERVAL_DAY'
65 # SQL_MODE The SQL mode in effect at the time the event was created
66 # or altered.
67 # STARTS For recurring event whose definition includes a STARTS
68 # clause, this column contains the corresponding
69 # DATETIME value. If there is no STARTS clause affecting
70 # the timing of the event, this column is empty.
71 # ENDS For a recurring event whose definition includes a ENDS
72 # clause, this column contains the corresponding DATETIME
73 # value. If there is no ENDS clause affecting the timing
74 # of the event, this column contains NULL.
75 # STATUS ENABLED, DISABLED or SLAVESIDE_DISABLED
76 # ON_COMPLETION PRESERVE or NOT PRESERVE.
77 # CREATED Date and time of event creation
78 # LAST_ALTERED Date and time of the last event modification
79 # never modified -> value = CREATED
80 # LAST_EXECUTED Date and time of start of last event execution
81 # never executed -> value IS NULL.
82 # EVENT_COMMENT The text of a comment. Default: empty string.
83 # ORIGINATOR The server ID of the MySQL server on which the event was
84 # created; used in replication. Default: 0
85 # CHARACTER_SET_CLIENT Session value of the character_set_client system variable
86 # when the event was created.
87 # COLLATION_CONNECTION Session value of the collation_connection system
88 # variable when the event was created.
89 # DATABASE_COLLATION Collation of the database with which the event
90 # is associated.
91 #
92 eval DESCRIBE information_schema.$is_table;
93 --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE
94 eval SHOW CREATE TABLE information_schema.$is_table;
95 eval SHOW COLUMNS FROM information_schema.$is_table;
96
97 # Note: Retrieval of information within information_schema.columns about
98 # information_schema.events is in is_columns_is.test.
99
100 # Check event_catalog event_body, event_type, event_type, status, on_completion
101 SELECT event_catalog, event_name, event_body, event_type, event_type,
102 status, on_completion
103 FROM information_schema.events
104 WHERE event_catalog IS NOT NULL or
105 event_body NOT IN ('SQL') or
106 event_type NOT IN ('ONE TIME','RECURRING') or
107 status NOT IN ('ENABLED','DISABLED','SLAVESIDE_DISABLED') or
108 on_completion NOT IN ('PRESERVE','NOT PRESERVE');
109
110 # FIXME: Check the regression tests and implement tests checking the
111 # functionality if missing.
112
113
114 --echo ########################################################################
115 --echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
116 --echo # DDL on INFORMATION_SCHEMA tables are not supported
117 --echo ########################################################################
118 # 3.2.1.3: Ensure that no user may execute an INSERT statement on any
119 # INFORMATION_SCHEMA table.
120 # 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
121 # INFORMATION_SCHEMA table.
122 # 3.2.1.5: Ensure that no user may execute a DELETE statement on any
123 # INFORMATION_SCHEMA table.
124 # 3.2.1.8: Ensure that no user may create an index on an
125 # INFORMATION_SCHEMA table.
126 # 3.2.1.9: Ensure that no user may alter the definition of an
127 # INFORMATION_SCHEMA table.
128 # 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
129 # 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
130 # other database.
131 # 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
132 # in an INFORMATION_SCHEMA table.
133 #
134 --disable_warnings
135 DROP DATABASE IF EXISTS db_datadict;
136 --enable_warnings
137 CREATE DATABASE db_datadict;
138 --replace_result $engine_type <engine_type>
139 eval
140 CREATE TABLE db_datadict.t1 (f1 BIGINT)
141 ENGINE = $engine_type;
142
143 --error ER_DBACCESS_DENIED_ERROR
144 INSERT INTO information_schema.events
145 SELECT * FROM information_schema.events;
146
147 --error ER_DBACCESS_DENIED_ERROR
148 UPDATE information_schema.events SET event_name = '1234567'
149 WHERE table_name = 't1';
150
151 --error ER_DBACCESS_DENIED_ERROR
152 DELETE FROM information_schema.events WHERE event_catalog IS NULL;
153 --error ER_DBACCESS_DENIED_ERROR
154 TRUNCATE information_schema.events;
155
156 --error ER_DBACCESS_DENIED_ERROR
157 CREATE INDEX my_idx_on_events ON information_schema.events(event_name);
158
159 --error ER_DBACCESS_DENIED_ERROR
160 ALTER TABLE information_schema.events DROP PRIMARY KEY;
161 --error ER_DBACCESS_DENIED_ERROR
162 ALTER TABLE information_schema.events ADD f1 INT;
163
164 --error ER_DBACCESS_DENIED_ERROR
165 DROP TABLE information_schema.events;
166
167 --error ER_DBACCESS_DENIED_ERROR
168 ALTER TABLE information_schema.events RENAME db_datadict.events;
169 --error ER_DBACCESS_DENIED_ERROR
170 ALTER TABLE information_schema.events RENAME information_schema.xevents;
171
172 # Cleanup
173 DROP DATABASE db_datadict;
174