1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
|
#
# Bug#27502530: DISABLING NO_ENGINE_SUBSTITUTION DOES NOT BEHAVE AS
# DOCUMENTED
#
# Started the server by disabling InnoDB using system variable 'disabled_storage_engines'
SELECT @@disabled_storage_engines;
@@disabled_storage_engines
InnoDB
SET DEFAULT_STORAGE_ENGINE= MyISAM;
SELECT @@default_storage_engine;
@@default_storage_engine
MyISAM
# NO_ENGINE_SUBSTITUTION enabled
SET SQL_MODE= 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE t1(c1 INT) ENGINE= MyISAM;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO t1 VALUES(1);
CREATE TABLE t2(c1 INT) ENGINE= InnoDB;
ERROR HY000: Storage engine InnoDB is disabled (Table creation is disallowed).
CREATE TEMPORARY TABLE t2(c1 INT) ENGINE= InnoDB;
ERROR HY000: Storage engine InnoDB is disabled (Table creation is disallowed).
ALTER TABLE t1 ENGINE= InnoDB;
ERROR HY000: Storage engine InnoDB is disabled (Table creation is disallowed).
# Create a table in MYISAM, which will become disabled after restart
CREATE TABLE t2 (c1 INT) ENGINE=MYISAM;
# Restart the server disabling the myisam storage engine using variable 'disabled_storage_engines'
SELECT @@disabled_storage_engines;
@@disabled_storage_engines
myisam,example
SET @old_default_engine= @@default_storage_engine;
# Changing the default engine to InnoDB
SET DEFAULT_STORAGE_ENGINE= InnoDB;
SELECT @@default_storage_engine;
@@default_storage_engine
InnoDB
CREATE TABLE t2(c1 INT) ENGINE= MyISAM;
ERROR HY000: Storage engine MyISAM is disabled (Table creation is disallowed).
SELECT * FROM t1;
c1
1
ALTER TABLE t2 ENGINE=example;
ERROR 42000: Unknown storage engine 'example'
SET SQL_MODE='';
ALTER TABLE t2 ENGINE=example;
Warnings:
Warning 1286 Unknown storage engine 'example'
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`c1` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SET SQL_MODE= 'NO_ENGINE_SUBSTITUTION';
# Checking table creation with dynamic storage plugins
INSTALL PLUGIN example SONAME 'ha_example.so';
ALTER TABLE t2 ENGINE=example;
ERROR HY000: Storage engine EXAMPLE is disabled (Table creation is disallowed).
SET SQL_MODE='';
ALTER TABLE t2 ENGINE=example;
Warnings:
Warning 1286 Unknown storage engine 'EXAMPLE'
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`c1` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t2;
SET SQL_MODE= 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE t1(a int) ENGINE=EXAMPLE;
ERROR HY000: Storage engine EXAMPLE is disabled (Table creation is disallowed).
# NO_ENGINE_SUBSTITUTION disabled
SET SQL_MODE='';
# The disabled engine is substituted with the default engine for the table.
CREATE TABLE t2(c1 INT) ENGINE= MyISAM;
Warnings:
Warning 3161 Storage engine MyISAM is disabled (Table creation is disallowed).
Warning 1266 Using storage engine InnoDB for table 't2'
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`c1` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE t3 LIKE t1;
Warnings:
Warning 3161 Storage engine MyISAM is disabled (Table creation is disallowed).
Warning 1266 Using storage engine InnoDB for table 't3'
SHOW CREATE TABLE t3;
Table Create Table
t3 CREATE TABLE `t3` (
`c1` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TEMPORARY TABLE t4(c1 INT) ENGINE= MyISAM;
Warnings:
Warning 3161 Storage engine MyISAM is disabled (Table creation is disallowed).
Warning 1266 Using storage engine InnoDB for table 't4'
SHOW CREATE TABLE t4;
Table Create Table
t4 CREATE TEMPORARY TABLE `t4` (
`c1` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE t5 (c1 INT) ENGINE= ARCHIVE;
# ALTER TABLE ... ENGINE reports a warning and the table is not altered.
ALTER TABLE t5 ENGINE= MyISAM;
Warnings:
Warning 1286 Unknown storage engine 'MyISAM'
SHOW CREATE TABLE t5;
Table Create Table
t5 CREATE TABLE `t5` (
`c1` int DEFAULT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE PROCEDURE p1()
BEGIN
CREATE TABLE t6(c1 INT) ENGINE= MyISAM;
END /
CALL p1();
Warnings:
Warning 3161 Storage engine MyISAM is disabled (Table creation is disallowed).
Warning 1266 Using storage engine InnoDB for table 't6'
SHOW CREATE TABLE t6;
Table Create Table
t6 CREATE TABLE `t6` (
`c1` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE t7 (c1 INT) ENGINE= EXAMPLE;
Warnings:
Warning 3161 Storage engine EXAMPLE is disabled (Table creation is disallowed).
Warning 1266 Using storage engine InnoDB for table 't7'
SHOW CREATE TABLE t7;
Table Create Table
t7 CREATE TABLE `t7` (
`c1` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SET DEFAULT_STORAGE_ENGINE= MyISAM;
CREATE TABLE t8 (c1 INT) ENGINE= MyISAM;
ERROR HY000: Storage engine MyISAM is disabled (Table creation is disallowed).
CREATE TABLE t8 (c1 INT) ENGINE= EXAMPLE;
ERROR HY000: Storage engine EXAMPLE is disabled (Table creation is disallowed).
#
# Bug#29899151: CORE AT CREATE TABLE WITH FK FOR TABLE TRANSFERRED
# FROM MYISAM TO INNODB
#
# Allow engine substitution
SET sql_mode = "";
SET DEFAULT_STORAGE_ENGINE= InnoDB;
CREATE TABLE parent_table (i INT PRIMARY KEY);
CREATE TABLE child_table (
i INT,
CONSTRAINT fk_parent_table
FOREIGN KEY (i)
REFERENCES parent_table (i) ON DELETE CASCADE
) ENGINE=MyISAM;
Warnings:
Warning 3161 Storage engine MyISAM is disabled (Table creation is disallowed).
Warning 1266 Using storage engine InnoDB for table 'child_table'
DROP TABLE child_table;
DROP TABLE parent_table;
# Verify that creating a temporary table LIKE a table in a storage
# engine which does not support temporary tables, defaults to the
# default_tmp_storage_engine, even with no_substitution.
CREATE TEMPORARY TABLE tt1 LIKE performance_schema.setup_consumers;
SHOW CREATE TABLE tt1;
Table Create Table
tt1 CREATE TEMPORARY TABLE `tt1` (
`NAME` varchar(64) NOT NULL,
`ENABLED` enum('YES','NO') NOT NULL,
PRIMARY KEY (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE tt1;
SET default_tmp_storage_engine=MYISAM;
CREATE TEMPORARY TABLE tt1(c1 INT);
ERROR HY000: Storage engine MyISAM is disabled (Table creation is disallowed).
CREATE TEMPORARY TABLE tt1 LIKE performance_schema.setup_consumers;
ERROR HY000: Table storage engine 'PERFORMANCE_SCHEMA' does not support the create option 'TEMPORARY'
UNINSTALL PLUGIN EXAMPLE;
DROP PROCEDURE p1;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
DROP TABLE t4;
DROP TABLE t5;
DROP TABLE t6;
DROP TABLE t7;
SET @@default_storage_engine=@old_default_engine;
SET sql_mode= DEFAULT;
# Restart the server disabling archive engine via '--skip-archive or --archive=off'
SET @old_default_engine= @@default_storage_engine;
SET DEFAULT_STORAGE_ENGINE= MyISAM;
SET DEFAULT_TMP_STORAGE_ENGINE= MyISAM;
# NO_ENGINE_SUBSTITUTION enabled
SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
CREATE TABLE t1 (c1 INT) ENGINE= ARCHIVE;
ERROR 42000: Unknown storage engine 'ARCHIVE'
CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE= ARCHIVE;
ERROR 42000: Unknown storage engine 'ARCHIVE'
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
ALTER TABLE t1 ENGINE= ARCHIVE;
ERROR 42000: Unknown storage engine 'ARCHIVE'
# NO_ENGINE_SUBSTITUTION disabled
SET SQL_MODE='';
CREATE TABLE t2 (c1 INT) ENGINE=ARCHIVE;
Warnings:
Warning 1286 Unknown storage engine 'ARCHIVE'
Warning 1266 Using storage engine MyISAM for table 't2'
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`c1` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TEMPORARY TABLE t3 (c1 INT) ENGINE= ARCHIVE;
Warnings:
Warning 1286 Unknown storage engine 'ARCHIVE'
Warning 1266 Using storage engine MyISAM for table 't3'
SHOW CREATE TABLE t3;
Table Create Table
t3 CREATE TEMPORARY TABLE `t3` (
`c1` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# ALTER TABLE .. ENGINE reports a warning and the table is not altered.
ALTER TABLE t1 ENGINE= ARCHIVE;
Warnings:
Warning 1286 Unknown storage engine 'ARCHIVE'
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
SET @@default_storage_engine= @old_default_engine;
SET sql_mode = DEFAULT;
# restart
|
