Recover missing externals IPs after VM deletion

There is a known issue in Abiquo releases prior to 4.2.3 which may cause external IPs to "disappear" from the API once the VM using them is deleted. The symptoms for this issue are these:

• Once the VM using it is deleted, the IP disappears from the available list for the network.
• If you try to create the IP again in the external network, you get 500 internal server error.

To confirm you are experiencing this issue, open a MySQL session to kinton DB and run the query below:

CREATE TEMPORARY TABLE IF NOT EXISTS rasd_wrong AS (
    SELECT idmanagement
    FROM rasd_management
    WHERE 
        idvm IS null AND
        temporal IS NOT null
        AND idresourcetype=10
);

If the query has any results, use the next one to get and identify the IPs which has disappeared from the API:

SELECT ip,vlan_network_name
FROM ip_pool_management
WHERE idmanagement IN (
    SELECT idmanagement
    FROM rasd_wrong
);

To solve it, use the query below. As any procedure involving manually modification of the DB, remember to backup the DB before executing it as a precaution measure:

UPDATE rasd_management
SET temporal = null
WHERE idmanagement IN (
    SELECT idmanagement
    FROM rasd_wrong
);

The procedure uses a temporary table which will exists as long as the MySQL session where it was created, and will be deleted automatically once you close it, so, all queries must be executed in the same session.