Need help with MYSQL table crash

[salmarmarsson]

Hello Experts,

I had a issue with a MYSQL Table. Here's my scenario.
CentOS Linux 6.4
MySQL version 5.1.69

When trying to run a script to update some tables the Mysql shows a warning at the EventData table

• I first check the table

[root@myserver]# myisamchk EventData.MYI --check
Checking MyISAM file: EventData.MYI
Data records: 46215316 Deleted blocks: 0
myisamchk: warning: 3 clients are using or haven't closed the table properly
- 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 record links
myisamchk: error: Found wrong record at 7619297220
MyISAM-table 'EventData.MYI' is corrupted
Fix it using switch "-r" or "-o"

Then I try to use the -r option however it did not repair
myisamchk -r /var/lib/mysql/gts/EventData.MYI

I did try to run the same code around 2 - 3 times but it did not work. I lookup on the web and found this code below however it do not work and stop with the message Segmentation fault (core dumped) as you can see below.

[root@myserver]# myisamchk --silent --force --fast --update-state /var/lib/mysql/gts/*.MYI
myisamchk: MyISAM file /var/lib/mysql/gts/Driver.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: MyISAM file /var/lib/mysql/gts/EventData.MYI
myisamchk: warning: Table is marked as crashed and last repair failed
myisamchk: error: Found wrong record at 7619297220
Segmentation fault (core dumped)

Is there anyway that record could be deleted or replaced? I now I might loose data but data but in worst case scenario I think if the repair do not work I could give it a try.

Any help will be appreciated.

 

[Fallen Kell]

First thing first. Shutdown your mysqld if you have not already done so. Second, make a backup copy of your mysql data directory. This way, you can work on attempting to fix it and not worry about screwing it up more (since you can always copy back the original backup you just made to get back to the state you are currently in).

I would first try and determine which exact table has the issue (I am assuming you know all the tables in your database) by running:
myisamchk --check <table_name>

And do the above on each and every table you have in your database. Once you have determined which table has the corrupted data:
myisamchk --safe-recover --force <table_name>

If that fails, your index file may need to be recreated (again, make a backup copy of your mysql database data directory first):
1) Change directory into your database directory that contains the crashed table
2) Move the table's data file (<table_name>.MYD) to a safe place
3) Invoke mysql and recreate a new empty table by executing the following which uses the table description file (<table_name>.frm) to regenerate a new data and index files:
"mysql> TRUNCATE TABLE <table_name>;"
4) Exit mysql and move your original data file back into the database directory
5) Attempt "myisamchk --recover <table_name>" now to rebuild the index file on the data in your table

 

[labeebshahidi]

Bring up your database in recovery mode is one of the methods for mysql tables, another variant is accessible and must be used if you can't find any other

[Link deleted - looked like spam -- Programming Moderator Ken g6]

You should bring down your database. Shut it down in case it’s still running and spamming these messages in your log. As last resort, you may also kill the process. In order to bring back your database you will need to start it in recovery mode, with innodb_force_recovery. You should know this recovery mode makes your databases read only. Users connecting to it can not update, insert or other way alter existing data. To prevent your MySQL getting hammered the second it comes back, I suggest you to also change port of MySQL server from 3306 to something random. Add innodb_force_recovery=1 to your my.cnf In case your server doesn’t want to come back, you may further increase this number from1 to 6, check MySQL manual to see what the differences are.
Be sure to check your MySQL logs, and if it loops with something like:
InnoDB: Waiting for the background threads to start
You should also add innodb_purge_threads=0 to your my.cnf.
So all together to bring back database, I had to add these 3 parameters in my.cnf:
port = 8881
innodb_force_recovery=3
innodb_purge_threads=0

 

[aidenburke]

Guys, the same issue was already solved there http://www.sqlservercentral.com/Forums/Topic1618894-2893-1.aspx