headers
Erol | 20 Jul 2009 21:16
Favicon

Frustrating mysql problem.

Im having the oddest and most frustrating problem with mysql.

Currently when I fire up bind-dlz and mysql all works as it
should. When I query for zones in the mysql database I get the
results I should. A dig against the nameserver returns the proper
results.

However after an hour or two I start getting the error below when
I try and run the same query.

mysql driver unable to return result set for findzone query

A dig will return a REFUSED.

The database is not on local disk but it is on the same (internal) network. When this
happens I am able to use the mysql client , connect to the
database and run the sql queries.

I ran a tcpdump on the nameserver. When the error occurs it is not
attempting to connect to the DB server.

Ive searched high and low to no avail. This has grown beyond
frustrating now.

Has anyone experienced anything like this .... anyone?

Thanks.

Best,
/erol
(Continue reading)

Permalink | Reply | 7 comments |
headers
daniele_athome | 20 Jul 2009 22:11
Picon

Re: Frustrating mysql problem.

On Mon, Jul 20, 2009 at 21:16, Erol<erol <at> easydns.com> wrote:
> Has anyone experienced anything like this .... anyone?
>

Hi, I experienced the same thing; this problem took me to change the
zone backend (I wrote a script that converts mysql zones in bind zone
files, in a cronjob).

During the first days, I thought it was a bad configuration of
queries, or missing fields etc. I fixed some things, and for a few
days it seemed to work well.
But just after 2 or 3 days it started to break again. After 3 weeks
spent to find where the problem was, I gave up.
Actually, I could find into bind source code what was wrong, but after
some studies I decided to use zone files (using a mysql2zone cronjob)
instead of mysql queries, because it is much faster (just a little
delay when bind starts).

Greetings,
--

-- 
daniele_athome

------------------------------------------------------------------------------
Enter the BlackBerry Developer Challenge  
This is your chance to win up to $100,000 in prizes! For a limited time, 
vendors submitting new applications to BlackBerry App World(TM) will have
the opportunity to enter the BlackBerry Developer Challenge. See full prize  
details at: http://p.sf.net/sfu/Challenge
Permalink | Reply | 0 comments |
headers
Chris Hastie | 20 Jul 2009 23:29
Picon

Re: Frustrating mysql problem.

On 20/07/09 20:16, Erol wrote:
> Im having the oddest and most frustrating problem with mysql.
> 
> Currently when I fire up bind-dlz and mysql all works as it
> should. When I query for zones in the mysql database I get the
> results I should. A dig against the nameserver returns the proper
> results.
> 
> However after an hour or two I start getting the error below when
> I try and run the same query.
> 
> mysql driver unable to return result set for findzone query

This might be old and out of date information, but bind-dlz does not
cope with reconnecting if the MySQL disappears for any reason.

This is the patch I used against 9.5.0

--- contrib/dlz/drivers/dlz_mysql_driver.c.orig 2007-11-15
09:08:05.000000000 +0000
+++ contrib/dlz/drivers/dlz_mysql_driver.c      2007-11-15
09:10:49.000000000 +0000
 <at>  <at>  -923,6 +923,13  <at>  <at> 
        pass = getParameterValue(argv[1], "pass=");
        socket = getParameterValue(argv[1], "socket=");

+        if(mysql_options((MYSQL *) dbi->dbconn, MYSQL_OPT_RECONNECT,
"1")) {
+               isc_log_write(dns_lctx, DNS_LOGCATEGORY_DATABASE,
+                             DNS_LOGMODULE_DLZ, ISC_LOG_ERROR,
(Continue reading)

Permalink | Reply | 2 comments |
headers
Igor Serebryany | 21 Jul 2009 03:13

Re: Frustrating mysql problem.

I haven't had a problem like this, but in our setup BIND talks to a
local MySQL via domain socket. Each of our three DNS servers has a local
MySQL daemon which is a replication slave to the master db.  This is
much faster than zone files, and it's really easy to add additional dns
servers and it keeps the load off of our master DB server and it has
security advantages... anyway, i'll stop raving ;)

--Igor

On Mon, Jul 20, 2009 at 03:16:27PM -0400, Erol wrote:
> Im having the oddest and most frustrating problem with mysql.
> 
> Currently when I fire up bind-dlz and mysql all works as it
> should. When I query for zones in the mysql database I get the
> results I should. A dig against the nameserver returns the proper
> results.
> 
> However after an hour or two I start getting the error below when
> I try and run the same query.
> 
> mysql driver unable to return result set for findzone query
> 
> A dig will return a REFUSED.
> 
> The database is not on local disk but it is on the same (internal) network. When this
> happens I am able to use the mysql client , connect to the
> database and run the sql queries.
> 
> I ran a tcpdump on the nameserver. When the error occurs it is not
> attempting to connect to the DB server.
(Continue reading)

Permalink | Reply | 0 comments |
headers
Todd Lyons | 21 Jul 2009 06:17
Gravatar

Re: Frustrating mysql problem.

On Mon, Jul 20, 2009 at 12:16 PM, Erol<erol <at> easydns.com> wrote:
> Im having the oddest and most frustrating problem with mysql.
>
> Currently when I fire up bind-dlz and mysql all works as it
> should. When I query for zones in the mysql database I get the
> results I should. A dig against the nameserver returns the proper
> results.
>
> However after an hour or two I start getting the error below when
> I try and run the same query.
>
> mysql driver unable to return result set for findzone query
>
> A dig will return a REFUSED.
>
> The database is not on local disk but it is on the same (internal) network. When this
> happens I am able to use the mysql client , connect to the
> database and run the sql queries.
>
> I ran a tcpdump on the nameserver. When the error occurs it is not
> attempting to connect to the DB server.
>
> Ive searched high and low to no avail. This has grown beyond
> frustrating now.
>
> Has anyone experienced anything like this .... anyone?

Yes, this was my issue:

CentOS52[root <at> ivdns51 ~]# grep OPTIONS /etc/sysconfig/named
(Continue reading)

Permalink | Reply | 1 comment |
headers
Erol | 21 Jul 2009 12:51
Favicon

Re: Frustrating mysql problem.

On Mon, Jul 20, 2009 at 09:17:25PM -0700, Todd Lyons wrote:

> Yes, this was my issue:
> 
> CentOS52[root <at> ivdns51 ~]# grep OPTIONS /etc/sysconfig/named
> # OPTIONS="whatever"     --  These additional options will be passed to named
> OPTIONS="-n 1"

We tried this already and to no avail.
We're going to patch the source with the mysql reconnect patch.

/erol

------------------------------------------------------------------------------
Enter the BlackBerry Developer Challenge  
This is your chance to win up to $100,000 in prizes! For a limited time, 
vendors submitting new applications to BlackBerry App World(TM) will have
the opportunity to enter the BlackBerry Developer Challenge. See full prize  
details at: http://p.sf.net/sfu/Challenge
Permalink | Reply | 0 comments |
headers
Erol | 21 Jul 2009 20:54
Favicon

Re: Frustrating mysql problem.

Thanks for the patch.
This looks to have done the trick.

/erol

On Mon, Jul 20, 2009 at 10:29:12PM +0100, Chris Hastie wrote:
> On 20/07/09 20:16, Erol wrote:
> > Im having the oddest and most frustrating problem with mysql.
> > 
> > Currently when I fire up bind-dlz and mysql all works as it
> > should. When I query for zones in the mysql database I get the
> > results I should. A dig against the nameserver returns the proper
> > results.
> > 
> > However after an hour or two I start getting the error below when
> > I try and run the same query.
> > 
> > mysql driver unable to return result set for findzone query
> 
> This might be old and out of date information, but bind-dlz does not
> cope with reconnecting if the MySQL disappears for any reason.
> 
> This is the patch I used against 9.5.0
> 
> --- contrib/dlz/drivers/dlz_mysql_driver.c.orig 2007-11-15
> 09:08:05.000000000 +0000
> +++ contrib/dlz/drivers/dlz_mysql_driver.c      2007-11-15
> 09:10:49.000000000 +0000
>  <at>  <at>  -923,6 +923,13  <at>  <at> 
>         pass = getParameterValue(argv[1], "pass=");
(Continue reading)

Permalink | Reply | 1 comment |
headers
Todd Lyons | 22 Jul 2009 02:56
Gravatar

Re: Frustrating mysql problem.

On Tue, Jul 21, 2009 at 11:54 AM, Erol<erol <at> easydns.com> wrote:
> Thanks for the patch.
> This looks to have done the trick.
>> This is the patch I used against 9.5.0
>>
>> --- contrib/dlz/drivers/dlz_mysql_driver.c.orig 2007-11-15
>> 09:08:05.000000000 +0000
>> +++ contrib/dlz/drivers/dlz_mysql_driver.c      2007-11-15
>> 09:10:49.000000000 +0000
>>  <at>  <at>  -923,6 +923,13  <at>  <at> 
>>         pass = getParameterValue(argv[1], "pass=");
>>         socket = getParameterValue(argv[1], "socket=");
>>
>> +        if(mysql_options((MYSQL *) dbi->dbconn, MYSQL_OPT_RECONNECT,
>> "1")) {
>> +               isc_log_write(dns_lctx, DNS_LOGCATEGORY_DATABASE,
>> +                             DNS_LOGMODULE_DLZ, ISC_LOG_ERROR,
>> +                             "Could not set database reconnect option");
>> +        }
>> +
>> +
>>         for (j=0; dbc == NULL && j < 4; j++)
>>                 dbc = mysql_real_connect((MYSQL *) dbi->dbconn, host,
>>                                          user, pass, dbname, port, socket,

I only ever glanced at it before, never seriously considered using it
because we just haven't needed to.  Your feedback though is prompting
me to add it in to handle those situations which will undoubtedly
occur with the addition of load.  Maybe we can lobby Adam to add it in
as part of his standard dlz patch set.  I'll look through RH's
(Continue reading)

Permalink | Reply | 0 comments |

Gmane