Cheers, Aaron
----- Original Message ----- From: "Gleb Paharenko" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, November 07, 2004 5:23 AM
Subject: Re: Tables Crash when I Delete A Row
Hi.
From which version did you upgrade? If you upgraded from 4.0 you should carefully read:
http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html
May be REPAIR with USE_FRM will be helpful. Some times after ugrade tables have to be rebuilt or repaired.
Aaron <[EMAIL PROTECTED]> wrote:[-- text/plain, encoding quoted-printable, charset: iso-8859-1, 122 lines --]
Hi all , I am having some issues with 4.1.7 on Redhat 9 Kernel 2.4.20-8SMP
The other day I upgraded to RH9 and then put on: mysql-standard-4.1.7-pc-linux-i686-icc-glibc23
Since then , it seems that one of (not all!) our tables has taken to crashing when you delete records from it. It spits out the following error:
Incorrect key file for table 'Offers_To_Sell'; try to repair it
Ok , well If I do a CHECK TABLE , I get this:
mysql> CHECK TABLE Offers_To_Sell ;
+-------------------------+-------+----------+-----------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+-------+----------+-----------------------------+
| Offers_To_Sell | check | warning | Table is marked as crashed |
| Offers_To_Sell | check | error | Found 265847 keys of 265850 |
| Offers_To_Sell | check | error | Corrupt |
+-------------------------+-------+----------+-----------------------------+
3 rows in set (2.80 sec)
Now , if I try to REPAIR , I get this:
mysql> REPAIR TABLE Offers_To_Sell ;
+-------------------------+--------+----------+-------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+--------+----------+-------------------------------------------------+
| worldbid.Offers_To_Sell | repair | error | 2 when fixing table |
| worldbid.Offers_To_Sell | repair | error | Can't copy datafile-header to tempfile, error 9 |
| worldbid.Offers_To_Sell | repair | status | Operation failed |
+-------------------------+--------+----------+-------------------------------------------------+
3 rows in set (2 min 5.49 sec)
If I shell out and use myisamchk --quick , I get this:
myisamchk --quick --tmpdir=/var/tmp Offers_To_Sell
Checking MyISAM file: Offers_To_Sell
Data records: 0 Deleted blocks: 0
myisamchk: warning: Table is marked as crashed and last repair failed
- check file-size
myisamchk: warning: Size of indexfile is: 207900672 Should be: 19564544
myisamchk: warning: Size of datafile is: 200488316 Should be: 200488292
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Found 265850 keys of 0
- check record links
myisamchk: error: Record-count is not ok; is 265850 Should be: 0
myisamchk: warning: Found 265850 parts Should be: 1 parts
MyISAM-table 'Offers_To_Sell' is corrupted
Fix it using switch "-r" or "-o
Then when I do a -r I get this:
myisamchk -r --verbose Offers_To_Sell.MYI
- recovering (with sort) MyISAM-table 'Offers_To_Sell.MYI'
Data records: 47344
- Fixing index 1
- Searching for keys, allocating buffer for 174743 keys
- Dumping 47344 keys
- Fixing index 2
- Searching for keys, allocating buffer for 47345 keys
- Dumping 47344 keys
- Fixing index 3
- Searching for keys, allocating buffer for 19239 keys
- Last merge and dumping keys
- Fixing index 4
- Searching for keys, allocating buffer for 47345 keys
- Dumping 47344 keys
- Fixing index 5
- Searching for keys, allocating buffer for 47345 keys
- Dumping 47344 keys
- Fixing index 6
- Searching for keys, allocating buffer for 47345 keys
- Dumping 47344 keys
- Fixing index 7
- Searching for keys, allocating buffer for 47345 keys
- Dumping 47344 keys
- Fixing index 8
- Searching for keys, allocating buffer for 47345 keys
- Dumping 47344 keys
- Fixing index 9
- Searching for keys, allocating buffer for 47345 keys
- Dumping 47344 keys
- Fixing index 10
- Searching for keys, allocating buffer for 45574 keys
myisamchk: error: 22 when fixing table
MyISAM-table 'Offers_To_Sell.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag
Then, I can fix the problem by using the --safe-recover option , but as soon as a delete is done on the table , it corrupts again.
Anyone have any ideas?
Tanks, Aaron
-- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
