Re: mysql dlz centos error "extra input text"

Hi all

Ok i found now a solution for me. The problem was in the queries. Some
of the queries returned also the contact, serial, refresh, etc. when
the entries wasn't a SOA entry. This is strange, because i used the
sample queries which are spread in the internet. But now with this
sample below all works on my site :-) Perhaps this also helps someone.

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.data,
  CASE WHEN LOWER(r.type)='soa' THEN CONCAT_WS(' ',z.contact, z.serial,
z.refresh, z.retry, z.expire, z.minimum) ELSE '' END
  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,'\"') ELSE
r.data END
  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%'}";
};

Greetings
Roger

Am 24.08.2011 11:33, schrieb Roger Meier:
> Am 23.08.2011 17:04, schrieb Todd Lyons:
>>> Here is the dlz part of my bind configuration:
>>>        {SELECT zone FROM dns_zones WHERE zone = '%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.
> In downloaded the source rpm and checked the source and the CentOS 6
> version is still with the %. In the source: tseg->sql =
> isc_mem_strdup(mctx,isc_string_separate(&right_str,"%"));
>>>        {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')}
>> 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 = r.zoneid AND z.zone = 'example.org'
>>     AND(LOWER(r.type) = 'soa' OR LOWER(r.type) = 'ns')
>>
>> There doesn't seem to be any extra tabs or spaces in your paste that I can see.
> I also now tried to change the charset of the mysql tables, first i used
> utf8 then
> i changed to latin1 but the same issue.
>>> 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');
>> The above data and table isn't used anywhere in your posted config.
> Yes this is correct, i wan't use this table later, when the current
> solution works.
> I wan't to use the table later in a small php webgui.
>>> 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.
> yeah it shoul'd be "ns1.example.com." in the dns_records.data field of the
> SOA record, but if also i change this. The error still exists. I look
> through the source
> code and see that he only put all rows beginning with r.data together and
> add between the rows a space that it looks like:
>
> ns1.example.com. postmaster.example.com. 2011082300 3600 3600 86400 3600
>
> this and the ttl and the type (SOA) are arguments for the following
> function:
>
> result = dns_sdlz_putrr(lookup, safeGet(row[1]),ttl, tmpString);
>
>>> 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
>>>                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
>> 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
> Thanks :-)
>
> Greeting
> Roger
>
>
> ------------------------------------------------------------------------------
> EMC VNX: the world's simplest storage, starting under $10K
> The only unified storage solution that offers unified management 
> Up to 160% more powerful than alternatives and 25% more efficient. 
> Guaranteed. http://p.sf.net/sfu/emc-vnx-dev2dev
> _______________________________________________
> Bind-dlz-testers mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/bind-dlz-testers


------------------------------------------------------------------------------
EMC VNX: the world's simplest storage, starting under $10K
The only unified storage solution that offers unified management 
Up to 160% more powerful than alternatives and 25% more efficient. 
Guaranteed. http://p.sf.net/sfu/emc-vnx-dev2dev