search cancel

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


Article ID: 7856


Updated On:


CA Spectrum


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 | 



This error can happen on multiple versions of Spectrum.


This is generally caused by an extremely active database.  


Shutdown tomcat. 

Shutdown mysql.

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

Find the line:



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".


How to change the mySql configuration file safely.