domains table notified_serial needs bigint instead of integer

[klaus3000]

• Program: Authoritative
• Issue type: Bug report

Short description

It seems there was a change from PDNS 4.0 to PDNS 4.1 (or even earlier) how serials in the range from 2^31 - 2^32-1 were handled.

Old version: internally the notified_serial was treated as unsigned, but when written into the DB, it was stored as INTEGER (signed). Hence, serials > 2^31 where stored as negative values. When read from DB the notified_serial was transparently converted again into the correct unsigned value.

PDNS 4.1: now the database code wants to write the unsigned type as unsigned type into the DB, and this fails (testet with Postgresql backend):

pdns[32458]: AXFR done for 'xxx.wien', zone committed with serial number 2824191541
pdns[32458]: Queued also-notification of domain 'xxx.wien' to 176.9.98.135:53
pdns[32458]: Unable to AXFR zone 'xxx.wien' from remote 'xx.59.115.xx' (PDNSException): GSQLBackend unable to refresh domain_id 533900: Fatal error during query: update soap_domains set notified_serial=$1 where id=$2: ERROR: value "2824191541" is out of range for type integer

Further, reading the old-style negative values will fail to:

Unable to parse record during incoming AXFR of 'xxx.ch' (std::exception): stoul returned result out of unsigned int range; data was "-1470775755"

Fix new installations:

• In the DB schema change the notified_serial type from INTEGER to BIGINT

Fix existing installations:

ALTER TABLE domains ALTER notified_serial TYPE BIGINT;
BEGIN;
NOTE: at this point PowerDNS will receive Postgresql Errors (prepared statements) and should be restartet
UPDATE domains SET notified_serial = NULL WHERE notified_serial < 0;
COMMIT;

Environment

• Operating system: Debian
• Software version: PDNS 4.1 + Postgresql Backend
• Software source: self compiled

[robert-s-lee]

Wanted check on the progress On this issue.

[Habbie]

There's no progress, but I've moved it from 4.1.x to 4.2.0 so that we will likely look at it soon.

[Habbie]

MSSQL mildly suffers from this issue - in 4.2, it still does the mapping to negative like older versions of pdns did with postgres, so it does not error.

[Habbie]

#8097 for postgres