PDNS authoritative server ID column overrun for records

ronnybremer commented 3 hours ago

[x] This is not a support question, I have read about opensource and will send support questions to the IRC channel, GitHub Discussions or the mailing list.
[x] I have read and understood the 'out in the open' support policy

Program: Authoritative
Issue type: Bug report

Short description

Using MySQL as a backend the ID column did overflow after many years of operation. ID column is current type "INT" and hit the ceiling at 2147483646.

Environment

Operating system: Rocky Linux 8
Software version: 4.6.4
Software source: OS repo

Steps to reproduce

let it run for a long time with lots of dynamic updates coming in through AXFR
monitor the MAX(ID) value in the DB on the records table
see it reaching the maximum INT value

Expected behaviour

maybe using BIGINT?

Actual behaviour

INT column did reach the maximum value

Other information

maybe there is a way to "re-org" the table, but I wouldn't know if the ID column from the records table is referenced somewhere else.

Habbie commented 3 hours ago

What happens now that you have reached the limit? Is MySQL refusing inserts?

ronnybremer commented 3 hours ago

unable to xfr zone (PDNSException): GSQLBackend unable to feed record xxx.com|NS: Could not execute mysql statement: insert into records (content,ttl,prio,type,domain_id,disabled,name,ordername,auth) values (?,?,?,?,?,?,?,?,?): Duplicate entry '2147483647' for key 'records.PRIMARY'

Habbie commented 3 hours ago

maybe using BIGINT?

the schema has said BIGINT since 4.1.0. Did you miss part of a schema migration?

ronnybremer commented 3 hours ago

@Habbie uhm ... that would be possible, I will verify it. Thank you for the hint!

ronnybremer commented 3 hours ago

grrr ... thank you again @Habbie! The schema was modified before, but the 3.4 to 4.1 was never executed. I did that now and the issue immediately went away.

PowerDNS / pdns