Interesting bug/oversight

--=_50a9ffd5-1f3f-4aff-a7a5-1c77a2f7caa0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

Just encountered an interesting issue.

I use DNS names instead of IPs in mysql grants. Yes, I'm aware of the performance impact, that's not an issue.

I just found out through failing logins that a server was still connecting to an old DNS server, and properly updated the resolv.conf. Commandline host lookups then returned correct results.

However, even after repeated flush hosts commands, the MySQL kept returning wrong results. Only after a full restart did it pick itself up and start doing proper lookups. I strongly suspect that this is due to it internally caching the nameserver, too, and not refreshing that along with the host cache on a flush hosts command.

Can anyone confirm this is the case, and wether or not a bug has been logged about it? I can't seem to find one.

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--=_50a9ffd5-1f3f-4aff-a7a5-1c77a2f7caa0--

Re: Interesting bug/oversight

--20cf301cc3c2e05b6b04a39e8a87
Content-Type: text/plain; charset=ISO-8859-1

Johan,

Consider also the DNS TTL.

If you flush hosts in MySQL it'll ask again the OS to resolve a name , but
if that is still in the DNS cache it could return that 'old' value instead
of querying the newly updated NS.

I'm not sure thou, may be test by restarting the name server cache
deamon */etc/rc.d/init.d/nscd
restart *

Claudio



2011/5/19 Johan De Meersman

> Just encountered an interesting issue.
>
> I use DNS names instead of IPs in mysql grants. Yes, I'm aware of the
> performance impact, that's not an issue.
>
> I just found out through failing logins that a server was still connecting
> to an old DNS server, and properly updated the resolv.conf. Commandline host
> lookups then returned correct results.
>
> However, even after repeated flush hosts commands, the MySQL kept returning
> wrong results. Only after a full restart did it pick itself up and start
> doing proper lookups. I strongly suspect that this is due to it internally
> caching the nameserver, too, and not refreshing that along with the host
> cache on a flush hosts command.
>
> Can anyone confirm this is the case, and wether or not a bug has been
> logged about it? I can't seem to find one.
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>



--
Claudio

--20cf301cc3c2e05b6b04a39e8a87--

Re: Interesting bug/oversight

--=_b407f622-472b-43db-9bc9-5b14b98bac99
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

----- Original Message -----

> From: "Claudio Nanni"

> Consider also the DNS TTL.
That should be irrelevant when changing DNS servers :-)

> If you flush hosts in MySQL it'll ask again the OS to resolve a name
> , but if that is still in the DNS cache it could return that 'old'
> value instead of querying the newly updated NS.
I know, but it's another DNS server so not applicable. Also, I did verify on the commandline :-)

> I'm not sure thou, may be test by restarting the name server cache
> deamon /etc/rc.d/init.d/nscd restart
Not running local caching. The host only runs MySQL which has it's own cache, so that would be a useless layer.

Nice try :-)

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--=_b407f622-472b-43db-9bc9-5b14b98bac99--

Re: Interesting bug/oversight

In the last episode (May 19), Johan De Meersman said:
> I use DNS names instead of IPs in mysql grants. Yes, I'm aware of the
> performance impact, that's not an issue.
>
> I just found out through failing logins that a server was still connecting
> to an old DNS server, and properly updated the resolv.conf. Commandline
> host lookups then returned correct results.
>
> However, even after repeated flush hosts commands, the MySQL kept
> returning wrong results. Only after a full restart did it pick itself up
> and start doing proper lookups. I strongly suspect that this is due to it
> internally caching the nameserver, too, and not refreshing that along with
> the host cache on a flush hosts command.
>
> Can anyone confirm this is the case, and wether or not a bug has been
> logged about it? I can't seem to find one.

I doubt that mysql calls anything other than gethostbyname() or
getaddrinfo(), so your behaviour is probably dependant on whatever OS you
are running and how often its local resolver re-checks resolv.conf. Usually
that's only once when a program starts. If you're running bind, nscd, or
some other intermediate DNS client on your machine, bouncing that should
work. If not, you'll need to bounce mysql.

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Interesting bug/oversight

----- Original Message -----
> From: "Dan Nelson"
>
> I doubt that mysql calls anything other than gethostbyname() or
> getaddrinfo(), so your behaviour is probably dependant on whatever OS
> you are running and how often its local resolver re-checks resolv.conf.
> Usually that's only once when a program starts. If you're running bind,
> nscd, or some other intermediate DNS client on your machine, bouncing that
> should work. If not, you'll need to bounce mysql.

Yep, that was my first though, too. The documentation also confirms that the daemon calls gethostbyaddr() and gethostbyname(). However, as I said, it failed to switch to the new nameserver upon changing the resolv.conf, and didn't until I kicked the daemon in the olives.

Production machine also pointed to the wrong DNS server, but since I can't just restart that (badly written Java apps go boom) it still hasn't switched. Adding the correct entry to /etc/hosts does work around the issue, further confirming that yes, it probably does use the standard resolver.

Random *nix people in the meantime confirm that this is not only a MySQL problem; although I can't help but wonder if it would be possible to work around it in the flush hosts procedure.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org