Problem with compressed tables

Hello,

I’m busy developing an application that uses a large database and would like to compress my tables to save on diskspace.

I tried to compress a myisam table with 150M rows and everything went fine according to the commandline tools

Initial table:
-rw-rw---- 1 mysql mysql 5482838160 2006-10-04 10:33 netflow_old.MYD
-rw-rw---- 1 mysql mysql 2120808448 2006-10-04 10:51 netflow_old.MYI

Then I did: ‘myisampack -v netflow_old’ and 5 minutes later the directory showed:
-rw-rw---- 1 mysql mysql 3810187229 2006-10-04 10:33 netflow_old.MYD
-rw-rw---- 1 mysql mysql 1024 2006-10-04 11:08 netflow_old.MYI

There where no errors. Next I did ‘myisamchk -rq netflow_old’ and 30 minutes later I got:
-rw-rw---- 1 mysql mysql 3810187229 2006-10-04 10:33 netflow_old.MYD
-rw-rw---- 1 mysql mysql 2388990976 2006-10-04 11:38 netflow_old.MYI

No errors…so far so good )

But when I did ‘myisamchk netflow_old’, it returned:
Checking MyISAM file: netflow_old
Data records: 152301060 Deleted blocks: 0
myisamchk: warning: Table is marked as crashed

  • check file-size
    myisamchk: warning: Size of indexfile is: 2388990976 Should be: 2120808448
    myisamchk: error: Size of datafile is: 3810187229 Should be: 5482838160
  • check record delete-chain
  • check key delete-chain
  • check index reference
  • check data record references index: 1
    myisamchk: error: Found key at page 426564608 that points to record outside datafile
    MyISAM-table ‘netflow_old’ is corrupted
    Fix it using switch “-r” or “-o”

Did I do something wrong? (I can’t use the table anymore now)

I use the mysql package from debian-stable (mysql Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i386))

Kind regards,
Dirk

Note:

-rw-rw---- 1 mysql mysql 2120808448 2006-10-04 10:51 netflow_old.MYI

myisamchk: warning: Size of indexfile is: 2388990976 Should be: 2120808448
myisamchk: error: Size of datafile is: 3810187229 Should be: 5482838160

It looks like index header was not updated. Could MySQL server be running on this data or something like it.

I would recheck it and try to repair table again.