An upgrade of PowerDNS to version 4.7 (and later) requires a schema change in the MySQL database (when MySQL is used as backend).
This schema change involves the following SQL queries:
ALTER TABLE domains ADD options VARCHAR(64000) DEFAULT NULL;
ALTER TABLE domains ADD catalog VARCHAR(255) DEFAULT NULL;
ALTER TABLE domains MODIFY type VARCHAR(8) NOT NULL;
CREATE INDEX catalog_idx ON domains(catalog);
Sounds easy, right?
However the first ALTER TABLE could lead to the following error:
mysql> ALTER TABLE domains ADD options VARCHAR(64000) DEFAULT NULL;
ERROR 1074 (42000): Column length too big for column 'options' (max = 21845); use BLOB or TEXT instead
As it turns out, the table "domains" is using the utf8 charset:
mysql> show create table domains;
| domains | CREATE TABLE `domains` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`master` varchar(128) DEFAULT NULL,
`last_check` int DEFAULT NULL,
`type` varchar(6) NOT NULL,
`notified_serial` int DEFAULT NULL,
`account` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=561 DEFAULT CHARSET=utf8mb3 |
According to an old mailing list entry and also to some other research for this MySQL error, this turns out to be an error only happening on utf8 charsets. The mentioned mailing list entry mentions to use latin1 instead.
By taking a closer look at the base schema file (modules/gmysqlbackend/schema.mysql.sql), all tables inside the PowerDNS database have a default charset of latin1. But why would our "domains" table have a charset of utf8mb3?
It turns out that the latin1 encoding was added after our initial installation of PowerDNS (which happened many years ago). As the CREATE TABLE queries (back then) did not mention a character set for the table, MySQL just applied the default charset, which was utf8mb3 at the time.
If PowerDNS has switched to latin1, it's safe to assume we can switch our existing table from utf8mb3 to latin1, too.
Every database/table alteration should happen AFTER a backup (mysqldump). You've been warned!
The table's default charset can easily be changed with yet another ALTER TABLE query:
mysql> ALTER TABLE domains CONVERT TO CHARACTER SET latin1;
Query OK, 11 rows affected (0.27 sec)
Records: 11 Duplicates: 0 Warnings: 0
To verify this worked, we can ask MySQL to show us the new CREATE TABLE output:
mysql> show create table domains;
| domains | CREATE TABLE `domains` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`master` varchar(128) DEFAULT NULL,
`last_check` int DEFAULT NULL,
`type` varchar(6) NOT NULL,
`notified_serial` int DEFAULT NULL,
`account` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=561 DEFAULT CHARSET=latin1 |
Voilà, the "domains" table now has a latin1 default charset!
Now with the domains table running with a latin1 charset, the schema upgrade queries all worked without an error:
mysql> ALTER TABLE domains ADD options VARCHAR(64000) DEFAULT NULL;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE domains ADD catalog VARCHAR(255) DEFAULT NULL;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE domains MODIFY type VARCHAR(8) NOT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> CREATE INDEX catalog_idx ON domains(catalog);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
And with this database schema upgrade applied, old PowerDNS version can successfully be upgraded to newer versions (4.7+).
AWS Android Ansible Apache Apple Atlassian BSD Backup Bash Bluecoat CMS Chef Cloud Coding Consul Containers CouchDB DB DNS Database Databases Docker ELK Elasticsearch Filebeat FreeBSD Galera Git GlusterFS Grafana Graphics HAProxy HTML Hacks Hardware Icinga Influx Internet Java KVM Kibana Kodi Kubernetes LVM LXC Linux Logstash Mac Macintosh Mail MariaDB Minio MongoDB Monitoring Multimedia MySQL NFS Nagios Network Nginx OSSEC OTRS Office PGSQL PHP Perl Personal PostgreSQL Postgres PowerDNS Proxmox Proxy Python Rancher Rant Redis Roundcube SSL Samba Seafile Security Shell SmartOS Solaris Surveillance Systemd TLS Tomcat Ubuntu Unix VMWare VMware Varnish Virtualization Windows Wireless Wordpress Wyse ZFS Zoneminder