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;