migrating from mydns to bind9.4/DLZ extensions

[Vince Hoffman]

Hi,
	I previously sent this to the DLZ-testers list but with bind9.4 coming which 
has the DLZ extensions integrated I thought her might be a good place to ask 
as well. Sorry if you get it twice because of this 
	I'm trying to query an existing mydns mysql database from a bind9-dlz
server as mydns is getting too flaky to be usable anymore. Does anyone
have a sample dlz config for this as i'm getting to the point of giving
in. I am runnning Freebsd 6.1 and the bind9-dlz port (bind 9.3.2 and appropriate patches)

My current DLZ config looks like this:

dlz "Mysql zone" {
        database "mysql
        {host=localhost dbname=mydns user=root}
        {select origin from soa where origin = '%zone%.' }
        {select rr.ttl, rr.type, rr.aux, case when rr.type='TXT' then
concat('\"',rr.data,'\"') when rr.type='SOA' then concat_ws(' ',
rr.data, soa.mbox, soa.serial, soa.refresh, soa.retry, soa.expire,
soa.minimum) else rr.data end from rr,soa where soa.origin='%zone%.' and
rr.name='%record%' and rr.zone=soa.id}";
};


(stolen almost completely from the examples page at
http://bind-dlz.sourceforge.net/mysql_example.html with a little
tweaking for the mydns schema)

Running the query manually gives:
(jhary at prawn)$mysql -uroot -BANDmydns -e "select rr.ttl, rr.type,
rr.aux, case when rr.type='TXT' then concat('\"',rr.data,'\"') when
rr.type='SOA' then concat_ws(' ', rr.data, soa.mbox, soa.serial,
soa.refresh, soa.retry, soa.expire, soa.minimum) else rr.data end from
rr,soa where soa.origin='example.net.' and rr.name='' and rr.zone=soa.id;"

3600    A       0       212.84.xx.xx
3600    A       0       212.84.xx.xx
3600    MX      30      foo.hosts.net.uk.
3600    MX      30      bar.hosts.net.uk.
3600    MX      50      mail1.hosts.net.uk.
3600    MX      50      mail2.hosts.net.uk.
3600    NS      0       ns0.example.net.
3600    NS      0       ns1.example.net.
3600    NS      0       ns2.example.net.

(names/ips changed to protect the guilty)

I think the issue is that mydns doesnt have SOA as a type but as a
separate table.

(jhary at prawn)$mysql -u root -D mydns -e "describe soa;"

+----------+------------------+------+-----+---------+----------------+                                                                 
|Field     | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| origin   | varchar(255)     | NO   | UNI |         |                |
| ns       | varchar(255)     | NO   |     |         |                |
| mbox     | varchar(255)     | NO   |     |         |                |
| serial   | int(10) unsigned | NO   |     | 1       |                |
| refresh  | int(10) unsigned | NO   |     | 28800   |                |
| retry    | int(10) unsigned | NO   |     | 3600    |                |
| expire   | int(10) unsigned | NO   |     | 604800  |                |
| minimum  | int(10) unsigned | NO   |     | 86400   |                |
| ttl      | int(10) unsigned | NO   |     | 86400   |                |
| notifySlaves | char(1)      | NO   | MUL | n       |                |
+----------+------------------+------+-----+---------+----------------+
[16:17:13:~]
(jhary at prawn)$mysql -u root -D mydns -e "describe rr;"
+-------+--------------------------------------------------------------+------+-----+---------+----------------+
| Field | Type                                                         |
Null | Key | Default | Extra          |
+-------+--------------------------------------------------------------+------+-----+---------+----------------+
| id    | int(10) unsigned                                             |
NO   | PRI | NULL    | auto_increment |
| zone  | int(10) unsigned                                             |
NO   | MUL | 0       |                |
| name  | varchar(64)                                                  |
NO   |     |         |                |
| type  | enum('A','AAAA','CNAME','HINFO','MX','NS','PTR','SRV','TXT') |
YES  | MUL | NULL    |                |
| data  | varchar(255)                                                 |
NO   |     |         |                |
| aux   | int(10) unsigned                                             |
NO   |     | 0       |                |
| ttl   | int(10) unsigned                                             |
NO   |     | 86400   |                |
+-------+--------------------------------------------------------------+------+-----+---------+----------------+


(excuse nasty linewraps)
Anyone have any suggestions? i'm getting way out of my depth with the
SQL queries. Obviously it would make more sense to reformat this to a
more sensible schema but currently thats not an option due to database size and other apps that access it.

Thanks,
Vince