summaryrefslogtreecommitdiff
path: root/mysql-test/main/repair.result
blob: 0be2251e922e184023ab0ea6f3c4e0747a2ecdb0 (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
call mtr.add_suppression("character set is multi-byte");
drop table if exists t1;
create table t1 SELECT 1,"table 1";
repair table t1 use_frm;
Table	Op	Msg_type	Msg_text
test.t1	repair	warning	Number of rows changed from 0 to 1
test.t1	repair	status	OK
alter table t1 ENGINE=HEAP;
repair table t1 use_frm;
Table	Op	Msg_type	Msg_text
test.t1	repair	note	The storage engine for the table doesn't support repair
drop table t1;
create table t1(id int PRIMARY KEY, st varchar(10), KEY st_key(st));
insert into t1 values(1, "One");
alter table t1 disable keys;
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t1	0	PRIMARY	1	id	A	1	NULL	NULL		BTREE		
t1	1	st_key	1	st	A	NULL	NULL	NULL	YES	BTREE	disabled	
repair table t1 extended;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t1	0	PRIMARY	1	id	A	1	NULL	NULL		BTREE		
t1	1	st_key	1	st	A	NULL	NULL	NULL	YES	BTREE	disabled	
drop table t1;
repair table t1 use_frm;
Table	Op	Msg_type	Msg_text
test.t1	repair	Error	Table 'test.t1' doesn't exist
test.t1	repair	status	Operation failed
create table t1 engine=myisam SELECT 1,"table 1";
flush tables;
repair table t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	Error	Incorrect file format 't1'
test.t1	repair	error	Corrupt
repair table t1 use_frm;
Table	Op	Msg_type	Msg_text
test.t1	repair	warning	Number of rows changed from 0 to 1
test.t1	repair	status	OK
drop table t1;
CREATE TABLE t1(a INT, KEY(a));
INSERT INTO t1 VALUES(1),(2),(3),(4),(5);
SET myisam_repair_threads=2;
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
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	a	1	a	A	5	NULL	NULL	YES	BTREE		
SET myisam_repair_threads=@@global.myisam_repair_threads;
DROP TABLE t1;
CREATE TABLE t1(a INT);
USE mysql;
REPAIR TABLE test.t1 USE_FRM;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
USE test;
DROP TABLE t1;
CREATE TABLE t1(a CHAR(255), KEY(a));
SET myisam_sort_buffer_size=4096;
INSERT INTO t1 VALUES
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0');
Warnings:
Error	1034	myisam_sort_buffer_size is too small. X
Error	1034	Number of rows changed from 0 to 157
SET myisam_repair_threads=2;
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	error	myisam_sort_buffer_size is too small. X
test.t1	repair	warning	Number of rows changed from 0 to 157
test.t1	repair	status	OK
SET myisam_repair_threads=@@global.myisam_repair_threads;
SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size;
DROP TABLE t1;
CREATE TABLE t1(a CHAR(255), KEY(a));
SET myisam_sort_buffer_size=4496;
INSERT INTO t1 VALUES
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0');
SET myisam_repair_threads=2;
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
SET myisam_repair_threads=@@global.myisam_repair_threads;
SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size;
DROP TABLE t1;
# End of 4.1 tests
# Test with a saved table from 4.1
SHOW TABLE STATUS LIKE 't1';
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment	Max_index_length	Temporary
t1	MyISAM	9	Fixed	2	5	10	21474836479	1024	0	NULL	#	#	NULL	latin1_swedish_ci	NULL			17179868160	N
SELECT * FROM t1;
id
1
2
# Run CHECK TABLE, it should indicate table need a REPAIR TABLE
CHECK TABLE t1 FOR UPGRADE;
Table	Op	Msg_type	Msg_text
test.t1	check	error	Upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it!
# REPAIR old table USE_FRM should fail
REPAIR TABLE t1 USE_FRM;
Table	Op	Msg_type	Msg_text
test.t1	repair	warning	Number of rows changed from 0 to 1
test.t1	repair	status	OK
# Run REPAIR TABLE to upgrade .frm file
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
SHOW TABLE STATUS LIKE 't1';
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment	Max_index_length	Temporary
t1	MyISAM	10	Fixed	1	7	7	1970324836974591	1024	0	NULL	#	#	NULL	latin1_swedish_ci	NULL			17179868160	N
SELECT * FROM t1;
id
1
REPAIR TABLE t1 USE_FRM;
Table	Op	Msg_type	Msg_text
test.t1	repair	warning	Number of rows changed from 0 to 1
test.t1	repair	status	OK
SELECT * FROM t1;
id
1
DROP TABLE t1;
# End of 5.0 tests
DROP TABLE IF EXISTS tt1;
CREATE TEMPORARY TABLE tt1 (c1 INT);
REPAIR TABLE tt1 USE_FRM;
Table	Op	Msg_type	Msg_text
tt1	repair	error	Cannot repair temporary table from .frm file
DROP TABLE tt1;
#
# Bug #48248 assert in MDL_ticket::upgrade_shared_lock_to_exclusive
#
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INT);
LOCK TABLES t1 READ;
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	Error	Table 't1' was locked with a READ lock and can't be updated
test.t1	repair	status	Operation failed
UNLOCK TABLES;
DROP TABLE t1;
#
# Test for bug #50784 "MDL: Assertion `m_tickets.is_empty() ||
#                      m_tickets.front() == m_trans_sentinel'"
#
drop tables if exists t1, t2;
create table t1 (i int);
create table t2 (j int);
set @@autocommit= 0;
repair table t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
test.t2	repair	status	OK
set @@autocommit= default;
drop tables t1, t2;
#
# Check that we have decent error messages when using crashed
# .frm file from MySQL 3.23
#
# Test with a saved table from 3.23
SET @save_global_character_set_server= @@global.character_set_server;
set @@global.character_set_server=@@character_set_server;
select count(*) from t1;
ERROR HY000: Got error 190 "Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an" from storage engine MyISAM
check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	Error	Got error 190 "Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an" from storage engine MyISAM
test.t1	check	error	Corrupt
repair table t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	Error	Got error 190 "Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an" from storage engine MyISAM
test.t1	repair	error	Corrupt
repair table t1 use_frm;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
select count(*) from t1;
count(*)
0
check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
drop table t1;
set @@global.character_set_server=@save_global_character_set_server;
create table t1 (a blob);
create view v1 as select * from t1;
repair view v1;
Table	Op	Msg_type	Msg_text
test.v1	repair	status	OK
drop view v1;
drop table t1;
# End of 5.5 tests
CREATE TABLE t1 (i INT) ENGINE=MyISAM;
INSERT t1 VALUES (1);
LOCK TABLE t1 WRITE;
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
SELECT * FROM INFORMATION_SCHEMA.TABLES;
SELECT * FROM t1;
i
1
UNLOCK TABLES;
DROP TABLE t1;
# End of 10.0 tests