Delete Instance entry from Databases Openstack

First take a dump of sql for any inconvenience happened during the delete entries

          mysqldump -u root -p --all-databases > xxxxx.sql

Now do the following procedure to remove the entry from the databases,


 Use nova;



1) SELECT id, uuid, vm_state, deleted, power_state FROM instances WHERE vm_state = "error";

If you change the flag of deleted field to 1, then it will not display on the dashboard of openstack. But the entry persist in the database only.

    mysql> update instances set deleted=1 where uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';

2) Check the security groups table security_group_instance_association, any value assosicated with particular uuid

    mysql> select * from security_group_instance_association;

3) Check with block_device_mapping table, any devices attached to particular uuid.

   mysql> select * from block_device_mapping where instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';

4) Check the info caches table

   mysql> select * from instance_info_caches where instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';

5) Check the instance action table

 mysql> select *  from instance_actions where instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';

 mysql> select created_at, project_id, action, instance_uuid, message, deleted  from instance_actions where instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';

6) Check the instance_actions table

     select *  from instance_actions where instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';

   action_id (FKC) is mapped into instance_actions table id (PRI)

7)Check the faults table

   mysql> select id,instance_uuid,  code, message, host, deleted from instance_faults where instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';

8) Check instance extra table

 mysql> select flavor, instance_uuid from instance_extra where instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';

9) Check instance meta data

select * from instance_system_metadata where instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';


Now Cleaning Process of the table:

1) Execute the following commands to clean the tables;

     
SELECT id, uuid, vm_state, deleted, power_state FROM instances WHERE vm_state = "error";

DELETE FROM security_group_instance_association WHERE instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';

DELETE FROM block_device_mapping WHERE instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';

DELETE FROM instance_info_caches WHERE instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';

DELETE FROM instance_actions WHERE instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920'; (It gives foriegn key violation error)

select id, instance_uuid  from instance_actions where instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';



DELETE FROM instance_actions_events where action_id=956;

DELETE FROM instance_actions_events where action_id=957;

DELETE FROM instance_actions_events where action_id=958;

DELETE FROM instance_actions WHERE instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';

DELETE FROM instance_faults WHERE instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';

DELETE FROM instance_extra WHERE instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';

DELETE FROM instance_system_metadata WHERE instance_uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';

DELETE FROM instances WHERE uuid='698b67f0-6904-4090-ad4d-a9bf79ae5920';