Re: 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 18:34:43 -0700
Newsgroups	gmane.network.dns.powerdns.user
Message-ID	<[email protected]>
Just to add a bit more, I found this exact issue listed on github but 
have applied the proposed fix and am still experiencing the daemon failure.

https://github.com/PowerDNS/pdns/issues/11892

Cheers,
-Chris

On 8/8/24 4:58 PM, Chris Moody via Pdns-users wrote:
> 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

-- 
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)