Debian Codegraph @

mysql-test/suite/funcs_1/datadict/is_tables.inc

raw
1 # suite/funcs_1/datadict/is_tables.inc
2 #
3 # Check the layout of information_schema.tables and the impact of
4 # CREATE/ALTER/DROP TABLE/VIEW/SCHEMA ... on it.
5 #
6 # Note:
7 # This test is not intended
8 # - to show information about the all time existing tables
9 # within the databases information_schema and mysql
10 # - for checking storage engine properties
11 # Therefore please do not alter $engine_type and $other_engine_type.
12 # Some results of the subtests depend on the storage engines assigned.
13 #
14 # Author:
15 # 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
16 # testsuite funcs_1
17 # Create this script based on older scripts and new code.
18 # Last Change:
19 # 2008-06-11 mleich Move t/is_tables.test to this file and
20 #
21
22 let $is_table = TABLES;
23
24 # The table INFORMATION_SCHEMA.TABLES 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.TABLES layout
38 --echo #########################################################################
39 # Ensure that the INFORMATION_SCHEMA.TABLES table has the following columns,
40 # in the following order:
41 #
42 # TABLE_CATALOG (always shows 'def'),
43 # TABLE_SCHEMA (shows the name of the database, or schema, in which an
44 # accessible table resides),
45 # TABLE_NAME (shows the name of a table which the current user may access),
46 # TABLE_TYPE (shows whether the table is a BASE TABLE, a TEMPORARY table,
47 # or a VIEW),
48 # ENGINE (shows the storage engine used for the table),
49 # VERSION (shows the version number of the table's .frm file),
50 # ROW_FORMAT (shows the table's row storage format; either FIXED, DYNAMIC
51 # or COMPRESSED),
52 # TABLE_ROWS (shows the number of rows in the table),
53 # AVG_ROW_LENGTH (shows the average length of the table's rows),
54 # DATA_LENGTH (shows the length of the table's data file),
55 # MAX_DATA_LENGTH (shows the maximum length of the table's data file),
56 # INDEX_LENGTH (shows the length of the index file associated with the table),
57 # DATA_FREE (shows the number of allocated, unused bytes),
58 # AUTO_INCREMENT (shows the next AUTO_INCREMENT value, where applicable),
59 # CREATE_TIME (shows the timestamp of the time the table was created),
60 # UPDATE_TIME (shows the timestamp of the time the table's data file was
61 # last updated),
62 # CHECK_TIME (shows the timestamp of the time the table was last checked),
63 # TABLE_COLLATION (shows the table's default collation),
64 # CHECKSUM (shows the live checksum value for the table, if any; otherwise NULL),
65 # CREATE_OPTIONS (shows any additional options used in the table's definition;
66 # otherwise NULL),
67 # TABLE_COMMENT (shows the comment added to the table's definition;
68 # otherwise NULL).
69 #
70 --source suite/funcs_1/datadict/datadict_bug_12777.inc
71 eval DESCRIBE information_schema.$is_table;
72 --source suite/funcs_1/datadict/datadict_bug_12777.inc
73 eval SHOW CREATE TABLE information_schema.$is_table;
74 --source suite/funcs_1/datadict/datadict_bug_12777.inc
75 eval SHOW COLUMNS FROM information_schema.$is_table;
76
77 # Note: Retrieval of information within information_schema.columns about
78 # information_schema.tables is in is_columns_is.test.
79
80 # Show that TABLE_CATALOG is always 'def'.
81 SELECT table_catalog, table_schema, table_name
82 FROM information_schema.tables WHERE table_catalog IS NULL OR table_catalog <> 'def';
83
84
85 --echo ################################################################################
86 --echo # Testcase 3.2.12.2 + 3.2.12.3: INFORMATION_SCHEMA.TABLES accessible information
87 --echo ################################################################################
88 # 3.2.12.2: Ensure that the table shows the relevant information on every base table
89 # and view on which the current user or PUBLIC has privileges.
90 # 3.2.12.3: Ensure that the table does not show any information on any tables
91 # on which the current user and public have no privileges.
92 #
93 # Note: Check of content within information_schema.tables about tables within
94 # database is in
95 # mysql is_tables_mysql.test
96 # information_schema is_tables_is.test
97 # test% is_tables_<engine>.test
98 #
99 --disable_warnings
100 DROP DATABASE IF EXISTS db_datadict;
101 --enable_warnings
102 CREATE DATABASE db_datadict charset latin1;
103
104 --error 0,ER_CANNOT_USER
105 DROP USER 'testuser1'@'localhost';
106 CREATE USER 'testuser1'@'localhost';
107 GRANT CREATE, CREATE VIEW, INSERT, SELECT ON db_datadict.*
108 TO 'testuser1'@'localhost' WITH GRANT OPTION;
109 --error 0,ER_CANNOT_USER
110 DROP USER 'testuser2'@'localhost';
111 CREATE USER 'testuser2'@'localhost';
112 --error 0,ER_CANNOT_USER
113 DROP USER 'testuser3'@'localhost';
114 CREATE USER 'testuser3'@'localhost';
115
116 --replace_result $engine_type <engine_type>
117 eval
118 CREATE TABLE db_datadict.tb1 (f1 INT, f2 INT, f3 INT)
119 ENGINE = $engine_type;
120 ANALYZE TABLE db_datadict.tb1;
121
122 GRANT SELECT ON db_datadict.tb1 TO 'testuser1'@'localhost';
123 GRANT ALL ON db_datadict.tb1 TO 'testuser2'@'localhost' WITH GRANT OPTION;
124
125 let $my_select = SELECT * FROM information_schema.tables
126 WHERE table_schema = 'db_datadict' ORDER BY table_name;
127 let $my_show = SHOW TABLES FROM db_datadict;
128
129 --echo # Establish connection testuser1 (user=testuser1)
130 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
131 connect (testuser1, localhost, testuser1, , db_datadict);
132 # tb2 is not granted to anyone
133 --replace_result $engine_type <engine_type>
134 eval
135 CREATE TABLE tb2 (f1 DECIMAL)
136 ENGINE = $engine_type;
137 ANALYZE TABLE tb2;
138 --replace_result $engine_type <engine_type>
139 eval
140 CREATE TABLE tb3 (f1 VARCHAR(200))
141 ENGINE = $engine_type;
142 ANALYZE TABLE tb3;
143 GRANT SELECT ON db_datadict.tb3 to 'testuser3'@'localhost';
144 GRANT INSERT ON db_datadict.tb3 to 'testuser2'@'localhost';
145 CREATE VIEW v3 AS SELECT * FROM tb3;
146 GRANT SELECT ON db_datadict.v3 to 'testuser3'@'localhost';
147
148 if ($have_bug_32285)
149 {
150 --disable_ps_protocol
151 }
152 # We do not want to check here values affected by
153 # - the storage engine used
154 # - Operating system / Filesystem
155 # - start time of test
156 # 1 TABLE_CATALOG
157 # 2 TABLE_SCHEMA
158 # 3 TABLE_NAME
159 # 4 TABLE_TYPE
160 # 5 ENGINE affected by storage engine used
161 # 6 VERSION
162 # 7 ROW_FORMAT affected by storage engine used
163 # 8 TABLE_ROWS
164 # 9 AVG_ROW_LENGTH affected by storage engine used
165 # 10 DATA_LENGTH affected by storage engine used and maybe OS
166 # 11 MAX_DATA_LENGTH affected by storage engine used and maybe OS
167 # 12 INDEX_LENGTH affected by storage engine used and maybe OS
168 # 13 DATA_FREE affected by storage engine used and maybe OS
169 # 14 AUTO_INCREMENT
170 # 15 CREATE_TIME depends roughly on start time of test (*)
171 # 16 UPDATE_TIME depends roughly on start time of test (*)
172 # 17 CHECK_TIME depends roughly on start time of test and storage engine (*)
173 # 18 TABLE_COLLATION
174 # 19 CHECKSUM affected by storage engine used
175 # 20 CREATE_OPTIONS
176 # 21 TABLE_COMMENT affected by some storage engines
177 # (*) In case of view or temporary table NULL.
178 --replace_column 5 "#ENG#" 7 "#RF#" 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "#CRT" 16 "#UT#" 17 "#CT#" 19 "#CS#"
179 eval $my_select;
180 --enable_ps_protocol
181 --sorted_result
182 eval $my_show;
183
184 --echo # Establish connection testuser2 (user=testuser2)
185 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
186 connect (testuser2, localhost, testuser2, , db_datadict);
187 if ($have_bug_32285)
188 {
189 --disable_ps_protocol
190 }
191 --replace_column 5 "#ENG#" 7 "#RF#" 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "#CRT" 16 "#UT#" 17 "#CT#" 19 "#CS#"
192 eval $my_select;
193 --enable_ps_protocol
194 --sorted_result
195 eval $my_show;
196
197 --echo # Establish connection testuser3 (user=testuser3)
198 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
199 connect (testuser3, localhost, testuser3, , db_datadict);
200 if ($have_bug_32285)
201 {
202 --disable_ps_protocol
203 }
204 --replace_column 5 "#ENG#" 7 "#RF#" 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "#CRT" 16 "#UT#" 17 "#CT#" 19 "#CS#"
205 eval $my_select;
206 --enable_ps_protocol
207 --sorted_result
208 eval $my_show;
209
210 --echo # Switch to connection default (user=root)
211 connection default;
212 # we see only 'public' tables
213 if ($have_bug_32285)
214 {
215 --disable_ps_protocol
216 }
217 --replace_column 5 "#ENG#" 7 "#RF#" 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "#CRT" 16 "#UT#" 17 "#CT#" 19 "#CS#"
218 eval $my_select;
219 --enable_ps_protocol
220 --sorted_result
221 eval $my_show;
222
223 # Cleanup
224 --echo # Close connection testuser1, testuser2, testuser3
225 disconnect testuser1;
226 disconnect testuser2;
227 disconnect testuser3;
228 DROP USER 'testuser1'@'localhost';
229 DROP USER 'testuser2'@'localhost';
230 DROP USER 'testuser3'@'localhost';
231 DROP DATABASE db_datadict;
232
233
234 --echo #########################################################################
235 --echo # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TABLES modifications
236 --echo #########################################################################
237 # 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
238 # column) automatically inserts all relevant information on that
239 # object into every appropriate INFORMATION_SCHEMA table.
240 # 3.2.1.14: Ensure that the alteration of any existing database object
241 # automatically updates all relevant information on that object in
242 # every appropriate INFORMATION_SCHEMA table.
243 # 3.2.1.15: Ensure that the dropping of any existing database object
244 # automatically deletes all relevant information on that object from
245 # every appropriate INFORMATION_SCHEMA table.
246 #
247 --disable_warnings
248 DROP TABLE IF EXISTS test.t1_my_table;
249 DROP DATABASE IF EXISTS db_datadict;
250 --enable_warnings
251 CREATE DATABASE db_datadict charset latin1;
252
253 SELECT table_name FROM information_schema.tables
254 WHERE table_name LIKE 't1_my_table%';
255 --replace_result $engine_type <engine_type>
256 eval
257 CREATE TABLE test.t1_my_table (f1 BIGINT)
258 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
259 COMMENT = 'Initial Comment' ENGINE = $engine_type;
260 ANALYZE TABLE test.t1_my_table;
261 # Settings used in CREATE TABLE must be visible in information_schema.tables.
262 --vertical_results
263 --replace_column 5 "#ENG#" 7 "#RF#" 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "#CRT" 16 "#UT#" 17 "#CT#" 19 "#CS#"
264 SELECT * FROM information_schema.tables
265 WHERE table_name = 't1_my_table';
266 --horizontal_results
267 #
268 # Check modification of TABLE_NAME
269 SELECT table_name FROM information_schema.tables
270 WHERE table_name LIKE 't1_my_table%';
271 RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
272 ANALYZE TABLE test.t1_my_tablex;
273 SELECT table_name FROM information_schema.tables
274 WHERE table_name LIKE 't1_my_table%';
275 #
276 # Check modification of TABLE_SCHEMA
277 SELECT table_schema,table_name FROM information_schema.tables
278 WHERE table_name = 't1_my_tablex';
279 RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
280 SELECT table_schema,table_name FROM information_schema.tables
281 WHERE table_name = 't1_my_tablex';
282 #
283 # Check modification of ENGINE
284 --replace_result $engine_type <engine_type>
285 SELECT table_name, engine FROM information_schema.tables
286 WHERE table_name = 't1_my_tablex';
287 --replace_result $other_engine_type <other_engine_type>
288 eval
289 ALTER TABLE db_datadict.t1_my_tablex
290 ENGINE = $other_engine_type;
291 ANALYZE TABLE db_datadict.t1_my_tablex;
292 --replace_result $other_engine_type <other_engine_type>
293 SELECT table_name, engine FROM information_schema.tables
294 WHERE table_name = 't1_my_tablex';
295 #
296 # Check modification of TABLE_ROWS
297 SELECT table_name, table_rows FROM information_schema.tables
298 WHERE table_name = 't1_my_tablex';
299 INSERT INTO db_datadict.t1_my_tablex VALUES(1),(2);
300 ANALYZE TABLE db_datadict.t1_my_tablex;
301 SELECT table_name, table_rows FROM information_schema.tables
302 WHERE table_name = 't1_my_tablex';
303 #
304 # Check indirect modification of TABLE_COLLATION
305 SELECT table_name, table_collation FROM information_schema.tables
306 WHERE table_name = 't1_my_tablex';
307 ALTER TABLE db_datadict.t1_my_tablex DEFAULT CHARACTER SET utf8;
308 SELECT table_name, table_collation FROM information_schema.tables
309 WHERE table_name = 't1_my_tablex';
310 # Check direct modification of TABLE_COLLATION
311 SELECT table_name, table_collation FROM information_schema.tables
312 WHERE table_name = 't1_my_tablex';
313 ALTER TABLE db_datadict.t1_my_tablex
314 DEFAULT CHARACTER SET latin1 COLLATE latin1_german1_ci;
315 SELECT table_name, table_collation FROM information_schema.tables
316 WHERE table_name = 't1_my_tablex';
317 #
318 # Check modification of TABLE_COMMENT
319 SELECT table_name, TABLE_COMMENT FROM information_schema.tables
320 WHERE table_name = 't1_my_tablex';
321 ALTER TABLE db_datadict.t1_my_tablex COMMENT 'Changed Comment';
322 SELECT table_name, TABLE_COMMENT FROM information_schema.tables
323 WHERE table_name = 't1_my_tablex';
324 #
325 # Check modification of AUTO_INCREMENT
326 SELECT table_name, AUTO_INCREMENT FROM information_schema.tables
327 WHERE table_name = 't1_my_tablex';
328 ALTER TABLE db_datadict.t1_my_tablex
329 ADD f2 BIGINT AUTO_INCREMENT, ADD PRIMARY KEY (f2);
330 ANALYZE TABLE db_datadict.t1_my_tablex;
331 SELECT table_name, AUTO_INCREMENT FROM information_schema.tables
332 WHERE table_name = 't1_my_tablex';
333 #
334 # Check modification of ROW_FORMAT
335 SELECT table_name, ROW_FORMAT FROM information_schema.tables
336 WHERE table_name = 't1_my_tablex';
337 ALTER TABLE db_datadict.t1_my_tablex ROW_FORMAT = dynamic;
338 ANALYZE TABLE db_datadict.t1_my_tablex;
339 SELECT table_name, ROW_FORMAT FROM information_schema.tables
340 WHERE table_name = 't1_my_tablex';
341 #
342 # Check "growth" of UPDATE_TIME and modification of CHECKSUM
343 SELECT table_name, checksum FROM information_schema.tables
344 WHERE table_name = 't1_my_tablex';
345 ALTER TABLE db_datadict.t1_my_tablex CHECKSUM = 1;
346 ANALYZE TABLE db_datadict.t1_my_tablex;
347 SELECT table_name, checksum IS NOT NULL FROM information_schema.tables
348 WHERE table_name = 't1_my_tablex';
349 SELECT UPDATE_TIME, checksum INTO @UPDATE_TIME, @checksum
350 FROM information_schema.tables
351 WHERE table_name = 't1_my_tablex';
352 # Enforce a time difference bigger than the smallest unit (1 second).
353 --sleep 1.1
354 INSERT INTO db_datadict.t1_my_tablex SET f1 = 3;
355 FLUSH TABLES;
356 ANALYZE TABLE db_datadict.t1_my_tablex;
357 SELECT UPDATE_TIME > @UPDATE_TIME
358 AS "Is current UPDATE_TIME bigger than before last INSERT?"
359 FROM information_schema.tables
360 WHERE table_name = 't1_my_tablex';
361 SELECT checksum <> @checksum
362 AS "Is current CHECKSUM different than before last INSERT?"
363 FROM information_schema.tables
364 WHERE table_name = 't1_my_tablex';
365 #
366 # Information is used later
367 SELECT CREATE_TIME INTO @CREATE_TIME FROM information_schema.tables
368 WHERE table_name = 't1_my_tablex';
369 #
370 # Check impact of DROP TABLE
371 SELECT table_name FROM information_schema.tables
372 WHERE table_name LIKE 't1_my_table%';
373 DROP TABLE db_datadict.t1_my_tablex;
374 SELECT table_name FROM information_schema.tables
375 WHERE table_name LIKE 't1_my_table%';
376 #
377 # Check "growth" of CREATE_TIME
378 --replace_result $other_engine_type <other_engine_type>
379 eval
380 CREATE TABLE test.t1_my_tablex (f1 BIGINT)
381 ENGINE = $other_engine_type;
382 ANALYZE TABLE test.t1_my_tablex;
383 SELECT CREATE_TIME > @CREATE_TIME
384 AS "Is current CREATE_TIME bigger than for the old dropped table?"
385 FROM information_schema.tables
386 WHERE table_name = 't1_my_tablex';
387 DROP TABLE test.t1_my_tablex;
388 #
389 # Check a VIEW
390 CREATE VIEW test.t1_my_tablex AS SELECT 1;
391 ANALYZE TABLE test.t1_my_tablex;
392 --vertical_results
393 --replace_column 15 "#CRT"
394 SELECT * FROM information_schema.tables
395 WHERE table_name = 't1_my_tablex';
396 --horizontal_results
397 DROP VIEW test.t1_my_tablex;
398 SELECT table_name FROM information_schema.tables
399 WHERE table_name = 't1_my_tablex';
400 #
401 # Check a temporary table
402 --replace_result $other_engine_type <other_engine_type>
403 eval
404 CREATE TEMPORARY TABLE test.t1_my_tablex
405 ENGINE = $other_engine_type
406 AS SELECT 1;
407 --vertical_results
408 SELECT table_name, table_type FROM information_schema.tables
409 WHERE table_name = 't1_my_tablex';
410 --horizontal_results
411 DROP TEMPORARY TABLE test.t1_my_tablex;
412 #
413 # Check impact of DROP SCHEMA
414 --replace_result $engine_type <engine_type>
415 eval
416 CREATE TABLE db_datadict.t1_my_tablex
417 ENGINE = $engine_type AS
418 SELECT 1;
419 ANALYZE TABLE db_datadict.t1_my_tablex;
420 SELECT table_name FROM information_schema.tables
421 WHERE table_name = 't1_my_tablex';
422 DROP DATABASE db_datadict;
423 SELECT table_name FROM information_schema.tables
424 WHERE table_name = 't1_my_tablex';
425
426
427 --echo ########################################################################
428 --echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
429 --echo # DDL on INFORMATION_SCHEMA tables are not supported
430 --echo ########################################################################
431 # 3.2.1.3: Ensure that no user may execute an INSERT statement on any
432 # INFORMATION_SCHEMA table.
433 # 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
434 # INFORMATION_SCHEMA table.
435 # 3.2.1.5: Ensure that no user may execute a DELETE statement on any
436 # INFORMATION_SCHEMA table.
437 # 3.2.1.8: Ensure that no user may create an index on an
438 # INFORMATION_SCHEMA table.
439 # 3.2.1.9: Ensure that no user may alter the definition of an
440 # INFORMATION_SCHEMA table.
441 # 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
442 # 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
443 # other database.
444 # 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
445 # in an INFORMATION_SCHEMA table.
446 #
447 --disable_warnings
448 DROP DATABASE IF EXISTS db_datadict;
449 --enable_warnings
450 CREATE DATABASE db_datadict charset latin1;
451 --replace_result $engine_type <engine_type>
452 eval
453 CREATE TABLE db_datadict.t1 (f1 BIGINT)
454 ENGINE = $engine_type;
455 ANALYZE TABLE db_datadict.t1;
456
457 --error ER_DBACCESS_DENIED_ERROR
458 INSERT INTO information_schema.tables
459 SELECT * FROM information_schema.tables;
460
461 # Note:
462 # 1. We can get different error messages here.
463 # 2. We do not want to unify the individual messages to the far to unspecific
464 # 'Got one of the listed errors'.
465 let $my_error_message =
466 ##### The previous statement must fail ######
467 # Server type | expected error name | expected error message
468 # --------------------------------------------------------------------------------------------------------------------
469 # MySQL | ER_DBACCESS_DENIED_ERROR | ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
470 ;
471 --disable_abort_on_error
472 UPDATE information_schema.tables SET table_schema = 'test'
473 WHERE table_name = 't1';
474 if (!$mysql_errno)
475 {
476 --echo $my_error_message
477 exit;
478 }
479 --enable_abort_on_error
480
481
482 --error ER_DBACCESS_DENIED_ERROR
483 DELETE FROM information_schema.tables WHERE table_name = 't1';
484 --error ER_DBACCESS_DENIED_ERROR
485 TRUNCATE information_schema.tables;
486
487 --error ER_DBACCESS_DENIED_ERROR
488 CREATE INDEX my_idx_on_tables ON information_schema.tables(table_schema);
489
490 --error ER_DBACCESS_DENIED_ERROR
491 ALTER TABLE information_schema.tables DROP PRIMARY KEY;
492 --error ER_DBACCESS_DENIED_ERROR
493 ALTER TABLE information_schema.tables ADD f1 INT;
494
495 --error ER_DBACCESS_DENIED_ERROR
496 DROP TABLE information_schema.tables;
497
498 --error ER_DBACCESS_DENIED_ERROR
499 ALTER TABLE information_schema.tables RENAME db_datadict.tables;
500 --error ER_DBACCESS_DENIED_ERROR
501 ALTER TABLE information_schema.tables RENAME information_schema.xtables;
502
503 # Cleanup
504 DROP DATABASE db_datadict;
505