VM Won't Delete, MySQL Lock Wait Timeout

Issue

The virtual machine won't delete with the error:

Mysql2::Error: Lock wait timeout exceeded; try restarting transaction xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Environment

OnApp 3.x.x, 4.x, 5.x

Resolution

There are a couple of different options to solve this one. It is recommended that you increase the lock wait timeout in the MySQL configuration, restart the MySQL server and then attempt the VM delete again. It can also happen that you repeat the delete, and it takes a few more attempts and may also cause other errors to occur. If further errors do appear, please contact our Technical Support team.

To increase the lock wait timeout:

1. SSH into the control server as the root user
2. Edit the file /etc/my.cnf :
   Under the [mysqld] section, add or edit innodb_lock_wait_timeout=####
   Replacing #### with the amount of seconds you would like. The default is 50, and a good number to increase this to is 120 or higher, depending on the amount of disk statistics the VMs have.
3. Verify no transactions are running/pending and stop the OnApp daemon with /etc/init.d/onapp stop
4. Restart the MySQL server with /etc/init.d/mysqld restart
5. Optionally, verify the MySQL settings by opening the mysql cli and running SELECT @@innodb_lock_wait_timeout;
6. Start the OnApp daemon again with /etc/init.d/onapp start

Once this is done, the delete query should finish properly and also prevent the problem 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. This often occurs on virtual machines that have been on the cloud for a long time, and also if there are other heavy database queries happening on the server at the same time. 

Note:

This failure may delete the virtual machine's vdisk.  If the error message "Error running lvchange: One or more specified logical volume(s) not found." appears in that following log failure, refer to this article.