Re: mysql dlz centos error "extra input text"

On Tue, Aug 23, 2011 at 6:21 AM, Roger Meier <[email protected]> wro=
te:

> 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

Haven't done that yet, hope to soon.

> Here is the dlz part of my bind configuration:
> =A0 =A0 =A0 =A0{SELECT zone FROM dns_zones WHERE zone =3D '%zone%'}

It wasn't obvious to me if the RT #21294 is in this released version
or not.  In my (9.7.3-P1) rpms, I have to use $zone$ and $record$
instead of the older method of % signs.  Though IIRC, it complained
about it at startup and wouldn't start, so that won't be your problem.

> =A0 =A0 =A0 =A0{SELECT z.ttl, r.type, r.mxpriority,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0CASE WHEN LOWER(r.type)=3D'txt' THEN
> CONCAT('\"',r.data,'\"') ELSE r.data END
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM =A0dns_records r,dns_zones z
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0WHERE z.id =3D r.zoneid AND z.zone =3D '%z=
one%' AND r.host =3D
> '%record%' AND NOT (LOWER(r.type) =3D 'soa' OR LOWER(r.type) =3D 'ns')}
> =A0 =A0 =A0 =A0{SELECT z.ttl, r.type, r.mxpriority, r.data, z.contact,
> z.serial, z.refresh, z.retry, z.expire, z.minimum
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM dns_records r, dns_zones z
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0WHERE z.id =3D r.zoneid AND z.zone =3D '%z=
one%' AND
> (LOWER(r.type) =3D 'soa' OR LOWER(r.type) =3D 'ns')}

The first query gets all but SOA (and NS) records.  The second query
gets your SOA records.  The answer it gets back is:

> (8, 1, '@', 'SOA', 'postmaster.example.com.', NULL, 20);

'postmaster.example.com.' is not a full SOA record.  However, it seems
like the following query should result in the correct answers (just
from looking at your data) because it should concat all the r.data and
z.* entries it pulls up.

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 =3D r.zoneid AND z.zone =3D 'example.org'
    AND(LOWER(r.type) =3D 'soa' OR LOWER(r.type) =3D 'ns')

There doesn't seem to be any extra tabs or spaces in your paste that I can =
see.

> CREATE TABLE IF NOT EXISTS `dns_servers` (
> =A0`id` int(11) NOT NULL AUTO_INCREMENT,
> =A0`host` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
> =A0`description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
> =A0PRIMARY KEY (`id`)
> ) ENGINE=3DInnoDB =A0DEFAULT CHARSET=3Dutf8 COLLATE=3Dutf8_unicode_ci
> AUTO_INCREMENT=3D3 ;
>
> INSERT INTO `dns_servers` (`id`, `host`, `description`) VALUES
> (1, 'ns1.example.com', 'Primary DNS');

The above data and table isn't used anywhere in your posted config.

> 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);

So the above is the data tha tit should be using to make the SOA
record.  To be honest, your dns_zones.contact field seems to be the
same as your dns_records.data field in the SOA record.  To me they
seem interchangeable, so I see nothing wrong with the lookup that
you're doing.

> 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.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
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM dns_records r, dns_zones z
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0WHERE z.id =3D r.zoneid AND z.zone =3D 'ex=
ample.com' AND
> (LOWER(r.type) =3D 'soa' OR LOWER(r.type) =3D 'ns')
>
> 23-Aug-2011 15:30:13.467 dns_rdata_fromtext: buffer-0x7f15faebe460:1:
> near 'postmaster.example.com.': extra input text

It's complaining about that r.data field.  I actually store my SOA as
a complete string, so I don't do the same split style lookups as you
and can't tell you authoritatively that it should work, but I can tell
you that according to the docs, it should work.

Good luck...               Todd
-- =

If Americans could eliminate sugary beverages, potatoes, white bread,
pasta, white rice and sugary snacks, we would wipe out almost all the
problems we have with weight and diabetes and other metabolic
diseases. -- Dr. Walter Willett, Harvard School of Public Health

---------------------------------------------------------------------------=
---
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