PowerDNS MySQL schema update
Summary
Updated MySQL schema for PowerDNS prevents correct work.
/usr/share/pdns-backend-mysql/schema# cat ./4.1.0_to_4.2.0_schema.mysql.sql ALTER TABLE domains MODIFY notified_serial INT UNSIGNED DEFAULT NULL; ALTER TABLE records DROP COLUMN change_date;
Steps to reproduce
Implement PowerDNS with MySQL schema starting from 4.2.0 version of PowerDNS.
Correct behaviour
Schemas should be compatible
Environment
Server OS + version: Debian 10 and up ISPConfig version: 3.9.0 and up Software version of the related software: PowerDNS 4.2.0 and up
Proposed fix to ispconfig/server/plugins-available/powerdns_plugin.inc.php
58d57
< `change_date` int(11) default NULL,
150c149
< $app->db->query("INSERT INTO powerdns.records (domain_id, name, type, content, ttl, prio, change_date, ispconfig_id) VALUES (?, ?, 'SOA', ?, ?, 0, UNIX_TIMESTAMP(), ?)", $zone_id, $origin, $content, $ttl, $ispconfig_id);
---
> $app->db->query("INSERT INTO powerdns.records (domain_id, name, type, content, ttl, prio, ispconfig_id) VALUES (?, ?, 'SOA', ?, ?, 0, ?)", $zone_id, $origin, $content, $ttl, $ispconfig_id);
184c183
< $app->db->query("UPDATE powerdns.records SET name = ?, content = ?, ttl = ?, change_date = UNIX_TIMESTAMP() WHERE ispconfig_id = ? AND type = 'SOA'", $origin, $content, $ttl, $data["new"]["id"]);
---
> $app->db->query("UPDATE powerdns.records SET name = ?, content = ?, ttl = ? WHERE ispconfig_id = ? AND type = 'SOA'", $origin, $content, $ttl, $data["new"]["id"]);
328d326
< $change_date = time();
331c329
< $app->db->query("INSERT INTO powerdns.records (domain_id, name, type, content, ttl, prio, change_date, ispconfig_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", $zone_id, $name, $type, $content, $ttl, $prio, $change_date, $ispconfig_id);
---
> $app->db->query("INSERT INTO powerdns.records (domain_id, name, type, content, ttl, prio, ispconfig_id) VALUES (?, ?, ?, ?, ?, ?, ?)", $zone_id, $name, $type, $content, $ttl, $prio, $ispconfig_id);
394d391
< $change_date = time();
396c393
< $app->db->query("UPDATE powerdns.records SET name = ?, type = ?, content = ?, ttl = ?, prio = ?, change_date = UNIX_TIMESTAMP() WHERE ispconfig_id = ? AND type != 'SOA'", $name, $type, $content, $ttl, $prio, $ispconfig_id);
---
> $app->db->query("UPDATE powerdns.records SET name = ?, type = ?, content = ?, ttl = ?, prio = ? WHERE ispconfig_id = ? AND type != 'SOA'", $name, $type, $content, $ttl, $prio, $ispconfig_id);
Related log entries
16.06.2023-09:46 - WARNING - Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO powerdns.records (domain_id, name, type, content, ttl, prio, change_date, ispconfig_id) VALUES ('143', '', 'A', '**', '3600', '0', 1686897961, '4950') -> 1054 (Unknown column 'change_date' in 'field list')