I have one table in my database that is completely unhappy with
mysqldump. However, it acts like it's just fine.

Performing checks on this table comes up okay:

mysql> CHECK TABLE post;

+----------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| forum_new.post | check | status | OK |
+----------------+-------+----------+----------+
1 row in set (1 min 8.13 sec)

myisamchk is also happy with it:

myisamchk -c post
Checking MyISAM file: post
Data records: 2012438 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check data record references index: 4
- check data record references index: 5
- check data record references index: 6
- check data record references index: 7
- check data record references index: 8
- check record links

The more extensive check even comes up okay:

myisamchk -m post
Checking MyISAM file: post
Data records: 2012438 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check data record references index: 4
- check data record references index: 5
- check data record references index: 6
- check data record references index: 7
- check data record references index: 8
- check record links

But when I run mysqldump, I get this error:
mysqldump -aA --opt -Q -u backup_user -p > file.sql
mysqldump: Got error: 1016: Can't open file: 'post.MYI' (errno: 144)
when using LOCK TABLES

And when I make sure that we don't use locks, it still doesn't work:

mysqldump -A --skip-opt -u backup_user -p > post.sql
mysqldump: mysqldump: Couldn't execute 'show create table `post`':
Can't open file: 'post.MYI' (errno: 144) (1016)

However, everything is running fine with this database. I can select
from it, insert stuff into it, etc, with absolutely no problems at
all. Repeated repairs have done nothing. At one point, I even copied
everything out of this table into a new table (via INSERT INTO
new_table SELECT * FROM oldtable), and dropped the old table, and I
get the exact same error message.

I am running 4.1.11 on Debian Sarge. Since I can't get a backup of
this (critical) data, I'm loathe to do anything too risky. Any
thoughts or suggestions would be greatly appreciated.

Hi,

On Dec 21, 2007 8:08 PM, cirisme <cirisme@theologyweb.com> wrote:
> I have one table in my database that is completely unhappy with
> mysqldump. However, it acts like it's just fine.
>
> Performing checks on this table comes up okay:
>
> mysql> CHECK TABLE post;
>
> +----------------+-------+----------+----------+
> | Table | Op | Msg_type | Msg_text |
> +----------------+-------+----------+----------+
> | forum_new.post | check | status | OK |
> +----------------+-------+----------+----------+
> 1 row in set (1 min 8.13 sec)
>
> myisamchk is also happy with it:
>
> myisamchk -c post
> Checking MyISAM file: post
> Data records: 2012438 Deleted blocks: 0
> - check file-size
> - check record delete-chain
> - check key delete-chain
> - check index reference
> - check data record references index: 1
> - check data record references index: 2
> - check data record references index: 3
> - check data record references index: 4
> - check data record references index: 5
> - check data record references index: 6
> - check data record references index: 7
> - check data record references index: 8
> - check record links
>
> The more extensive check even comes up okay:
>
> myisamchk -m post
> Checking MyISAM file: post
> Data records: 2012438 Deleted blocks: 0
> - check file-size
> - check record delete-chain
> - check key delete-chain
> - check index reference
> - check data record references index: 1
> - check data record references index: 2
> - check data record references index: 3
> - check data record references index: 4
> - check data record references index: 5
> - check data record references index: 6
> - check data record references index: 7
> - check data record references index: 8
> - check record links
>
> But when I run mysqldump, I get this error:
> mysqldump -aA --opt -Q -u backup_user -p > file.sql
> mysqldump: Got error: 1016: Can't open file: 'post.MYI' (errno: 144)
> when using LOCK TABLES
>
> And when I make sure that we don't use locks, it still doesn't work:
>
> mysqldump -A --skip-opt -u backup_user -p > post.sql
> mysqldump: mysqldump: Couldn't execute 'show create table `post`':
> Can't open file: 'post.MYI' (errno: 144) (1016)
>
> However, everything is running fine with this database. I can select
> from it, insert stuff into it, etc, with absolutely no problems at
> all. Repeated repairs have done nothing. At one point, I even copied
> everything out of this table into a new table (via INSERT INTO
> new_table SELECT * FROM oldtable), and dropped the old table, and I
> get the exact same error message.
>
> I am running 4.1.11 on Debian Sarge. Since I can't get a backup of
> this (critical) data, I'm loathe to do anything too risky. Any
> thoughts or suggestions would be greatly appreciated.

Very odd. perror shows this:

baron@kanga:~$ perror 144
MySQL error code 144: Table is crashed and last repair failed

Try to use SELECT INTO OUTFILE to get the data out.

Is there anything odd about your setup, such as the data being on NFS?
(just speculating).

On Dec 21, 2007, at 9:15 PM, Baron Schwartz wrote:

> Hi,
>
> On Dec 21, 2007 8:08 PM, cirisme <cirisme@theologyweb.com> wrote:
>> I have one table in my database that is completely unhappy with
>> mysqldump. However, it acts like it's just fine.
>>
>> Performing checks on this table comes up okay:
>>
>> mysql> CHECK TABLE post;
>>
>> +----------------+-------+----------+----------+
>> | Table | Op | Msg_type | Msg_text |
>> +----------------+-------+----------+----------+
>> | forum_new.post | check | status | OK |
>> +----------------+-------+----------+----------+
>> 1 row in set (1 min 8.13 sec)
>>
>> myisamchk is also happy with it:
>>
>> myisamchk -c post
>> Checking MyISAM file: post
>> Data records: 2012438 Deleted blocks: 0
>> - check file-size
>> - check record delete-chain
>> - check key delete-chain
>> - check index reference
>> - check data record references index: 1
>> - check data record references index: 2
>> - check data record references index: 3
>> - check data record references index: 4
>> - check data record references index: 5
>> - check data record references index: 6
>> - check data record references index: 7
>> - check data record references index: 8
>> - check record links
>>
>> The more extensive check even comes up okay:
>>
>> myisamchk -m post
>> Checking MyISAM file: post
>> Data records: 2012438 Deleted blocks: 0
>> - check file-size
>> - check record delete-chain
>> - check key delete-chain
>> - check index reference
>> - check data record references index: 1
>> - check data record references index: 2
>> - check data record references index: 3
>> - check data record references index: 4
>> - check data record references index: 5
>> - check data record references index: 6
>> - check data record references index: 7
>> - check data record references index: 8
>> - check record links
>>
>> But when I run mysqldump, I get this error:
>> mysqldump -aA --opt -Q -u backup_user -p > file.sql
>> mysqldump: Got error: 1016: Can't open file: 'post.MYI' (errno: 144)
>> when using LOCK TABLES
>>
>> And when I make sure that we don't use locks, it still doesn't work:
>>
>> mysqldump -A --skip-opt -u backup_user -p > post.sql
>> mysqldump: mysqldump: Couldn't execute 'show create table `post`':
>> Can't open file: 'post.MYI' (errno: 144) (1016)
>>
>> However, everything is running fine with this database. I can select
>> from it, insert stuff into it, etc, with absolutely no problems at
>> all. Repeated repairs have done nothing. At one point, I even copied
>> everything out of this table into a new table (via INSERT INTO
>> new_table SELECT * FROM oldtable), and dropped the old table, and I
>> get the exact same error message.
>>
>> I am running 4.1.11 on Debian Sarge. Since I can't get a backup of
>> this (critical) data, I'm loathe to do anything too risky. Any
>> thoughts or suggestions would be greatly appreciated.
>
> Very odd. perror shows this:
>
> baron@kanga:~$ perror 144
> MySQL error code 144: Table is crashed and last repair failed
>
> Try to use SELECT INTO OUTFILE to get the data out.
>
> Is there anything odd about your setup, such as the data being on NFS?
> (just speculating).

No, nothing strange that I could possibly think of, certainly not on
NFS. I will try the SELECT INTO OUTFILE soon, that is a very ful
suggestion.