Issue
When deleting a virtual server, the following message appears:
Mysql2::Error: Lock wait timeout exceeded; try restarting transaction xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
CODE
Environment
OnApp 3.x.x, 4.x, 5.x, 6.x
Resolution
There are a couple of different options to solve this issue. It is recommended to increase the lock wait timeout in the MySQL configuration, restart the MySQL server, and then try again to delete the virtual server. In case of any other errors that occur, contact OnApp Support.
To increase the lock wait timeout:
- SSH into the Control Panel server as a root user.
- Edit the
/etc/my.cnf file:
• Under the [mysqld] section, add or edit innodb_lock_wait_timeout=#### by replacing #### with the desired amount of seconds. The default amount is 50. It is recommended to increase it to 120 or higher, depending on the volume of disk statistics the virtual servers have. - Check if there are no transactions are running/pending and stop the OnApp daemon with
/etc/init.d/onapp stop. - Restart the MySQL server with
/etc/init.d/mysqld restart. - Optionally, verify the MySQL settings by opening the MySQL CLI and running
SELECT @@innodb_lock_wait_timeout;. - Start the OnApp daemon again with
/etc/init.d/onapp start.
Once it is done, the delete query should finish properly and also prevent the issue from happening in the future.
Cause
This error occurs when the database either is too busy or has too many records in the table it is attempting to delete from. It often occurs on virtual servers that have been in the cloud for a long time and if there are other heavy database queries running on the server at the same time.
