summaryrefslogtreecommitdiff
path: root/mysql-test/r/mysqlcheck.result
blob: 8894616fbb3379b10b6da16d21bdb7c3cae5c562 (plain)
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
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
call mtr.add_suppression("Invalid .old.. table or database name");
DROP TABLE IF EXISTS t1, `t``1`, `t 1`, test.`t.1`, v1;
drop view if exists t1, `t``1`, `t 1`, test.`t.1`, 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.roles_mapping                                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.roles_mapping                                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.roles_mapping                                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.roles_mapping                                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
ALTER TABLE bug47205 FORCE;
# 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	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;
#
#MDEV-6128:[PATCH] mysqlcheck wrongly escapes '.' in table names
#
create table `t.1` (id int);
create view `v.1` as select 1;
mysqlcheck test t.1
test.t.1                                           OK
mysqlcheck --all-in-1 test t.1
test.t.1                                           OK
mysqlcheck --all-in-1 --databases --process-views test
test.t.1                                           OK
test.v.1                                           OK
create table `t.2`(a varchar(20) primary key) default character set utf8 collate utf8_general_ci engine=innodb;
flush table `t.2`;
mysqlcheck --check-upgrade --auto-repair test
test.t.1                                           OK
test.t.2
error    : Table rebuild required. Please do "ALTER TABLE `t.2` FORCE" or dump/reload to fix it!
test.t.3                                           Needs upgrade

Repairing tables
test.t.3                                           OK
check table `t.1`, `t.2`, `t.3`;
Table	Op	Msg_type	Msg_text
test.t.1	check	status	OK
test.t.2	check	status	OK
test.t.3	check	status	OK
check table `t.1`, `t.2`, `t.3` for upgrade;
Table	Op	Msg_type	Msg_text
test.t.1	check	status	OK
test.t.2	check	status	OK
test.t.3	check	status	OK
drop view `v.1`;
drop table test.`t.1`, `t.2`, `t.3`;
#
# MDEV-8123 mysqlcheck: new --process-views option conflicts with --quick, --extended and such
#
create view v1 as select 1;
mysqlcheck --process-views test
test.v1                                            OK
mysqlcheck --process-views --extended test
test.v1                                            OK
mysqlcheck --process-views --fast test
mysqlcheck --process-views --quick test
test.v1                                            OK
mysqlcheck --process-views --check-only-changed test
mysqlcheck --process-views --medium-check test
test.v1                                            OK
mysqlcheck --process-views --check-upgrade test
test.v1                                            OK
drop view v1;
#
# MDEV-8124 mysqlcheck: --auto-repair runs REPAIR TABLE instead of REPAIR VIEW on views
#
create table t1(a int);
mysqlcheck --process-views --check-upgrade --auto-repair test
test.t1                                            OK
test.v1                                            Needs upgrade

Repairing views
test.v1                                            OK
drop view v1;
drop table t1;
create table `#mysql50#t1``1` (a int) engine=myisam;
show tables;
Tables_in_test
t1`1
drop table `t1``1`;
call mtr.add_suppression("ha_myisam");
call mtr.add_suppression("Checking table");
create database mysqltest1;
create table mysqltest1.t1 (a int) engine=myisam;
create table t2 (a int);
check table mysqltest1.t1;
Table	Op	Msg_type	Msg_text
mysqltest1.t1	check	warning	Size of datafile is: 4       Should be: 0
mysqltest1.t1	check	error	got error: 0 when reading datafile at record: 0
mysqltest1.t1	check	error	Corrupt
mtr.global_suppressions                            Table is already up to date
mtr.test_suppressions                              Table is already up to date
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                           OK
mysql.innodb_table_stats                           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.roles_mapping                                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
mysqltest1.t1
warning  : Table is marked as crashed
warning  : Size of datafile is: 4       Should be: 0
error    : got error: 0 when reading datafile at record: 0
error    : Corrupt
test.t2                                            Table is already up to date

Repairing tables
mysqltest1.t1                                      OK
drop table t2;
drop database mysqltest1;
#
#MDEV-7384 [PATCH] add PERSISENT FOR ALL option to mysqlanalyze/mysqlcheck
#
create table t1(a int);
insert into t1 (a) values (1), (2), (3);
select * from mysql.column_stats;
db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency	hist_size	hist_type	histogram
test.t1                                            Engine-independent statistics collected
status   : OK
select * from mysql.column_stats where db_name = 'test' and table_name = 't1';
db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency	hist_size	hist_type	histogram
test	t1	a	1	3	0.0000	4.0000	1.0000	0	NULL	NULL
drop table t1;