mysql dlz centos error "extra input text"

Hello there

I have setup CentOS 6 with Bind 9.7 incl. DLZ support and chroot.

bind-9.7.0-5.P2.el6_0.1.x86_64
bind-chroot-9.7.0-5.P2.el6_0.1.x86_64
bind-sdb-9.7.0-5.P2.el6_0.1.x86_64

Here is the dlz part of my bind configuration:

dlz "mysql zone" {
        database "mysql
        {host=127.0.0.1 port=3306 dbname=4s_admin user=named
pass=4GrutIdvat3}
        {SELECT zone FROM dns_zones WHERE zone = '%zone%'}
        {SELECT z.ttl, r.type, r.mxpriority,
                CASE WHEN LOWER(r.type)='txt' THEN
CONCAT('\"',r.data,'\"') ELSE r.data END
                FROM  dns_records r,dns_zones z
                WHERE z.id = r.zoneid AND z.zone = '%zone%' AND r.host =
'%record%' AND NOT (LOWER(r.type) = 'soa' OR LOWER(r.type) = 'ns')}
        {SELECT z.ttl, r.type, r.mxpriority, r.data, z.contact,
z.serial, z.refresh, z.retry, z.expire, z.minimum
                FROM dns_records r, dns_zones z
                WHERE z.id = r.zoneid AND z.zone = '%zone%' AND
(LOWER(r.type) = 'soa' OR LOWER(r.type) = 'ns')}
        {SELECT z.ttl, r.type, r.host, r.mxpriority,
                CASE WHEN LOWER(r.type)='txt' THEN
CONCAT('\"',r.data,'\"') AS data ELSE r.data AS data END,
                z.contact, z.serial, z.refresh, z.retry, z.expire, z.minimum
                FROM dns_records r, dns_zones z
                WHERE z.id = r.zoneid AND z.zone = '%zone%' AND NOT
(LOWER(r.type) = 'soa' OR LOWER(r.type) = 'ns')}
        {SELECT z.zone FROM dns_zones z, dns_xfr x WHERE z.id = x.zoneid
AND z.zone = '%zone%' AND x.client = '%client%'}
        {UPDATE dns_records r, dns_zones z SET r.count = r.count + 1
WHERE z.id = r.zoneid AND z.zone = '%zone%'}";
};

Here is a sample dump of my database:

CREATE TABLE IF NOT EXISTS `dns_records` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `zoneid` int(11) NOT NULL,
  `host` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `type` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
  `data` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mxpriority` int(11) DEFAULT NULL,
  `count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
AUTO_INCREMENT=9 ;

INSERT INTO `dns_records` (`id`, `zoneid`, `host`, `type`, `data`,
`mxpriority`, `count`) VALUES
(2, 1, 'www', 'A', '192.168.6.64', NULL, 20),
(3, 1, '@', 'A', '192.168.6.64', NULL, 20),
(4, 1, 'www2', 'CNAME', 'www.example.com.', NULL, 20),
(5, 1, '@', 'MX', 'example.com.', 10, 20),
(6, 1, '@', 'NS', 'ns1.example.com.', NULL, 20),
(7, 1, '@', 'NS', 'ns2.example.com.', NULL, 20),
(8, 1, '@', 'SOA', 'postmaster.example.com.', NULL, 20);

CREATE TABLE IF NOT EXISTS `dns_servers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `host` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
AUTO_INCREMENT=3 ;

INSERT INTO `dns_servers` (`id`, `host`, `description`) VALUES
(1, 'ns1.example.com', 'Primary DNS');

CREATE TABLE IF NOT EXISTS `dns_xfr` (
  `zoneid` int(11) NOT NULL,
  `client` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  KEY `zone` (`zoneid`),
  KEY `client` (`client`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `dns_xfr` (`zoneid`, `client`) VALUES
(1, '192.168.0.3');

CREATE TABLE IF NOT EXISTS `dns_zones` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `zone` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `ttl` int(11) NOT NULL DEFAULT '3600',
  `refresh` int(11) NOT NULL DEFAULT '3600',
  `retry` int(11) NOT NULL DEFAULT '3600',
  `expire` int(11) NOT NULL DEFAULT '86400',
  `minimum` int(11) NOT NULL DEFAULT '3600',
  `serial` int(11) NOT NULL DEFAULT '2011082300',
  `contact` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT
'postmaster.example.com.',
  `serverid` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
AUTO_INCREMENT=2 ;

INSERT INTO `dns_zones` (`id`, `zone`, `ttl`, `refresh`, `retry`,
`expire`, `minimum`, `serial`, `contact`, `serverid`) VALUES
(1, 'example.com', 3600, 3600, 3600, 86400, 3600, 2011082300,
'postmaster.example.com.', 1);

When i now start bind with "named -u named -g -d 9 -t /var/named/chroot"
and make soa request "dig -t SOA @192.168.0.2 example.com"
i become the following entries in the output:

23-Aug-2011 15:30:13.462
Query String: SELECT zone FROM dns_zones WHERE zone = 'example.com'

23-Aug-2011 15:30:13.463
Query String: UPDATE dns_records r, dns_zones z SET r.count = r.count +
1 WHERE z.id = r.zoneid AND z.zone = 'example.com'

23-Aug-2011 15:30:13.466
Query String: SELECT z.ttl, r.type, r.mxpriority,
                CASE WHEN LOWER(r.type)='txt' THEN
CONCAT('"',r.data,'"') ELSE r.data END
                FROM  dns_records r,dns_zones z
                WHERE z.id = r.zoneid AND z.zone = 'example.com' AND
r.host = '@' AND NOT (LOWER(r.type) = 'soa' OR LOWER(r.type) = 'ns')

23-Aug-2011 15:30:13.466
Query String: SELECT z.ttl, r.type, r.mxpriority, r.data, z.contact AS
resp_person, z.serial, z.refresh, z.retry, z.expire, z.minimum
                FROM dns_records r, dns_zones z
                WHERE z.id = r.zoneid AND z.zone = 'example.com' AND
(LOWER(r.type) = 'soa' OR LOWER(r.type) = 'ns')

23-Aug-2011 15:30:13.467 dns_rdata_fromtext: buffer-0x7f15faebe460:1:
near 'postmaster.example.com.': extra input text
23-Aug-2011 15:30:13.467 dns_sdlz_putrr returned error. Error code was:
extra input text
23-Aug-2011 15:30:13.467
Query String: SELECT z.ttl, r.type, r.mxpriority,
                CASE WHEN LOWER(r.type)='txt' THEN
CONCAT('"',r.data,'"') ELSE r.data END
                FROM  dns_records r,dns_zones z
                WHERE z.id = r.zoneid AND z.zone = 'example.com' AND
r.host = '@' AND NOT (LOWER(r.type) = 'soa' OR LOWER(r.type) = 'ns')

23-Aug-2011 15:30:13.467
Query String: SELECT z.ttl, r.type, r.mxpriority, r.data, z.contact AS
resp_person, z.serial, z.refresh, z.retry, z.expire, z.minimum
                FROM dns_records r, dns_zones z
                WHERE z.id = r.zoneid AND z.zone = 'example.com' AND
(LOWER(r.type) = 'soa' OR LOWER(r.type) = 'ns')

23-Aug-2011 15:30:13.468 dns_rdata_fromtext: buffer-0x7f15faebe0b0:1:
near 'postmaster.example.com.': extra input text
23-Aug-2011 15:30:13.468 dns_sdlz_putrr returned error. Error code was:
extra input text
23-Aug-2011 15:30:13.468 client 192.168.2.203#47975: query failed
(SERVFAIL) for example.com/IN/SOA at query.c:4387

Perhaps i'm routine-blinded  but i don't find the part which is the
reason for the "extra input text" errors. :(

Greetings
Roger


------------------------------------------------------------------------------
Get a FREE DOWNLOAD! and learn more about uberSVN rich system, 
user administration capabilities and model configuration. Take 
the hassle out of deploying and managing Subversion and the 
tools developers use with it. http://p.sf.net/sfu/wandisco-d2d-2