Error while log archival and clean-up for table

Error:

For the past few days, we've been getting the following error.

Error while log archival and clean-up for table "AU_TRANSACTIONLOG"
Lock wait timeout exceeded; try restarting transaction
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
com.mysql.jdbc.Statement.executeUpdate(Statement.java:1402)
com.mysql.jdbc.Statement.executeUpdate(Statement.java:1317)
db.DatabaseUtil.executeDeleteQuery(Unknown Source)
logging.LogArchiveAndCleanUpAction.performCleanUpOfTransactionalTable(Unknown Source)
logging.LogArchiveAndCleanUpAction.clearLogFromTable(Unknown Source)
logging.LogArchiveAndCleanUpAction.performArchivingAndCleanUp(Unknown Source)
logging.LogArchiveAndCleanUpAction.main(Unknown Source)

This is on the cluster environment with one shared database.

 

Cause:

This error usually comes when the table is locked out in case of large data present in it or due to the large number of uncommitted transactions etc. If you'll try to delete a bunch of records using MYSQL administrator or some other DB tool then you'll receive the same error.

Solution:

Increasing value of innodb_lock_wait_timeout should resolve your issue. We would recommend to consult your DBA for an expert advice to remove the lock on the table "AU_TRANSACTIONLOG". Also Refer the below link for more information:.

http://stackoverflow.com/questions/12726665/java-sql-sqlexception-lock-wait-timeout-exceeded-try-restarting-transaction-ex

Have more questions? Submit a request

2 Comments

  • 0
    Avatar
    David Paras

    From these logs I conclude both nodes are trying to clean things up, but you mentioned they should not in cluster. That made me checking the server-configure.properties file and found the cluster is not enabled yet...

  • 0
    Avatar
    David Paras

    As found by you clustering is not enabled due to which separate log archival cleanup is performed on both the nodes at the same time. Since both the nodes are pointed to the same database this might be a reason behind this server lockout. Please enable the clustering or disable the second node and then verify the results.

Article is closed for comments.