General MySQL Troubleshooting Repairing Database Corruption and Missing Files
Article ID: 48806
Updated On:
Products
Issue/Introduction
Description:
The first section of this article explains how to repair corrupted tables. The following two sections explain how to resolve errors dealing with missing.MYI and .MYD files (these error messages occur when you attempt to repair a table).
Solution:
Repairing the entire database
First, you should check all tables and try a first-level repair of ones that are corrupt. To do this:
- Stop all NetQoS Services.
- Restart only the NetQoS MySQL51 service.
- Open a command prompt. (Start -> Run -> cmd )
- Run the command
mysqlcheck --auto-repair -A
- If any tables are shown to be corrupted, re-run the above command and see if the table(s) still shows as corrupt.
NOTE:
DO NOT run this on NetVoyant. ( This command usually causes corruption of the .MRG files in NetVoyant.
You will need to run a repair or rebuild (see below) of the .MRG files.
Repairing individual tables
If the table(s) still shows corrupt you may need to do a more thorough second-level repair. To do this:
- Open the affected database:
mysql -D <databasename>
(You may need to specify the port on some products using mysql 5.1, so add -P3308 after mysql)
- Repair the table:
repair table <tablename>;
Double-check that there are no further problems by running the repair twice.
If repair table fails or if .MYI is missing, e.g you receive a message such as Can't find file: '<tablename>.MYI' (errno: 2), try the following:
- Open the affected database:
mysql -D <databasename>
- Repair the table:
repair table <tablename> use_frm;
Using myisamchk
If a table fails to repair using the repair table command, you can try using myisamchk
NOTE: Stop MySQL and the NetQoS services before running myisamchk
Default paths to myisamchk:
4.1: D:\NetQoS\MySQL\bin\
5.1: D:\NetQoS\MySQL51\bin\
To run myisamchk:
- Stop MySQL
- Navigate to the database directory via the command line
- Run the command:
<Path to MyISAMchk>\myisamchk.exe -<options> <table name>
Examples:
D:\NetQoS\MySQL51\bin\myisamchk.exe -r agent_definitions
D:\NetQoS\MySQL51\bin\myisamchk.exe -f properties
D:\NetQoS\MySQL\bin\myisamchk.exe -o servers
- Restart MySQL and run a check table to see if the table is still corrupt.
Check options:
-r Recover. Can fix almost anything except unique keys that aren't unique. Try this first.
-f Force. Overwrite old temporary files. Try this second
-o Uses old recovery method; Slower than '-r' but can handle a couple of cases where '-r' reports that it can't fix the data file. Try this last
Repairing partition tables (NV only)
NetVoyant uses partition/merge tables, which are only used for the tables holding data for datasets. You can also identify them by the presence of .MRG files for those table names in the database folder.
NV 6.1 and prior: D:\NetVoyant\db\nms2
NV 7.x: D:\netqos\mysql51\data\nms2
Each dataset has the following merge tables:
_rate
_ltd
_gtd
_eqd
So for the reach (Reachability) dataset, you will have one of each of those tables starting with reach (e.g, reach_rate, reach_ltd, etc.).
The reach_poll table is not a merge table, so the standard repair command should be used on that table, and any other non-merge table.
If the table is one of the NetVoyant merge tables, you need to use the dbmaintain utility (present in any NV installation) to repair the merge table. Issue the commands for dbmaintain from a command prompt outside of mysql (mysql needs to be running).
(DO NOT USE ON NON MERGE TABLES IT WILL DELETE THEM):
- From the command line (NOT inside MySQL)
dbmaintain -repair <merge_table_name>
Example: for the Interface Statistics LTD merge table you would do:
dbmaintain -repair ifstats_ltd
You can also have them use dbrepair (included in the DBTools package) on the entire nms2 database. dbmaintain doesn't repair flat tables, while dbrepair fixes both flat and merge tables.
Rerunning the dbmaintain nightly repartition manually (NV only)
Sometimes if repartition fails due to mysql errors repeatedly, the merge tables can get large, which in turn can lead to service issues which can lead to mysql corruption. This doesn't always occur, but it is a good idea to repartition any large files to prevent issues.
dbmaintain -v -repartition all
This will run through every table and properly build the daily tables, and also get rid of any tables that have exceeded the retention rate.
Note: This operation is also performed nightly at 12:00 am.
Regenerating merge tables (NV only)
If you aren't able to repair a data table in NV, then there is a utility present on all NV systems that can be used to regenerate the table using the original 'create table' information.
This will create the table as new, with no data in it, but it will fix the mysql errors. This is only valid for the data tables themselves, which are merge tables of the form <dataset>_<rollup> (ex: ifstats_rate).
You can enter 'tblgen' in command to see the different syntax options that are available, but as a general rule, you will only need these commands:
tblgen -poll <dataset_name> - That will recreate the dataset_poll table for the dataset.
tblgen -rate <dataset_name> - That will recreate the dataset_rate table for the dataset.
tblgen -ltd <dataset_name> - That will recreate the dataset_ltd table for the dataset.
tblgen -gtd <dataset_name> - That will recreate the dataset_gtd table for the dataset.
tblgen -eqd <dataset_name> - That will recreate the dataset_eqd table for the dataset.
You can also generate all the tables for the dataset by just using this command: tblgen <dataset_name>
Once the table has been regenerated, restart mysql and the NetVoyant Service Manager service.
Environment
Component:
Feedback
