Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2015
    Posts
    1

    Answered: Need help with MYSQL table crash

    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.

  2. Best Answer
    Posted by 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 https://mysql.recoverytoolbox.com/ Recovery Toolbox for MySQL

    You should bring down your database. Shut it down in case its 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 doesnt 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"


  3. #2
    Join Date
    Jun 2015
    Posts
    1
    Provided Answers: 1
    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 https://mysql.recoverytoolbox.com/ Recovery Toolbox for MySQL

    You should bring down your database. Shut it down in case its 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 doesnt 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •