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.

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

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));

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;