MySQL problem: Crashed MySQL database and unable to browse in NFA
Go to Admin – System Status
Check if there is Last polled status was *** days ago.
After trying different ways to resolve this issue(as this issue can come up from time to time) we created the following procedure which is described in the following steps:
- Go to the NFA console (***.***.***.xx)
- Open cmd and connect to MySQL:
mysql -P 3308
Type: show processlist;
- All the processes that are running in the database are shown. We must check the column “command” and see which processes are running (a query is being executed) or sleeping. The oldest query (see the column “time”) shows the state “sending data …”. It means that there is a deadlock between some tables and the next queries cannot be executed. What we need to do is “kill” the oldest query. For this action, we check the oldest query ID. The next queries cannot be executed after this query is completed. You can see the state “waiting for table…” in all those queries.
- kill XXXX; (where the Xs represent the number ID of the query)
- The oldest process and the next ones are deleted. We can confirm it by typing again show process list;
- Once it is done we must restart the watchdog service as well as the Analyzer Query and Analyzer Report service.
- this is the process list from the NFA console
In the column state was this error message : delete from rt1 using interface_relations_staged,
We need check always the time and the command column if the process is on sleep mode
Once the above steps are done, we will see, after running again the command show processlist, that the query which was impacting in the system and the ones which were blocked are gone
- Check again NFA - Admin -system status
Check on the harvester if the records are up to date E:\CA\NFA\Netflow\datafiles\HarvesterArchive
In this directory
E:\CA\NFA\Netflow\datafiles\ReaperInput we can see the last file from (dedicated date ) that means that is according to the message in NFA (Harvester - Last Reeper Timestamps ) is xx behind/ according to the error message in NFA System status/. This folder should be empty. The data in this file are not “pushed” to the console.
We also checked if the flow is going on
When this folder will be empty, the error mgs from NFA should also disappear.