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
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
|
# suite/funcs_1/t/is_events.test
#
# Check the layout of information_schema.events and some functionality of it.
#
# This test is not intended for checking storage engine properties
# Therefore please do not alter $engine_type and $other_engine_type.
#
# Author:
# 2008-02-29 mleich WL#4203 Reorganize and fix the data dictionary tests of
# testsuite funcs_1
#
let $engine_type = MEMORY;
let $other_engine_type = MyISAM;
let $is_table = EVENTS;
# The table INFORMATION_SCHEMA.EVENTS must exist
eval SHOW TABLES FROM information_schema LIKE '$is_table';
--echo #######################################################################
--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
--echo #######################################################################
# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
# statement, just as if it were an ordinary user-defined table.
#
--source suite/funcs_1/datadict/is_table_query.inc
--echo #########################################################################
--echo # Testcase 3.2.12.1: INFORMATION_SCHEMA.EVENTS layout
--echo #########################################################################
# Ensure that the INFORMATION_SCHEMA.EVENTS table has the following columns,
# in the following order:
#
# EVENT_CATALOG always NULL
# EVENT_SCHEMA The name of the schema (database) to which this
# event belongs.
# EVENT_NAME The name of the event.
# DEFINER The user who created the event.
# 'user_name'@'host_name' format!
# TIME_ZONE The time zone in effect when schedule for the event was
# last modified
# EVENT_BODY The language used for the statements in the event's
# DO clause. (always SQL)
# EVENT_DEFINITION The text of the SQL statement making up the event's
# DO clause.
# EVENT_TYPE One of the two values ONE TIME or RECURRING.
# EXECUTE_AT one-time event: DATETIME value specified in the AT clause
# of the CREATE EVENT statement used to
# create the event
# or of the last ALTER EVENT statement that modified
# the event.
# INTERVAL_VALUE recurring events; numeric portion of the event's
# EVERY clause.
# INTERVAL_FIELD recurring events: units portion of the EVERY clause
# governing the timing of the event, prefixed with
# 'INTERVAL_'. Example: 'INTERVAL_DAY'
# SQL_MODE The SQL mode in effect at the time the event was created
# or altered.
# STARTS For recurring event whose definition includes a STARTS
# clause, this column contains the corresponding
# DATETIME value. If there is no STARTS clause affecting
# the timing of the event, this column is empty.
# ENDS For a recurring event whose definition includes a ENDS
# clause, this column contains the corresponding DATETIME
# value. If there is no ENDS clause affecting the timing
# of the event, this column contains NULL.
# STATUS ENABLED, DISABLED or SLAVESIDE_DISABLED
# ON_COMPLETION PRESERVE or NOT PRESERVE.
# CREATED Date and time of event creation
# LAST_ALTERED Date and time of the last event modification
# never modified -> value = CREATED
# LAST_EXECUTED Date and time of start of last event execution
# never executed -> value IS NULL.
# EVENT_COMMENT The text of a comment. Default: empty string.
# ORIGINATOR The server ID of the MySQL server on which the event was
# created; used in replication. Default: 0
# CHARACTER_SET_CLIENT Session value of the character_set_client system variable
# when the event was created.
# COLLATION_CONNECTION Session value of the collation_connection system
# variable when the event was created.
# DATABASE_COLLATION Collation of the database with which the event
# is associated.
#
eval DESCRIBE information_schema.$is_table;
--replace_result ENGINE=MyISAM "" ENGINE=Aria "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" ""
eval SHOW CREATE TABLE information_schema.$is_table;
eval SHOW COLUMNS FROM information_schema.$is_table;
# Note: Retrieval of information within information_schema.columns about
# information_schema.events is in is_columns_is.test.
# Check event_catalog event_body, event_type, event_type, status, on_completion
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');
# FIXME: Check the regression tests and implement tests checking the
# functionality if missing.
--echo ########################################################################
--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
--echo # DDL on INFORMATION_SCHEMA tables are not supported
--echo ########################################################################
# 3.2.1.3: Ensure that no user may execute an INSERT statement on any
# INFORMATION_SCHEMA table.
# 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
# INFORMATION_SCHEMA table.
# 3.2.1.5: Ensure that no user may execute a DELETE statement on any
# INFORMATION_SCHEMA table.
# 3.2.1.8: Ensure that no user may create an index on an
# INFORMATION_SCHEMA table.
# 3.2.1.9: Ensure that no user may alter the definition of an
# INFORMATION_SCHEMA table.
# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
# other database.
# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
# in an INFORMATION_SCHEMA table.
#
--disable_warnings
DROP DATABASE IF EXISTS db_datadict;
--enable_warnings
CREATE DATABASE db_datadict;
--replace_result $engine_type <engine_type>
eval
CREATE TABLE db_datadict.t1 (f1 BIGINT)
ENGINE = $engine_type;
--error ER_DBACCESS_DENIED_ERROR
INSERT INTO information_schema.events
SELECT * FROM information_schema.events;
--error ER_DBACCESS_DENIED_ERROR,ER_NON_UPDATABLE_TABLE
UPDATE information_schema.events SET event_name = '1234567'
WHERE table_name = 't1';
--error ER_DBACCESS_DENIED_ERROR
DELETE FROM information_schema.events WHERE event_catalog IS NULL;
--error ER_DBACCESS_DENIED_ERROR
TRUNCATE information_schema.events;
--error ER_DBACCESS_DENIED_ERROR
CREATE INDEX my_idx_on_events ON information_schema.events(event_name);
--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.events DROP PRIMARY KEY;
--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.events ADD f1 INT;
--error ER_DBACCESS_DENIED_ERROR
DROP TABLE information_schema.events;
--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.events RENAME db_datadict.events;
--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.events RENAME information_schema.xevents;
# Cleanup
DROP DATABASE db_datadict;
|
