How to Diagnose and Repair MySQL Error? : MySQL Database Repair Blogs

Published by Jacob Martin on July 31, 2014

The database files are prone to corruption in nature and even a single instance of improper handling can lead to corruption. MySQL is a bit exception. The database files that are created and maintained by the MySQL are prone to errors. The file that gets corrupted generally leads to error messages when you try to extract or access the data stored in them. However there are many solutions for these types of available but before using them you firstly need to determine the actual meaning of that error first.

By knowing and understanding the error message this can help you to resolve them easily. however it is very difficult to understand the meaning of each of the error as there is a big of these errors and which is very easier to diagnose than other are. Here are some of the errors mentioned below:

 “
120326 16:56:45 [ERROR] /usr/sbin/mysqld: Incorrect key file for table ‘/tmp/#sql_21b2_0.MYI’; try to repair it
120326 16:56:45 [ERROR] Got an error from unknown thread, storage/myisam/mi_write.c:223
120326 16:56:45 [ERROR] /usr/sbin/mysqld: Sort aborted: Error writing file ‘/tmp/MYK74Kpi’ (Errcode: 28)”

This part of error is of Error code 28. However all the MySQL database does not exhibits this errors but it is very hard to understand. This is because there is a human understandable explanatory option provided by the MySQL that is available in “perror” utility/ If you find the explanation for the error 28 in “perror” utility then you will find:

   “$ perror 28
OS error code 28: No space left on device”

The explanation of this error or the human understandable form of this error message indicates that there is no space in your device. Generic explanation of this error is:

MySQL creates temporary table with the use of MyISAM storage engine. However the MyISAM exhibits error message when the disk gets filled up. This happens because the disk is unable to handle the disk full error elegantly.

Sometimes you can see that the peoples may get into big trouble with the appearance of error codes as they do not know their meaning. Some of the examples to understand this error then you need know about error 13 which is meant that permission has got denied and the error code 2 gets induced when there is no such files or directory available in the database. The most common reaction that you may say after getting the error 13 is “I cannot get it as I have the permission’ and after getting error code 2 is “I can see the file/directories by myself so how they do not exist; of course, they do”.  In these cases you need to understand the problem more deeply. If these error codes say that the permission is denied then the permission gets denied as the code cannot be wrong.

Therefore whenever you come across these error messages while using the MySQL database then firstly you need to diagnose the problem then take necessary steps to rectify them.

In this situation when you come to know that the error 28 gets induced when there is no disk space present in the hard disk. Then the Microsoft recommends you to check the file system where the MySQL is being operated. This file system is single as well as multiple dedicated file systems such as datadir, log files, and tmpdir.

So now follow the steps mentioned below to resolve this issue:

Step 1: Firstly Stop using the MySQL Server:

# /etc/init.d/mysql stop You can use the following command:

# /etc/init.d/mysqld stop

Step 2: Then Check the file system and /tmp Directories:

$ df –h $ cd /tmp

$ df –h /tmp

Step 3: Then Remove the Files from /tmp for Freeing the Space:

# cd /tmp
# rm –rf *

Step 4: Then you need to Go to /var/log directory and remove or compress the log files

Step 5: Then you need to Check and Repair Your ISAM table by using the myisamchk command: Run the command mentioned below to perform this task:

# cd /var/lib/mysql
# myisamchk

Step 6: Then you need to Increase the disk space by adding new hard disk or removing all unwanted applications and software

Step 7: Then you need to Start MySQl Server:

# /etc/init.d/mysql start Or

# /etc/init.d/mysql start

With the help of these simple steps, you can easily resolve above error message.

You can even do this with the help of third part MySQL Repair Tool which is powerful data recovery software for corrupted MySQl Server database files, table structure and data stored in it. This software easily retrieves the information that is present in Transact-SQL script which is used for quick database recreation.