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:
Triage: Triaged: D3 (Medium)

[8 Apr 2009 20:48] Jan Kneschke
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.
[14 Aug 2009 11:41] Sveta Smirnova
Thank you for the report.

Verified as described.

Probably related to bug #19806
[7 Jan 2013 21:02] Domas Mituzas
this is still an issue in all versions!!!!!111
[7 Jan 2013 22:17] James Day
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.
[8 Jan 2013 0:37] Domas Mituzas
says it is still broken in 5.6
[8 Jan 2013 0:59] Domas Mituzas
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.
[8 Jan 2013 7:56] Sergei Golubchik
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
[14 Feb 2013 5:51] Tatjana Nuernberg
> 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?
[16 Feb 2013 1:01] Ben Krug
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.
[19 Feb 2013 23:54] Domas Mituzas
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.
[24 Feb 2013 5:36] Tatjana Nuernberg
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!
[23 Sep 2014 15:37] Paul Dubois
Fixed in 5.7.5. Preliminary changelog entry:

For new connections, the server could allocate thread IDs that were
not actually free.
[25 Sep 2014 16:43] Paul Dubois
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.