Hello,

I work with the mysql database for a couple of months now could not open it, because there was an error: “Incorrect key file for table: '...'. Try to repair it”

Who can tell how open the database?

Thanks to all.

If you have ssh access you can try running:

mysqlcheck --repair --databases db_name

 

What engine is your DB using, InnoDB or MySAM ?

The storage engine (MyISAM) DOES support repair table. You should be able to repair it.

If the repair fails then it's a sign that the table is very corrupted, you have no choice but to restore it from backups.

If you have other systems (e.g. non-production with same software versions and schema) with an identical table then you might be able to fix it with some hackery (copying the frm an MYI files, followed by a repair).

In essence, the trick is to avoid getting broken tables in the first place. This means always shutting your db down cleanly, never having it crash and never having hardware or power problems. In practice this isn't very likely, so if durability matters you may want to consider a more crash-safe storage engine.

To fix this do the following please or in case it isn’t enough for your mysql database restoration, then I would advise you to learn helpful threads dedicated to solving MySQL issues

 

http://community.office365.com/en-us/f/172/p/266451/815406.aspx

https://social.msdn.microsoft.com/Forums/en-US/fb94219c-fbf8-455d-b912-5544be11f186/how-to-fix-my-sql-database?forum=sqldataaccess

https://www.repairtoolbox.com/mysqlrepair.html Repair Toolbox for MySQL – in case you can’t get back mysql database, you may apply this one solution

 

Force InnoDB Recovery

1. Stop mysqld and back up all files located in the /var/lib/mysql/ directory:
2. # /etc/init.d/mysqld stop
3. # mkdir /root/mysql_backup
4. # cp -r /var/lib/mysql/* /root/mysql_backup/
5. Add the option innodb_force_recovery to the [mysqld] section in /etc/my.cnf. This option will allow you to start mysqld and create a database dump.
6. #/etc/my.cnf
7. [mysqld]
8. innodb_force_recovery = 4

NOTE: You can increase the option to five or six until you receive a proper dump.

1. Start the mysqld service:
2. # /etc/init.d/mysqld start
3. Dump all databases:
4. # mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` -A | sed '1i\SET FOREIGN_KEY_CHECKS = 0;' > /root/dumpall.sql

If the dump fails with an error that reads:

Incorrect information in file: './psa/APSApplicationItems.frm' when using LOCK TABLES"`

... then you need to increase innodb_force_recovery and try to dump the databases again. If you are not able to dump the databases, try using method II ("Copy table content") or III ("Restore from the backup") below.

1. Remove all files from /var/lib/mysql/ except the mysql folder:
2. # rm -rf `ls -d /var/lib/mysql/* | grep -v "/var/lib/mysql/mysql"`
3. Remove the innodb_force_recovery option from the /etc/my.cnf file and restart mysqld:
4. # /etc/init.d/mysqld restart
5. Check the /var/log/mysqld.log for any errors.
6. Restore the databases from the dump:

# mysql -uadmin -p`cat /etc/psa/.psa.shadow` < /root/dumpall.sql