Trying to upgrade PowerDNS - required database schema upgrade failed (Column length too big)

Written by - 0 comments

Published on - Listed in PowerDNS MySQL Databases


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?

ERROR 1074 (42000): Column length too big

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.

latin1 charset was added after our PowerDNS setup

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.

PowerDNS mysql schema using latin1 as default charset for tables

If PowerDNS has switched to latin1, it's safe to assume we can switch our existing table from utf8mb3 to latin1, too.

Change default charset of domains table

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!

Schema upgrade working now

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


Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.

RSS feed

Blog Tags:

  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