The Problem

Occasionally on a very busy server, you may not be able to log in to Open-AudIT and will see an error as below.

A Database Error Occurred


Error Number: 1206


The total number of locks exceeds the lock table size


/* logon::check_defaults */ DELETE FROM `logs` WHERE (`severity` = 0 AND `timestamp` < (NOW() - INTERVAL 180 DAY)) OR (`severity` = 1 AND `timestamp` < (NOW() - INTERVAL 180 DAY)) OR (`severity` = 2 AND `timestamp` < (NOW() - INTERVAL 180 DAY)) OR (`severity` = 3 AND `timestamp` < (NOW() - INTERVAL 180 DAY)) OR (`severity` = 4 AND `timestamp` < (NOW() - INTERVAL 180 DAY)) OR (`severity` = 5 AND `timestamp` < (NOW() - INTERVAL 90 DAY)) OR (`severity` = 6 AND `timestamp` < (NOW() - INTERVAL 30 DAY)) OR (`severity` = 7 AND `timestamp` < (NOW() - INTERVAL 7 DAY))


Filename: controllers/logon.php


Line Number: 351

Why?

Open-AudIT logs to a table in the database named (surprisingly) "logs". The issue is that we're running a query upon the logs table that affects too many rows at once.

The specific MySQL error code (1206) is listed here. MySQL has a configurable option for this called innodb_buffer_pool_size. This can be set in my.cnf (details below).

The Resolution

To resolve the issue you can:

• manually delete log entries while limiting the query and/or 
• adjust the option and restart MySQL
• set the Open-AudIT config options for log purging

To manually delete the affected rows, run the below SQL. 

Manually Delete Log Entries

NOTE - this command may need to be run several times.

NOTE #2 - If the command fails, try decreasing the LIMIT of 1000 to something smaller.

Windows

c:\xampplite\mysql\bin\mysql.exe -u openaudit -popenauditpassword -e "USE openaudit; DELETE FROM `logs` WHERE `severity` = 7 AND `timestamp` < (NOW() - INTERVAL 7 DAY) LIMIT 1000;"

Linux

mysql -u openaudit -popenauditpassword -e "USE openaudit; DELETE FROM `logs` WHERE `severity` = 7 AND `timestamp` < (NOW() - INTERVAL 7 DAY) LIMIT 1000;"

Adjust the configurable option in MySQL

Windows

Open the file c:\xampplite\mysql\bin\my.cnf in a text editor.

Find the value for innodb_buffer_pool_size and increase as required.

Restart the MySQL service.

Linux

There may be no actual option set. In that case, it will default to 16M. Add an entry (if required) or adjust the existing entry.

Restart the MySQL service.

Set the Open-AudIT config to purge log entries

As an Open-AudIT user who has the Admin role, go to menu -> Admin -> Configuration -> List Configuration.

You will see entries for log_retain_level_[0-7]. I would first adjust the "debug" level logs (ie - 7) to be purged more aggressively. Say 4 days. This is entirely up to you as the user. If you have increased your innodb_buffer_pool_size, this may not be required at all. You could even increase this retention period - assuming your innodb_buffer_pool_size (and server) can cope.