How to Fix ‘Backend error: GSQLBackend unable to list keys’ in PowerDNS

After upgrading to Ubuntu 22.04, you may encounter an error when attempting to start PowerDNS. The error message typically looks like this:

Backend error: GSQLBackend unable to list keys: Could not prepare statement: select cryptokeys.id, flags, active, published, content from domains, cryptokeys where cryptokeys.domain_id=domains.id and name=?: Unknown column 'published' in 'field list'

This guide will walk you through the steps to resolve this issue.

Pre-requisites

• Access to a terminal window/command line
• A user account with sudo or root privileges
• MySQL access to the backend database that supports PowerDNS

Steps to Fix the Issue

1. Backup the Current Database

Before making any changes, it’s crucial to back up the current database to prevent data loss.

mysqldump -u root -p -B pdns > pdns_backup.sql

2. Add the Missing Column to the “cryptokeys” Table

The error indicates that the published column is missing from the cryptokeys table. We’ll add this column to the table.

mysql -u root -p
use pdns;
ALTER table cryptokeys add column published BOOL DEFAULT 1 after active;

3. Restart PowerDNS

Finally, restart PowerDNS to apply the changes.

systemctl restart pdns

Conclusion

By following these steps, you should be able to resolve the ‘Backend error: GSQLBackend unable to list keys’ issue in PowerDNS. This guide can serve as a reference for future occurrences and help others facing the same problem.