Question
How do I clear old pending backups on the entire cloud without having to remove them individually?
Environment
All OnApp Versions
***Please see this article for additional details about working with the OnApp database***
https://onapp.zendesk.com/entries/22073502-mysql-how-to-access-the-mysql-database-in-onapp
Answer
The backup can be removed using the Delete button on the backup overview page, but if there are numerous backups in this state on the cloud spread across multiple VMs, this can be time-consuming.
Here is how to remove them all at once.
1. Log into the MySQL command line on the CP server and run the following query to check the status of all pending backups:
mysql> select * from backups where built = 0;
2. This will return all backups that are in pending state. You will want to review this list for any backups that have a created_at date of the day you run the query. Any backup set for that day or up to a couple of days prior may still be waiting to run in the CP. You will want to check the transactions table for any running or pending transactions:
mysql> select * from transactions where status = 'running' or status = 'pending';
3. If there are any pending or running backup transactions, you will want to check the transaction parent_id against the backup id from the previous query, as this indicates that the backup is likely going to run. Deleting a pending backup that has a running or pending transaction will cause errors in the log.
4. Once you have identified backups that you don't want to delete, you will want to decide the date that you want to delete backups prior to. So, for example, if the oldest backup that is listed in the transaction table has a created_at date of 2012-01-13 12:12:03, then you would be safe in deleting all pending backups that have a created_at date prior to that date. In this case, the following query would delete all pending backups that were created prior to January 13 2012 at 12:00:00 noon:
mysql> delete from backups where built = 0 and created_at < '2012-01-13 12:00:00';
Additional Info
When a backup fails, it will sometimes leave a pending backup on the backup list for a VM. This can prevent new autobackups from being taken.