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
|
CREATE TABLE t1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES(42);
CREATE TABLE t1b LIKE t1;
INSERT INTO t1b VALUES(3);
CREATE TABLE t1z LIKE t1;
CREATE TABLE t1t (id TINYINT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t0t LIKE t1t;
INSERT INTO t1t VALUES(123);
FLUSH TABLES t1,t1b,t1t FOR EXPORT;
UNLOCK TABLES;
CREATE TABLE t5_7 LIKE t1;
CREATE TABLE t5_7b LIKE t1b;
CREATE TABLE t10_1 LIKE t1;
CREATE TABLE t10_1b LIKE t1b;
ALTER TABLE t1 DISCARD TABLESPACE;
ALTER TABLE t1b DISCARD TABLESPACE;
ALTER TABLE t1z DISCARD TABLESPACE;
ALTER TABLE t1t DISCARD TABLESPACE;
ALTER TABLE t0t DISCARD TABLESPACE;
ALTER TABLE t5_7 DISCARD TABLESPACE;
ALTER TABLE t5_7b DISCARD TABLESPACE;
ALTER TABLE t10_1 DISCARD TABLESPACE;
ALTER TABLE t10_1b DISCARD TABLESPACE;
FLUSH TABLES;
ALTER TABLE t0t IMPORT TABLESPACE;
Warnings:
Warning 1810 IO Read error: (2, No such file or directory) Error opening './test/t0t.cfg', will attempt to import without schema verification
INSERT INTO t0t VALUES(NULL);
SELECT * FROM t0t;
id
123
124
DROP TABLE t0t;
ALTER TABLE t1 IMPORT TABLESPACE;
Warnings:
Warning 1810 IO Read error: (2, No such file or directory) Error opening './test/t1.cfg', will attempt to import without schema verification
ALTER TABLE t1b IMPORT TABLESPACE;
Warnings:
Warning 1810 IO Read error: (2, No such file or directory) Error opening './test/t1b.cfg', will attempt to import without schema verification
ALTER TABLE t1z IMPORT TABLESPACE;
Warnings:
Warning 1810 IO Read error: (2, No such file or directory) Error opening './test/t1z.cfg', will attempt to import without schema verification
ALTER TABLE t1t IMPORT TABLESPACE;
Warnings:
Warning 1810 IO Read error: (2, No such file or directory) Error opening './test/t1t.cfg', will attempt to import without schema verification
ALTER TABLE t5_7 IMPORT TABLESPACE;
Warnings:
Warning 1810 IO Read error: (2, No such file or directory) Error opening './test/t5_7.cfg', will attempt to import without schema verification
ALTER TABLE t5_7b IMPORT TABLESPACE;
Warnings:
Warning 1810 IO Read error: (2, No such file or directory) Error opening './test/t5_7b.cfg', will attempt to import without schema verification
ALTER TABLE t10_1 IMPORT TABLESPACE;
Warnings:
Warning 1810 IO Read error: (2, No such file or directory) Error opening './test/t10_1.cfg', will attempt to import without schema verification
ALTER TABLE t10_1b IMPORT TABLESPACE;
Warnings:
Warning 1810 IO Read error: (2, No such file or directory) Error opening './test/t10_1b.cfg', will attempt to import without schema verification
FOUND 1 /InnoDB: Resetting PAGE_ROOT_AUTO_INC from 128 to 123 on table `test`\.`t0t`/ in mysqld.1.err
FOUND 1 /InnoDB: Resetting PAGE_ROOT_AUTO_INC from 0 to 42 on table `test`\.`t1z`/ in mysqld.1.err
FOUND 1 /InnoDB: Resetting PAGE_ROOT_AUTO_INC from 128 to 123 on table `test`\.`t1t`/ in mysqld.1.err
FOUND 1 /InnoDB: Resetting PAGE_ROOT_AUTO_INC from 3 to 42 on table `test`\.`t5_7` \(created with version 50744\)/ in mysqld.1.err
FOUND 1 /InnoDB: Resetting PAGE_ROOT_AUTO_INC from 3 to 42 on table `test`\.`t10_1` \(created with version 100149\)/ in mysqld.1.err
FOUND 5 /InnoDB: Resetting PAGE_ROOT_AUTO_INC/ in mysqld.1.err
# restart: --read-only
CHECK TABLE t1, t1b, t1t, t1z, t5_7, t5_7b, t10_1, t10_1b;
Table Op Msg_type Msg_text
test.t1 check status OK
test.t1b check status OK
test.t1t check status OK
test.t1z check status OK
test.t5_7 check note Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed
test.t5_7 check status OK
test.t5_7b check note Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed
test.t5_7b check status OK
test.t10_1 check note Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed
test.t10_1 check status OK
test.t10_1b check note Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed
test.t10_1b check status OK
CHECK TABLE t1, t1b, t1t, t1z, t5_7, t5_7b, t10_1, t10_1b FOR UPGRADE;
Table Op Msg_type Msg_text
test.t1 check status OK
test.t1b check status OK
test.t1t check status OK
test.t1z check status OK
test.t5_7 check note Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed
test.t5_7 check status OK
test.t5_7b check note Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed
test.t5_7b check status OK
test.t10_1 check note Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed
test.t10_1 check status OK
test.t10_1b check note Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed
test.t10_1b check status OK
# restart: --innodb-read-only --read-only
CHECK TABLE t1, t1b, t1t, t1z, t5_7, t5_7b, t10_1, t10_1b;
Table Op Msg_type Msg_text
test.t1 check status OK
test.t1b check status OK
test.t1t check status OK
test.t1z check status OK
test.t5_7 check note Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed
test.t5_7 check status OK
test.t5_7b check note Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed
test.t5_7b check status OK
test.t10_1 check note Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed
test.t10_1 check status OK
test.t10_1b check note Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed
test.t10_1b check status OK
CHECK TABLE t1, t1b, t1t, t1z, t5_7, t5_7b, t10_1, t10_1b FOR UPGRADE;
Table Op Msg_type Msg_text
test.t1 check status OK
test.t1b check status OK
test.t1t check status OK
test.t1z check status OK
test.t5_7 check note Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed
test.t5_7 check status OK
test.t5_7b check note Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed
test.t5_7b check status OK
test.t10_1 check note Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed
test.t10_1 check status OK
test.t10_1b check note Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed
test.t10_1b check status OK
# restart: --innodb-read-only
CHECK TABLE t1, t1b, t1t, t1z, t5_7, t5_7b, t10_1, t10_1b;
Table Op Msg_type Msg_text
test.t1 check status OK
test.t1b check status OK
test.t1t check status OK
test.t1z check status OK
test.t5_7 check status Operation failed
test.t5_7b check status Operation failed
test.t10_1 check status Operation failed
test.t10_1b check status Operation failed
CHECK TABLE t1, t1b, t1t, t1z, t5_7, t5_7b, t10_1, t10_1b FOR UPGRADE;
Table Op Msg_type Msg_text
test.t1 check status OK
test.t1b check status OK
test.t1t check status OK
test.t1z check status OK
test.t5_7 check status Operation failed
test.t5_7b check status Operation failed
test.t10_1 check status Operation failed
test.t10_1b check status Operation failed
SELECT COUNT(*) FROM t1;
COUNT(*)
1
SELECT COUNT(*) FROM t1b;
COUNT(*)
1
SELECT COUNT(*) FROM t1t;
COUNT(*)
1
SELECT COUNT(*) FROM t1z;
COUNT(*)
1
SELECT COUNT(*) FROM t5_7;
COUNT(*)
1
SELECT COUNT(*) FROM t5_7b;
COUNT(*)
1
SELECT COUNT(*) FROM t10_1;
COUNT(*)
1
SELECT COUNT(*) FROM t10_1b;
COUNT(*)
1
# restart
CHECK TABLE t1, t1b, t1t, t1z, t5_7, t5_7b, t10_1, t10_1b FOR UPGRADE;
Table Op Msg_type Msg_text
test.t1 check status OK
test.t1b check status OK
test.t1t check status OK
test.t1z check status OK
test.t5_7 check note Auto_increment checked and .frm file version updated
test.t5_7 check status OK
test.t5_7b check note Auto_increment checked and .frm file version updated
test.t5_7b check status OK
test.t10_1 check note Auto_increment checked and .frm file version updated
test.t10_1 check status OK
test.t10_1b check note Auto_increment checked and .frm file version updated
test.t10_1b check status OK
INSERT INTO t1 VALUES(NULL);
INSERT INTO t1b VALUES(NULL);
INSERT INTO t1t VALUES(NULL);
INSERT INTO t1z VALUES(NULL);
INSERT INTO t5_7 VALUES(NULL);
INSERT INTO t5_7b VALUES(NULL);
INSERT INTO t10_1 VALUES(NULL);
INSERT INTO t10_1b VALUES(NULL);
CHECK TABLE t1, t1b, t1t, t1z, t5_7, t5_7b, t10_1, t10_1b FOR UPGRADE;
Table Op Msg_type Msg_text
test.t1 check status OK
test.t1b check status OK
test.t1t check status OK
test.t1z check status OK
test.t5_7 check status OK
test.t5_7b check status OK
test.t10_1 check status OK
test.t10_1b check status OK
SELECT * FROM t1;
id
4
42
SELECT * FROM t1b;
id
3
347
SELECT * FROM t1t;
id
123
124
SELECT * FROM t1z;
id
42
43
SELECT * FROM t5_7;
id
42
43
SELECT * FROM t5_7b;
id
3
347
SELECT * FROM t10_1;
id
42
43
SELECT * FROM t10_1b;
id
3
347
DROP TABLE t1, t1b, t1t, t1z, t5_7, t5_7b, t10_1, t10_1b;
|
