· 6 years ago · May 13, 2019, 08:36 PM
1DELIMITER $$
2
3/*
4 * rmsg
5 *
6 * Procedure to print messages
7 */
8DROP PROCEDURE IF EXISTS rmsg$$
9CREATE PROCEDURE rmsg(msg VARCHAR(255))
10BEGIN
11 select msg as "message";
12END$$
13
14
15/*
16 * r_show_purged_instances_summary
17 *
18 */
19DROP PROCEDURE IF EXISTS r_show_purged_instances_summary$$
20CREATE PROCEDURE r_show_purged_instances_summary()
21BEGIN
22
23 call rmsg ("Top 10 Accounts with most purged instances");
24 select instance.account_id, COUNT(*)
25 from instance
26 where instance.state='purged'
27 limit 10;
28
29END$$
30
31
32DROP PROCEDURE IF EXISTS r_cleanup_volume_storage_pool_map$$
33CREATE PROCEDURE r_cleanup_volume_storage_pool_map(account_id bigint(20))
34BEGIN
35
36 call rmsg("cleaning table: volume_storage_pool_map");
37
38 DELETE IGNORE volume_storage_pool_map
39 from volume_storage_pool_map
40 inner join volume
41 on volume_storage_pool_map.volume_id=volume.id
42 inner join instance
43 on volume.instance_id=instance.id
44 where
45 instance.state='purged' and
46 instance.account_id=account_id;
47
48 select row_count();
49
50END$$
51
52DROP PROCEDURE IF EXISTS r_cleanup_snapshot_storage_pool_map$$
53CREATE PROCEDURE r_cleanup_snapshot_storage_pool_map(account_id bigint(20))
54BEGIN
55
56 call rmsg("cleaning table: snapshot_storage_pool_map");
57
58 delete ignore snapshot_storage_pool_map
59 from snapshot_storage_pool_map
60 inner join snapshot
61 on snapshot_storage_pool_map.snapshot_id=snapshot.id
62 inner join volume
63 on snapshot.volume_id=volume.id
64 inner join instance
65 on volume.instance_id=instance.id
66 where
67 instance.state='purged' and
68 instance.account_id=account_id;
69
70 select row_count();
71
72END$$
73
74
75DROP PROCEDURE IF EXISTS r_cleanup_snapshot$$
76CREATE PROCEDURE r_cleanup_snapshot(account_id bigint(20))
77BEGIN
78
79 call rmsg("cleaning table: snapshot");
80
81 DELETE IGNORE snapshot
82 from snapshot
83 inner join volume
84 on snapshot.volume_id=volume.id
85 inner join instance
86 on volume.instance_id=instance.id
87 where
88 instance.state='purged' and
89 instance.account_id=account_id;
90
91 select row_count();
92
93END$$
94
95
96
97DROP PROCEDURE IF EXISTS r_cleanup_mount$$
98CREATE PROCEDURE r_cleanup_mount(account_id bigint(20))
99BEGIN
100
101 call rmsg("cleaning table: mount");
102 delete ignore mount
103 from mount
104 inner join volume
105 on mount.volume_id=volume.id
106 inner join instance
107 on volume.instance_id=instance.id
108 where
109 instance.state='purged' and
110 instance.account_id=account_id;
111
112 select row_count();
113
114END$$
115
116
117
118DROP PROCEDURE IF EXISTS r_cleanup_backup$$
119CREATE PROCEDURE r_cleanup_backup(account_id bigint(20))
120BEGIN
121
122 call rmsg("cleaning table: backup");
123
124 DELETE IGNORE backup
125 from backup
126 inner join volume
127 on backup.volume_id=volume.id
128 inner join instance
129 on volume.instance_id=instance.id
130 where
131 instance.state='purged' and
132 instance.account_id=account_id;
133
134 select row_count();
135
136END$$
137
138
139DROP PROCEDURE IF EXISTS r_cleanup_volume$$
140CREATE PROCEDURE r_cleanup_volume(account_id bigint(20))
141BEGIN
142
143 call rmsg("cleaning table: volume");
144
145 delete ignore volume
146 from volume
147 inner join instance
148 on volume.instance_id=instance.id
149 where
150 instance.state='purged' and
151 instance.account_id=account_id;
152
153 select row_count();
154
155END$$
156
157DROP PROCEDURE IF EXISTS r_cleanup_service_log$$
158CREATE PROCEDURE r_cleanup_service_log(account_id bigint(20))
159BEGIN
160
161 call rmsg("cleaning table: service_log");
162
163 delete ignore service_log
164 from service_log
165 inner join instance
166 on service_log.instance_id=instance.id
167 where
168 instance.state='purged' and
169 instance.account_id=account_id;
170
171 select row_count();
172
173END$$
174
175
176DROP PROCEDURE IF EXISTS r_cleanup_service_expose_map$$
177CREATE PROCEDURE r_cleanup_service_expose_map(account_id bigint(20))
178BEGIN
179
180 call rmsg("cleaning table: service_expose_map");
181 delete ignore service_expose_map
182 from service_expose_map
183 inner join instance
184 on service_expose_map.instance_id=instance.id
185 where
186 service_expose_map.state in ('purged', 'removed', 'removing', 'purging') and
187 instance.state='purged' and
188 instance.account_id=account_id;
189
190 select row_count();
191
192END$$
193
194
195DROP PROCEDURE IF EXISTS r_cleanup_service_event$$
196CREATE PROCEDURE r_cleanup_service_event(account_id bigint(20))
197BEGIN
198
199 call rmsg("cleaning table: service_event");
200 delete ignore service_event
201 from service_event
202 inner join instance
203 on service_event.instance_id=instance.id
204 where
205 instance.state='purged' and
206 instance.account_id=account_id;
207
208 select row_count();
209
210END$$
211
212
213DROP PROCEDURE IF EXISTS r_cleanup_port$$
214CREATE PROCEDURE r_cleanup_port(account_id bigint(20))
215BEGIN
216 call rmsg("cleaning table: port");
217
218 delete ignore port
219 from port
220 inner join instance
221 on port.instance_id=instance.id
222 where
223 port.state in ('purged', 'removed', 'inactive') and
224 instance.state='purged' and
225 instance.account_id=account_id;
226
227 select row_count();
228
229END$$
230
231
232DROP PROCEDURE IF EXISTS r_cleanup_ip_address_nic_map$$
233CREATE PROCEDURE r_cleanup_ip_address_nic_map(account_id bigint(20))
234BEGIN
235 call rmsg("cleaning table: ip_address_nic_map");
236
237 delete ignore ip_address_nic_map
238 from ip_address_nic_map
239 inner join nic
240 on ip_address_nic_map.nic_id=nic.id
241 inner join instance
242 on nic.instance_id=instance.id
243 where
244 instance.state='purged' and
245 instance.account_id=account_id;
246
247 select row_count();
248
249END$$
250
251
252
253DROP PROCEDURE IF EXISTS r_cleanup_nic$$
254CREATE PROCEDURE r_cleanup_nic(account_id bigint(20))
255BEGIN
256 call rmsg("cleaning table: nic");
257
258 delete ignore nic
259 from nic
260 inner join instance
261 on nic.instance_id=instance.id
262 where
263 instance.state='purged' and
264 instance.account_id=account_id;
265
266 select row_count();
267
268END$$
269
270
271
272
273
274DROP PROCEDURE IF EXISTS r_cleanup_network_service_provider_instance_map$$
275CREATE PROCEDURE r_cleanup_network_service_provider_instance_map(account_id bigint(20))
276BEGIN
277 call rmsg("cleaning table: network_service_provider_instance_map");
278
279 delete ignore network_service_provider_instance_map
280 from network_service_provider_instance_map
281 inner join instance
282 on network_service_provider_instance_map.instance_id=instance.id
283 where
284 instance.state='purged' and
285 instance.account_id=account_id;
286
287 select row_count();
288
289END$$
290
291
292DROP PROCEDURE IF EXISTS r_cleanup_mount2$$
293CREATE PROCEDURE r_cleanup_mount2(account_id bigint(20))
294BEGIN
295
296 call rmsg("cleaning table: mount");
297 delete ignore mount
298 from mount
299 inner join instance
300 on mount.instance_id=instance.id
301 where
302 instance.state='purged' and
303 instance.account_id=account_id;
304
305 select row_count();
306
307END$$
308
309
310DROP PROCEDURE IF EXISTS r_cleanup_load_balancer_target$$
311CREATE PROCEDURE r_cleanup_load_balancer_target(account_id bigint(20))
312BEGIN
313 call rmsg("cleaning table: load_balancer_target");
314
315 delete ignore load_balancer_target
316 from load_balancer_target
317 inner join instance
318 on load_balancer_target.instance_id=instance.id
319 where
320 instance.state='purged' and
321 instance.account_id=account_id;
322
323 select row_count();
324
325END$$
326
327DROP PROCEDURE IF EXISTS r_cleanup_instance_link$$
328CREATE PROCEDURE r_cleanup_instance_link(account_id bigint(20))
329BEGIN
330 call rmsg("cleaning table: instance_link");
331
332 delete ignore instance_link
333 from instance_link
334 inner join instance
335 on instance_link.instance_id=instance.id
336 where
337 instance.state='purged' and
338 instance.account_id=account_id;
339
340 select row_count();
341
342END$$
343
344
345DROP PROCEDURE IF EXISTS r_cleanup_instance_label_map$$
346CREATE PROCEDURE r_cleanup_instance_label_map(account_id bigint(20))
347BEGIN
348 call rmsg("cleaning table: instance_label_map");
349
350 delete ignore instance_label_map
351 from instance_label_map
352 inner join instance
353 on instance_label_map.instance_id=instance.id
354 where
355 instance.state='purged' and
356 instance.account_id=account_id;
357
358 select row_count();
359
360END$$
361
362
363DROP PROCEDURE IF EXISTS r_cleanup_instance_host_map$$
364CREATE PROCEDURE r_cleanup_instance_host_map(account_id bigint(20))
365BEGIN
366 call rmsg("cleaning table: instance_host_map");
367
368 delete ignore instance_host_map
369 from instance_host_map
370 inner join instance
371 on instance_host_map.instance_id=instance.id
372 where
373 instance_host_map.state in ('purged', 'removed', 'removing', 'purging') and
374 instance.state='purged' and
375 instance.account_id=account_id;
376
377 select row_count();
378
379END$$
380
381
382DROP PROCEDURE IF EXISTS r_cleanup_healthcheck_instance_host_map$$
383CREATE PROCEDURE r_cleanup_healthcheck_instance_host_map(account_id bigint(20))
384BEGIN
385 call rmsg("cleaning table: healthcheck_instance_host_map");
386
387 delete ignore healthcheck_instance_host_map
388 from healthcheck_instance_host_map
389 inner join instance
390 on healthcheck_instance_host_map.instance_id=instance.id
391 where
392 instance.state='purged' and
393 instance.account_id=account_id;
394
395 select row_count();
396
397END$$
398
399DROP PROCEDURE IF EXISTS r_cleanup_healthcheck_instance_host_map_2$$
400CREATE PROCEDURE r_cleanup_healthcheck_instance_host_map_2(account_id bigint(20))
401BEGIN
402 call rmsg("cleaning table: healthcheck_instance_host_map");
403
404 delete ignore healthcheck_instance_host_map
405 from healthcheck_instance_host_map
406 inner join healthcheck_instance
407 on healthcheck_instance_host_map.healthcheck_instance_id=healthcheck_instance.id
408 inner join instance
409 on healthcheck_instance.instance_id=instance.id
410 where
411 instance.state='purged' and
412 instance.account_id=account_id;
413
414 select row_count();
415
416END$$
417
418
419DROP PROCEDURE IF EXISTS r_cleanup_healthcheck_instance$$
420CREATE PROCEDURE r_cleanup_healthcheck_instance(account_id bigint(20))
421BEGIN
422 call rmsg("cleaning table: healthcheck_instance");
423
424 delete ignore healthcheck_instance
425 from healthcheck_instance
426 inner join instance
427 on healthcheck_instance.instance_id=instance.id
428 where
429 instance.state='purged' and
430 instance.account_id=account_id;
431
432 select row_count();
433
434END$$
435
436DROP PROCEDURE IF EXISTS r_cleanup_credential_instance_map$$
437CREATE PROCEDURE r_cleanup_credential_instance_map(account_id bigint(20))
438BEGIN
439 call rmsg("cleaning table: credential_instance_map");
440
441 delete ignore credential_instance_map
442 from credential_instance_map
443 inner join instance
444 on credential_instance_map.instance_id=instance.id
445 where
446 instance.state='purged' and
447 instance.account_id=account_id;
448
449 select row_count();
450
451END$$
452
453
454DROP PROCEDURE IF EXISTS r_cleanup_instance_link$$
455CREATE PROCEDURE r_cleanup_instance_link(account_id bigint(20))
456BEGIN
457 call rmsg("cleaning table: instance_link");
458
459 delete ignore instance_link
460 from instance_link
461 inner join instance
462 on instance_link.instance_id=instance.id
463 where
464 instance.state='purged' and
465 instance.account_id=account_id;
466
467 select row_count();
468
469 delete ignore instance_link
470 from instance_link
471 inner join instance
472 on instance_link.target_instance_id=instance.id
473 where
474 instance.state='purged' and
475 instance.account_id=account_id;
476
477 select row_count();
478
479END$$
480
481
482DROP PROCEDURE IF EXISTS r_cleanup_instance$$
483CREATE PROCEDURE r_cleanup_instance(account_id bigint(20))
484BEGIN
485
486 call rmsg("cleaning table: instance");
487
488 delete ignore from instance
489 WHERE
490 instance.network_container_id != '' and
491 instance.state='purged' and
492 instance.account_id=account_id;
493
494 select row_count();
495
496 delete ignore from instance
497 WHERE
498 instance.network_container_id = '' and
499 instance.state='purged' and
500 instance.account_id=account_id;
501
502 select row_count();
503
504 delete ignore from instance
505 WHERE
506 instance.network_container_id is NOT NULL and
507 instance.state='purged' and
508 instance.account_id=account_id;
509
510 select row_count();
511
512 delete ignore from instance
513 WHERE
514 instance.network_container_id is NULL and
515 instance.state='purged' and
516 instance.account_id=account_id;
517
518 select row_count();
519
520END$$
521
522/*
523 * r_cleanup_purged_instances_of_account
524 *
525 * Procedure to clean up instances of a given account
526 */
527DROP PROCEDURE IF EXISTS r_cleanup_purged_instances_of_account$$
528CREATE PROCEDURE r_cleanup_purged_instances_of_account(account_id bigint(20))
529BEGIN
530 call rmsg (concat("Cleaning purged instances for account_id: ", account_id));
531
532 call r_cleanup_volume_storage_pool_map(account_id);
533 call r_cleanup_snapshot_storage_pool_map(account_id);
534 call r_cleanup_snapshot(account_id);
535 call r_cleanup_mount(account_id);
536 call r_cleanup_backup(account_id);
537 call r_cleanup_volume(account_id);
538 call r_cleanup_service_log(account_id);
539 call r_cleanup_service_expose_map(account_id);
540 call r_cleanup_service_event(account_id);
541 call r_cleanup_port(account_id);
542 call r_cleanup_ip_address_nic_map(account_id);
543 call r_cleanup_nic(account_id);
544 call r_cleanup_network_service_provider_instance_map(account_id);
545 call r_cleanup_mount2(account_id);
546 call r_cleanup_load_balancer_target(account_id);
547 call r_cleanup_instance_link(account_id);
548 call r_cleanup_instance_label_map(account_id);
549 call r_cleanup_instance_host_map(account_id);
550 call r_cleanup_healthcheck_instance_host_map_2(account_id);
551 call r_cleanup_healthcheck_instance_host_map(account_id);
552 call r_cleanup_healthcheck_instance(account_id);
553 call r_cleanup_credential_instance_map(account_id);
554
555 call r_cleanup_instance(account_id);
556
557END$$
558
559DELIMITER ;