MySQL Backup Restore Fails with 'The total number of locks exceeds the lock table size' Exception
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Symptoms
Restoring a MySQL backup, fails with the following exception reported in the fisheye-debug-<date>.log:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
INFO - Closing com.cenqua.fisheye.ctl.Restore$RestoreApplicationContext@3b0090a4<com.cenqua.fisheye.ctl.Restore$RestoreApplicationContext@3b0090a4[X]<com.cenqua.fisheye.ctl.Restore$RestoreApplicationContext@3b0090a4>>: startup date [Thu Jul 02 22:16:47 PDT 2015]; root of context hierarchy
INFO - *** application context closed ***
java.io.IOException: Error talking to database: Problem with constraints script /data/fecru-3.7.0/sql/MYSQL/schema/constraints_89.sql
at com.atlassian.crucible.migration.item.SQLBackup.restore(SQLBackup.java:208)
at com.atlassian.crucible.migration.item.SQLBackup$1.restore(SQLBackup.java:173)
at com.cenqua.fisheye.ctl.Restore.run(Restore.java:197)
at com.cenqua.fisheye.ctl.Restore.main(Restore.java:273)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at com.cenqua.fisheye.FishEyeCtl.mainImpl(FishEyeCtl.java:99)
at com.cenqua.fisheye.FishEyeCtl.main(FishEyeCtl.java:42)
Caused by: com.cenqua.crucible.hibernate.CruDBException: Problem with constraints script /data/fecru-3.7.0/sql/MYSQL/schema/constraints_89.sql
at com.cenqua.crucible.hibernate.DefaultDBControl.addConstraints(DefaultDBControl.java:411)
at com.atlassian.crucible.migration.item.DBImporter.importData(DBImporter.java:145)
at com.atlassian.crucible.migration.item.SQLBackup.restore(SQLBackup.java:192)
... 9 more
Caused by: com.cenqua.crucible.hibernate.CruDBException: SQL script error on line 49: "alter table cru_fr_detail add index FK4F2200B487D2F44 (cru_revision_id), add constraint FK4F2200B487D2F44 foreign key (cru_revision_id) references cru_revision (cru_revision_id);"
(The total number of locks exceeds the lock table size), please contact http://www.atlassian.com/support/
at com.cenqua.crucible.hibernate.DefaultDBControl.executeScript(DefaultDBControl.java:593)
at com.cenqua.crucible.hibernate.DefaultDBControl.executeScript(DefaultDBControl.java:524)
at com.cenqua.crucible.hibernate.DefaultDBControl.addConstraints(DefaultDBControl.java:405)
... 11 more
Caused by: java.sql.SQLException: The total number of locks exceeds the lock table size
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2531)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1618)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1549)
at com.cenqua.crucible.hibernate.DefaultDBControl.executeScript(DefaultDBControl.java:565)
... 13 more
Restore failed. This may leave your target FISHEYE_INST directory (/opt/crucible/inst) in an inconsistent state.
Cause
This exception can occur when the innodb_buffer_pool_size MySQL variable is too small for the instance. InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory. As per the MySQL documentation, this variable is:
Specifies the size of the buffer pool. If your buffer pool is small and you have sufficient memory, making the pool larger can improve performance by reducing the amount of disk I/O needed as queries access InnoDB tables.
In MySQL InnoDB, row level locks are implemented by having special lock table, located in the buffer pool, where a small record is allocated for each hash and for each row locked on that page bit can be set.
As per this MySQL Forums query:
The SQL statement sets locks on all the records it scans. If you have a small buffer pool, then the InnoDB lock table may indeed grow so big that it does not fit in the buffer pool.
Some more details about this can be found here:
http://bugs.mysql.com/bug.php?id=15667
http://www.mysqldbahelp.com/2010/02/what-error-total-number-of-locks.html
http://mrothouse.wordpress.com/2006/10/20/mysql-error-1206/
https://www.percona.com/blog/2006/07/13/how-much-memory-innodb-locks-really-take/
Resolution
Increase the value of the innodb_buffer_pool_size for the MySQL database and run the restore again.
Was this helpful?