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

Written by Claudio Kuenzler - 0 comments

Published on October 2nd 2024 - 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.

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.