Repair MySQL Database Error

Submitted by editor on Mon, 10/24/2016 - 23:37

Repair all tables
mysqlcheck -u root -p --auto-repair --check --all-databases

Repair a database
mysqlcheck -u root -p  --repair --databases databasesname

Mysql table repair failed: “Can't create new tempfile”
myisamchk -r -f databasename/the_table.MYI


MYSQL: Out of resources when opening file... (Errcode: 24)

The mysql error:  Out of resources when opening file... (Errcode: 24)   indicates that the number of files that msyql is permitted to open has been exceeded.
To set this variable to a higher number, edit the /etc/my.cnf file and add the lines:

open_files_limit = 5000

Then be sure to restart mysql with:   sudo /etc/init.d/mysql restart


Database create error (Error Code : #1146)

#1146 - Table 'database_name.table_name' doesn't exist

probably due to a corrupted file

Solution 1

(You can delete the corrupted file like ..../database_name/table_name.frm)

1.Export the database.
mysqldump -u root -p database_name > db_temp_file.sql
2. Delete the database : database_name
3. Recreate a DB wit same name (database_name)
3. Import backup file
mysql -u root -p database_name < db_temp_file.sql

Solution 2 (You can also try this solution)
mysql -u mysql_user -p
mysql> use database_name;
mysql> show tables; # If the table that's been giving you grief shows here, then you can try to run a SELECT query on it to see if any data is there, but if you get an error saying the table doesn't exist, then...
mysql> drop table table_name;
mysql> quit

Add new comment