Posted in:

Troubleshooting MyISAMchk Errors: A Comprehensive Guide

MyISAM was the first storage engine used in MySQL (versions before 5.5). Though InnoDB is now the default storage engine, MyISAM storage engine is still widely used due to its simplicity and performance.

MyISAM tables are susceptible to corruption due to factors, like hardware failure, power outages, etc. To manage MyISAM tables, there is a utility in MySQL, which is called myisamchk. It is a command-line utility that allows users to perform maintenance, analysis, and repair tasks. 

Moreover, myisamchk utility plays a fundamental role in maintaining the well-being of MyISAM tables. It can perform tasks such as detecting data corruption, optimizing table structures, and recovering lost data. Though myisamchk is a powerful utility, users often encounter errors while working with it. 

In this comprehensive guide, we look into some common myisamchk errors that users usually encounter and provides the solutions to troubleshooting these errors.

Common MyISAMchk Errors and their Solutions

Below, we have listed some common errors that you may encounter while using the myisamchk utility. We have also provided possible solutions to resolve these errors.  

Message: Can’t create a new thread (errno %d); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

The error message indicates that the system doesn’t have sufficient resources (both memory and file descriptors), making it impossible to create a new thread for the myisamchk process. 

To fix the issue, consider the following actions:

  • Increase system resources, such as adding more memory or expanding file limits.
  • Divide the workload into smaller and manageable segments. Running multiple instances of myisamchk in parallel can help distribute the load.
  • Error: MyISAM table is broken

This error message indicates that MyISAM table is corrupt or damaged and requires repair. 

You can try to fix it by using the following command:

myisamchk –recover table_name.MYI.

This command initiates the repair process for the specified table.

If the issue persists, you can try the –safe-recover option. This option attempts a more cautious recovery procedure.

  • Error: Keypointers and record positions doesn’t match

This error comes up when the keypointers and record positions within a MyISAM index fail to align. 

To address this issue, utilize the following command:

myisamchk –safe-recover table_name.MYI

The –safe-recover option uses a more conservative recovery mode aimed at rectifying the mismatched index components and reconciling disparities in index components.

  • Error: Record at pos: xxx is not remove-marked

This error indicates inconsistencies within the records. 

To fix this error, execute the following repair command:

myisamchk –recover table_name.MYI

If the problem persists, consider using the –force option with myisamchk. This option removes inconsistent records, thus resolving the issue.

What if the MySQL Database is Corrupted?

In case the database is corrupted and the myisamchk command fails, you can take the help of a third-party MySQL database recovery tool. Among the available tools in the market, one of the powerful and easy-to-use tools is Stellar Repair for MySQL. This tool can repair corrupt MySQL databases and restore all the database objects. After repair, it allows to save the repaired data in various formats, including MySQL, SQL Script, MariaDB, HTML, CSV, and XLS.

Key features of Stellar Repair for MySQL are:

    • Repairs corrupted MySQL database
    • Repairs MySQL database tables of both the storage engines – InnoDB and MyISAM
    • Repairs MySQL and MariaDB databases created on Windows and Linux.
  • Fixes all types of MySQL corruption errors

Now, let’s see how this tool works.

  • Download the tool from the official Stellar’s website. Choose whether you want to download the version for Windows or Linux. Then, press the Free Download button.

Free Download software interface for Linux and Windows

  • Double-click the downloaded executable file to install the application. Follow the simple instructions to complete the installation process.
  • When Stellar Repair for MySQL starts, the “Select Data Folder” dialog box appears.

  • Enter the path to the folder that contains the database file you want to repair. Press the OK button.

Note: You must stop the MySQL Service before proceeding with the recovery process.

  • A dialog box shows the list of available databases. Choose the one you want to repair. Then, press the Repair button.

  • The tool will start scanning the database file. After the scan, it will show all the recoverable data.

  • When the database is repaired, press the Save button.

  • Choose the format (e.g. MySQL) to save the data. 
  • Enter host name, username, and password. Then, press the Save button.

  • At the end of the process, the Save Complete dialog box appears. Click OK.

Conclusion

Above, we have discussed some common MySQL database errors, specifically errors related to the myisamchk command, along with their solutions. If the MySQL database is corrupted and myisamchk command doesn’t work, you can use a dedicated MySQL database recovery tool, like Stellar Repair for MySQL. It can easily repair corrupt MySQL database file and help fix corruption related errors.