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
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
|
#
# Testing indexing with ALTER on inward table (in-place)
#
CREATE TABLE t1 (c INT NOT NULL, d CHAR(10) NOT NULL) ENGINE=CONNECT;
Warnings:
Warning 1105 No table_type. Will be set to DOS
Warning 1105 No file name. Table will use t1.dos
INSERT INTO t1 VALUES (1,'One'), (2,'Two'), (3,'Three');
SELECT * FROM t1;
c d
1 One
2 Two
3 Three
CREATE INDEX xc ON t1(c);
DESCRIBE SELECT * FROM t1 WHERE c = 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref xc xc 4 const 1
DROP INDEX xc ON t1;
CREATE INDEX xd ON t1(d);
DROP INDEX xd ON t1;
ALTER TABLE t1 ADD INDEX xc (c), ADD INDEX xd (d);
SHOW INDEX FROM t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 1 xc 1 c A NULL NULL NULL XINDEX
t1 1 xd 1 d A NULL NULL NULL XINDEX
ALTER TABLE t1 DROP INDEX xc, DROP INDEX xd;
SHOW INDEX FROM t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
#
# Testing modifying columns inward table (not in-place)
#
ALTER TABLE t1 MODIFY COLUMN c CHAR(5) NOT NULL;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` char(5) NOT NULL,
`d` char(10) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1
SELECT * FROM t1;
c d
1 One
2 Two
3 Three
ALTER TABLE t1 MODIFY COLUMN c INT NOT NULL;
#
# Fails because indexing must be in-place
#
ALTER TABLE t1 MODIFY COLUMN c CHAR(10) NOT NULL, ADD INDEX xd (d);
ERROR 0A000: Alter operations not supported together by CONNECT
#
# Testing changing table type (not in-place)
#
ALTER TABLE t1 TABLE_TYPE=CSV HEADER=1 QUOTED=1;
SELECT * FROM t1;
c d
1 One
2 Two
3 Three
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) NOT NULL,
`d` char(10) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=CSV `HEADER`=1 `QUOTED`=1
# create an outward table used to see the t1 file
CREATE TABLE t2 (line VARCHAR(100) NOT NULL) ENGINE=CONNECT FILE_NAME='t1.csv';
Warnings:
Warning 1105 No table_type. Will be set to DOS
SELECT * FROM t2;
line
"c","d"
1,"One"
2,"Two"
3,"Three"
#
# Testing changing engine
#
DROP TABLE t1;
CREATE TABLE t1 (c INT NOT NULL, d CHAR(10) NOT NULL) ENGINE=CONNECT;
Warnings:
Warning 1105 No table_type. Will be set to DOS
Warning 1105 No file name. Table will use t1.dos
INSERT INTO t1 VALUES (1,'One'), (2,'Two'), (3,'Three');
ALTER TABLE t1 ADD INDEX xc (c), ADD INDEX xd (d);
ALTER TABLE t1 ENGINE = MYISAM;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) NOT NULL,
`d` char(10) NOT NULL,
KEY `xc` (`c`),
KEY `xd` (`d`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SHOW INDEX FROM t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 1 xc 1 c A NULL NULL NULL BTREE
t1 1 xd 1 d A NULL NULL NULL BTREE
SELECT * FROM t1;
c d
1 One
2 Two
3 Three
ALTER TABLE t1 ENGINE = CONNECT TABLE_TYPE=DBF;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) NOT NULL,
`d` char(10) NOT NULL,
KEY `xc` (`c`),
KEY `xd` (`d`)
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=DBF
SHOW INDEX FROM t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 1 xc 1 c A NULL NULL NULL XINDEX
t1 1 xd 1 d A NULL NULL NULL XINDEX
SELECT * FROM t1;
c d
1 One
2 Two
3 Three
DROP TABLE t1, t2;
#
# Testing ALTER on outward tables
#
CREATE TABLE t1 (c INT NOT NULL, d CHAR(10) NOT NULL) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='tf1.txt' ENDING=1;
INSERT INTO t1 VALUES (1,'One'), (2,'Two'), (3,'Three');
SELECT * FROM t1;
c d
1 One
2 Two
3 Three
CREATE TABLE t2 (line VARCHAR(100) NOT NULL) ENGINE=CONNECT FILE_NAME='tf1.txt';
Warnings:
Warning 1105 No table_type. Will be set to DOS
SELECT * FROM t2;
line
1One
2Two
3Three
#
# Indexing works the same
#
ALTER TABLE t1 ADD INDEX xc (c), ADD INDEX xd (d);
SHOW INDEX FROM t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 1 xc 1 c A NULL NULL NULL XINDEX
t1 1 xd 1 d A NULL NULL NULL XINDEX
SELECT d FROM t1 WHERE c = 2;
d
Two
ALTER TABLE t1 DROP INDEX xc, DROP INDEX xd;
SHOW INDEX FROM t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
#
# Other alterations do not modify the file
#
ALTER TABLE t1 MODIFY COLUMN c CHAR(5) NOT NULL;
Warnings:
Warning 1105 This is an outward table, table data were not modified.
SELECT * FROM t2;
line
1One
2Two
3Three
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` char(5) NOT NULL,
`d` char(10) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=fix `FILE_NAME`='tf1.txt' `ENDING`=1
SELECT * FROM t1;
ERROR HY000: Got error 174 'File tf1.txt is not fixed length, len=66 lrecl=16' from CONNECT
ALTER TABLE t1 MODIFY COLUMN c INT NOT NULL;
Warnings:
Warning 1105 This is an outward table, table data were not modified.
#
# Changing column order
#
ALTER TABLE t1 MODIFY COLUMN c INT NOT NULL AFTER d;
Warnings:
Warning 1105 This is an outward table, table data were not modified.
SELECT * FROM t2;
line
1One
2Two
3Three
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`d` char(10) NOT NULL,
`c` int(11) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=fix `FILE_NAME`='tf1.txt' `ENDING`=1
# Wrong result
SELECT * FROM t1;
d c
1
2
3
ALTER TABLE t1 MODIFY COLUMN c INT NOT NULL FIRST;
Warnings:
Warning 1105 This is an outward table, table data were not modified.
# What should have been done
ALTER TABLE t1 MODIFY c INT NOT NULL FLAG=0 AFTER d, MODIFY d CHAR(10) NOT NULL FLAG=11;
Warnings:
Warning 1105 This is an outward table, table data were not modified.
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`d` char(10) NOT NULL `FLAG`=11,
`c` int(11) NOT NULL `FLAG`=0
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=fix `FILE_NAME`='tf1.txt' `ENDING`=1
SELECT * FROM t1;
d c
One 1
Two 2
Three 3
#
# Changing to another engine is Ok
# However, the data file is not deleted.
#
ALTER TABLE t1 ENGINE=ARIA;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`d` char(10) NOT NULL /* `FLAG`=11 */,
`c` int(11) NOT NULL /* `FLAG`=0 */
) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 /* `TABLE_TYPE`=fix `FILE_NAME`='tf1.txt' `ENDING`=1 */
set @old_sql_mode=@@sql_mode;
set sql_mode=ignore_bad_table_options;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`d` char(10) NOT NULL `FLAG`=11,
`c` int(11) NOT NULL `FLAG`=0
) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 `TABLE_TYPE`=fix `FILE_NAME`='tf1.txt' `ENDING`=1
set sql_mode=@old_sql_mode;
SELECT * from t1;
d c
One 1
Two 2
Three 3
SELECT * from t2;
line
1One
2Two
3Three
#
# Changing back to CONNECT fails
# Sure enough, the data file was not deleted.
#
ALTER TABLE t1 ENGINE=CONNECT;
ERROR HY000: Operation denied. Table data would be modified.
#
# But changing back to CONNECT succeed
# if the data file does not exist.
#
ALTER TABLE t1 ENGINE=CONNECT;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`d` char(10) NOT NULL `FLAG`=11,
`c` int(11) NOT NULL `FLAG`=0
) ENGINE=CONNECT DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 `TABLE_TYPE`=fix `FILE_NAME`='tf1.txt' `ENDING`=1
SELECT * from t1;
d c
One 1
Two 2
Three 3
SELECT * from t2;
line
1One
2Two
3Three
DROP TABLE t1, t2;
|
