Need help with MYSQL table crash

[xaverkahl]

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.

[Luuk]

Does an export work?

mysqldump <datbasename> EventData >EventData.sql

If this works, you should always be able to restore your data...

Did you try to restart the database?
This should take care of this message " warning: 3 clients are using or haven't closed the table properly"

[akilathans]

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
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

[sultantrum]

Repairing tables with mysqlcheck or another variant to learn helpful topics about mysql databases

https://social.msdn.microsoft.com/Fo...=sqldataaccess

Repairing tables

In most cases, only the index will be corrupted (the index is a separate, smaller, file with records that point to the main data file) - actual data corruption is extremely rare. Fixing most forms of corruption is relatively easy. As with checking, there are three ways to repair tables. These all only work with MyISAM tables - to repair corruption of the other table types, you will need to restore from backup:

• The REPAIR TABLE SQL statement (obviously the server must be running for this)
• The mysqlcheck command-line utility (the server can be running)
• The myisamchk command-line utility (the server must be down, or the tables inactive)
Repairing a table requires twice as much disk space as the original table (a copy of the data is made), so make sure you are not going to run out of disk space before you start.