MySQL Bugs: #44167: mysql->thread_id only exposes the lower 32bit of the connection-id

Bug #44167	mysql->thread_id only exposes the lower 32bit of the connection-id
Submitted:	8 Apr 2009 20:48	Modified:	25 Sep 2014 16:43
Reporter:	Jan Kneschke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.33, 5.0, 5.1, azalea bzr	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
the thread_id in the mysql-protocol wraps at the 4-byte boundary while in internal thread-id/connection-id increments up to the 8byte-boundary on 64bit systems as it is a ulong.

How to repeat:
# start mysqld
# attach with dbx to it
(dbx) assign thread_id = 4294967440U
(dbx) cont

$ mysql 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 144
Server version: 5.1.33 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql [localhost] {msandbox} ((none)) > select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|      4294967440 | 
+-----------------+
1 row in set (0.00 sec)

Suggested fix:
Fix the protocol to expose the thread-id correctly like using a length-encoded int instead.

Thank you for the report.

Verified as described.

Probably related to bug #19806

this is still an issue in all versions!!!!!111

See http://dev.mysql.com/doc/refman/5.6/en/news-5-6-9.html which has a lot of changes in this area. If you still have a problem after that version please let us know.

Related bugs addressed by the fixes there include bug #19806 and bug#65715.

says it is still broken in 5.6

from that page you linked:

"To avoid problems with mysql_thread_id() and mysql_kill(), do not use them. To get the connection ID, execute a SELECT CONNECTION_ID() query and retrieve the result."

"mysql_thread_id() is unchanged; the client/server protocal has only 4 bytes for the ID value. This function returns an incorrect (truncated) value for connection IDs larger than 32 bits and should be avoided."

so 5.6 still has same issue - API is still broken and returns incorrect data and there's no reasonable workaround, issuing "SELECT ..." is not feasible in many cases.

Doesn't seem to be fixed. The underlying type for the thread_id wasn't changed, it's still ulong in 5.6, not ulonglong

> API is still broken and returns incorrect data

It's the wire protocol that is the issue.
Hence why 

Bug#14236124: WRONG CONNECTION ID (THREAD ID) IN THE GENERAL AND SLOW QUERY LOGS
Bug#11745768: WRONG DATATYPE RETURNED FOR CONNECTION_ID()?
Bug#14575699: WRONG DATATYPE RETURNED FOR IS_USED_LOCK()?

were addressed (as well as SHOW PROCESSLIST, perfschema, etc.),
while this was not, and 

Bug#11753308: MYSQL_KILL() AND COM_PROCESS_KILL ONLY HANDLE 32-BIT IDS

mostly contains failsafes.

> issuing "SELECT ..." is not feasible in many cases.

Could you be more specific?

As mentioned in oracle bug report 11753308 , 

This function is very useful if you want to do any automated killing of threads, as it allows to get thread_id. Also, it does get thread_id without running a query, which in case of auto-reconnect is relatively useful, see, you don't always know when reconnect happened and thread_id can change without noticing, so it is better to have thread_id populated by C API and then fetching it at will. But this is broken forever and never been working on 64-bit platforms. 

Hence, large sites with many connections and auto-reconnect cannot safely use SELECT.  Not to mention the better performance and less overhead of using the C API, as described above.

Tatjana, I understand it is wire protocol issue, it has been wire protocol issue forever. 
I'm not questioning the fact that his bug has been fixed or not, I know it is not :-) 
And I know that other bugs were fixed (or what kind of workarounds were done, like "do not use this").
I've done enough research on this, as number of my exclamation marks indicates.

And indeed, as Ben says, "SELECT @@thread_id" is not feasible as:
a) You cannot run that from another thread, so you have to always pre-save it on existing thread
b) Auto-reconnect can change it at any time, and the only way to have it in definite form, is not to use auto-reconnect (which could also qualify as broken auto-reconnect ;-)
c) It is another roundtrip, which may be a bad idea to do before every query. Even with multiple query packet, that is significant tax on CPU and client code complexity.

The only workaround here is to actually query from another thread information_schema.processlist based on 32 bits that are saved, but this is lossy and dirty and hacky, so, again, not that feasible.

Domas,
 
As the bit with the exclamation marks goes, we know the 
issue persists, that's why this ticket is in "verified"
state. ;) 
 
The re-iteration of what has and hasn't been done and why
is to get a common starting point.  I figured you were 
aware of all those points, but this way, we might get 
input from a broader audience. 
 
Previous multi-patchset fixed what could be fixed immediately. 
We know the *limitation* exists, but our information on whether 
it's an *issue* for many customers, how they would rate its 
impact, and what use cases it comes up in (and how they work 
around them now) is somewhat limited.  Hence the asking about
the context of your thread-killing.  (E.g. if in scenario c)
threads were sufficiently short lived, getting the ID via SQL on 
connect, and then again whenever the mysql_thread_id() changes
might provide a stop-gap workaround that's significantly 
cheaper than SELECTing all the time.) 
 
As for b), it seems somewhat consistent with auto-reconnect
dropping the rest of your state on the floor. :-/
  
Anyway, if it had turned out to be more a theoretical complaint, 
then our answer would probably have been, "We know it's flawed, 
and we intend to deprecate the interface." 
 
Thanks for the input so far!

Fixed in 5.7.5. Preliminary changelog entry:

For new connections, the server could allocate thread IDs that were
not actually free.

Revised changelog entry.

If connection IDs went beyond the 32-bit limit and started over at 1,
the server now ensures that IDs still in use will not be reissued.