search

kennethkalmer / powerdns-on-rails

Rails frontend for PowerDNS running MySQL or PostgreSQL backends
http://kennethkalmer.github.com/powerdns-on-rails/
MIT License
190 stars 54 forks source link

Integrating PowerDNS on Rails with an existing PowerDNS database #60

Open rodmur opened 9 years ago

rodmur commented 9 years ago

So I was trying to install PowerDNS on Rails on an existing installation of PowerDNS with a database I installed with their suggested schema, which you can see below:

https://doc.powerdns.com/md/authoritative/installation/

Which I think is the same as what is in /usr/share/doc/pdns/schema.mysql.sql, in the pdns-static package that the PowerDNS folks provide. However, when I do the "rake db:migrate" on a copy of my production database from PowerDNS I get:

$ rake db:migrate
==  CreateDomains: migrating ==================================================
-- create_table(:domains)
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Table 'domains' already exists: CREATE TABLE `domains` (`id` int(11) DEFAULT NULL au
to_increment PRIMARY KEY, `name` varchar(255), `master` varchar(255), `last_check` int(11), `type` 
varchar(255) DEFAULT 'NATIVE', `notified_serial` int(11), `account` varchar(255), `ttl` int(11) DEF
AULT 86400, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL) ENGINE=InnoDB/var/lib/g....

So I guess my question is: What is the best technique for integrating PowerDNS with Rails with an existing database? Should I just modify the schema?

rodmur commented 9 years ago

So this appears to be more of a problem between major releases of PowerDNS. Correct me if I'm wrong, but PowerDNS on Rails was designed more for 2.9 or earlier of PowerDNS, but the latest versions of PowerDNS Authoritative server is 3.4.1. So I did a diff on the schema I'm using for my production database and the one that PowerDNS on Rails creates. Here's a diff I got from mysqldiff, a perl tool.

## mysqldiff 0.43
## 
## Run on Tue Jan 20 16:01:02 2015
## Options: host=localhost, user=root, debug=0, password=XXXX
##
## ---   db: powerdns (host=localhost user=root)
## +++   db: powerdns_development (host=localhost user=root)

DROP TABLE comments;

DROP TABLE cryptokeys;

DROP TABLE domainmetadata;

ALTER TABLE domains CHANGE COLUMN account account varchar(255) DEFAULT NULL; # was varchar(40) DEFAULT NULL
ALTER TABLE domains CHANGE COLUMN name name varchar(255) DEFAULT NULL; # was varchar(255) NOT NULL
ALTER TABLE domains CHANGE COLUMN master master varchar(255) DEFAULT NULL; # was varchar(128) DEFAULT NULL
ALTER TABLE domains CHANGE COLUMN type type varchar(255) DEFAULT 'NATIVE'; # was varchar(6) NOT NULL
ALTER TABLE domains ADD COLUMN notes text;
ALTER TABLE domains ADD COLUMN user_id int(11) DEFAULT NULL;
ALTER TABLE domains ADD COLUMN ttl int(11) DEFAULT '86400';
ALTER TABLE domains ADD COLUMN updated_at datetime NOT NULL;
ALTER TABLE domains ADD COLUMN created_at datetime NOT NULL;
ALTER TABLE domains DROP INDEX name_index; # was UNIQUE (name)
ALTER TABLE domains ADD INDEX index_domains_on_name (name);
ALTER TABLE domains ENGINE=InnoDB DEFAULT CHARSET=latin1; # was ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=latin1
ALTER TABLE records CHANGE COLUMN content content varchar(255) NOT NULL; # was varchar(64000) DEFAULT NULL
ALTER TABLE records DROP COLUMN CONSTRAINT; # was records_ibfk_1 FOREIGN KEY (domain_id) REFERENCES domains (id) ON DELETE CASCADE
ALTER TABLE records CHANGE COLUMN type type varchar(255) NOT NULL; # was varchar(10) DEFAULT NULL
ALTER TABLE records DROP COLUMN disabled; # was tinyint(1) DEFAULT '0'
ALTER TABLE records CHANGE COLUMN domain_id domain_id int(11) NOT NULL; # was int(11) DEFAULT NULL
ALTER TABLE records DROP COLUMN auth; # was tinyint(1) DEFAULT '1'
ALTER TABLE records DROP COLUMN ordername; # was varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL
ALTER TABLE records CHANGE COLUMN ttl ttl int(11) NOT NULL; # was int(11) DEFAULT NULL
ALTER TABLE records CHANGE COLUMN name name varchar(255) NOT NULL; # was varchar(255) DEFAULT NULL
ALTER TABLE records ADD COLUMN created_at datetime DEFAULT NULL;
ALTER TABLE records ADD COLUMN updated_at datetime DEFAULT NULL;
ALTER TABLE records DROP INDEX domain_id; # was INDEX (domain_id)
ALTER TABLE records DROP INDEX nametype_index; # was INDEX (name,type)
ALTER TABLE records DROP INDEX recordorder; # was INDEX (domain_id,ordername)
ALTER TABLE records ADD INDEX index_records_on_name_and_type (name,type);
ALTER TABLE records ADD INDEX index_records_on_domain_id (domain_id);
ALTER TABLE records ADD INDEX index_records_on_name (name);
ALTER TABLE records ENGINE=InnoDB DEFAULT CHARSET=latin1; # was ENGINE=InnoDB AUTO_INCREMENT=3114 DEFAULT CHARSET=latin1
DROP TABLE supermasters;

DROP TABLE tsigkeys;

CREATE TABLE audits (
  id int(11) NOT NULL AUTO_INCREMENT,
  auditable_id int(11) DEFAULT NULL,
  auditable_type varchar(255) DEFAULT NULL,
  associated_id int(11) DEFAULT NULL,
  associated_type varchar(255) DEFAULT NULL,
  user_id int(11) DEFAULT NULL,
  user_type varchar(255) DEFAULT NULL,
  username varchar(255) DEFAULT NULL,
  action varchar(255) DEFAULT NULL,
  audited_changes text,
  version int(11) DEFAULT '0',
  created_at datetime DEFAULT NULL,
  comment varchar(255) DEFAULT NULL,
  remote_address varchar(255) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY auditable_index (auditable_id,auditable_type),
  KEY auditable_parent_index (associated_id,associated_type),
  KEY user_index (user_id,user_type),
  KEY index_audits_on_created_at (created_at),
  KEY associated_index (associated_id,associated_type)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE auth_tokens (
  id int(11) NOT NULL AUTO_INCREMENT,
  domain_id int(11) DEFAULT NULL,
  user_id int(11) DEFAULT NULL,
  token varchar(255) NOT NULL,
  permissions text NOT NULL,
  created_at datetime NOT NULL,
  updated_at datetime NOT NULL,
  expires_at datetime NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE macro_steps (
  id int(11) NOT NULL AUTO_INCREMENT,
  macro_id int(11) DEFAULT NULL,
  action varchar(255) DEFAULT NULL,
  record_type varchar(255) DEFAULT NULL,
  name varchar(255) DEFAULT NULL,
  content varchar(255) DEFAULT NULL,
  ttl int(11) DEFAULT NULL,
  prio int(11) DEFAULT NULL,
  position int(11) NOT NULL,
  active tinyint(1) DEFAULT '1',
  note varchar(255) DEFAULT NULL,
  created_at datetime NOT NULL,
  updated_at datetime NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE macros (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) DEFAULT NULL,
  description varchar(255) DEFAULT NULL,
  user_id int(11) DEFAULT NULL,
  active tinyint(1) DEFAULT '0',
  created_at datetime NOT NULL,
  updated_at datetime NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE record_templates (
  id int(11) NOT NULL AUTO_INCREMENT,
  zone_template_id int(11) DEFAULT NULL,
  name varchar(255) DEFAULT NULL,
  record_type varchar(255) NOT NULL,
  content varchar(255) NOT NULL,
  ttl int(11) NOT NULL,
  prio int(11) DEFAULT NULL,
  created_at datetime NOT NULL,
  updated_at datetime NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE schema_migrations (
  version varchar(255) NOT NULL,
  UNIQUE KEY unique_schema_migrations (version)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT,
  login varchar(255) DEFAULT NULL,
  email varchar(255) DEFAULT NULL,
  encrypted_password varchar(128) NOT NULL DEFAULT '',
  password_salt varchar(255) NOT NULL DEFAULT '',
  created_at datetime DEFAULT NULL,
  updated_at datetime DEFAULT NULL,
  remember_token varchar(255) DEFAULT NULL,
  remember_token_expires_at datetime DEFAULT NULL,
  confirmation_token varchar(255) DEFAULT NULL,
  confirmed_at datetime DEFAULT NULL,
  state varchar(255) DEFAULT 'passive',
  deleted_at datetime DEFAULT NULL,
  admin tinyint(1) DEFAULT '0',
  auth_tokens tinyint(1) DEFAULT '0',
  confirmation_sent_at datetime DEFAULT NULL,
  reset_password_token varchar(255) DEFAULT NULL,
  remember_created_at datetime DEFAULT NULL,
  reset_password_sent_at datetime DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE zone_templates (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) DEFAULT NULL,
  ttl int(11) DEFAULT '86400',
  created_at datetime NOT NULL,
  updated_at datetime NOT NULL,
  user_id int(11) DEFAULT NULL,
  type varchar(255) DEFAULT 'NATIVE',
  master varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;