Unauthenticated user login hangs?

[Phaethar]

Hey all,

Having some issues with a new MySQL system. We're running the 64-bit version of MySQL 4.1.11 on Fedora 3. MySQL was installed from the original RPMs. The server is a dual Opteron system, 4 gigs of memory, 15k RPM SCSI drives, gig network, etc. We replaced our old server, which was running version 4.0.17 with this box. For the most part, things have been working great (and very fast!) so far. However, a couple times we've had everything hang on us while waiting for mysql to authenticate a new connection. Running a show processlist shows the strings like this:

| 108160 | unauthenticated user | 192.168.XXX.XXX:3449 | NULL | Connect | NULL | login | NULL
| 108162 | unauthenticated user | 192.168.XXX.XXX:39610 | NULL | Connect | NULL | login | NULL |
| 108163 | unauthenticated user | 192.168.XXX.XXX:39611 | NULL | Connect | NULL | login | NULL |
| 108164 | unauthenticated user | 192.168.XXX.XXX:39612 | NULL | Connect | NULL | login | NULL |
| 108165 | unauthenticated user | 192.168.XXX.XXX:39613 | NULL | Connect | NULL | login | NULL |
| 108166 | unauthenticated user | 192.168.XXX.XXX:39614 | NULL | Connect | NULL | login | NULL |
| 108167 | unauthenticated user | 192.168.XXX.XXX:39615 | NULL | Connect | NULL | login | NULL |


We'll have 20-30 of these within a couple of minutes, and they just keep piling up. During this time, no new connections can be made. All new connections are coming from our internal network as well. About the only thing we've been able to do is start killing these processes until finding the one that's causing the problem, at which time everything else goes through. Even trying to kill the process takes a long time though, once we hit the one that apparently has the problem. Eventually it will usually die and things we work normally again for a little while. As of about noon today, this has happened 3 times already.

So, I've looked around a bit and have seen that this issue shows up from time to time, but nobody ever has a resolution for it, or even a cause to explain why it's happening. I'm hoping that someone has some ideas to try to attempt to work through this problem. I can provide my.cnf configuratons if they will help any.

Any suggestions would really be appreciated here.

Thanks.

 

[azzazzello]

any chance some program written to interract with the MySQL is the problem? Say, it runs in a cron, and if it fails to log in just keeps trying for X number of times, but its login informaton is incorrect...

 

[Phaethar]

The thing is that it isn't just 1 program that's interacting with MySQL. We have a lot of programs running all the time, and they all read the login info from the same place when they're called to login to MySQL. So, if the login info was not correct in this 1 place, then nothing would work. Since these same scripts are working 99% of the time, and only since we upgraded to the new version is this popping up, I have to think it's something in the way MySQL is handling user authentication requests. Something seems to be causing it to hang up, and in the process, prevent any other requests from going through.

 

[azzazzello]

Here's a shot in the dark...What version did you upgrade from? Version 4.1 uses a new authentication scheme, which is incompatible with old client libraries. Perhaps some of your client libraries are using and old-style authentication scheme, in which case it would not work. Read what I am talking about here

http://dev.mysql.com/doc/mysql/en/old-client.html

Easiest thing for you to test this would be to change the passwords to old encryption using OLD_PASSWORD in MySQL. The change is pretty unintrusive.