Run myisamchk to resolve crashed MariaDB tables

2/12/2019 7:38 PM

2/12/2019 7:38 PM

This article provides information on myisamchk and MariaDB tables.

 

What does a myisamchck do and why is myisam important?

MyISAM is the default table structure for MySQL databases and as opposed to MS-SQL for example which can do individual row locks, MyISAM does entire table locks and doesn't allow another modification until the first Update is completed and the transaction is completed

 

MyISAMDBCHK will check, compress and repair MariaDB tables.

Note: Storage Manager versions 5.6 and later use MariaDB. For earlier versions, MYSQL is used.

For versions prior to v5.6, substitute MySQL for MariaDB.

 

When to run a myisamchk?

If the following error appears in the web console or logs:

java.sql.SQLException: Table " is marked as crashed and last (automatic?) repair failed.

A myisamchk can be run to fix the crashed tables.

Note: You can find your MariaDB log at the Storage Manager Server \mariadb\data\ location. The file is named machine name.err.

Storage Manager

A common cause of crashed tables in MariaDB can be an antivirus/intrusion detection or a backup software or for that matter any software that might try to read/write from MySQL/Mariadb at the same time that MySQL/Mariadb is trying to edit/modify/delete/ add data. MyISAM is very particular about being the only software that should be reading/writing.

 

If the tables are locked at the same time when Storage Manager is trying to write to them, this can cause corruption of the tables.

To prevent this from happening, you need to add exceptions to these tools so that they cannot access the  \mariadb folder and subfolders at the same time Storage Manager is trying to read/write/modify.

 

For more information, you may refer to https://mariadb.com/kb/en/mariadb/myisamchk/.  (© 2016 Mariadb, available at https://mariadb.com, obtained on July 14, 2017.)

Run myisamchk to fix crashed tables on Windows

1. Open the services menu and click the Stop button to stop the MariaDB service
2. Open a command prompt and navigate to \mariadb\data\storage.
3. Run the following command:

   for %i in (*.MYI) do ..\..\bin\myisamchk ==defaults-file=..\.\my.cnf -c -r -a -f -v %i

    

   Option	Description
   -c	Check tables for errors
   -r	Recover, this option can fix most issues except unique keys that are not unique
   -a	Analyze distribution of keys
   -f	Force override of old temporary files
   -v	Verbose, this option prints information to the screen

4. Let the myisamchk complete.
5. Start the MariaDB and SolarWinds Storage Manager services.

 

Run myisamchk to fix crashed tables on Linux

1. Stop the MariaDB service.
2. Run the following command: 

   /usr/bin/storage_manager_server_stop

3. Go to /mariadb/bin.
4. Run the following command: 

   ./myisamchk --defaults-file=../my.cnf -c -r -a -f -v ../data/storage/*.MYI

5. Let myisamchk complete.
6. Start the SolarWinds Storage Manager /usr/bin/storage_manager_server start.

 

Repair individual tables

Sometimes, myisamchk fails to fix all the corrupted tables. When this happens, users can perform the following procedure to repair individual tables instead of using *.MYI as a wild card to go after all index files

Note: It is strongly recommended to create a backup of the crashed table(s) before starting with the below steps. Under some circumstances, the following steps may cause data loss to the corrupted tables. Possible causes include but are not limited to file system errors.

1. Make sure that the database services are running.
2. Open a command prompt and navigate to the \mariadb\bin directory.
3. Run the following command: 

   mysql -u -p storage

   
   The MariaDB username and password can be found in the \Storage_Manager_Server/webapps/ROOT/bin/server.properties file.
4. Execute the command to repair all table indexes.

For more information regarding table repairs, refer to the following link:

http://dev.mysql.com/doc/refman/5.0/...air-table.html (© 2016 Mariadb, available at https://mariadb.com, obtained on July 14, 2017.)


Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.  You elect to use third party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.