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

book

Article ID: 7856

calendar_today

Updated On:

Products

CA Spectrum

Issue/Introduction

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 | 

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

Cause

This is generally caused by an extremely active database.  

Environment

This error can happen on multiple versions of Spectrum.

Resolution

Shutdown tomcat. 



Shutdown mysql.



Locate the file my-spectrum.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://comm.support.ca.com/kb/How-to-find-the-mySQL-configuration-file-mycnf/KB000015656

 

How to change the mySql configuration file safely.

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

https://comm.support.ca.com/kb/How-can-I-change-MySQLs-innodblogfilesize-parameter/KB000015663