Upgrade correpted InnoDB tables

[Mattias J]

Earlier today we tried upgrading one of our MySQL servers from 4.0.15 to
4.1.8 using RPMs. This resulted in corrupted InnoDB tables seemingly
randomly selected across the different databases (about 40 databases with
similar schemas plus an addition 5 with different tables).

The error message when trying to access one of those tables is:
Error: 1016 - Can't open file: 'account_text.ibd' (errno: 1)

Since there were no important data on this particular server we tried
dropping and recreating the table. When trying to drop it, we receive
Error: 1051 - Unknown table 'account_text'
So therefore we removed the .fmt file manually and then tried to recreate
the table. Here is the result:
Error: 1005 - Can't create table './DB000001/account_text.frm' (errno: 121)

What is going on here...?
How would we go about recovering?
I found some bugs in the database (such as #3859, #3594, #3147) that talk
about similar symptoms. Are these problems in any way related to any bug?
Could we have done anything wrong during the upgrade (I did not perform it
myself)? What could we do to prevent this from happening when upgrading the
other servers?

Thanks in advance,
Mattias J

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=myo...@freebsd.csie.nctu.edu.tw

[Eric Bergen]

If you don't have any data in innodb, delete and recreate the
tablespace including the frm files. This will give you a fresh 4.1
table space to import into.

-Eric

> To unsubscribe: http://lists.mysql.com/mysql?unsub=eric....@gmail.com
>
>

--
Eric Bergen
eric....@gmail.com
http://www.bleated.com

[Mattias J]

There are mostly just 2 or 3 corrupted tables out of about 100 in each
database/schema/namespace and if possible we would like to keep the
information in the other tables.

[Heikki Tuuri]

Mattias,

please use

innodb_table_monitor

to print the contents of the InnoDB internal data dictionary.
http://dev.mysql.com/doc/mysql/en/InnoDB_Monitor.html

Regards,

Heikki

[Mattias J]

Ok. This is what happened:
We removed MySQL from the server and re-installed 4.0(.21) then put back
some old data.
When trying to upgrade to 4.1.9 we encountered the same problem, with the
exact same tables being corrupted!

For one of the tables, as an example, the innodb_table_monitor says
050114 15:29:21 InnoDB: table DB000001/account_text is in the new
compact format
InnoDB: of MySQL 5.0.3 or later
InnoDB: Failed to load table DB000001/account_text
This occurs 51 times in total for 40+ databases with 130+ tables in each
(though different tables in each database).

We have a copy of this data and could easily reproduce the error by
restoring the data and then upgrading from 4.0 to 4.1(.8 or .9).

To unsubscribe: http://lists.mysql.com/mysql?unsub=myo...@freebsd.csie.nctu.edu.tw

[Heikki Tuuri]

Mattias,

this is very strange. Have you used MySQL-5.0.x to create those tables? Or
which MySQL version did you use when you created those tables?

[Heikki Tuuri]

Mattias,

was it so that you RECREATED those tables with 4.0.21, and you STILL got the
error in 4.1.8?

[Mattias J]

At 2005-01-14 18:09, you wrote:
>this is very strange. Have you used MySQL-5.0.x to create those tables? Or
>which MySQL version did you use when you created those tables?

>...

>was it so that you RECREATED those tables with 4.0.21, and you STILL got
>the error in 4.1.8?

We have never used MySQL 5.

What we did was to delete the InnoDB data files and all other MySQL data
from the corrupted server. Then copied the data (InnoDB, .frm etc) from an
old server, still running 4.0.15. Then we uninstalled MySQL and installed
4.0.21 on the server where the tables were corrupted and checked to make
sure the copied data was fine. After that we upgraded MySQL to 4.1.9 and
the tables where corrupted again.
On monday I will see if we have a server "to spare" so we can test if this
is reproducible in another environment.

I wrote

>To unsubscribe: http://lists.mysql.com/mysql?unsub=mjs...@expertsystem.se

[Heikki Tuuri]

Mattias,

----- Original Message -----
From: "Mattias J" <mjs...@expertsystem.se>
Newsgroups: mailing.database.myodbc
Sent: Friday, January 14, 2005 8:50 PM
Subject: Re: Upgrade correpted InnoDB tables

> At 2005-01-14 18:09, you wrote:
>>this is very strange. Have you used MySQL-5.0.x to create those tables? Or
>>which MySQL version did you use when you created those tables?
>>...
>>was it so that you RECREATED those tables with 4.0.21, and you STILL got
>>the error in 4.1.8?
>
> We have never used MySQL 5.
>
> What we did was to delete the InnoDB data files and all other MySQL data
> from the corrupted server. Then copied the data (InnoDB, .frm etc) from an
> old server, still running 4.0.15. Then we uninstalled MySQL and installed
> 4.0.21 on the server where the tables were corrupted and checked to make
> sure the copied data was fine. After that we upgraded MySQL to 4.1.9 and
> the tables where corrupted again.
> On monday I will see if we have a server "to spare" so we can test if this
> is reproducible in another environment.

the explanation has to be that the MIX_ID column in the InnoDB internal data
dictionary has got a value != 0. I do not see any way it could get that
value, except through corruption in the main memory. But then it is very
strange how the corruption has not spoiled the data dictionary completely.

You can fix the problem by dropping the table (using <= 4.0.22) and
reimporting it from a dump. We will add diagnostic code to 4.1.10 that can
give us a clue if this problem reappears.

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/