mySQL Repair Fail

andrewm

Is there anything you can do when a myisamchk repair fails?

Thanks!
Andrew

Sxooter

Restore from backup?

andrewm

Its really wierd, I repaired the file again and it worked fine:

myisamchk -r PRODUCTS;
- recovering (with sort) MyISAM-table 'PRODUCTS'
Data records: 1914
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
- Fixing index 5
- Fixing index 6
- Fixing index 7
- Fixing index 8
- Fixing index 9
- Fixing index 10
- Fixing index 11
- Fixing index 12

And then I checked it and nothin is wrong:

myisamchk -m PRODUCTS;
Checking MyISAM file: PRODUCTS
Data records:    1914   Deleted blocks:       0
- check file-size
- check key delete-chain
- check record 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 data record references index: 9
- check data record references index: 10
- check data record references index: 11
- check data record references index: 12
- check record links

But when I try to update any of the fields that are text or varchar it gives me this error:
ERROR 1034: Incorrect key file for table: 'PRODUCTS'. Try to repair it

I don't understand why it will let me update int fields, but not varchar or text fields?

andrewm

I just tried an extended check and nothing wrong was found.

andrewm

Ok, i just tried the "check" feature within mysql and go this:

mysql> check table PRODUCTS extended;
+-------------------+-------+----------+----------------------------+
| Table             | Op    | Msg_type | Msg_text                   |
+-------------------+-------+----------+----------------------------+
| pbreview.PRODUCTS | check | warning  | Table is marked as crashed |
| pbreview.PRODUCTS | check | status   | OK                         |
+-------------------+-------+----------+----------------------------+
2 rows in set (0.19 sec)

Why are there two PRODUCTS tables???

Sxooter

I've heard that sometimes it takes two passes for the myisamcheck to fix all the problems it encounters.

andrewm

I ran it like 10 times and now mysql check outputs this:

mysql> check table PRODUCTS extended;
+-------------------+-------+----------+----------+
| Table             | Op    | Msg_type | Msg_text |
+-------------------+-------+----------+----------+
| pbreview.PRODUCTS | check | status   | OK       |
+-------------------+-------+----------+----------+
1 row in set (0.16 sec)

But I still am getting this:

ERROR 1034: Incorrect key file for table: 'PRODUCTS'. Try to repair it

And its only when I try to update text and varchar fields. Updating int fields works fine.

Thanks for spending time to try to help me 🙂

Starts looking for recent backup

Sxooter

Oh, I thought it had fixed the problem. It sounds like a broken index. try dropping and recreating the index and see if that helps. P.s. I'm a postgresql guy, so I'm just guessing wildly here. 🙂

andrewm

Well, I found out its because either A: I am stupid or B: One of the other people who helps on the site is stupid. I would put my money on A 😃

  FULLTEXT KEY PRODUCTNAME_2 (PRODUCTNAME,DESCRIPTION),
  FULLTEXT KEY PRODUCTNAME_3 (PRODUCTNAME,DESCRIPTION),
  FULLTEXT KEY PRODUCTNAME_4 (PRODUCTNAME),
  FULLTEXT KEY PRODUCTNAME_5 (PRODUCTNAME),
  FULLTEXT KEY PRODUCTNAME_6 (PRODUCTNAME),
  FULLTEXT KEY PRODUCTNAME_7 (PRODUCTNAME),
  FULLTEXT KEY PRODUCTNAME (PRODUCTNAME)

:rolleyes: