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
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
|
DROP TABLE IF EXISTS t1, `t``1`, `t 1`;
drop view if exists v1;
drop database if exists client_test_db;
mtr.global_suppressions OK
mtr.test_suppressions OK
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
mtr.global_suppressions Table is already up to date
mtr.test_suppressions Table is already up to date
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.innodb_index_stats
note : Table does not support optimize, doing recreate + analyze instead
status : OK
mysql.innodb_table_stats
note : Table does not support optimize, doing recreate + analyze instead
status : OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
mysql.column_stats Table is already up to date
mysql.columns_priv Table is already up to date
mysql.db Table is already up to date
mysql.event Table is already up to date
mysql.func Table is already up to date
mysql.gtid_slave_pos Table is already up to date
mysql.help_category Table is already up to date
mysql.help_keyword Table is already up to date
mysql.help_relation Table is already up to date
mysql.help_topic Table is already up to date
mysql.host Table is already up to date
mysql.index_stats Table is already up to date
mysql.innodb_index_stats
note : Table does not support optimize, doing recreate + analyze instead
status : OK
mysql.innodb_table_stats
note : Table does not support optimize, doing recreate + analyze instead
status : OK
mysql.plugin Table is already up to date
mysql.proc Table is already up to date
mysql.procs_priv Table is already up to date
mysql.proxies_priv Table is already up to date
mysql.servers Table is already up to date
mysql.table_stats Table is already up to date
mysql.tables_priv Table is already up to date
mysql.time_zone Table is already up to date
mysql.time_zone_leap_second Table is already up to date
mysql.time_zone_name Table is already up to date
mysql.time_zone_transition Table is already up to date
mysql.time_zone_transition_type Table is already up to date
mysql.user Table is already up to date
create table t1 (a int) engine=myisam;
create view v1 as select * from t1;
test.t1 OK
test.t1 Table is already up to date
test.t1 OK
test.t1 Table is already up to date
drop view v1;
drop table t1;
create table `t``1`(a int) engine=myisam;
create table `t 1`(a int) engine=myisam;
test.t 1 OK
test.t`1 OK
drop table `t``1`, `t 1`;
create database d_bug25347;
use d_bug25347;
create table t_bug25347 (a int) engine=myisam;
create view v_bug25347 as select * from t_bug25347;
insert into t_bug25347 values (1),(2),(3);
flush tables;
removing and creating
d_bug25347.t_bug25347
Error : Incorrect file format 't_bug25347'
error : Corrupt
insert into t_bug25347 values (4),(5),(6);
ERROR HY000: Incorrect file format 't_bug25347'
d_bug25347.t_bug25347
warning : Number of rows changed from 0 to 3
status : OK
insert into t_bug25347 values (7),(8),(9);
select * from t_bug25347;
a
1
2
3
7
8
9
select * from v_bug25347;
a
1
2
3
7
8
9
drop view v_bug25347;
drop table t_bug25347;
drop database d_bug25347;
use test;
create view v1 as select * from information_schema.routines;
check table v1, information_schema.routines;
Table Op Msg_type Msg_text
test.v1 check status OK
information_schema.routines check note The storage engine for the table doesn't support check
drop view v1;
call mtr.add_suppression("Error reading file './test/t1.frm'");
CREATE TABLE t1(a INT) engine=myisam;
CREATE TABLE t2(a INT) engine=myisam;
test.t1
Error : Incorrect information in file: './test/t1.frm'
error : Corrupt
test.t2 OK
DROP TABLE t1, t2;
End of 5.0 tests
create table t1(a int) engine=myisam;
create view v1 as select * from t1;
show tables;
Tables_in_test
t1
v1
show tables;
Tables_in_test
#mysql50#v-1
t1
v1
test.t1 OK
show tables;
Tables_in_test
t1
v-1
v1
drop view v1, `v-1`;
drop table t1;
SET NAMES utf8;
CREATE TABLE `#mysql50#@` (a INT) engine=myisam;
SHOW TABLES;
Tables_in_test
#mysql50#@
SET NAMES DEFAULT;
mysqlcheck --fix-table-names --databases test
SET NAMES utf8;
SHOW TABLES;
Tables_in_test
@
DROP TABLE `@`;
CREATE TABLE `я` (a INT) engine=myisam;
SET NAMES DEFAULT;
call mtr.add_suppression("@003f.frm' \\(errno: 22\\)");
mysqlcheck --default-character-set="latin1" --databases test
call mtr.add_suppression("Can't find file: '..test.@003f.frm'");
test.?
Error : Table doesn't exist
status : Operation failed
mysqlcheck --default-character-set="utf8" --databases test
test.я OK
SET NAMES utf8;
DROP TABLE `я`;
SET NAMES DEFAULT;
CREATE DATABASE `#mysql50#a@b`;
USE `#mysql50#a@b`;
CREATE TABLE `#mysql50#c@d` (a INT) engine=myisam;
CREATE TABLE t1 (a INT) engine=myisam;
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA="#mysql50#a@b" ORDER BY trigger_name;
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
def #mysql50#a@b tr1 INSERT def #mysql50#a@b #mysql50#c@d 0 NULL SET NEW.a = 10 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
def #mysql50#a@b tr2 INSERT def #mysql50#a@b t1 0 NULL SET NEW.a = 100 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
mysqlcheck --fix-db-names --fix-table-names --all-databases
USE `a@b`;
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA="a@b" ORDER BY trigger_name;
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
def a@b tr1 INSERT def a@b c@d 0 NULL SET NEW.a = 10 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost utf8 utf8_general_ci latin1_swedish_ci
def a@b tr2 INSERT def a@b t1 0 NULL SET NEW.a = 100 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost utf8 utf8_general_ci latin1_swedish_ci
INSERT INTO `c@d` VALUES (2), (1);
SELECT * FROM `c@d`;
a
20
10
INSERT INTO t1 VALUES (3), (5);
SELECT * FROM t1;
a
300
500
DROP DATABASE `a@b`;
USE test;
#
# Bug #31821: --all-in-1 and --fix-table-names don't work together
#
drop table if exists `#mysql50#t1-1`;
create table `#mysql50#t1-1` (a int) engine=myisam;
show tables like 't1-1';
Tables_in_test (t1-1)
t1-1
drop table `t1-1`;
create table `#mysql50#t1-1` (a int) engine=myisam;
show tables like 't1-1';
Tables_in_test (t1-1)
t1-1
drop table `t1-1`;
End of 5.1 tests
#
# Bug #35269: mysqlcheck behaves different depending on order of parameters
#
#
# Bug#11755431 47205: MAP 'REPAIR TABLE' TO RECREATE +ANALYZE FOR
# ENGINES NOT SUPPORTING NATIVE
#
DROP TABLE IF EXISTS bug47205;
#
# Test 1: Check that ALTER TABLE ... rebuilds the table
CREATE TABLE bug47205(a VARCHAR(20) PRIMARY KEY)
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci engine=innodb;
INSERT INTO bug47205 VALUES ("foobar");
FLUSH TABLE bug47205;
# Replace the FRM with a 5.0 FRM that will require upgrade
# Should indicate that ALTER TABLE ... FORCE is needed
CHECK TABLE bug47205 FOR UPGRADE;
Table Op Msg_type Msg_text
test.bug47205 check error Table rebuild required. Please do "ALTER TABLE `bug47205` FORCE" or dump/reload to fix it!
# ALTER TABLE ... FORCE should rebuild the table
# and therefore output "affected rows: 1"
ALTER TABLE bug47205 FORCE;
affected rows: 1
info: Records: 1 Duplicates: 0 Warnings: 0
# Table should now be ok
CHECK TABLE bug47205 FOR UPGRADE;
Table Op Msg_type Msg_text
test.bug47205 check status OK
DROP TABLE bug47205;
#
# Test 2: InnoDB - REPAIR not supported
CREATE TABLE bug47205(a VARCHAR(20) PRIMARY KEY)
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci engine=innodb;
FLUSH TABLE bug47205;
# Replace the FRM with a 5.0 FRM that will require upgrade
# Should indicate that ALTER TABLE .. FORCE is needed
CHECK TABLE bug47205 FOR UPGRADE;
Table Op Msg_type Msg_text
test.bug47205 check error Table rebuild required. Please do "ALTER TABLE `bug47205` FORCE" or dump/reload to fix it!
# Running mysqlcheck to check and upgrade
test.bug47205
error : Table rebuild required. Please do "ALTER TABLE `bug47205` FORCE" or dump/reload to fix it!
Repairing tables
# Table should now be ok
CHECK TABLE bug47205 FOR UPGRADE;
Table Op Msg_type Msg_text
test.bug47205 check status OK
DROP TABLE bug47205;
#
# Test 3: MyISAM - REPAIR supported
# Use an old FRM that will require upgrade
# Should indicate that REPAIR TABLE is needed
CHECK TABLE bug47205 FOR UPGRADE;
Table Op Msg_type Msg_text
test.bug47205 check error Table upgrade required. Please do "REPAIR TABLE `bug47205`" or dump/reload to fix it!
# Running mysqlcheck to check and upgrade
test.bug47205 Needs upgrade
Repairing tables
test.bug47205 OK
# Table should now be ok
CHECK TABLE bug47205 FOR UPGRADE;
Table Op Msg_type Msg_text
test.bug47205 check status OK
DROP TABLE bug47205;
|
