MySQL Bugs: #12253: mysqlcheck does not work when --fast used with --auto-recover

Bug #12253	mysqlcheck does not work when --fast used with --auto-recover
Submitted:	28 Jul 2005 22:36	Modified:	12 Aug 2005 1:40
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	4.1.13	OS:	Any (all)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
myisamcheck does not  repair table if used with options  --fast --auto-recover:

shrek:/var/lib/mysql/test # mysqlcheck  --fast  --force --auto-repair test bad
test.bad
warning  : Table is marked as crashed
error    : Size of indexfile is: 265306        Should be: 270336
error    : Corrupt
shrek:/var/lib/mysql/test # mysqlcheck  --fast  --force --auto-repair test bad
test.bad
warning  : Table is marked as crashed
error    : Size of indexfile is: 265306        Should be: 270336
error    : Corrupt

This one works: 

shrek:/var/lib/mysql/test # mysqlcheck    --force --auto-repair test bad
test.bad
warning  : Table is marked as crashed
error    : Size of indexfile is: 265306        Should be: 270336
error    : Corrupt

Repairing tables
test.bad 

How to repeat:
Make corrupted table and run --fast  --force --auto-repair for it.

I need to find out what the  steps are to create a corrupt table.

I get this on my mysql compile of 4.1.14 (after kill -9 of the running mysqld after inserting data into 'crash') :

radha:~/mysql-build/mysql-4.1.clean/myisam patg$ ../client/mysqlcheck -S /tmp/mysqld-4.1-5552.sock --fast  --force --auto-repair test crash test.crash
test.crash
warning  : 1 client is using or hasn't closed the table properly
status   : OK
test.crash                                         Table is already up to date

radha:~/mysql-build/mysql-4.1.clean/myisam patg$ ../client/mysqlcheck -S /tmp/mysqld-4.1-5552.sock --fast  --force --auto-repair test crash test.crash
test.crash                                         Table is already up to date
test.crash                                         Table is already up to date

Then, to make the file more corrupt, I crash the server, then edited both the index file and the data file:

radha:~/mysql-build/mysql-4.1.clean/myisam patg$ ../client/mysqlcheck -S /tmp/mysqld-4.1-5552.sock --fast  --force --auto-repair test crash test.crash
test.crash
error    : Can't open file: 'crash.MYI' (errno: 126)
test.crash
error    : Can't open file: 'crash.MYI' (errno: 126)

more findings, without '--fast', it 'tries' to repair: 

radha:~/mysql-build/mysql-4.1.clean/myisam patg$ ../client/mysqlcheck -S /tmp/mysqld-4.1-5552.sock --force --auto-repair test crash test.crash
test.crash
error    : Can't open file: 'crash.MYI' (errno: 126)
test.crash
error    : Can't open file: 'crash.MYI' (errno: 126)

Repairing tables
test.crash
error    : Can't open file: 'crash.MYI' (errno: 126)
test.crash
error    : Can't open file: 'crash.MYI' (errno: 126)

Ok, it breaks as advertised:

radha:~/mysql-build/mysql-4.1.clean/myisam patg$ ../client/mysqlcheck -S /tmp/mysqld-4.1-5552.sock --fast  --force --auto-repair test crash test.crash
test.crash
warning  : Table is marked as crashed
warning  : 1 client is using or hasn't closed the table properly
error    : Size of datafile is: 123         Should be: 132
error    : Corrupt
test.crash
warning  : Table is marked as crashed
warning  : 1 client is using or hasn't closed the table properly
error    : Size of datafile is: 123         Should be: 132
error    : Corrupt
radha:~/mysql-build/mysql-4.1.clean/myisam patg$ ../client/mysqlcheck -S /tmp/mysqld-4.1-5552.sock --force --auto-repair test crash test.crash
test.crash
warning  : Table is marked as crashed
warning  : 1 client is using or hasn't closed the table properly
error    : Size of datafile is: 123         Should be: 132
error    : Corrupt
test.crash
warning  : Table is marked as crashed
warning  : 1 client is using or hasn't closed the table properly
error    : Size of datafile is: 123         Should be: 132
error    : Corrupt

Repairing tables
test.crash
info     : Wrong bytesec:   0-  2-102 at 0; Skipped
warning  : Number of rows changed from 6 to 0
status   : OK
test.crash                                         OK

The problem is that with --fast, it ends up running 'repair table':

(this is my sql log while running each command)
050810 22:32:47       3 Connect     patg@localhost as anonymous on 
                      3 Init DB     test
                      3 Query       CHECK TABLE `crash`
050810 22:32:48       3 Query       CHECK TABLE `test`.`crash`
                      3 Query       REPAIR TABLE `test`.`crash`
                      3 Query       REPAIR TABLE `test`.`crash`
                      3 Quit

I found that the code was not adding the table it found in 'CHECK' to the list of tables to be repaired if 'opt_fast' (--fast). I changed that logic to not add the table even if 'opt_fast' is selected, and now it works:

radha:~/mysql-build/mysql-4.1.clean/client patg$ ./mysqlcheck -S /tmp/mysqld-4.1-5552.sock --fast  --force --auto-repair test crash test.crash
test.crash
warning  : Table is marked as crashed
warning  : 1 client is using or hasn't closed the table properly
error    : Size of datafile is: 214         Should be: 220
error    : Corrupt
test.crash
warning  : Table is marked as crashed
warning  : 1 client is using or hasn't closed the table properly
error    : Size of datafile is: 214         Should be: 220
error    : Corrupt

Repairing tables
test.crash
info     : Found block with impossible length 107 at 0; Skipped
warning  : Number of rows changed from 5 to 0
status   : OK
test.crash                                         OK
radha:~/mysql-build/mysql-4.1.clean/client patg$

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28133

changeset pushed.