1
# suite/funcs_1/t/is_engines.test
3
# Check the layout of information_schema.engines
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.
14
# 2008-02-29 mleich WL#4203 Reorganize and fix the data dictionary tests of
18
# --source suite/funcs_1/datadict/datadict.pre
20
let $engine_type = MEMORY;
21
let $other_engine_type = MyISAM;
23
let $is_table = ENGINES;
25
# The table INFORMATION_SCHEMA.ENGINES must exist
26
eval SHOW TABLES FROM information_schema LIKE '$is_table';
28
--echo #######################################################################
29
--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
30
--echo #######################################################################
31
# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
32
# statement, just as if it were an ordinary user-defined table.
34
--source suite/funcs_1/datadict/is_table_query.inc
37
--echo #########################################################################
38
--echo # Testcase 3.2.12.1: INFORMATION_SCHEMA.ENGINES layout
39
--echo #########################################################################
40
# Ensure that the INFORMATION_SCHEMA.ENGINES table has the following columns,
41
# in the following order:
51
# YES The feature is supported and is active.
52
# NO The feature is not supported = The server was compiled without
53
# support for the feature.
54
# DISABLED The feature is supported but has been disabled.
56
eval DESCRIBE information_schema.$is_table;
57
eval SHOW CREATE TABLE information_schema.$is_table;
58
eval SHOW COLUMNS FROM information_schema.$is_table;
60
# Note: Retrieval of information within information_schema.columns about
61
# information_schema.engines is in is_columns_is.test.
63
# FIXME: Check the regression tests and implement tests checking the
64
# functionality if missing.
67
--echo ########################################################################
68
--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
69
--echo # DDL on INFORMATION_SCHEMA tables are not supported
70
--echo ########################################################################
71
# 3.2.1.3: Ensure that no user may execute an INSERT statement on any
72
# INFORMATION_SCHEMA table.
73
# 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
74
# INFORMATION_SCHEMA table.
75
# 3.2.1.5: Ensure that no user may execute a DELETE statement on any
76
# INFORMATION_SCHEMA table.
77
# 3.2.1.8: Ensure that no user may create an index on an
78
# INFORMATION_SCHEMA table.
79
# 3.2.1.9: Ensure that no user may alter the definition of an
80
# INFORMATION_SCHEMA table.
81
# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
82
# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
84
# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
85
# in an INFORMATION_SCHEMA table.
88
DROP DATABASE IF EXISTS db_datadict;
90
CREATE DATABASE db_datadict;
91
--replace_result $engine_type <engine_type>
93
CREATE TABLE db_datadict.t1 (f1 BIGINT)
94
ENGINE = $engine_type;
96
--error ER_DBACCESS_DENIED_ERROR
97
INSERT INTO information_schema.engines
98
SELECT * FROM information_schema.engines;
100
--error ER_DBACCESS_DENIED_ERROR
101
UPDATE information_schema.engines SET engine = '1234567';
103
--error ER_DBACCESS_DENIED_ERROR
104
DELETE FROM information_schema.engines WHERE support IN ('DEFAULT','YES');
105
--error ER_DBACCESS_DENIED_ERROR
106
TRUNCATE information_schema.engines;
108
--error ER_DBACCESS_DENIED_ERROR
109
CREATE INDEX my_idx_on_engines ON information_schema.engines(engine);
111
--error ER_DBACCESS_DENIED_ERROR
112
ALTER TABLE information_schema.engines DROP PRIMARY KEY;
113
--error ER_DBACCESS_DENIED_ERROR
114
ALTER TABLE information_schema.engines ADD f1 INT;
116
--error ER_DBACCESS_DENIED_ERROR
117
DROP TABLE information_schema.engines;
119
--error ER_DBACCESS_DENIED_ERROR
120
ALTER TABLE information_schema.engines RENAME db_datadict.engines;
121
--error ER_DBACCESS_DENIED_ERROR
122
ALTER TABLE information_schema.engines RENAME information_schema.xengines;
125
DROP DATABASE db_datadict;