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