MySQL Bugs: #6070: "unauthenticated user" "reading from net" process hangs

Bug #6070	"unauthenticated user" "reading from net" process hangs
Submitted:	13 Oct 2004 20:17	Modified:	10 Apr 17:19
Reporter:	Jim Taylor
Status:	No Feedback
Category:	Server	Severity:	S2 (Serious)
Version:	4.1.10, 5.0.67	OS:	FreeBSD (FreeBSD 4.9)
Assigned to:		Target Version:

Description:
Our customers connect and disconnect from MySQL all day long.  Typically about 200
connections are active at any one time.  We've set net_read_timeout=30,
net_write_timeout=60, and wait_timeout=600 to try to prevent connections from hanging due
to network problems or users turning off PC's without disconnecting from the database.  

Normally, things work fine.  However about once a day, we'll notice the database is
running slow.  "Show Processlist" shows 500 connections instead of the usual 200, many of
which have been idle much longer than the 10-minute wait_timeout should allow.  Somewhere
on the "show processlist" report, there will be a process for User "unauthenticated user"
with Command "connect" and State "reading from net".

If we issue a "Kill" command for the "unauthenticated user" process, MySQL immediately
returns to normal.  Not only does the killed process go away, so do about 200 other
processes (which probably should have timed out long ago).

How to repeat:
Hard to do...happens randomly

Suggested fix:
Check wait_timeout logic to see why a "reading from net" process would not time out, and
why it would prevent other processes from timing out

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

What "more information" do you want?  "Re-read the instructions" is not helpful--I did
read them, and I believe I provided all the necessary information!

Hi,

Are you using the --skip-name-resolve option?  The gethostbyname() function on FreeBSD
4.x 
and earlier is not thread safe.  It can cause the server to hang or eat up 99% of the
cpu.

If you have not seen this page it is helpful as well as the blogs linked to from the
page:
http://dev.mysql.com/doc/mysql/en/FreeBSD.html

This as well as the other main "gotchas", if you will, with FreeBSD have been addressed
in 
FreeBSD 5.

Best Regards

Yes, we are using skip-name-resolve; the "Host" column on Show Processlist shows just IP
addresses (or "localhost" for local processes).

This problem is happening increasingly often (4 times so far today)!

Hi,

We need to be able to create a repeatable test case to proceed through this avenue. 
There are a 
myriad of things that could be causing these symptoms such as duplexing problems.  This
could 
take time and effor to track down.

If you do not have a support contract we now offer per incident support and you can also
use the 
free community based avenues:
lists.mysql.com
mysql.com/IRC

Best of luck

Could it be that these ~200 "unauthenticated user" connections came from the same
application (I mean, same process, same host) ?

How many new connections do you have in, say, a minute ?
(you said, 200 connections are active, but I'm asking about the incoming rate).

Did I understood correctly that you noticed the problem because MySQL became slow ?
(normally, waiting connections shouldn't take CPU time)

What binary do you use - our or from the ports ?
Was it libc_r or linuxthreads build ?

According to our ISP (who installed it) our MySQL install is
mysql-standard-4.0.18-unknown-freebsd4.7-i386.

We get about 2 connections per minute from a variety of hosts (the "unauthenticated user"
is not always from the same host).

I don't think "unauthenticated user" "reading from net" by itself slows performance.  The
problem is that MySQL waits for the "unauthenticated user" to complete the connection
before timing out any other processes.  If the connection never completes, wait_timeout
is ignored and the number of processes grows and grows (and I assume would hit
max_connections if we didn't manually kill the "unauthenticated user" process first).

I don't expect you guys to debug whatever weird network problem might prevent a
connection from completing.  But whatever MySQL code terminates timed-out processes
should work even if there's an incomplete connection.  And if the connection stays
incomplete for wait_timeout seconds, it also should be killed.  The fact that that
doesn't happen is the bug I'm reporting.  I'm hoping someone familiar with the
process-timeout part of MySQL should be able to fix it relatively easily....

Sorry, I wasn't able to repeat it (FreeBSD 4.7-RC2, so I'm sure close to 4.9 enough)

I modified libmysqlclient to sleep(3600) after it reads an auth packet from the server
but before it sends a reply back. 'SHOW PROCESSLIST' indeed shows "unauthenticated user"
processes that are "reading from net". But they all timeouts very fast (I was forking
them in a loop) - so I failed to repeat the problem.

The problem is still happening on our server.  Just now, we had 700 processes, I killed
the one "unauthenticated user" "reading from net" process and immediately 500 others
timed out and went away (leaving us with the expected 200 open processes).  

Is there anything else we can do to help isolate the problem?

The only thing I could think of is to provide us with some way to repeat the  problem.
E.g. a  C program, perl or shell script that, being run against mysqld server, makes your
situation to appear.

We upgraded to version 4.0.21 about a week ago and the problem has not appeared since. 
Since it was happening on a daily basis before, I'm cautiously optimistic that whatever
was causing it got fixed in the new version.

I have 4.1.7 Version of MySQL, OS is FreeBSD 5.x And sorry but new versions do not help to
fix this problem. There is some serious stuff. The DB server worked fine about one mouth,
and suddenly it slows down. In process list I see many lines begining width:
unauthenticated user...
Mysql gets about 2000~ requests in a minute, and `unauthenticated user` lines there are
about 200~ periodically..
Interesting is that this server exepts only some local IP adress. Firewall is used and so
on..
At night when load is low, we still have these `unauthenticated users` but only few.

I am using 4.1.11 (upgraded from 4.1.10 where the problem started) on Windows and am
having a similar problem.  

All my applications use a special user I created for access to the appropriate database. 
Suddenly today my users complained that all the applications had severly slowed down. 
When I opened MySQL Administrator and looked at the users they are all listed as
unauthenticated and status was Login.  

In my case the data reads and writes are performed, but at EXTREMLY slow rates.  

I do not know how to recreate the problem either.

We were experiencing a lot of performance problems -- not only the "unauthenticated user"
issue, but MySQL CPU utilization would suddenly start growing rapidly and within a few
seconds, the database would stop responding.

We switched to the "Linux Threads" implementation of MySQL (though we're still running on
FreeBSD).  We have had no performance problems since (even though our total traffic has
continued to increase).   I suggest giving that a try....

I'd like to know if any of the contributers to this bug are still experiencing problems?

This is specifically related to the "unauthenticated user"s in SHOW PROCESSLIST output.
Everyone should ensure they are using --skip-name-resolve at startup of the mysql server
to rule out slow DNS issues and are using the latest version.

Also, please ensure you are using the binaries provided by us.

We have always used skip-name-resolve, so DNS is not the cause of this problem, and we use
MySQL binaries.

We have not experienced the problem since switching to Linux Threads.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I don't think I'm able to provide any more information than Jim did, but we recently
encountered this problem after our hard drive space was 100% utilized (after an upgrade
... the old databases were never removed).  One of our ColdFusion servers kept spawning
all of these Unauthenticated User processes, with the same behavior (kill one and nearly
all of the rest were stopped instantly).  After we rebooted the ColdFusion server (not
the MySQL server), the problem was solved and all of these processes stopped.  So it
wasn't necessarily a problem with MySQL itself, but it looked like some problem with the
ODBC connectors on the ColdFusion server.

We are currently having issues with the "unauthenticated user - Connect - Reading from
net".
We have implemented the "skip-name-resolve" in the my.cnf file but we are still recieving
these "unauthenticated user" coming up every couple of seconds.
There are only about 2 present at any time however since I have noticed this user the
systems speed slows down considerably and then once killed speeds up again.
We have about an average of 10 jobs per second on this server which is a Dual, Dual Core
3.2GHz machine with 4GB of ram.
Does anyone have any other ideas?

See bug #9678 fixed in 4.0.28, 4.1.22, 5.0.25 and 5.1.12 and please report whether the
problem still exists with those versions of MySQL or later.

We still have the same problem on 5.0.27. We are using the Linux x86 generic RPM
(statically linked against glibc 2.2.5) downloads. 

Did it happen to anyone with 5.0.25 above which the fix should be in? Should I move to a
later version?

thanks.

Please do try a later version, if only because of the large number of other changes made
since 5.0.27.

Hy everybody !
i have the same problem..
im on gentoo 
on raid 1
running dev-db/mysql-5.0.60-r1
i have averagely 50 persistant local connexions (normal)
but when i get a few connexions from my website averagely 5/sec
using skip-name-resolve changed anything ...
My showprocesslist gives : 
Supprimer  	184  	unauthenticated user  	88.191.50.**:46122  	aucune  	Connect  	
	Reading from net  	---
Supprimer 	186 	unauthenticated user 	88.191.50.**:46124 	aucune 	Connect 		Reading from
net 	---
Supprimer 	189 	unauthenticated user 	88.191.50.**:46130 	aucune 	Connect 		Reading from
net 	---

i have reinstalled mysql and now its ok

after a few mins it come back like before,

PROBLEM NOT SOLVED !!!!

HEEELLLLLPPPP PLZ !!!!

Same problem with 5.0.67 AND 5.1

Thank you for the feedback.

This can be duplicate of already verified feature request: bug #27465.

To check this is not new problem in time when you see such behavior next time please
compare number of threads connected and value of max_connections which should be equal or
higher than connected threads + 1.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi,

I already started MySQL with --skip-name-resolve but still found a lot of requests that
show "unauthenticated user" in the process list. Here is the information that I copy
using mtop.

  
localhost  mysqld 5.1.37-log up 5 day(s), 19:59 hrs
22 threads: 2 running, 1 cached. Queries/slow: 15/0 Cache Hit: 99.99%
Opened tables: 0  RRN: 237  TLW: 94.8K  SFJ: 0  SMP: 0  QPS: 2

ID       USER     HOST             DB           TIME   COMMAND STATE        INFO
7710678  root     localhost                            Query                show full
processlist
7711295  wsd      17116.2.12:40716 vstco               Sleep
7711321  wsd      17116.2.12:40768 vstco               Sleep
7711324  wsd      17116.2.12:40772 vstco               Sleep
7711329  wsd      17116.2.12:40782 vstco               Sleep
7711332  wsd      17116.2.12:40805 vstco               Query   Sorting resu SELECT ...
FROM user AS user 
7711331  wsd      17116.2.12:40804 vstco               Sleep
7711340  add      17116.2.12:40816 openx               Sleep
7711343  wsd      17116.2.12:40820 vstco               Sleep
7711346  ada      17116.2.12:40823 openx               Query   checking pri SELECT ...
FROM application_variable 
7711345  ada      17116.2.12:40822 openx               Sleep
7711349  wsd      17116.2.12:40827 vstco               Sleep
7711351  ada      17116.2.12:40831 open2               Sleep
7711352  ada      17116.2.12:40834 open2               Sleep
7711354  ada      17116.2.12:40837 open2               Sleep
7711353  ada      17116.2.12:40836 open2               Sleep
7711355  unauthen 17116.2.12:40840                     Connect Reading from
7711356  wsd      17116.2.12:40841                     Sleep
7711357  unauthen 17116.2.12:40842                     Connect Reading from
7711358  unauthen 17116.2.12:40843                     Connect Reading from
7711359  ada      17116.2.12:40844                     Sleep
7711360  unauthen 17116.2.12:40845                     Connect Reading from