The Problem

Occasionally on a an extremely busy server, you may not have the ability to log in to Open-AudIT and will watch an error together below.

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


A Database Error OccurredError Number: 1206The total variety of locks over the lock table size/* logon::check_defaults */ DELETE indigenous `logs` where (`severity` = 0 and `timestamp`
Why?Open-AudIT logs come a table in the database called (surprisingly) "logs". The worry is that we"re running a query top top the logs table that affects too many rows in ~ once.

The certain MySQL error password (1206) is noted here. MySQL has a configurable alternative for this called innodb_buffer_pool_size. This have the right to be collection in my.cnf (details below).

The Resolution

To solve the worry you can:

manually delete log in entries when limiting the questions and/or adjust the option and restart MySQLset the Open-AudIT config alternatives for log in purging

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

Manually Delete log in Entries

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

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

Windows


c:\xampplite\mysql\bin\mysql.exe -u openaudit -popenauditpassword -e "USE openaudit; DELETE native `logs` whereby `severity` = 7 and `timestamp`
Linux


mysql -u openaudit -popenauditpassword -e "USE openaudit; DELETE native `logs` where `severity` = 7 and `timestamp`
 

Adjust the configurable alternative in MySQL

Windows

Open the document c:\xampplite\mysql\bin\my.cnf in a message editor.

Find the value for innodb_buffer_pool_size and also increase together required.

Restart the MySQL service.

Linux

There may be no actual alternative set. In the case, it will default come 16M. Add an entry (if required) or change the existing entry.

Restart the MySQL service.

 

Set the Open-AudIT config come purge log in entries

As one Open-AudIT user who has actually the Admin role, go to menu -> Admin -> configuration -> perform Configuration.

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

You will watch entries for log_retain_level_<0-7>. I would an initial adjust the "debug" level logs (ie - 7) to be purged more aggressively. Speak 4 days. This is entirely up come you together the user. If you have actually increased your innodb_buffer_pool_size, this might not be compelled at all. You can even boost this retention duration - assuming your innodb_buffer_pool_size (and server) deserve to cope.

 

 


Overview
Content Tools

it is provided by a free Atlassian Confluence Open resource Project License granted to tacoemojishirt.com. Evaluate Confluence today.

printed by Atlassian Confluence 6.15.2
Atlassian
"serverDuration": 74, "requestCorrelationId": "7f367a58a2588f99"