MySQL jobs fail for Spectrum Report Manager, with error: "The total number of locks exceeds the lock table size"

Document ID : KB000007856
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

We received the following error while running a mySQL optimization command on the reporting database.

 

mysql> optimize table event; 

+-----------------+----------+----------+-------------------------------------------------------------------+ 

| Table | Op | Msg_type | Msg_text| 

+-----------------+----------+----------+-------------------------------------------------------------------+ 

| reporting.event | optimize | note | Table does not support optimize, doing recreate + analyze instead | 

| reporting.event | optimize | error | The total number of locks exceeds the lock table size | 

| reporting.event | optimize | status | Operation failed | 

+-----------------+----------+----------+-------------------------------------------------------------------+ 

Environment:
This error can happen on multiple versions of Spectrum.
Cause:

This is generally caused by an extremely active database.  

Resolution:

Shutdown tomcat. 

Shutdown mysql.

Locate the file my.cnf from the server and open in a text editor. 

Find the line:

          innodb_buffer_pool_size

 

On CA Spactrum 10.x, this is set to 256M.  Increase this by 256M and test if the problem has been fixed by changing above line to:

         innodb_buffer_pool_size = 512M 

 

Modify the $SPECROOT/mysql/my-spectrum.cnf file.

If the innodb_lock_wait_timeout parameter is not present, add it to the end of the file and set it to 300 as follows: 

         innodb_lock_wait_timeout = 300

 

Restart mysql. 

Restart tomcat.

Test the optimize command, as the results may differ, based on the size of your reporting database.  If it errors again, increase the innodb_buffer_pool_size by another 256M and retry.  

It's better to increase gradually, until it resolves the problem, while also ensuring that there are no side effects, in other parts of the system.

Additional Information:

How to find the mySQL configuration file "my.cnf".

https://support.ca.com/us/knowledge-base-articles.TEC1199167.html

 

How to change the mySql configuration file safely.

https://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html

https://support.ca.com/us/knowledge-base-articles.TEC1629334.html