Intrusion Prevention System Manager database tuning fails to complete

Technical Articles ID: KB77883
Last Modified: 2022-12-07 05:09:21 Etc/GMT

Environment

Trellix Intrusion Prevention System

IMPORTANT: Trellix IPS Manager has transitioned from MySQL to MariaDB.

The Database used in Manager 9.1 changes to MariaDB with release 9.1.7.77.
This article features different steps if using Manager 9.1.7.77 and later.

IMPORTANT:
Before you make changes to the database, ensure that you have taken an all table backup. For guidance on the backup, see the "Using the database admin tool" section of the NSP Manager Administration Guide for your release. When you have completed the backup, then perform the following solution.

Problem

Your weekly scheduled database tuning fails and you see one of the following errors:

Database Backup Failure
Scheduled backup failed

The following solutions explain how to free up disk space to allow database tuning to finish.

Cause

Intrusion Prevention System Manager database tuning fails because of disk space limitations.

Solution 1

Run database tuning using the dbadmin.bat command-line tool. This tool allows the tuning to complete more quickly because you have to stop the Manager. It also provides diagnostic information in the background command prompt windows, which can be helpful in identifying problems and checking the tuning progress. Running the command-line tool might be successful, even if the manager tuning fails.

Stop the Manager Service by clicking the Network Security icon in the notification area and selecting Stop Manager.

NOTE: After you finish troubleshooting, restart the Manager by clicking the Network Security icon in the notification area, and clicking Start Manager.
Start Windows Explorer and navigate to the \App\bin\ folder.
Double-click dbadmin.bat.
From the Java applet, click the Maintenance tab.
On the DB Tuning tab, click Tune Database.

The command-prompt window (behind the applet) shows status information as the tuning progresses. If the tuning process fails, note any error messages displayed in the command-prompt window.
The dbtuning process requires the amount of free space to be greater than the largest table size to complete successfully. If the available space isn't sufficient, you have to free up disk space or reduce the size of the largest table.

Solution 2

Reduce the amount of disk space in use (free up manager HDD space):

Open Windows Explorer and navigate to your database installation:
- For 9.1.7.75 and earlier:
  <$install_directory>\McAfee\Network Security Manager\MySQL\bin\
- For 9.1.7.77 and later, 10.1.9 and earlier:
  <$install_directory>\McAfee\Network Security Manager\MariaDB\bin
- For 10.1.10 and later:
  <$install_director>\Program Files\Trellix\IPS Manager\MariaDB\bin
Sort the files by size, verify the largest table size, and confirm how much space is available on that drive.
Verify the backup location and number of backups being saved:
- The default backup location is /App/Backups. But, it's common to move the backups to the drive that hosts the database because of space limitations on the App drive.
- There's an XML file in the Backups folder named backupsearchpath.xml that lists any locations that have been used for backups. Check these locations and determine if freeing up disk space might alleviate use on the database drive.
- If moving backups frees up HDD space, move the backups to alternative locations.
Check for log files or other programs installed on the database drive:

NOTE: We recommend that you move the files to an alternate location, rather than deleting without taking a backup.
- For 9.1.7.75 and earlier:
  Check and clear the MySQL process logs in the \MySQL\data folder.
- For 9.1.7.77 and later including 10.x:
  Check and clear the MariaDB process logs in the \MariaDB\data folder.
- For all Managers:
  Previous installations that have been upgraded might leave log files behind.
  Consider removing these older log files if they're large.
Check for and clean up the tmp_ tables in the \data\lf folder:
- If you've not recently upgraded, these tables might be from an older version.
- Check the date stamp on the files, determine their age, and delete old versions.
Drop old temp tables:
1. Log on to the Manager database by typing the following command and pressing Enter:
  - For 9.1.7.75 and earlier:
    Type <Installation path\mysql\bin mysql -uroot -p<DB Password> lf and press Enter.
  - For 9.1.7.77 and later including 10.x:
    Type <Installation path>\MariaDB\bin mysql -uroot -p<DB Password> lf and press Enter
    
    NOTE: For upgrades, the default location is the previous database installation directory.
2. Type the following command and press Enter:
  
  drop table <tablename>
3. Repeat as needed.
Run the tuning again.
If the tuning fails, and you still have too much data, manually delete some of the oldest data to allow the tuning to complete:
1. Stop the manager service.
2. From the command prompt, change to the Database <installation directory> /bin folder.
  - For 9.1.7.75 and earlier:
    Type cd <$install_directory>\McAfee\Network Security Manager\MySQL\bin\ and press Enter.
  - For 9.1.7.77 and later, 10.1.9 and earlier:
    Type cd <$install_directory>\McAfee\Network Security Manager\MariaDB\bin and press Enter.
  - For 10.1.10 and later:
    Type cd <$install_directory>\Program Files\Trellix\IPS Manager\MariaDB and press Enter.
3. Log on to the Database:
  1. Type mysql -uroot -p lf and press Enter.
  2. Type your root Database password.
4. Determine how many rows of data you have in your largest table (which is likely iv_alert or iv_packetlog).
  Type the following command and press Enter:
  
  select count(*) from <table>
  
  For example: select count(*) from iv_alert;
5. Confirm the layout of the table by typing the following command and pressing Enter:
  
  describe iv_alert;
6. Delete the oldest data. We recommend that you maintain 90 days worth of data. But, you might have to remove more depending on how much space is available on your drive.
  Type the following command and press Enter:
  
  delete from iv_alert where creationTime<'2013-01-25 00:00:00';
  
  (replace the date with a more suitable one, using the format YYYY-MM-DD HH:MM:SS)
7. Repeat for iv_packetlog. Type the following command and press Enter:
  
  delete from iv_packetlog where creationTime<'2013-01-25 00:00:00';
  
  (replace the date with a more suitable one, using the format YYYY-MM-DD HH:MM:SS)

Affected Products

Languages:

This article is available in the following languages:

Knowledge Center

Intrusion Prevention System Manager database tuning fails to complete

Environment

Problem

Cause

Solution 1

Solution 2

Affected Products

Languages:

Title

Title

Knowledge Center

Intrusion Prevention System Manager database tuning fails to complete

Environment

Problem

Cause

Solution 1

Solution 2

Affected Products

Languages:

Choose your region

Title

Title