Symptoms
A PowerDNS-related task fails in POA with the following error message:
Operation with storage caused error: 'select id from domains where name=$1 did not return correct result. tuplesNumber=0'
The example of the failed task is provided below:
Task name Update reverse zone 1 in the PowerDNS plugin 10
Last execution output Operation with storage caused error: 'select id from domains where name=$1 did not return correct result. tuplesNumber=0'
Cause
Database records on PowerDNS name servers are inconsistent - some records are missing.
It may happen if PowerDNS database was restored from backup or was corrupted.
Resolution
First, find which record is missing in the PowerDNS database using the steps below.
Try to find ID or name of the broken DNS zone in the failed task properties and the find the zone in POA Control Panel at Operations Director > DNS Manager > Domains.
E.g. in the name of the failed task provided above it might be seen that POA tries to update the 'reverse zone 1'. In POA Provider Control Panel at Operations Director > DNS Manager > Reverse DNS Zones you may find such zone in status 'In Sync - No'.
Another way to find what exactly domain zone POA tries to update on PowerDNS servers is to look at POA debug log:
Increase POA debug log verbosity to TRACE level if not enabled using instructions from KB article: https://kb.cloudblue.com/en/9039
Log into the POA Management Node and run the following command to save tail of the POA debug log to a file:
tail -f poa.debug.log > tempfile.log
Run the failed task in POA Task Manager, wait until if fails again and interrupt the 'tail' utility in console (press Ctrl-C)
In the tempfile.log file created by the 'tail' utility find the lines like the following:
Aug 22 08:53:11 osscore : DBG [1:4325:f4de3b90:48550 1:4508:f4235b90 PowerDns]: [task:1174893 DNSManagement::PowerDns::PgConnection::GetDomainIdByName] PowerDNS: query: select id from domains where name=customer.com Aug 22 08:53:11 osscore : DBG [1:4325:f4de3b90:48550 1:4508:f4235b90 lib]: [task:1174893 DNSManagement::PowerDns::PgConnection::GetDomainIdByName] {module_id="PowerDns"; code="10"} Operation with storage caused error: 'select id from domains where name=$1 did not return correct result. tuplesNumber=0'
The log entries provided above show the error message which may be seen in the failed POA task along with the name of the problem domain zone (the line above the line with the error message).
On each PowerDNS server managed by POA execute the SQL query found on the previous step in POA debug log in the PostgreSQL database, e.g.:
SELECT id FROM domains WHERE name=customer.com;
That will help you to identify which exactly record is missing in PowerDNS database.
To fix the problem insert record for the domain in question on each PowerDNS server where record is missing, e.g.:
BEGIN;
INSERT INTO domains (name, master, last_check, type, notified_serial, account, plugin_id) VALUES ('customer.com', NULL, NULL, 'MASTER', 0, NULL, NULL);
COMMIT; -- commit changes only if you are sure that the correct record was added to the database