Skip to content

Instantly share code, notes, and snippets.

@leodotcloud

leodotcloud/cattle_cleanup_purged_instances_from_db.sql

Last active October 11, 2019 09:22
Show Gist options
  • Save leodotcloud/ca050d41031b317ee266bab89825c965 to your computer and use it in GitHub Desktop.
Save leodotcloud/ca050d41031b317ee266bab89825c965 to your computer and use it in GitHub Desktop.
Download ZIP
Cattle: MySQL procedures to clean up purged instances from Database
Raw
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 ;
@leodotcloud
Copy link
Author

leodotcloud commented May 3, 2019 •
edited
Loading

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)

@leodotcloud
Copy link
Author

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment