How to repair crashed MySql table?

Products

• CA Infrastructure Management
• CA Performance Management

Issue/Introduction

All Data Sources in Performance Management show Synchronization Failure.

All Data Sources show a successful connectivity result via the Test option on the Manage Data Sources page.

Errors referring to database tables being marked as crashed show up in the Device Manager service DMService.log file. This log is found in (default path) /opt/CA/PerformanceCenter/DM/logs directory.

An example error is:

Caused by: java.sql.SQLException: Table './netqosportal/ds_item_members' is marked as crashed and should be repaired

The error states that the dst_interface table in the netqosportal database is the problem table.

The error may be seen for other databases and tables. It may be seen for a single database and table. It may be seen for multiple database and table combinations.

Environment

All supported Performance Management releases

Cause

The most common cause is an unexpected reboot of the Performance Center host server.

Rebooting a server with a running database with open connections can cause this scenario.

When those open connections are in the middle of work, for example writing information to the database, and get closed unexpectedly they are unable to complete the work.

This results in broken tables.

Resolution

The following steps are best used when a single database table is broken.

If more than one table is broken, it may be easier to use the following command to fix any broken tables found at once. Enter the password when prompted.

./mysql -uroot -p --repair --all-databases

To repair a single broken table follow these steps.

1. Shut down all Performance Center services. Shut down the console services, then the Device Manager service, then the Event Manager service then the SSO service.
2. Go to the /opt/CA/MySql/bin directory (default install path shown)
3. Enter the MySql prompt with this command, entering the password when prompted.
   • ./mysql -uroot -p
4. In the MySql prompt run the following command to check the status of the problem table using the DB_Name and Table_Name the error specifies.
   • check table DB_NAME.Table_Name;
   • For example in a lab with a broken table we run and see:
     • mysql> check table netqosportal.dst_interface;
       +----------------------------+-------+----------+---------------------------------------------------------------------+
       | Table                      | Op    | Msg_type | Msg_text                                                            |
       +----------------------------+-------+----------+---------------------------------------------------------------------+
       | netqosportal.dst_interface | check | warning  | Table is marked as crashed                                          |
       | netqosportal.dst_interface | check | warning  | 1 client is using or hasn't closed the table properly               |
       | netqosportal.dst_interface | check | warning  | Found 1336576 deleted space in delete link chain. Should be 1337084 |
       | netqosportal.dst_interface | check | error    | Found 23082 deleted rows in delete link chain. Should be 23090      |
       | netqosportal.dst_interface | check | error    | record delete-link-chain corrupted                                  |
       | netqosportal.dst_interface | check | error    | Corrupt                                                             |
       +----------------------------+-------+----------+---------------------------------------------------------------------+
       6 rows in set (0.03 sec)
5. Now we repair the table with the command:
   • repair table DB_NAME.Table_Name;
   • For example in a lab with a broken table we run and see:
     • mysql> repair table netqosportal.dst_interface;
       +----------------------------+--------+----------+----------+
       | Table                      | Op     | Msg_type | Msg_text |
       +----------------------------+--------+----------+----------+
       | netqosportal.dst_interface | repair | status   | OK       |
       +----------------------------+--------+----------+----------+
       1 row in set (2 min 24.57 sec)
6. Lastly check the table again to ensure it's repaired and reports an OK state.
   • check table DB_NAME.Table_Name;
   • For example in a lab with a broken table we run and see:
     • mysql> check table netqosportal.dst_interface;
       +----------------------------+-------+----------+----------+
       | Table                      | Op    | Msg_type | Msg_text |
       +----------------------------+-------+----------+----------+
       | netqosportal.dst_interface | check | status   | OK       |
       +----------------------------+-------+----------+----------+
       1 row in set (9.09 sec)
7. Restart the Performance Center services. Start SSO service first, then the Event Manager service. Next start the Device Manager service, wait 30 seconds and start the console service.