Deleting a host leaves behind orphaned certificates

[riversdev0]

Describe the bug
When deleting a host, the certificate assigned to that host is left behind in the database.

Your Environment (please supply the following information):
n/a

Steps To Reproduce

1. On the MySQL database, run select c.id,serial,h.id,hostname from certificates as c left join hosts as h on c.id=h.c_id;
2. Go to a zone
3. Pick a host and find its serial number in the database output
4. Delete that host from that zone
5. Re-run the MySQL query in step 1, and see that the serial is still in the table, and the hostname is gone.

Caution: a certificate should not be deleted unless it has no hosts that reference it. (A certificate is able to have more than 1 host referencing it.)

Additional Info
Before:

| 306 | 12347781757552318374407394452727290987     | 27   | myserver.example.com        |

After:

| 306 | 12347781757552318374407394452727290987     | NULL | NULL                        |

[phpipam]

Hi, under canning > Cron jobs there is a "Remove orphaned certificates" script that removes orphaned certificates.

try {
	# remove
	$certificates = $Database->runQuery("delete FROM certificates WHERE `t_id` = ? and is_manual = 0 and id NOT IN (SELECT c_id from hosts where c_id is not NULL)", [$tenant_id]);
} catch (Exception $e) {
    // print error
	$Common->errors[] = $e->getMessage();
	$Common->show_cli ($Common->get_last_error());
}

When this happens it however impacts reference of old certificates... So I guess it depends on your preference on how often to run this script.

[riversdev0]

Oh, I didn't remember that Cran job, thanks! In light of this, this issue can be discarded. The one thing I would ask is for a second and id NOT IN clause to be added to avoid deleting certs in the c_id_old column also.