MySQL Bugs: #9545: PROCESSLIST isn't updated properly for new connection before a query is done

Bug #9545	PROCESSLIST isn't updated properly for new connection before a query is done
Submitted:	31 Mar 2005 22:36	Modified:	24 Jul 2006 23:22
Reporter:	Harrison Fisk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	MacOS (Mac OS X/Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
When a new connection is created, it has the wrong "Command" and "State".  They stay as "Connect" and "Reading from net" even while the connection has been created and they are idle.  They should go to the normal Sleep state after the person has successfully logged in.

How to repeat:
In one connection open a new connection, but do not perform any queries at all (including ones for the client prompt setting).

In a second connection issue a SHOW PROCESSLIST command.  You should see a line like:

| 27 | root | localhost | NULL | Connect |    4 | Reading from net | NULL             |

It will stay like that until it issues a query.  It should be in sleep immediately.

Suggested fix:
Set the Command and State correctly after you finish connecting to mysqld.

We're also seeing this bug. We recently upgraded our server from 4.0.20 (standard mysql RPMs) to 4.1.14 (also standard mysql RPMs) on linux 2.6.12.3. The clients are still runing 4.0.20.

At first I thought we were seeing this bug:
http://bugs.mysql.com/bug.php?id=2814

But I'm not pretty sure it's actually this bug (9545) that we're seeing because:
1. The user field is correctly set, it is not "unauthenticated user"
2. All server names are in our /etc/hosts file and resolv.conf is correct
3. The host/ip field is correctly set and resolved to the hostname, it's not an IP
4. Tracking back to the original client process via netstat checks, it makes sense that it's clients that have connected, but not yet issued any queries
5. They do go away after a while, and with some stracing it does appear to be after a client has issued a query

I am pretty sure we are seeing this same bug in 4.1.11.  We are using a 3rd party clustering software (EMIC) based on this version.

Id: 1816961
User: app
Host: localhost
db: NULL
Command: Connect
Time: NULL
State: Reading from net
Info: NULL

I'm seeing this behaviour as well, on both CentOS 4 and an in-house fork of Red Hat 7.2 running 4.1.10 on the server-side.  Same thing happens with various client versions - 3.23.56, 4.0.20, and 4.1.10.  Doesn't make a difference whether the machine is loaded or not.

Possibly related, connections in this state seem to often linger longer than the wait_timeout value.  When this happens killing any one of the wedged processes will cause the rest to go away as well.

This appears to work correctly in 5.0. Since it is a minor issue, we won't be fixing it in 4.1.