How to delete tmp entries on virtualmachine database table

.A virtual machine reconfigure process in Abiquo includes a backup of database entries of the original resources. These entries depend on a temporary entry in the virtualmachine table. You can identify those entries by checking if the name field starts with 'tmp_'. Under certain circumstances the temporary entries can remain in the database, affecting resources limits and statistics.

Post_tmp_vms.png

Affectd tables that will hold temporary entries can be seen in the above diagram.

Before executing any of these delete sentences please make sure you have made a backup of your database.
Run this query to identify tmp entries in your database:
SELECT idVM
FROM virtualmachine
WHERE name LIKE (
SELECT name
FROM virtualmachine
WHERE name LIKE 'tmp_%'HAVING COUNT(name) > 1);

First of all, create a temporary table with all the idVMs in the temporary entries you want to delete:

CREATE TEMPORARY TABLE temp_entries AS (
SELECT idvm
FROM virtualmachine
WHERE name LIKE (
SELECT name
FROM virtualmachine
WHERE name LIKE 'tmp_%' HAVING COUNT(name) > 1));
Then delete all related entries in ip_pool_management:
DELETE ipm 
FROM ip_pool_management ipm,
rasd_management rm
WHERE rm.idManagement = ipm.idManagement AND
rm.temporal IS NOT NULL AND
rm.idVM IN ( SELECT idVM FROM temp_entries);

Also delete all dependent entries in disk_management:

DELETE dm 
FROM disk_management dm,
rasd_management rm
WHERE rm.idManagement = dm.idManagement AND
rm.temporal IS NOT NULL AND
rm.idVM IN (SELECT idVM FROM temp_entries);

  Then remove entries in the rasd_management table because there are no more dependent entries:

DELETE rm 
FROM rasd_management rm 
WHERE rm.temporal IS NOT NULL AND 
rm.idVM IN (SELECT idVM FROM temp_entries);

Now you can delete all temporary entries in virtualmachine table:

DELETE 
FROM virtualmachine
WHERE temporal IS NOT NULL AND
idVM IN (SELECT idVM FROM temp_entries);

Finally delete the temporary table:

DROP TABLE temp_entries;
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk