MySQL table key corruption problem

Dan Coutu coutu at snowy-owl.com
Thu Apr 15 23:41:29 EDT 2010


Lloyd Kvam wrote:
> On Thu, 2010-04-15 at 10:38 -0400, Dan Coutu wrote:
>   
>> I would really rather not have to rebuild the entire openfire db from
>> scratch, adding about 40 user accounts with preserved passwords and so
>> forth.
>>
>> Does anyone have ideas how I can fix this without losing data?
>>     
>
> mysqldump <database> [table_one] [table_two] ... > dump.sql
>         
>         should preserver all of your data.  Feeding the resulting sql
>         file back into mysql will rebuild the tables.  You can examine
>         the dump file to make sure that it seems to be complete.  You'll
>         need to block access to the database to prevent unwanted
>         transactions while you do the dump and restore.  The restore
>         could be done as:
>         
> <dump.sql mysql <database>
>
>
> Could you have failed to run an upgrade script in the past?  I have dim
> memories of scripts to upgrade 3 => 4 => 5.  The dump and restore will
> not get around changes within the mysql (/var/lib/mysql/mysql) database
> where user permissions and accounts are managed.
>   
Lloyd, thanks for the response. I was able to pretty well identify that
the corruption was a result of a bug in MySQL itself.

Using mysqldump didn't work out because it refused to dump the table
with the key corruption. So I could not do a dump/restore operation.

In the end I found that if I shutdown openfire so that the db was not
busy I could then use myisamchk (because the db was using myisam files
rather than innodb) directly on the MYI file for the table. That
revealed that the table had no records.

Because I had previously tried to use mysqlcheck --repair on the table
with no luck and a wide variety of other tricks such as the REPAIR TABLE
sql command I finally just dropped the table and re-created it. Openfire
provides the SQL with their distribution for the create command for
every table so I just cut/pasted it.

I was then able to start up openfire without errors. Interestingly
enough the server was not responsive right away but did 'wake up' and
start serving traffic after a while.

Thanks to everyone who responded to this one, it was  a great help!

Dan


More information about the gnhlug-discuss mailing list