Symptoms

Operations with domains or changing the VPS hostname fail with

Service Renewal Failed: Error: Parallels Operations Automation error #extype_id #120, module_id #db_service, Foreign key 'vps_dns_records_fk1' violation while executing ' DELETE FROM domains WHERE deleted 'y' AND NOT EXISTS (SELECT 1 FROM dns_resource_records r WHERE domains.domain_id r.domain_id)

Cause

This is caused by POA-82125. The domain associated with the VPS hostname was deleted from the Provider Control Panel or Customer Control Panel. This creates a constraint violation because domain_id is present in the "vps_dns_records" table, but the domain itself is marked as deleted in the "domains" table.

Resolution

In Parallels Operations Automation (POA) 5.5, the system does not allow users to delete a domain if a VPS with such hostname exists.

However, if the VPS has some 3rd level domain name *.domain.tld the issue will appear. This is issue POA-82125, which was fixed in POA 5.5.4.

A provider may try to manually modify the database to lift the constraints:

Note: it is recommended that you make backups prior to deletion.

plesk=> SELECT vps_dns_records.domain_id,rr_id FROM vps_dns_records JOIN domains ON (vps_dns_records.domain_id=domains.domain_id) WHERE deleted='y' AND domains.name='<DOMAIN_NAME>';
plesk=> begin; delete from dns_ptr_record_references where rr_id in (<PUT_ACTUAL_rr_ids>);
plesk=> delete from dns_ptr_records where rr_id in (<PUT_ACTUAL_rr_ids>);
plesk=> delete from vps_dns_records where domain_id = <PUT_ACTUAL_domain_id>;
plesk=> commit;

Internal content