Redundant connection to powerdns database

[edaleand]

Possibility of defining multiple databases in powerdns configuration

Hello. I am deploying powerdns with phpipam as main source of record entries. In powerdns database settings, is it somehow possible to point phpipam to two different master databases for redundacy purposes? Replication of the databases would be handled by the MYSQL backend. The database name, along with login, password and port would be the same, only the IPs would be configured double.

Syntax with semicolon is accepted, but when I poweroff the first server it stops working.

[phpipam]

Hi @edaleand ,

can you test if this code works for you ? It will try to connect to all databases defined by semicolon-delimited values, it will take first it is able to connect to... I can add warning output also if it fails to connect to primary host...

Replace function db_set in functions/classes/class.PowerDNS.php:

    /**
     * Sets database connection
     *
     * @access private
     */
    private function db_set () {
        // decode values form powerDNS
        $this->db_settings = strlen($this->settings->powerDNS)>10 ? pf_json_decode($this->settings->powerDNS) : pf_json_decode($this->db_set_db_settings ());

        // if comma delimited host
        if (strpos($this->db_settings->host, ";")!==false) {
            // get all databases
            $this->db_settings->host = explode(";", $this->db_settings->host);
            // check each, use first we are able to connect to
            foreach ($this->db_settings->host as $key=>$host) {
                // set connection
                unset($this->Database_pdns);
                $this->Database_pdns = new Database_PDO ($this->db_settings->username, $this->db_settings->password, $host, $this->db_settings->port, $this->db_settings->name);
                // check connection, try untill it fails
                if(!$this->db_check ()) {
                    $this->db_check_error = $this->error;
                }
                else {
                    break;
                }
            }
        }
        else {
            // set connection
            $this->Database_pdns = new Database_PDO ($this->db_settings->username, $this->db_settings->password, $this->db_settings->host, $this->db_settings->port, $this->db_settings->name);
        }
    }

[phpipam]

Please pull master branch and test.

[edaleand]

Having issues with new branch. Tried installation from scratch, but get this error upon database creation through web UI.

Cannot install sql SCHEMA file: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
query that failed:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL DEFAULT '',
  `authMethod` INT(2)  NULL  DEFAULT 1,
  `password` CHAR(128) DEFAULT NULL,
  `groups` varchar(1024) DEFAULT NULL,
  `role` text,
  `real_name` varchar(128) DEFAULT NULL,
  `email` varchar(254) DEFAULT NULL,
  `domainUser` binary(1) DEFAULT '0',
  `widgets` VARCHAR(1024)  NULL  DEFAULT 'statistics;favourite_subnets;changelog;top10_hosts_v4',
  `lang` INT(11) UNSIGNED  NULL  DEFAULT '9',
  `favourite_subnets` VARCHAR(1024)  NULL  DEFAULT NULL,
  `disabled` ENUM('Yes','No')  NOT NULL  DEFAULT 'No',
  `mailNotify` ENUM('Yes','No')  NOT NULL  DEFAULT 'No',
  `mailChangelog` ENUM('Yes','No')  NOT NULL  DEFAULT 'No',
  `passChange` ENUM('Yes','No')  NOT NULL  DEFAULT 'No',
  `editDate` TIMESTAMP  NULL  ON UPDATE CURRENT_TIMESTAMP,
  `lastLogin` TIMESTAMP  NULL,
  `lastActivity` TIMESTAMP  NULL,
  `compressOverride` ENUM('default','Uncompress') NOT NULL DEFAULT 'default',
  `hideFreeRange` tinyint(1) DEFAULT '0',
  `menuType` ENUM('Static','Dynamic')  NOT NULL  DEFAULT 'Dynamic',
  `menuCompact` TINYINT  NULL  DEFAULT '1',
  `2fa` BOOL  NOT NULL  DEFAULT '0',
  `2fa_secret` VARCHAR(32)  NULL  DEFAULT NULL,
  `theme` VARCHAR(32)  NULL  DEFAULT '',
  `token` VARCHAR(24)  NULL  DEFAULT NULL,
  `token_valid_until` DATETIME  NULL,
  `module_permissions` varchar(255) DEFAULT '{"vlan":"1","l2dom":"1","vrf":"1","pdns":"1","circuits":"1","racks":"1","nat":"1","pstn":"1","customers":"1","locations":"1","devices":"1","routing":"1","vaults":"1"}',
  `compress_actions` TINYINT(1)  NULL  DEFAULT '1',
  PRIMARY KEY (`username`),
  UNIQUE KEY `id_2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Same error if I create database manually and try to import db/SCHEMA.sql

[phpipam]

Which mysql version do you have ?

For < 5.7 change varchar(255) to varchar(191) for table users (username) and devices (hostname).

[edaleand]

Thank you. That did the trick.

The dual datbase config seems to work well. It now says Array under Database.

I still had to change the db_set function with the code you supplied. I was under the impression that it was now part of the branch I pulled. Also, when the main DB is down the whole PowerDNS admin interface is very slow. Not a biggie.

Thanks! Great work.