Hi,

I am trying to do cleanup on Newton (RHOSP 10) release nova DB, and want to remove old instances, which are removed LONG time ago (have about 7M rows on some tables).

I did some info gathering and found:

MariaDB [nova]> select TABLE_SCHEMA,TABLE_NAME,table_rows,AUTO_INCREMENT,TABLE_COMMENT from information_schema.tables where table_schema = 'nova' and table_rows >= 1000;
+--------------+--------------------------------+------------+----------------+---------------+
| TABLE_SCHEMA | TABLE_NAME                     | table_rows | AUTO_INCREMENT | TABLE_COMMENT |
+--------------+--------------------------------+------------+----------------+---------------+
| nova         | block_device_mapping           |    1021919 |        3644687 |               |
| nova         | instance_actions               |    2637028 |        7906316 |               |
| nova         | instance_actions_events        |     523519 |        8849807 |               |
| nova         | instance_extra                 |    1367875 |        3486497 |               |
| nova         | instance_faults                |       6079 |          14042 |               |
| nova         | instance_id_mappings           |    1162779 |        3486305 |               |
| nova         | instance_info_caches           |    1182036 |        3512678 |               |
| nova         | instance_metadata              |      62785 |         192781 |               |
| nova         | instance_system_metadata       |    7700511 |       20926265 |               |
| nova         | instances                      |    1192711 |        3486311 |               |
| nova         | reservations                   |    7059421 |       20933180 |               |
| nova         | s3_images                      |     160869 |         482213 |               |
| nova         | shadow_instance_actions_events |    2367811 |        7135407 |               |
| nova         | task_log                       |     103302 |         307409 |               |
| nova         | virtual_interfaces             |    3732556 |       10949276 |               |
+--------------+--------------------------------+------------+----------------+---------------+
15 rows in set (0.25 sec)

MariaDB [nova]> SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `COLUMN_NAME`, `REFERENCED_TABLE_SCHEMA`, `REFERENCED_TABLE_NAME`, `REFERENCED_COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` WHERE `TABLE_SCHEMA` = SCHEMA()  AND `REFERENCED_TABLE_NAME` IS NOT NULL ORDER BY `REFERENCED_TABLE_NAME` ASC;
+--------------+-------------------------------------+-------------------+-------------------------+-----------------------+------------------------+
| TABLE_SCHEMA | TABLE_NAME                          | COLUMN_NAME       | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------+-------------------------------------+-------------------+-------------------------+-----------------------+------------------------+
| nova         | aggregate_metadata                  | aggregate_id      | nova                    | aggregates            | id                     |
| nova         | aggregate_hosts                     | aggregate_id      | nova                    | aggregates            | id                     |
| nova         | pci_devices                         | compute_node_id   | nova                    | compute_nodes         | id                     |
| nova         | consoles                            | pool_id           | nova                    | console_pools         | id                     |
| nova         | instance_extra                      | instance_uuid     | nova                    | instances             | uuid                   |
| nova         | instance_system_metadata            | instance_uuid     | nova                    | instances             | uuid                   |
| nova         | virtual_interfaces                  | instance_uuid     | nova                    | instances             | uuid                   |
| nova         | consoles                            | instance_uuid     | nova                    | instances             | uuid                   |
| nova         | instance_faults                     | instance_uuid     | nova                    | instances             | uuid                   |
| nova         | instance_actions                    | instance_uuid     | nova                    | instances             | uuid                   |
| nova         | block_device_mapping                | instance_uuid     | nova                    | instances             | uuid                   |
| nova         | instance_info_caches                | instance_uuid     | nova                    | instances             | uuid                   |
| nova         | migrations                          | instance_uuid     | nova                    | instances             | uuid                   |
| nova         | instance_metadata                   | instance_uuid     | nova                    | instances             | uuid                   |
| nova         | security_group_instance_association | instance_uuid     | nova                    | instances             | uuid                   |
| nova         | fixed_ips                           | instance_uuid     | nova                    | instances             | uuid                   |
| nova         | instance_actions_events             | action_id         | nova                    | instance_actions      | id                     |
| nova         | instance_group_member               | group_id          | nova                    | instance_groups       | id                     |
| nova         | instance_group_policy               | group_id          | nova                    | instance_groups       | id                     |
| nova         | instance_type_projects              | instance_type_id  | nova                    | instance_types        | id                     |
| nova         | instance_type_extra_specs           | instance_type_id  | nova                    | instance_types        | id                     |
| nova         | reservations                        | usage_id          | nova                    | quota_usages          | id                     |
| nova         | security_group_rules                | parent_group_id   | nova                    | security_groups       | id                     |
| nova         | security_group_rules                | group_id          | nova                    | security_groups       | id                     |
| nova         | security_group_instance_association | security_group_id | nova                    | security_groups       | id                     |
+--------------+-------------------------------------+-------------------+-------------------------+-----------------------+------------------------+
25 rows in set (0.14 sec)

I compiled some delete line, but it gives me an error:

MariaDB [nova]> DELETE instances,instance_extra,instance_system_metadata,
virtual_interfaces,consoles,instance_faults,instance_actions,block_device_mapping,
instance_info_caches,migrations,instance_metadata,security_group_instance_association,
fixed_ips,instance_actions_events
FROM instances
JOIN instance_extra.instance_uuid = instances.uuid
JOIN instance_system_metadata.instance_uuid = instances.uuid
JOIN virtual_interfaces.instance_uuid = instances.uuid
JOIN consoles.instance_uuid = instances.uuid
JOIN instance_faults.instance_uuid = instances.uuid
JOIN instance_actions.instance_uuid = instances.uuid
JOIN block_device_mapping.instance_uuid = instances.uuid
JOIN instance_info_caches.instance_uuid = instances.uuid
JOIN migrations.instance_uuid = instances.uuid
JOIN instance_metadata.instance_uuid = instances.uuid
JOIN security_group_instance_association.instance_uuid = instances.uuid
JOIN fixed_ips.instance_uuid = instances.uuid
JOIN instance_actions_events.action_id = instance_actions.id
WHERE nova.instances.uuid = "d918a3b2-0a4b-4c73-9d06-ab58e458a648";
ERROR 1066 (42000): Not unique table/alias: 'instances'

--- update --- I have tried to add DB and get DN, table and column in apostrofas, but still getting error.

MariaDB [nova]> DELETE `nova`.`instances`,`nova`.`instance_extra`,`nova`.`instance_system_metadata`,`nova`.`virtual_interfaces`,`nova`.`consoles`,
    -> `nova`.`instance_faults`,`nova`.`instance_actions`,`nova`.`block_device_mapping`,`nova`.`instance_info_caches`,`nova`.`migrations`,
    -> `nova`.`instance_metadata`,`nova`.`security_group_instance_association`,`nova`.`fixed_ips`,`nova`.`instance_actions_events`
    -> FROM `nova`.`instances`
    -> JOIN `nova`.`instance_extra`.`instance_uuid` = `nova`.`instances`.`uuid`
    -> JOIN `nova`.`instance_system_metadata`.`instance_uuid` = `nova`.`instances`.`uuid`
    -> JOIN `nova`.`virtual_interfaces`.`instance_uuid` = `nova`.`instances`.`uuid`
    -> JOIN `nova ...