Help with corrupt ISAM table

kkubek · January 3, 2009

mySQL 4.1.20-standard

ISAM table orders_history

select recordID, ros_number,description,sku,model,manufacturer,emailDate,delivery_date,delv_method,status,vehicle,store_number,zip,email_from from orders_history where ros_number = '704572' order by 1,8;

ERROR 1030 (HY000): Got error 127 from storage engine

but this works:

select recordID, ros_number,description,sku,model,manufacturer,emailDate,delivery_date,delv_method,status,vehicle,store_number,zip,email_from from orders_history where ros_number = '417638735' order by 1,8;

isamchk shows an error:

isamchk -v orders_history

Checking ISAM file: orders_history

Data records: 20892617 Deleted blocks: 0

- check file-size

isamchk: warning: Size of datafile is: 49 Should be: 2739177090

- check delete-chain

index 1:

index 2:

No recordlinks

- check index reference

- check data record references index: 1

isamchk: error: Found key at page 1024 that points to record outside datafile

- check record links

isamchk: error: Recordlink that points outside datafile at 0

isamchk: error: Record-count is not ok; is 0 Should be: 20892617

isamchk: warning: Found 0 record-data and 0 unused data and 0 deleted-data

Total 0, Should be: 49

isamchk: warning: Found 0 parts Should be: 20892625 parts

ISAM-table 'orders_history' is corrupted

Fix it using switch "-r" or "-o"

But, trying to fix it fails:

isamchk -r orders_history

isamchk: Can't create/write to file 'orders_history.ISM' (Errcode: 17)

isamchk: error: Got error 17 when trying to recreate indexfile

ISAM-table 'orders_history' is not fixed because of errors

isamchk -o orders_history

isamchk: Can't create/write to file 'orders_history.ISM' (Errcode: 17)

isamchk: error: Got error 17 when trying to recreate indexfile

ISAM-table 'orders_history' is not fixed because of errors

ls -l orders_history.*

-rw-rw---- 1 mysql mysql 9782 Nov 10 2007 orders_history.frm

-rw-rw---- 1 mysql mysql 4294967345 Jan 3 14:42 orders_history.ISD

-rw-rw---- 1 mysql mysql 542180352 Jan 3 14:42 orders_history.ISM

Is this related to the fact that the ISD file is 4G ?

fenway · January 4, 2009

Why are you still using ISAM tables???? Convert them with:

ALTER TABLE tblname ENGINE=MYISAM;

Also, what does CHECK TABLES say? Does REPAIR fail???

kkubek · January 4, 2009

Fenway,

Thanks for the help.

I inherited this database on Thursday. I believe it was created a couple years ago, and has never been maintained ... until now when they started getting errors.

I should have included this in my original post, but I tried to convert to MyIsam:

mysql> alter table orders_history engine=myisam;

ERROR 1030 (HY000): Got error 127 from storage engine

and

-bash-3.00$ mysql_convert_table_format -user=root -password=password transactions orders_history

Can't convert orders_history: Error Got error 127 from storage engine

Check table says "The storage engine for the table doesn't support check"

Repair table says "The storage engine for the table doesn't support repair"

I've spent hours searching the Web, and trying different ideas to fix, this with no luck.

If/when we get this fixed, I'll probably convert everything to MyISAM. What are the advantages/disadvantages of MyISAM ?

Thanks in advance,

KK

fenway · January 4, 2009

ISAM is ancient... MyISAM was its replacement many years ago. Try deleting the index file.

kkubek · January 8, 2009

Hi,

I recently inherited a MySQL database that appears to have a corrupt table. I am an oracle DBA and have never used MySQL until this week.

Unfortunately, isamchk is giving me an error that's driving me nuts. I've searched the Web for hours, and can't find anything useful.

[mysql@localhost transactions]$ isamchk -r kk

isamchk: Can't create/write to file 'kk.ISM' (Errcode: 17)

isamchk: error: Got error 17 when trying to recreate indexfile

ISAM-table 'kk' is not fixed because of errors

[mysql@localhost transactions]$ perror 17

OS error code 17: File exists

Well duh, of course kk.ISM exists:

[mysql@localhost transactions]$ ll kk*

-rwxrwxrwx 1 mysql mysql 9782 Jan 4 05:27 kk.frm

-rwxrwxrwx 1 mysql mysql 4294967345 Jan 3 05:18 kk.ISD

-rwxrwxrwx 1 mysql mysql 541793280 Jan 3 08:05 kk.ISM

I would assume that isamchk would overwrite the file (permissions are OK), but maybe it doesn't overwrite. So, let's try moving it :

[mysql@localhost transactions]$ mv kk.ISM kk.ISM2

[mysql@localhost transactions]$ isamchk -r kk

isamchk: error: File 'kk' doesn't exist

Maybe something is locking the file ? It's not Mysqld because I copied the all 3 files to another server that mysqld is not running on.

[mysql@localhost transactions]$ ps -ef|grep mysq

mysql 14021 14020 0 12:01 pts/0 00:00:00 -bash

mysql 14198 14021 0 12:32 pts/0 00:00:00 ps -ef

mysql 14199 14021 0 12:32 pts/0 00:00:00 grep mysq

What am I missing ? Please help me keep what little sanity remains.

corbin · January 9, 2009

isamchk: Can't create/write to file 'kk.ISM' (Errcode: 17)

Looks like it doesn't have write perms for some reason.

Could be something else though.

fenway · January 9, 2009

Does sound like that, though the perms seem to be 777.

Can you dump the data after deleting the index without trying to repair it?

corbin · January 9, 2009

Hrmmm, now that I reread the error, it probably is refusing to overwrite it. It has the capability.

kkubek · January 10, 2009

Thanks for the help.

If i move the indexfile:

-bash-3.00$ mv kk.ISM kk.ISM2

-bash-3.00$ isamchk -r kk

isamchk: error: File 'kk' doesn't exist

Also, a dump won't work after moving indexfile:

-bash-3.00$ mv kk.ISM kk.ISM2

-bash-3.00$ mysqldump transactions kk

-- MySQL dump 10.9

--

-- Host: localhost Database: transactions

-- ------------------------------------------------------

-- Server version 4.1.20

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--

-- Table structure for table `kk`

--

DROP TABLE IF EXISTS `kk`;

CREATE TABLE `kk` (

`clientID` int(11) NOT NULL default '0',

`recordID` bigint(30) NOT NULL default '0',

`store_number` varchar(100) default NULL,

`pickup_location` varchar(100) default NULL,

`fname` varchar(100) default NULL,

`middle_initial` varchar(5) default NULL,

`lname` varchar(100) default NULL,

`phone1` varchar(100) default NULL,

`phone2` varchar(100) default NULL,

`phone3` varchar(100) default NULL,

`address` varchar(100) default NULL,

`city` varchar(255) default NULL,

`state` varchar(10) default NULL,

`zip` varchar(20) default NULL,

`comments` text,

`description` varchar(255) default NULL,

`sku` varchar(100) default NULL,

`model` varchar(100) default NULL,

`manufacturer` varchar(100) default NULL,

`delivery_date` varchar(100) default NULL,

`delivery_time` varchar(5) default NULL,

`delv_method` varchar(50) default NULL,

`status` varchar(20) default NULL,

`trans_date` varchar(100) default NULL,

`ros_number` varchar(15) NOT NULL default '',

`emailDate` date default '0000-00-00',

`result` varchar(25) default NULL,

`email_from` varchar(50) NOT NULL default '',

`recordID2` int(11) NOT NULL default '0',

`vehicle` varchar(20) default NULL,

`customer_email` varchar(100) default NULL,

KEY `idx_orders_rosnumber` (`recordID`),

KEY `idx_orders_recordid` (`ros_number`)

) ENGINE=ISAM DEFAULT CHARSET=latin1 PACK_KEYS=1;

--

-- Dumping data for table `kk`

--

/*!40000 ALTER TABLE `kk` DISABLE KEYS */;

LOCK TABLES `kk` WRITE;

mysqldump: Error 1030: Got error 127 from storage engine when dumping table `kk` at row: 0

And here's the kicker : the backups they do overwrite each day, and don't go to tape. So, there's no way to restore.

What other options do I have ? Is there any other support out there ? Can I send the files to someone who specializes in recovering ?

fenway · January 11, 2009

What other options do I have ? Is there any other support out there ? Can I send the files to someone who specializes in recovering ?

Hmm... this board won't let you upload large files... I think the limit is ~1MB.... how big is your table data? Also, is the more than one corrupt table? If you can't upload, I'll make a rare exception and will take a look if you send me an e-mail.

fenway · January 14, 2009

---bringing this discussion back on-line---

Yes, I think it does have to do with the 4GB limit... in fact, you're over it: 4GB = 4294967295! I didn't notice that originally.

What OS are you running?

Can you run "show table status like ' '" for each table?

mysql> show table status like 'orders_history';
+----------------+--------+---------+------------+----------+----------------+-------------+-----------------+-                      -------------+-----------+----------------+---------------------+---------------------+------------+-----------                      --------+----------+----------------+---------+
| 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 |
+----------------+--------+---------+------------+----------+----------------+-------------+-----------------+-                      -------------+-----------+----------------+---------------------+---------------------+------------+-----------                      --------+----------+----------------+---------+
| orders_history | ISAM   |       9 | Dynamic    | 21245130 |            134 |  2861006803 |      4294967295 |                          551282688 |         0 |           NULL | 2007-11-10 09:16:57 | 2009-01-12 17:20:02 | NULL       | latin1_swe                      dish_ci |     NULL | pack_keys=1    |         |
+----------------+--------+---------+------------+----------+----------------+-------------+-----------------+-                      -------------+-----------+----------------+---------------------+---------------------+------------+-----------                      --------+----------+----------------+---------+
1 row in set (0.00 sec)

I notice that max_data_lenght is 4G, but the file size in UNIX is slightly larger.

Is there any way we could:

1) move some data to another table

2) shrink original table

3) convert original table to MYI

4) bring back the data we copied ?

Take a look at this article; see if you can change max_rows with an ALTER statement as described... it's our best bet right now.

fenway,

I tried that one before. My first guess was that 4G limit was the problem and I found that same link.

Here's the results:
mysql> alter table kk max_rows = 2000000000 avg_row_length = 90;
ERROR 1030 (HY000): Got error 127 from storage engine
I tried various different values for the args, and always got the same error.

fyi, i don't think this is an OS limit. If it's a 4G issue it's gotta be MySQL

I tried catting all 3 files together into a single file, which was over 4.5G and it worked. I also checked ulimit, and it's unlimited.

fenway · January 14, 2009

What else have you tried w.r.t the isamchk command? I'm been re-reading the manpage.

1) Are you sure there's enough free space (at least 2-3 times the file size?) on the temp dir?

2) Do you have a backup of these files?

3) Have you tried to "force" the repair?

4) Have you tried to "safe-recover"?

Let's start with these.... you may have done this already, but I just wanted to be sure.

There's also this link from an old version of the manual that describes a 4-step process to recovery... has some interesting suggestions re: index file re-creation.

Keep me posted! Good luck.

kkubek · January 17, 2009

fenway,

Nope. no backup of these files. I inherited this db when they started getting the error messages, and it turns out the backup they were running gets overwritten each night.

I've tried every possible option in isamchk (including the ones you mentioned.

I also tried that 4 step option from the man page, but no luck.

Stupid question from a MySQL newbie, how do I know what dir is my temp dir ?

KK

fenway · January 18, 2009

It's usually the default OS temp dir -- see here

kkubek · January 18, 2009

my tmpdir has 22G, and the ISD file is 4G

fenway · January 18, 2009

OK... let's cover what you *can* do with the data.

[As for the backup, I didn't mean a mysql dump... i meant a physical copy of the files in question, in case we destroy them in the process.]

What errors/messages do you get from "force-recover"?

kkubek · January 18, 2009

fenway,

yes I have made copies of all the files

Here's the output from a few variations of isamchk:

[mysql@localhost transactions]$ isamchk -f -r orders_history

isamchk: Can't create/write to file 'orders_history.ISM' (Errcode: 17)

isamchk: error: Got error 17 when trying to recreate indexfile

ISAM-table 'orders_history' is not fixed because of errors

[mysql@localhost transactions]$ isamchk -f -q orders_history

Checking ISAM file: orders_history

Data records: 20877511 Deleted blocks: 0

- check file-size

isamchk: warning: Size of datafile is: 49 Should be: 2734016830

- check delete-chain

- check index reference

- check data record references index: 1

isamchk: error: Found key at page 1024 that points to record outside datafile

isamchk: Can't create/write to file 'orders_history.ISM' (Errcode: 17)

isamchk: error: Got error 17 when trying to recreate indexfile

ISAM-table 'orders_history' is not fixed because of errors

[mysql@localhost transactions]$ isamchk -o orders_history

isamchk: Can't create/write to file 'orders_history.ISM' (Errcode: 17)

isamchk: error: Got error 17 when trying to recreate indexfile

ISAM-table 'orders_history' is not fixed because of errors

[mysql@localhost transactions]$

[mysql@localhost transactions]$ ls -l orders_history.*

-rw-rw-rw- 1 mysql mysql 9782 Jan 3 03:03 orders_history.frm

-rw-rw-rw- 1 mysql mysql 4294967345 Jan 3 03:12 orders_history.ISD

-rw-rw-rw- 1 mysql mysql 541793280 Jan 3 03:14 orders_history.ISM

[mysql@localhost transactions]$ id

uid=500(mysql) gid=500(mysql) groups=500(mysql) context=root:system_r:unconfined_t:SystemLow-SystemHigh

[mysql@localhost transactions]$

fenway · January 19, 2009

I'm assuming that all of this is happening when mysqld is shutdown...

What about "isamchk -e -a -i -o"?

The error 17 is really bothering me... are you running this as a privileged user?

kkubek · January 19, 2009

I know, that error 17 is driving me nuts too.

I am mysql when I try this, and yes mysqld has been stoppped

[mysql@localhost transactions]$ isamchk -e -a -i -o orders_history

isamchk: Can't create/write to file 'orders_history.ISM' (Errcode: 17)

isamchk: error: Got error 17 when trying to recreate indexfile

ISAM-table 'orders_history' is not fixed because of errors

User time 0.00, System time 0.01

Maximum resident set size 0, Integral resident set size 0

Non-physical pagefaults 142, Physical pagefaults 0, Swaps 0

Blocks in 0 out 0, Messages in 0 out 0, Signals 0

Voluntary context switches 0, Involuntary context switches 1

fenway · January 19, 2009

Did you try running as root?

kkubek · January 19, 2009

Yup, same result ... the dreaded error 17

fenway · January 19, 2009

What is your tmpdir again? Is it is the default, or is it set in the my.cnf file? Why don't you set it to something unique and new for mysql and then try it again?

Can you drop any of the indexes directly? i.e. with ALTER TABLE DROP INDEX?

kkubek · January 21, 2009

fenway,

I tried changing tmpdir, by doing:

TMPDIR=/usr/local/mysql (from Linux)

and I still got the error 17.

I also tried droppping indexes:

mysql> ALTER TABLE kk DROP INDEX idx_orders_rosnumber;

ERROR 1030 (HY000): Got error 127 from storage engine

mysql> ALTER TABLE kk DROP INDEX idx_orders_recordid

-> ;

ERROR 1030 (HY000): Got error 127 from storage engine

mysql>

fenway · January 25, 2009

Not sure if you can use env vars from linux like that... did you try using --tmpdir?

But 127 is still record-table is crashed. Bah!

Did you say earlier that you could run some select statements at some point?

Sign In

Help with corrupt ISAM table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information