Question about recurring log SQL error upon upgrading to 4.7.3 due to Debian upgrade from 11.x to 12.6

Chris Moody via Pdns-users <[email protected]> Thu, 8 Aug 2024 16:58:41 -0700
Newsgroups	gmane.network.dns.powerdns.user
Message-ID	<[email protected]>
Hello all.

I'm scratching my head a bit on this one.  Just upgraded a node from 
Debian 11.x to 12.6 and thus have upgraded pdns-server to the 4.7.3 
version that comes with.  I also ran some ALTER TABLE commands to get 
the new mysql/mariadb schema up to par with this new version...in 
particular adding in the 'options' column to the 'domains' table.
https://docs.powerdns.com/authoritative/backends/generic-mysql.html

My 'domains' table now is this:
==========
MariaDB [pdns]> desc domains;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | 
Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| id              | int(11)          | NO   | PRI | NULL    | 
auto_increment |
| name            | varchar(255)     | NO   | UNI | NULL |                |
| master          | varchar(128)     | YES  |     | NULL |                |
| last_check      | int(11)          | YES  |     | NULL |                |
| type            | varchar(6)       | NO   |     | NULL |                |
| notified_serial | int(10) unsigned | YES  |     | NULL |                |
| account         | varchar(40)      | YES  |     | NULL |                |
| options         | varchar(64000)   | YES  |     | NULL |                |
| catalog         | varchar(255)     | YES  |     | NULL |                |
+-----------------+------------------+------+-----+---------+----------------+
9 rows in set (0.001 sec)


Where I am having issue however is the pdns daemon is in a restart loop 
throwing the following error... but I can run the exact SQL query it is 
claiming it cannot run and am getting results returned.

===[ Error ]=====
pdns_server[1760]: Exiting because communicator thread died with error: 
virtual void GSQLBackend::getUpdatedMasters(std::vector<DomainInfo>&, 
std::unordered_set<DNSName>&, CatalogHashMap&) unable to retrieve list 
of master domains: Could not prepare statement: select d.id, d.name, 
d.type, d.notified_serial,d.options, d.catalog,r.content from records r 
join domains d on r.domain_id=d.id and r.name=d.name where r.type='SOA' 
and r.disabled=0 and d.type in ('MASTER', 'PRODUCER'): Unknown column 
'd.options' in 'field list'
=====

===[ SQL working... some rows redacted from public mailinglist chatter 
]=====
MariaDB [pdns]> select d.id, d.name, d.type, 
d.notified_serial,d.options, d.catalog,r.content from records r join 
domains d on r.domain_id=d.id and r.name=d.name where r.type='SOA' and 
r.disabled=0 and d.type in ('MASTER', 'PRODUCER');
+----+------------------------------------------------------------------------+--------+-----------------+---------+---------+------------------------------------------------------------------------+
| id | name | type   | notified_serial | options | catalog | content |
+----+------------------------------------------------------------------------+--------+-----------------+---------+---------+------------------------------------------------------------------------+
|  1 | xxxyyyzzz.com 
                                                          | MASTER 
|      1622659014 | NULL    | NULL    | ns1.xxxyyyzzz.com 
postmaster.xxxyyyzzz.com 1622659019 60 120 3600 3600 |
| 14 | 30.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 15 | 255.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 16 | 60.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 17 | 31.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 18 | 240.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 19 | 0.0.10.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 29 | 90.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 30 | 89.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 31 | aaabbbcccddd.com | MASTER |      1622659014 | NULL    | NULL    | 
ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 3600 3600 |
| 32 | 88.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 36 | 2.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 37 | 50.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 38 | 27.8.10.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 39 | 25.8.10.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 40 | 10.16.172.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 41 | 99.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 46 | 254.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 47 | 20.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 48 | 21.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 49 | 61.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 50 | 200.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
| 51 | 32.168.192.in-addr.arpa | MASTER |      1622659014 | NULL    | 
NULL    | ns1.xxxyyyzzz.com postmaster.xxxyyyzzz.com 1622659014 60 120 
3600 3600 |
+----+------------------------------------------------------------------------+--------+-----------------+---------+---------+------------------------------------------------------------------------+

If a manual SQL query via the user that pdns is assigned to connect via 
is returning valid responses to the stated 'select' query that the log 
mentions is failing... why does the process believe the query is 
failing?  The daemon is restarting every second stating this failure.

Any insights are greatly appreciated.

Thanks,
-Chris

-- 
Node-Nine, Inc.
[email protected]
619.354.6463

_______________________________________________
Pdns-users mailing list
[email protected]
https://mailman.powerdns.com/mailman/listinfo/pdns-users
OpenPGP_signature.asc (application/pgp-signature, 495 B)