1
# suite/funcs_1/t/tables.test
3
# Check the layout of information_schema.tables and the impact of
4
# CREATE/ALTER/DROP TABLE/VIEW/SCHEMA ... on it.
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.
15
# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
17
# Create this script based on older scripts and new code.
20
# --source suite/funcs_1/datadict/datadict.pre
22
let $engine_type = MEMORY;
23
let $other_engine_type = MyISAM;
25
let $is_table = TABLES;
27
# The table INFORMATION_SCHEMA.TABLES must exist
28
eval SHOW TABLES FROM information_schema LIKE '$is_table';
30
--echo #######################################################################
31
--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
32
--echo #######################################################################
33
# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
34
# statement, just as if it were an ordinary user-defined table.
36
--source suite/funcs_1/datadict/is_table_query.inc
39
--echo #########################################################################
40
--echo # Testcase 3.2.12.1: INFORMATION_SCHEMA.TABLES layout
41
--echo #########################################################################
42
# Ensure that the INFORMATION_SCHEMA.TABLES table has the following columns,
43
# in the following order:
45
# TABLE_CATALOG (always shows NULL),
46
# TABLE_SCHEMA (shows the name of the database, or schema, in which an
47
# accessible table resides),
48
# TABLE_NAME (shows the name of a table which the current user may access),
49
# TABLE_TYPE (shows whether the table is a BASE TABLE, a TEMPORARY table,
51
# ENGINE (shows the storage engine used for the table),
52
# VERSION (shows the version number of the table's .frm file),
53
# ROW_FORMAT (shows the table's row storage format; either FIXED, DYNAMIC
55
# TABLE_ROWS (shows the number of rows in the table),
56
# AVG_ROW_LENGTH (shows the average length of the table's rows),
57
# DATA_LENGTH (shows the length of the table's data file),
58
# MAX_DATA_LENGTH (shows the maximum length of the table's data file),
59
# INDEX_LENGTH (shows the length of the index file associated with the table),
60
# DATA_FREE (shows the number of allocated, unused bytes),
61
# AUTO_INCREMENT (shows the next AUTO_INCREMENT value, where applicable),
62
# CREATE_TIME (shows the timestamp of the time the table was created),
63
# UPDATE_TIME (shows the timestamp of the time the table's data file was
65
# CHECK_TIME (shows the timestamp of the time the table was last checked),
66
# TABLE_COLLATION (shows the table's default collation),
67
# CHECKSUM (shows the live checksum value for the table, if any; otherwise NULL),
68
# CREATE_OPTIONS (shows any additional options used in the table's definition;
70
# TABLE_COMMENT (shows the comment added to the table's definition;
73
--source suite/funcs_1/datadict/datadict_bug_12777.inc
74
eval DESCRIBE information_schema.$is_table;
75
--source suite/funcs_1/datadict/datadict_bug_12777.inc
76
eval SHOW CREATE TABLE information_schema.$is_table;
77
--source suite/funcs_1/datadict/datadict_bug_12777.inc
78
eval SHOW COLUMNS FROM information_schema.$is_table;
80
# Note: Retrieval of information within information_schema.columns about
81
# information_schema.tables is in is_columns_is.test.
83
# Show that TABLE_CATALOG is always NULL.
84
SELECT table_catalog, table_schema, table_name
85
FROM information_schema.tables WHERE table_catalog IS NOT NULL;
88
--echo ################################################################################
89
--echo # Testcase 3.2.12.2 + 3.2.12.3: INFORMATION_SCHEMA.TABLES accessible information
90
--echo ################################################################################
91
# 3.2.12.2: Ensure that the table shows the relevant information on every base table
92
# and view on which the current user or PUBLIC has privileges.
93
# 3.2.12.3: Ensure that the table does not show any information on any tables
94
# on which the current user and public have no privileges.
96
# Note: Check of content within information_schema.tables about tables within
98
# mysql is_tables_mysql.test
99
# information_schema is_tables_is.test
100
# test% is_tables_<engine>.test
103
DROP DATABASE IF EXISTS db_datadict;
105
CREATE DATABASE db_datadict;
107
--error 0,ER_CANNOT_USER
108
DROP USER 'testuser1'@'localhost';
109
CREATE USER 'testuser1'@'localhost';
110
GRANT CREATE, CREATE VIEW, INSERT, SELECT ON db_datadict.*
111
TO 'testuser1'@'localhost' WITH GRANT OPTION;
112
--error 0,ER_CANNOT_USER
113
DROP USER 'testuser2'@'localhost';
114
CREATE USER 'testuser2'@'localhost';
115
--error 0,ER_CANNOT_USER
116
DROP USER 'testuser3'@'localhost';
117
CREATE USER 'testuser3'@'localhost';
119
--replace_result $engine_type <engine_type>
121
CREATE TABLE db_datadict.tb1 (f1 INT, f2 INT, f3 INT)
122
ENGINE = $engine_type;
124
GRANT SELECT ON db_datadict.tb1 TO 'testuser1'@'localhost';
125
GRANT ALL ON db_datadict.tb1 TO 'testuser2'@'localhost' WITH GRANT OPTION;
127
let $my_select = SELECT * FROM information_schema.tables
128
WHERE table_schema = 'db_datadict' ORDER BY table_name;
129
let $my_show = SHOW TABLES FROM db_datadict;
131
--echo # Establish connection testuser1 (user=testuser1)
132
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
133
connect (testuser1, localhost, testuser1, , db_datadict);
134
# tb2 is not granted to anyone
135
--replace_result $engine_type <engine_type>
137
CREATE TABLE tb2 (f1 DECIMAL)
138
ENGINE = $engine_type;
139
--replace_result $engine_type <engine_type>
141
CREATE TABLE tb3 (f1 VARCHAR(200))
142
ENGINE = $engine_type;
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';
150
--disable_ps_protocol
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
160
# 5 ENGINE affected by storage engine used
162
# 7 ROW_FORMAT affected by storage engine used
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
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 (*)
174
# 19 CHECKSUM affected by storage engine used
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#"
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);
189
--disable_ps_protocol
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#"
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);
202
--disable_ps_protocol
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#"
210
--echo # Switch to connection default (user=root)
212
# we see only 'public' tables
215
--disable_ps_protocol
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#"
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;
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.
248
DROP TABLE IF EXISTS test.t1_my_table;
249
DROP DATABASE IF EXISTS db_datadict;
251
CREATE DATABASE db_datadict;
253
SELECT table_name FROM information_schema.tables
254
WHERE table_name LIKE 't1_my_table%';
255
--replace_result $engine_type <engine_type>
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
# Settings used in CREATE TABLE must be visible in information_schema.tables.
262
--replace_column 5 "#ENG#" 7 "#RF#" 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "#CRT" 16 "#UT#" 17 "#CT#" 19 "#CS#"
263
SELECT * FROM information_schema.tables
264
WHERE table_name = 't1_my_table';
267
# Check modification of TABLE_NAME
268
SELECT table_name FROM information_schema.tables
269
WHERE table_name LIKE 't1_my_table%';
270
RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
271
SELECT table_name FROM information_schema.tables
272
WHERE table_name LIKE 't1_my_table%';
274
# Check modification of TABLE_SCHEMA
275
SELECT table_schema,table_name FROM information_schema.tables
276
WHERE table_name = 't1_my_tablex';
277
RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
278
SELECT table_schema,table_name FROM information_schema.tables
279
WHERE table_name = 't1_my_tablex';
281
# Check modification of ENGINE
282
--replace_result $engine_type <engine_type>
283
SELECT table_name, engine FROM information_schema.tables
284
WHERE table_name = 't1_my_tablex';
285
--replace_result $other_engine_type <other_engine_type>
287
ALTER TABLE db_datadict.t1_my_tablex
288
ENGINE = $other_engine_type;
289
--replace_result $other_engine_type <other_engine_type>
290
SELECT table_name, engine FROM information_schema.tables
291
WHERE table_name = 't1_my_tablex';
293
# Check modification of TABLE_ROWS
294
SELECT table_name, table_rows FROM information_schema.tables
295
WHERE table_name = 't1_my_tablex';
296
INSERT INTO db_datadict.t1_my_tablex VALUES(1),(2);
297
SELECT table_name, table_rows FROM information_schema.tables
298
WHERE table_name = 't1_my_tablex';
300
# Check indirect modification of TABLE_COLLATION
301
SELECT table_name, table_collation FROM information_schema.tables
302
WHERE table_name = 't1_my_tablex';
303
ALTER TABLE db_datadict.t1_my_tablex DEFAULT CHARACTER SET utf8;
304
SELECT table_name, table_collation FROM information_schema.tables
305
WHERE table_name = 't1_my_tablex';
306
# Check direct modification of TABLE_COLLATION
307
SELECT table_name, table_collation FROM information_schema.tables
308
WHERE table_name = 't1_my_tablex';
309
ALTER TABLE db_datadict.t1_my_tablex
310
DEFAULT CHARACTER SET latin1 COLLATE latin1_german1_ci;
311
SELECT table_name, table_collation FROM information_schema.tables
312
WHERE table_name = 't1_my_tablex';
314
# Check modification of TABLE_COMMENT
315
SELECT table_name, TABLE_COMMENT FROM information_schema.tables
316
WHERE table_name = 't1_my_tablex';
317
ALTER TABLE db_datadict.t1_my_tablex COMMENT 'Changed Comment';
318
SELECT table_name, TABLE_COMMENT FROM information_schema.tables
319
WHERE table_name = 't1_my_tablex';
321
# Check modification of AUTO_INCREMENT
322
SELECT table_name, AUTO_INCREMENT FROM information_schema.tables
323
WHERE table_name = 't1_my_tablex';
324
ALTER TABLE db_datadict.t1_my_tablex
325
ADD f2 BIGINT AUTO_INCREMENT, ADD PRIMARY KEY (f2);
326
SELECT table_name, AUTO_INCREMENT FROM information_schema.tables
327
WHERE table_name = 't1_my_tablex';
329
# Check modification of ROW_FORMAT
330
SELECT table_name, ROW_FORMAT FROM information_schema.tables
331
WHERE table_name = 't1_my_tablex';
332
ALTER TABLE db_datadict.t1_my_tablex ROW_FORMAT = dynamic;
333
SELECT table_name, ROW_FORMAT FROM information_schema.tables
334
WHERE table_name = 't1_my_tablex';
336
# Check "growth" of UPDATE_TIME and modification of CHECKSUM
337
SELECT table_name, checksum FROM information_schema.tables
338
WHERE table_name = 't1_my_tablex';
339
ALTER TABLE db_datadict.t1_my_tablex CHECKSUM = 1;
340
SELECT table_name, checksum IS NOT NULL FROM information_schema.tables
341
WHERE table_name = 't1_my_tablex';
342
SELECT UPDATE_TIME, checksum INTO @UPDATE_TIME, @checksum
343
FROM information_schema.tables
344
WHERE table_name = 't1_my_tablex';
345
# Enforce a time difference bigger than the smallest unit (1 second).
347
INSERT INTO db_datadict.t1_my_tablex SET f1 = 3;
348
SELECT UPDATE_TIME > @UPDATE_TIME
349
AS "Is current UPDATE_TIME bigger than before last INSERT?"
350
FROM information_schema.tables
351
WHERE table_name = 't1_my_tablex';
352
SELECT checksum <> @checksum
353
AS "Is current CHECKSUM different than before last INSERT?"
354
FROM information_schema.tables
355
WHERE table_name = 't1_my_tablex';
357
# Information is used later
358
SELECT CREATE_TIME INTO @CREATE_TIME FROM information_schema.tables
359
WHERE table_name = 't1_my_tablex';
361
# Check impact of DROP TABLE
362
SELECT table_name FROM information_schema.tables
363
WHERE table_name LIKE 't1_my_table%';
364
DROP TABLE db_datadict.t1_my_tablex;
365
SELECT table_name FROM information_schema.tables
366
WHERE table_name LIKE 't1_my_table%';
368
# Check "growth" of CREATE_TIME
369
--replace_result $other_engine_type <other_engine_type>
371
CREATE TABLE test.t1_my_tablex (f1 BIGINT)
372
ENGINE = $other_engine_type;
373
SELECT CREATE_TIME > @CREATE_TIME
374
AS "Is current CREATE_TIME bigger than for the old dropped table?"
375
FROM information_schema.tables
376
WHERE table_name = 't1_my_tablex';
377
DROP TABLE test.t1_my_tablex;
380
CREATE VIEW test.t1_my_tablex AS SELECT 1;
382
SELECT * FROM information_schema.tables
383
WHERE table_name = 't1_my_tablex';
385
DROP VIEW test.t1_my_tablex;
386
SELECT table_name FROM information_schema.tables
387
WHERE table_name = 't1_my_tablex';
389
# Check a temporary table
390
--replace_result $other_engine_type <other_engine_type>
392
CREATE TEMPORARY TABLE test.t1_my_tablex
393
ENGINE = $other_engine_type
396
SELECT table_name, table_type FROM information_schema.tables
397
WHERE table_name = 't1_my_tablex';
399
DROP TEMPORARY TABLE test.t1_my_tablex;
401
# Check impact of DROP SCHEMA
402
--replace_result $engine_type <engine_type>
404
CREATE TABLE db_datadict.t1_my_tablex
405
ENGINE = $engine_type AS
407
SELECT table_name FROM information_schema.tables
408
WHERE table_name = 't1_my_tablex';
409
DROP DATABASE db_datadict;
410
SELECT table_name FROM information_schema.tables
411
WHERE table_name = 't1_my_tablex';
414
--echo ########################################################################
415
--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
416
--echo # DDL on INFORMATION_SCHEMA tables are not supported
417
--echo ########################################################################
418
# 3.2.1.3: Ensure that no user may execute an INSERT statement on any
419
# INFORMATION_SCHEMA table.
420
# 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
421
# INFORMATION_SCHEMA table.
422
# 3.2.1.5: Ensure that no user may execute a DELETE statement on any
423
# INFORMATION_SCHEMA table.
424
# 3.2.1.8: Ensure that no user may create an index on an
425
# INFORMATION_SCHEMA table.
426
# 3.2.1.9: Ensure that no user may alter the definition of an
427
# INFORMATION_SCHEMA table.
428
# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
429
# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
431
# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
432
# in an INFORMATION_SCHEMA table.
435
DROP DATABASE IF EXISTS db_datadict;
437
CREATE DATABASE db_datadict;
438
--replace_result $engine_type <engine_type>
440
CREATE TABLE db_datadict.t1 (f1 BIGINT)
441
ENGINE = $engine_type;
443
--error ER_DBACCESS_DENIED_ERROR
444
INSERT INTO information_schema.tables
445
SELECT * FROM information_schema.tables;
447
--error ER_DBACCESS_DENIED_ERROR
448
UPDATE information_schema.tables SET table_schema = 'test'
449
WHERE table_name = 't1';
451
--error ER_DBACCESS_DENIED_ERROR
452
DELETE FROM information_schema.tables WHERE table_name = 't1';
453
--error ER_DBACCESS_DENIED_ERROR
454
TRUNCATE information_schema.tables;
456
--error ER_DBACCESS_DENIED_ERROR
457
CREATE INDEX my_idx_on_tables ON information_schema.tables(table_schema);
459
--error ER_DBACCESS_DENIED_ERROR
460
ALTER TABLE information_schema.tables DROP PRIMARY KEY;
461
--error ER_DBACCESS_DENIED_ERROR
462
ALTER TABLE information_schema.tables ADD f1 INT;
464
--error ER_DBACCESS_DENIED_ERROR
465
DROP TABLE information_schema.tables;
467
--error ER_DBACCESS_DENIED_ERROR
468
ALTER TABLE information_schema.tables RENAME db_datadict.tables;
469
--error ER_DBACCESS_DENIED_ERROR
470
ALTER TABLE information_schema.tables RENAME information_schema.xtables;
473
DROP DATABASE db_datadict;
added
removed
mysql-test/suite/funcs_1/t/is_tables.test
