Debian Codegraph @

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

raw
1 #
2 # Check the layout of information_schema.key_column_usage and the impact of
3 # CREATE/ALTER/DROP TABLE/VIEW/SCHEMA/COLUMN ... on it.
4 #
5 # Note:
6 # This test is not intended
7 # - to show information about the all time existing tables
8 # within the databases information_schema and mysql
9 # - for checking storage engine properties
10 # Therefore please do not alter $engine_type.
11 #
12 # Author:
13 # 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
14 # testsuite funcs_1
15 # Create this script based on older scripts and new code.
16 #
17
18 # Result differences depending on FS case sensitivity.
19 if (!$require_case_insensitive_file_system)
20 {
21 --source include/have_case_sensitive_file_system.inc
22 }
23
24 #--source include/have_myisam.inc
25 let $engine_type= InnoDB;
26
27 let $is_table = KEY_COLUMN_USAGE;
28
29 # The table INFORMATION_SCHEMA.KEY_COLUMN_USAGE must exist
30 eval SHOW TABLES FROM information_schema LIKE '$is_table';
31
32 --echo #######################################################################
33 --echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
34 --echo #######################################################################
35 # Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
36 # statement, just as if it were an ordinary user-defined table.
37 #
38 --source suite/funcs_1/datadict/is_table_query.inc
39
40
41 --echo #########################################################################
42 --echo # Testcase 3.2.7.1: INFORMATION_SCHEMA.KEY_COLUMN_USAGE layout
43 --echo #########################################################################
44 # Ensure that the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table has the following
45 # columns, in the following order:
46 #
47 # CONSTRAINT_CATALOG (always shows NULL),
48 # CONSTRAINT_SCHEMA (shows the database, or schema, in which an accessible
49 # constraint, or index, resides),
50 # CONSTRAINT_NAME (shows the name of the accessible constraint),
51 # TABLE_CATALOG (always shows NULL),
52 # TABLE_SCHEMA (shows the database, or schema, in which the table constrained
53 # by that constraint resides),
54 # TABLE_NAME (shows the name of the table constrained by the constraint),
55 # COLUMN_NAME (shows the name of a column that is the index key, or part of
56 # the index key),
57 # ORDINAL_POSITION (shows the ordinal position of the column within the
58 # constraint index),
59 # POSITION_IN_UNIQUE_CONSTRAINT (shows, for a foreign key column, the ordinal
60 # position of the referenced column within the referenced unique index;
61 # otherwise NULL).
62 # added with 5.0.6:
63 # REFERENCED_TABLE_SCHEMA,
64 # REFERENCED_TABLE_NAME,
65 # REFERENCED_COLUMN_NAME
66 #
67 --source suite/funcs_1/datadict/datadict_bug_12777.inc
68 eval DESCRIBE information_schema.$is_table;
69 --source suite/funcs_1/datadict/datadict_bug_12777.inc
70 eval SHOW CREATE TABLE information_schema.$is_table;
71 --source suite/funcs_1/datadict/datadict_bug_12777.inc
72 eval SHOW COLUMNS FROM information_schema.$is_table;
73
74 # Note: Retrieval of information within information_schema.columns about
75 # information_schema.key_column_usage is in is_columns_is.test.
76
77 # Show that CONSTRAINT_CATALOG and TABLE_CATALOG are always NULL.
78 SELECT constraint_catalog, constraint_schema, constraint_name, table_catalog,
79 table_schema, table_name, column_name
80 FROM information_schema.key_column_usage
81 WHERE (constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL) AND
82 table_name != 'ndb_binlog_index'
83 ORDER BY constraint_schema, constraint_name, table_schema, table_name, column_name;
84
85
86 --echo ########################################################################################
87 --echo # Testcase 3.2.7.2 + 3.2.7.3: INFORMATION_SCHEMA.KEY_COLUMN_USAGE accessible information
88 --echo ########################################################################################
89 # 3.2.7.2: Ensure that the table shows the relevant information on every column, defined to
90 # be part of an index key, which is accessible to the current user or to PUBLIC.
91 # 3.2.7.3: Ensure that the table does not show any information on any indexed column that is
92 # not accessible to the current user or PUBLIC.
93 #
94 --disable_warnings
95 DROP DATABASE IF EXISTS db_datadict;
96 --enable_warnings
97 CREATE DATABASE db_datadict;
98
99 --error 0,ER_CANNOT_USER
100 DROP USER 'testuser1'@'localhost';
101 CREATE USER 'testuser1'@'localhost';
102 --error 0,ER_CANNOT_USER
103 DROP USER 'testuser2'@'localhost';
104 CREATE USER 'testuser2'@'localhost';
105
106 USE db_datadict;
107
108 --replace_result $engine_type <engine_type>
109 eval
110 CREATE TABLE t1_1
111 (f1 INT NOT NULL, PRIMARY KEY(f1),
112 f2 INT, INDEX f2_ind(f2))
113 ENGINE = $engine_type;
114 GRANT SELECT ON t1_1 to 'testuser1'@'localhost';
115
116 --replace_result $engine_type <engine_type>
117 eval
118 CREATE TABLE t1_2
119 (f1 INT NOT NULL, PRIMARY KEY(f1),
120 f2 INT, INDEX f2_ind(f2))
121 ENGINE = $engine_type;
122 GRANT SELECT ON t1_2 to 'testuser2'@'localhost';
123 #FIXME: add foreign keys
124
125 let $select= SELECT * FROM information_schema.key_column_usage
126 WHERE table_name LIKE 't1_%'
127 ORDER BY constraint_catalog, constraint_schema, constraint_name,
128 table_catalog, table_schema, table_name, ordinal_position;
129
130 # show view of user root
131 eval $select;
132
133 --echo # Establish connection testuser1 (user=testuser1)
134 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
135 connect (testuser1, localhost, testuser1, , db_datadict);
136 eval $select;
137
138 --echo # Establish connection testuser2 (user=testuser2)
139 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
140 connect (testuser2, localhost, testuser2, , db_datadict);
141 eval $select;
142
143 # Cleanup
144 --echo # Switch to connection default and close connections testuser1, testuser2
145 connection default;
146 disconnect testuser1;
147 disconnect testuser2;
148 DROP USER 'testuser1'@'localhost';
149 DROP USER 'testuser2'@'localhost';
150 DROP TABLE t1_1;
151 DROP TABLE t1_2;
152 DROP DATABASE IF EXISTS db_datadict;
153
154
155 --echo ########################################################################################
156 --echo # Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.KEY_COLUMN_USAGE modifications
157 --echo ########################################################################################
158 # 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
159 # column) automatically inserts all relevant information on that
160 # object into every appropriate INFORMATION_SCHEMA table.
161 # 3.2.1.14: Ensure that the alteration of any existing database object
162 # automatically updates all relevant information on that object in
163 # every appropriate INFORMATION_SCHEMA table.
164 # 3.2.1.15: Ensure that the dropping of any existing database object
165 # automatically deletes all relevant information on that object from
166 # every appropriate INFORMATION_SCHEMA table.
167 #
168 --disable_warnings
169 DROP DATABASE IF EXISTS db_datadict;
170 DROP TABLE IF EXISTS test.t1_my_table;
171 --enable_warnings
172 CREATE DATABASE db_datadict;
173
174 SELECT table_name FROM information_schema.key_column_usage
175 WHERE table_name LIKE 't1_my_table%';
176 --replace_result $engine_type <engine_type>
177 eval
178 CREATE TABLE test.t1_my_table
179 (f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1,f2))
180 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
181 ENGINE = $engine_type;
182 # Settings used in CREATE TABLE must be visible
183 # in information_schema.key_column_usage.
184 --replace_column 8 #
185 --vertical_results
186 SELECT * FROM information_schema.key_column_usage
187 WHERE table_name = 't1_my_table' ORDER BY column_name;
188 --horizontal_results
189 #
190 # Check modification of TABLE_NAME
191 SELECT DISTINCT table_name FROM information_schema.key_column_usage
192 WHERE table_name LIKE 't1_my_table%';
193 RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
194 SELECT DISTINCT table_name FROM information_schema.key_column_usage
195 WHERE table_name LIKE 't1_my_table%';
196 #
197 # Check modification of TABLE_SCHEMA
198 SELECT DISTINCT table_schema,table_name FROM information_schema.key_column_usage
199 WHERE table_name = 't1_my_tablex';
200 RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
201 SELECT DISTINCT table_schema,table_name FROM information_schema.key_column_usage
202 WHERE table_name = 't1_my_tablex';
203 #
204 # Check modification of COLUMN_NAME
205 SELECT DISTINCT table_name, column_name FROM information_schema.key_column_usage
206 WHERE table_name = 't1_my_tablex'
207 ORDER BY table_name, column_name;
208 ALTER TABLE db_datadict.t1_my_tablex CHANGE COLUMN f1 first_col CHAR(12);
209 SELECT DISTINCT table_name, column_name FROM information_schema.key_column_usage
210 WHERE table_name = 't1_my_tablex'
211 ORDER BY table_name, column_name;
212 #
213 # Note: The size of the column list and the not very selective qualification
214 # is intended. I want to see that the schema names are equal and
215 # all records about 't1_my_tablex'.
216 let $my_select = SELECT constraint_schema, constraint_name, table_schema,
217 table_name, column_name, ordinal_position
218 FROM information_schema.key_column_usage
219 WHERE table_name = 't1_my_tablex'
220 ORDER BY constraint_schema, constraint_name, table_schema,
221 table_name, ordinal_position;
222 #
223 # Check ADD INDEX being not UNIQUE (does not show up in key_column_usage)
224 eval $my_select;
225 CREATE INDEX f2 ON db_datadict.t1_my_tablex(f2);
226 eval $my_select;
227 DROP INDEX f2 ON db_datadict.t1_my_tablex;
228 #
229 # Check ADD UNIQUE INDEX without name explicit assigned
230 eval $my_select;
231 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE (f2);
232 eval $my_select;
233 DROP INDEX f2 ON db_datadict.t1_my_tablex;
234 #
235 # Check ADD UNIQUE INDEX with name explicit assigned
236 eval $my_select;
237 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f2);
238 eval $my_select;
239 DROP INDEX my_idx ON db_datadict.t1_my_tablex;
240 eval $my_select;
241 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f4,first_col);
242 eval $my_select;
243 #
244 # Check DROP COLUMN
245 eval $my_select;
246 ALTER TABLE db_datadict.t1_my_tablex
247 DROP COLUMN first_col;
248 eval $my_select;
249 #
250 # Check impact of DROP TABLE
251 SELECT table_name, column_name
252 FROM information_schema.key_column_usage
253 WHERE table_name = 't1_my_tablex'
254 ORDER BY table_name, column_name;
255 DROP TABLE db_datadict.t1_my_tablex;
256 SELECT table_name, column_name
257 FROM information_schema.key_column_usage
258 WHERE table_name = 't1_my_tablex';
259 #
260 # No UNIQUE CONSTRAINT -> no entry in key_column_usage
261 SELECT table_name FROM information_schema.key_column_usage
262 WHERE table_name = 't1_my_tablex';
263 --replace_result $engine_type <engine_type>
264 eval
265 CREATE TABLE db_datadict.t1_my_tablex
266 ENGINE = $engine_type AS
267 SELECT 1 AS f1;
268 SELECT table_name FROM information_schema.key_column_usage
269 WHERE table_name = 't1_my_tablex';
270 # UNIQUE CONSTRAINT -> entry in key_column_usage
271 ALTER TABLE db_datadict.t1_my_tablex ADD PRIMARY KEY(f1);
272 SELECT table_name FROM information_schema.key_column_usage
273 WHERE table_name = 't1_my_tablex';
274 #
275 # Check impact of DROP SCHEMA
276 SELECT table_name FROM information_schema.key_column_usage
277 WHERE table_name = 't1_my_tablex';
278 DROP DATABASE db_datadict;
279 SELECT table_name FROM information_schema.key_column_usage
280 WHERE table_name = 't1_my_tablex';
281 #
282
283
284 --echo ########################################################################
285 --echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
286 --echo # DDL on INFORMATION_SCHEMA table are not supported
287 --echo ########################################################################
288 # 3.2.1.3: Ensure that no user may execute an INSERT statement on any
289 # INFORMATION_SCHEMA table.
290 # 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
291 # INFORMATION_SCHEMA table.
292 # 3.2.1.5: Ensure that no user may execute a DELETE statement on any
293 # INFORMATION_SCHEMA table.
294 # 3.2.1.8: Ensure that no user may create an index on an
295 # INFORMATION_SCHEMA table.
296 # 3.2.1.9: Ensure that no user may alter the definition of an
297 # INFORMATION_SCHEMA table.
298 # 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
299 # 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
300 # other database.
301 # 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
302 # in an INFORMATION_SCHEMA table.
303 #
304 --disable_warnings
305 DROP DATABASE IF EXISTS db_datadict;
306 DROP TABLE IF EXISTS db_datadict.t1;
307 --enable_warnings
308 CREATE DATABASE db_datadict;
309 --replace_result $engine_type <engine_type>
310 eval
311 CREATE TABLE db_datadict.t1 (f1 BIGINT)
312 ENGINE = $engine_type;
313
314 # Note(mleich):
315 # 1. We can get here different error messages.
316 # 2. We do not want to unify the individual messages to the far to unspecific
317 # 'Got one of the listed errors'.
318 let $my_error_message =
319 ##### The previous statement must fail ######
320 # Server type | expected error name | expected error message
321 # --------------------------------------------------------------------------------------------------------------------
322 # mysql | ER_DBACCESS_DENIED_ERROR | ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
323 ;
324
325 --disable_abort_on_error
326 INSERT INTO information_schema.key_column_usage
327 (constraint_schema, constraint_name, table_name)
328 VALUES ( 'mysql', 'primary', 'db');
329 if (!$mysql_errno)
330 {
331 --echo $my_error_message
332 exit;
333 }
334 --error ER_DBACCESS_DENIED_ERROR
335 INSERT INTO information_schema.key_column_usage
336 SELECT * FROM information_schema.key_column_usage;
337
338 # Note:
339 # 1. We can get different error messages here.
340 # 2. We do not want to unify the individual messages to the far to unspecific
341 # 'Got one of the listed errors'.
342 let $my_error_message =
343 ##### The previous statement must fail ######
344 # Server type | expected error name | expected error message
345 # --------------------------------------------------------------------------------------------------------------------
346 # mysql | ER_DBACCESS_DENIED_ERROR | ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
347 ;
348 --disable_abort_on_error
349 UPDATE information_schema.key_column_usage
350 SET table_name = 'db1' WHERE constraint_name = 'primary';
351 if (!$mysql_errno)
352 {
353 --echo $my_error_message
354 exit;
355 }
356 --enable_abort_on_error
357
358 --error ER_DBACCESS_DENIED_ERROR
359 DELETE FROM information_schema.key_column_usage WHERE table_name = 't1';
360 --error ER_DBACCESS_DENIED_ERROR
361 TRUNCATE information_schema.key_column_usage;
362
363 --error ER_DBACCESS_DENIED_ERROR
364 CREATE INDEX i3 ON information_schema.key_column_usage(table_name);
365
366 --error ER_DBACCESS_DENIED_ERROR
367 ALTER TABLE information_schema.key_column_usage ADD f1 INT;
368
369 --error ER_DBACCESS_DENIED_ERROR
370 DROP TABLE information_schema.key_column_usage;
371
372 --error ER_DBACCESS_DENIED_ERROR
373 ALTER TABLE information_schema.key_column_usage
374 RENAME db_datadict.key_column_usage;
375 --error ER_DBACCESS_DENIED_ERROR
376 ALTER TABLE information_schema.key_column_usage
377 RENAME information_schema.xkey_column_usage;
378 --enable_abort_on_error
379
380 # Cleanup
381 DROP TABLE db_datadict.t1;
382 DROP DATABASE db_datadict;