Debian Codegraph @

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

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