Debian Codegraph @

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

raw
1 # suite/funcs_1/t/is_engines.test
2 #
3 # Check the layout of information_schema.engines
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 and $other_engine_type.
11 # Some results of the subtests depend on the storage engines assigned.
12 #
13 # Author:
14 # 2008-02-29 mleich WL#4203 Reorganize and fix the data dictionary tests of
15 # testsuite funcs_1
16 #
17
18 let $engine_type = MEMORY;
19 let $other_engine_type = MyISAM;
20
21 let $is_table = ENGINES;
22
23 # The table INFORMATION_SCHEMA.ENGINES must exist
24 eval SHOW TABLES FROM information_schema LIKE '$is_table';
25
26 --echo #######################################################################
27 --echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
28 --echo #######################################################################
29 # Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
30 # statement, just as if it were an ordinary user-defined table.
31 #
32 --source suite/funcs_1/datadict/is_table_query.inc
33
34
35 --echo #########################################################################
36 --echo # Testcase 3.2.12.1: INFORMATION_SCHEMA.ENGINES layout
37 --echo #########################################################################
38 # Ensure that the INFORMATION_SCHEMA.ENGINES table has the following columns,
39 # in the following order:
40 #
41 # ENGINE
42 # SUPPORT
43 # COMMENT
44 # TRANSACTIONS
45 # XA
46 # SAVEPOINTS
47 #
48 # Value Meaning
49 # YES The feature is supported and is active.
50 # NO The feature is not supported = The server was compiled without
51 # support for the feature.
52 # DISABLED The feature is supported but has been disabled.
53 #
54 eval DESCRIBE information_schema.$is_table;
55 eval SHOW CREATE TABLE information_schema.$is_table;
56 eval SHOW COLUMNS FROM information_schema.$is_table;
57
58 # Note: Retrieval of information within information_schema.columns about
59 # information_schema.engines is in is_columns_is.test.
60
61 # FIXME: Check the regression tests and implement tests checking the
62 # functionality if missing.
63
64
65 --echo ########################################################################
66 --echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
67 --echo # DDL on INFORMATION_SCHEMA tables are not supported
68 --echo ########################################################################
69 # 3.2.1.3: Ensure that no user may execute an INSERT statement on any
70 # INFORMATION_SCHEMA table.
71 # 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
72 # INFORMATION_SCHEMA table.
73 # 3.2.1.5: Ensure that no user may execute a DELETE statement on any
74 # INFORMATION_SCHEMA table.
75 # 3.2.1.8: Ensure that no user may create an index on an
76 # INFORMATION_SCHEMA table.
77 # 3.2.1.9: Ensure that no user may alter the definition of an
78 # INFORMATION_SCHEMA table.
79 # 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
80 # 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
81 # other database.
82 # 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
83 # in an INFORMATION_SCHEMA table.
84 #
85 --disable_warnings
86 DROP DATABASE IF EXISTS db_datadict;
87 --enable_warnings
88 CREATE DATABASE db_datadict;
89 --replace_result $engine_type <engine_type>
90 eval
91 CREATE TABLE db_datadict.t1 (f1 BIGINT)
92 ENGINE = $engine_type;
93
94 --error ER_DBACCESS_DENIED_ERROR
95 INSERT INTO information_schema.engines
96 SELECT * FROM information_schema.engines;
97
98 --error ER_DBACCESS_DENIED_ERROR,ER_NON_UPDATABLE_TABLE
99 UPDATE information_schema.engines SET engine = '1234567';
100
101 --error ER_DBACCESS_DENIED_ERROR
102 DELETE FROM information_schema.engines WHERE support IN ('DEFAULT','YES');
103 --error ER_DBACCESS_DENIED_ERROR
104 TRUNCATE information_schema.engines;
105
106 --error ER_DBACCESS_DENIED_ERROR
107 CREATE INDEX my_idx_on_engines ON information_schema.engines(engine);
108
109 --error ER_DBACCESS_DENIED_ERROR
110 ALTER TABLE information_schema.engines DROP PRIMARY KEY;
111 --error ER_DBACCESS_DENIED_ERROR
112 ALTER TABLE information_schema.engines ADD f1 INT;
113
114 --error ER_DBACCESS_DENIED_ERROR
115 DROP TABLE information_schema.engines;
116
117 --error ER_DBACCESS_DENIED_ERROR
118 ALTER TABLE information_schema.engines RENAME db_datadict.engines;
119 --error ER_DBACCESS_DENIED_ERROR
120 ALTER TABLE information_schema.engines RENAME information_schema.xengines;
121
122 # Cleanup
123 DROP DATABASE db_datadict;
124