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.

You are watching: The total number of locks exceeds the lock table size


A Database Error OccurredError Number: 1206The total number of locks exceeds the lock table size/* logon::check_defaults */ DELETE FROM `logs` WHERE (`severity` = 0 AND `timestamp`
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 MySQLset 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`
Linux


mysql -u openaudit -popenauditpassword -e "USE openaudit; DELETE FROM `logs` WHERE `severity` = 7 AND `timestamp`
 

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.

See more: Minnesota Wild Schedule 2017-18, Minnesota Wild Schedule 2017

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.

 

 


Overview
Content Tools

Powered by a free Atlassian Confluence Open Source Project License granted to tacoemojishirt.com. Evaluate Confluence today.

Printed by Atlassian Confluence 6.15.2
Atlassian
{"serverDuration": 74, "requestCorrelationId": "7f367a58a2588f99"}