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
|
CREATE TABLE t1(a int) TABLESPACE ts ENGINE=MyISAM;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL
) /*!50100 TABLESPACE `ts` */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1(a int) TABLESPACE ts STORAGE DISK ENGINE=MyISAM;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL
) /*!50100 TABLESPACE `ts` STORAGE DISK */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1(a int) TABLESPACE ts STORAGE MEMORY ENGINE=MyISAM;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL
) /*!50100 TABLESPACE `ts` STORAGE MEMORY */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1(a int) STORAGE MEMORY ENGINE=MyISAM;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL
) /*!50100 STORAGE MEMORY */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1(a int) STORAGE DISK ENGINE=MyISAM;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL
) /*!50100 STORAGE DISK */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1(a int) TABLESPACE ts ENGINE=MyISAM;
ALTER TABLE t1 ADD COLUMN b int;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL,
`b` int DEFAULT NULL
) /*!50100 TABLESPACE `ts` */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1(a int) TABLESPACE ts STORAGE DISK ENGINE=MyISAM;
ALTER TABLE t1 ADD COLUMN b int;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL,
`b` int DEFAULT NULL
) /*!50100 TABLESPACE `ts` STORAGE DISK */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1(a int) ENGINE=MyISAM;
ALTER TABLE t1 TABLESPACE ts;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL
) /*!50100 TABLESPACE `ts` */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 TABLESPACE ts2;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL
) /*!50100 TABLESPACE `ts2` */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1(a int) ENGINE=MyISAM;
ALTER TABLE t1 STORAGE MEMORY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL
) /*!50100 STORAGE MEMORY */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 STORAGE DISK;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL
) /*!50100 STORAGE DISK */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1(a int) ENGINE=MyISAM;
ALTER TABLE t1 STORAGE MEMORY TABLESPACE ts;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL
) /*!50100 TABLESPACE `ts` STORAGE MEMORY */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 STORAGE DISK TABLESPACE ts2;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL
) /*!50100 TABLESPACE `ts2` STORAGE DISK */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
# 2. Non partitioned table DDL.
# 2.1 Create table.
CREATE TABLE t1 (i INTEGER) TABLESPACE innodb_file_per_table ENGINE InnoDB;
CREATE TABLE t2 (i INTEGER) TABLESPACE innodb_system ENGINE InnoDB;
# 2.2 Alter table.
ALTER TABLE t2 TABLESPACE `innodb_file_per_table.2`;
ERROR 42000: InnoDB: Tablespace names starting with `innodb_` are reserved.
# This is valid since MyISAM does not care:
ALTER TABLE t2 TABLESPACE `innodb_file_per_table.2` ENGINE MyISAM;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`i` int DEFAULT NULL
) /*!50100 TABLESPACE `innodb_file_per_table.2` */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# Table t1 is carried over to MyISAM using the dummy 'innodb_file_per_table':
ALTER TABLE t1 ENGINE MyISAM;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int DEFAULT NULL
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# Changing only engine back to InnoDB now will be rejected for t2:
ALTER TABLE t2 ENGINE InnoDB;
ERROR 42000: InnoDB: Tablespace names starting with `innodb_` are reserved.
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`i` int DEFAULT NULL
) /*!50100 TABLESPACE `innodb_file_per_table.2` */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# For t1, changing engine back to InnoDB will re-establish usage of the implicit tablespace:
ALTER TABLE t1 ENGINE InnoDB;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int DEFAULT NULL
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# Changing both engine and tablespace works:
ALTER TABLE t1 TABLESPACE innodb_system ENGINE InnoDB;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int DEFAULT NULL
) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t2 TABLESPACE innodb_file_per_table ENGINE InnoDB;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`i` int DEFAULT NULL
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# Keeping a valid tablespace through ALTER TABLE:
ALTER TABLE t1 ADD COLUMN (j INTEGER);
CREATE TABLESPACE ts ADD DATAFILE 'f.ibd' ENGINE InnoDB;
ALTER TABLE t1 TABLESPACE ts;
ALTER TABLE t1 ENGINE MyISAM;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int DEFAULT NULL,
`j` int DEFAULT NULL
) /*!50100 TABLESPACE `ts` */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 ENGINE InnoDB;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int DEFAULT NULL,
`j` int DEFAULT NULL
) /*!50100 TABLESPACE `ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
DROP TABLE t2;
DROP TABLESPACE ts;
# 1. Verify that ENGINE attribute is not needed for ALTER and DROP
# TABLESPACE
CREATE TABLESPACE ts1 ADD DATAFILE 'df1.ibd' ENGINE=InnoDB;
# No need to add ENGINE - looked up in DD
ALTER TABLESPACE ts1 ADD DATAFILE 'df2.ibd';
ERROR HY000: Failed to alter: TABLESPACE ts1
SHOW WARNINGS;
Level Code Message
Error 1533 Failed to alter: TABLESPACE ts1
Error 1178 The storage engine for the table doesn't support ALTER TABLESPACE ... ADD DATAFILE
# Specifying correct ENGINE is allowed, but triggers deprecation
# warning
ALTER TABLESPACE ts1 ADD DATAFILE 'df2.ibd' ENGINE=INNODB;
ERROR HY000: Failed to alter: TABLESPACE ts1
SHOW WARNINGS;
Level Code Message
Warning 1681 'ENGINE tablespace option' is deprecated and will be removed in a future release.
Error 1533 Failed to alter: TABLESPACE ts1
Error 1178 The storage engine for the table doesn't support ALTER TABLESPACE ... ADD DATAFILE
# Specifying a different ENGINE than the one stored in the DD is an
# error
ALTER TABLESPACE ts1 ADD DATAFILE 'df2.ibd' ENGINE=MYISAM;
ERROR HY000: Engine 'MYISAM' does not match stored engine 'InnoDB' for tablespace 'ts1'
DROP TABLESPACE ts1;
|
