Cattle: MySQL procedures to clean up purged instances from Database

cattle_cleanup_purged_instances_from_db.sql

DELIMITER $$

/*
 * rmsg
 *
 * Procedure to print messages
 */
DROP PROCEDURE IF EXISTS rmsg$$
CREATE PROCEDURE rmsg(msg VARCHAR(255))
BEGIN
    select msg as "message";
END$$


/*
 * r_show_purged_instances_summary
 *
 */
DROP PROCEDURE IF EXISTS r_show_purged_instances_summary$$
CREATE PROCEDURE r_show_purged_instances_summary()
BEGIN

  call rmsg ("Top 10 Accounts with most purged instances");
  select instance.account_id, COUNT(*)
  from instance
  where instance.state='purged'
  limit 10;

END$$


DROP PROCEDURE IF EXISTS r_cleanup_volume_storage_pool_map$$
CREATE PROCEDURE r_cleanup_volume_storage_pool_map(account_id bigint(20))
BEGIN

  call rmsg("cleaning table: volume_storage_pool_map");

  DELETE IGNORE volume_storage_pool_map
  from volume_storage_pool_map
  inner join volume
    on volume_storage_pool_map.volume_id=volume.id
  inner join instance
    on volume.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$

DROP PROCEDURE IF EXISTS r_cleanup_snapshot_storage_pool_map$$
CREATE PROCEDURE r_cleanup_snapshot_storage_pool_map(account_id bigint(20))
BEGIN

  call rmsg("cleaning table: snapshot_storage_pool_map");

  delete ignore snapshot_storage_pool_map
  from snapshot_storage_pool_map
  inner join snapshot
    on snapshot_storage_pool_map.snapshot_id=snapshot.id
  inner join volume
    on snapshot.volume_id=volume.id
  inner join instance
    on volume.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$


DROP PROCEDURE IF EXISTS r_cleanup_snapshot$$
CREATE PROCEDURE r_cleanup_snapshot(account_id bigint(20))
BEGIN

  call rmsg("cleaning table: snapshot");

  DELETE IGNORE snapshot
  from snapshot
  inner join volume
    on snapshot.volume_id=volume.id
  inner join instance
    on volume.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$



DROP PROCEDURE IF EXISTS r_cleanup_mount$$
CREATE PROCEDURE r_cleanup_mount(account_id bigint(20))
BEGIN

  call rmsg("cleaning table: mount");
  delete ignore mount
  from mount
  inner join volume
    on mount.volume_id=volume.id
  inner join instance
    on volume.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$



DROP PROCEDURE IF EXISTS r_cleanup_backup$$
CREATE PROCEDURE r_cleanup_backup(account_id bigint(20))
BEGIN

  call rmsg("cleaning table: backup");

  DELETE IGNORE backup
  from backup
  inner join volume
    on backup.volume_id=volume.id
  inner join instance
    on volume.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$


DROP PROCEDURE IF EXISTS r_cleanup_volume$$
CREATE PROCEDURE r_cleanup_volume(account_id bigint(20))
BEGIN

  call rmsg("cleaning table: volume");

  delete ignore volume
  from volume
  inner join instance
    on volume.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$

DROP PROCEDURE IF EXISTS r_cleanup_service_log$$
CREATE PROCEDURE r_cleanup_service_log(account_id bigint(20))
BEGIN

  call rmsg("cleaning table: service_log");

  delete ignore service_log
  from service_log
  inner join instance
    on service_log.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$


DROP PROCEDURE IF EXISTS r_cleanup_service_expose_map$$
CREATE PROCEDURE r_cleanup_service_expose_map(account_id bigint(20))
BEGIN

  call rmsg("cleaning table: service_expose_map");
  delete ignore service_expose_map
  from service_expose_map
  inner join instance
    on service_expose_map.instance_id=instance.id
  where
    service_expose_map.state in ('purged', 'removed', 'removing', 'purging') and
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$


DROP PROCEDURE IF EXISTS r_cleanup_service_event$$
CREATE PROCEDURE r_cleanup_service_event(account_id bigint(20))
BEGIN

  call rmsg("cleaning table: service_event");
  delete ignore service_event
  from service_event
  inner join instance
    on service_event.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$


DROP PROCEDURE IF EXISTS r_cleanup_port$$
CREATE PROCEDURE r_cleanup_port(account_id bigint(20))
BEGIN
  call rmsg("cleaning table: port");

  delete ignore port
  from port
  inner join instance
    on port.instance_id=instance.id
  where
    port.state in ('purged', 'removed', 'inactive') and
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$


DROP PROCEDURE IF EXISTS r_cleanup_ip_address_nic_map$$
CREATE PROCEDURE r_cleanup_ip_address_nic_map(account_id bigint(20))
BEGIN
  call rmsg("cleaning table: ip_address_nic_map");

  delete ignore ip_address_nic_map
  from ip_address_nic_map
  inner join nic
    on ip_address_nic_map.nic_id=nic.id
  inner join instance
    on nic.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$



DROP PROCEDURE IF EXISTS r_cleanup_nic$$
CREATE PROCEDURE r_cleanup_nic(account_id bigint(20))
BEGIN
  call rmsg("cleaning table: nic");

  delete ignore nic
  from nic
  inner join instance
    on nic.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$





DROP PROCEDURE IF EXISTS r_cleanup_network_service_provider_instance_map$$
CREATE PROCEDURE r_cleanup_network_service_provider_instance_map(account_id bigint(20))
BEGIN
  call rmsg("cleaning table: network_service_provider_instance_map");

  delete ignore network_service_provider_instance_map
  from network_service_provider_instance_map
  inner join instance
    on network_service_provider_instance_map.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$


DROP PROCEDURE IF EXISTS r_cleanup_mount2$$
CREATE PROCEDURE r_cleanup_mount2(account_id bigint(20))
BEGIN

  call rmsg("cleaning table: mount");
  delete ignore mount
  from mount
  inner join instance
    on mount.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$


DROP PROCEDURE IF EXISTS r_cleanup_load_balancer_target$$
CREATE PROCEDURE r_cleanup_load_balancer_target(account_id bigint(20))
BEGIN
  call rmsg("cleaning table: load_balancer_target");

  delete ignore load_balancer_target
  from load_balancer_target
  inner join instance
    on load_balancer_target.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$

DROP PROCEDURE IF EXISTS r_cleanup_instance_link$$
CREATE PROCEDURE r_cleanup_instance_link(account_id bigint(20))
BEGIN
  call rmsg("cleaning table: instance_link");

  delete ignore instance_link
  from instance_link
  inner join instance
    on instance_link.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$


DROP PROCEDURE IF EXISTS r_cleanup_instance_label_map$$
CREATE PROCEDURE r_cleanup_instance_label_map(account_id bigint(20))
BEGIN
  call rmsg("cleaning table: instance_label_map");

  delete ignore instance_label_map
  from instance_label_map
  inner join instance
    on instance_label_map.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$


DROP PROCEDURE IF EXISTS r_cleanup_instance_host_map$$
CREATE PROCEDURE r_cleanup_instance_host_map(account_id bigint(20))
BEGIN
  call rmsg("cleaning table: instance_host_map");

  delete ignore instance_host_map
  from instance_host_map
  inner join instance
    on instance_host_map.instance_id=instance.id
  where
    instance_host_map.state in ('purged', 'removed', 'removing', 'purging') and
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$


DROP PROCEDURE IF EXISTS r_cleanup_healthcheck_instance_host_map$$
CREATE PROCEDURE r_cleanup_healthcheck_instance_host_map(account_id bigint(20))
BEGIN
  call rmsg("cleaning table: healthcheck_instance_host_map");

  delete ignore healthcheck_instance_host_map
  from healthcheck_instance_host_map
  inner join instance
    on healthcheck_instance_host_map.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$

DROP PROCEDURE IF EXISTS r_cleanup_healthcheck_instance_host_map_2$$
CREATE PROCEDURE r_cleanup_healthcheck_instance_host_map_2(account_id bigint(20))
BEGIN
  call rmsg("cleaning table: healthcheck_instance_host_map");

  delete ignore healthcheck_instance_host_map
  from healthcheck_instance_host_map
  inner join healthcheck_instance
    on healthcheck_instance_host_map.healthcheck_instance_id=healthcheck_instance.id
  inner join instance
    on healthcheck_instance.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$


DROP PROCEDURE IF EXISTS r_cleanup_healthcheck_instance$$
CREATE PROCEDURE r_cleanup_healthcheck_instance(account_id bigint(20))
BEGIN
  call rmsg("cleaning table: healthcheck_instance");

  delete ignore healthcheck_instance
  from healthcheck_instance
  inner join instance
    on healthcheck_instance.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$

DROP PROCEDURE IF EXISTS r_cleanup_credential_instance_map$$
CREATE PROCEDURE r_cleanup_credential_instance_map(account_id bigint(20))
BEGIN
  call rmsg("cleaning table: credential_instance_map");

  delete ignore credential_instance_map
  from credential_instance_map
  inner join instance
    on credential_instance_map.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$

                                                                     
DROP PROCEDURE IF EXISTS r_cleanup_instance_link$$
CREATE PROCEDURE r_cleanup_instance_link(account_id bigint(20))
BEGIN
  call rmsg("cleaning table: instance_link");

  delete ignore instance_link
  from instance_link
  inner join instance
    on instance_link.instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

  delete ignore instance_link
  from instance_link
  inner join instance
    on instance_link.target_instance_id=instance.id
  where
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$


DROP PROCEDURE IF EXISTS r_cleanup_instance$$
CREATE PROCEDURE r_cleanup_instance(account_id bigint(20))
BEGIN

  call rmsg("cleaning table: instance");

  delete ignore from instance
  WHERE
    instance.network_container_id != '' and
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

  delete ignore from instance
  WHERE
    instance.network_container_id = '' and
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

  delete ignore from instance
  WHERE
    instance.network_container_id is NOT NULL and
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

  delete ignore from instance
  WHERE
    instance.network_container_id is NULL and
    instance.state='purged' and
    instance.account_id=account_id;

  select row_count();

END$$

/*
 * r_cleanup_purged_instances_of_account
 *
 * Procedure to clean up instances of a given account
 */
DROP PROCEDURE IF EXISTS r_cleanup_purged_instances_of_account$$
CREATE PROCEDURE r_cleanup_purged_instances_of_account(account_id bigint(20))
BEGIN
  call rmsg (concat("Cleaning purged instances for account_id: ", account_id));

  call r_cleanup_volume_storage_pool_map(account_id);
  call r_cleanup_snapshot_storage_pool_map(account_id);
  call r_cleanup_snapshot(account_id);
  call r_cleanup_mount(account_id);
  call r_cleanup_backup(account_id);
  call r_cleanup_volume(account_id);
  call r_cleanup_service_log(account_id);
  call r_cleanup_service_expose_map(account_id);
  call r_cleanup_service_event(account_id);
  call r_cleanup_port(account_id);
  call r_cleanup_ip_address_nic_map(account_id);
  call r_cleanup_nic(account_id);
  call r_cleanup_network_service_provider_instance_map(account_id);
  call r_cleanup_mount2(account_id);
  call r_cleanup_load_balancer_target(account_id);
  call r_cleanup_instance_link(account_id);
  call r_cleanup_instance_label_map(account_id);
  call r_cleanup_instance_host_map(account_id);
  call r_cleanup_healthcheck_instance_host_map_2(account_id);
  call r_cleanup_healthcheck_instance_host_map(account_id);
  call r_cleanup_healthcheck_instance(account_id);
  call r_cleanup_credential_instance_map(account_id);

  call r_cleanup_instance(account_id);

END$$

DELIMITER ;

Instructions:

Step 0 :

Take a back up of the database.

• Log in to the host running rancher/server
• Exec inside the rancher/server container
• Take a backup of the database
  mysqldump cattle > cattle.db.backup.sql

Step 1:

Download the above SQL file.

Step 2 :

Log in to MySQL shell

mysql -u ${MYSQL_USER} -h ${DB_HOST} -p${MYSQL_PASSWORD} ${MYSQL_DATABASE}

Step 3:

Load the SQL file

source cattle_cleanup_purged_instances_from_db.sql

Step 4

Find the top 10 accounts with most purged instances:

call r_show_purged_instances_summary()

Step 5

Clean up purged instances for a particular account.

call r_cleanup_purged_instances_of_account(5)

In case you run into the following error:

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dbname.instance.account_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Try changing the mode:

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';