MySQL Bugs: #19806: Wrong datatype returned for CONNECTION

Bug #19806	Wrong datatype returned for CONNECTION_ID()?
Submitted:	14 May 2006 18:40	Modified:	23 Aug 2012 16:28
Reporter:	Beat Vontobel (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:	5.0.21	OS:	Any (any)
Assigned to:		CPU Architecture:	Any

Description:
I'm not 100% sure about this (I'm no C programmer), but I just checked out the source to verify some behaviour of the server and I realized that the thread_id internally is of type "ulong" which is defined as "unsigned long" but the implementation of CONNECTION_ID() converts the value to a signed "longlong" value first and then returns it as a signed INT of width 10 at the SQL level.

Shouldn't the datatype of the return value of CONNECTION_ID() at the SQL level be an INT UNSIGNED (there are no negative values possible anyway)? Or what will happen if the connection id exceeds the signed range on a busy server? (That's actually what I wanted to check out.)

Sorry, if I got something wrong here...

How to repeat:
mysql> CREATE TABLE t SELECT CONNECTION_ID();
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESCRIBE t;
+-----------------+---------+------+-----+---------+-------+
| Field           | Type    | Null | Key | Default | Extra |
+-----------------+---------+------+-----+---------+-------+
| CONNECTION_ID() | int(10) | NO   |     | 0       |       | 
+-----------------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

Suggested fix:
Possibly make CONNECTION_ID() return an INT UNSIGNED.

Thank you for a problem report. I think, it is a reasonable feature request. But I do not think it is a bug, as all possible connection IDs will fit into INT(10), even signed, aren't they?

I absolutely agree with you that this is probably not the most urgent bug. ;-) But I don't think it's a feature request.

After all it's just an error to return an unsigned value as signed to the user. And with 69 connections per second you reach the critical point within a year - and yes, there are in fact MySQL servers with an uptime of one year out there (not 5.0 yet, of course): just checked one of the machines I still manage for my previous employer (luckily it didn't have to handle 69 connections per second). I wish I can do that some time with a 5.0 release as well ;-).

See also bug #44167

Noted in 5.7.0 changelog.

Connection ID (thread ID) values greater than 32 bits can occur on
some systems (such as 64-bit systems), causing these problems:

* Connection IDs written to the general query log and slow query log
  were incorrect. This was true for logging to both files and tables.

* The CONNECTION_ID() function could return a value with a data type
  too small for values larger than 32 bits.

* The mysql_thread_id() and mysql_kill() C API functions did not handle
  ID values larger than 32 bits. This could result in killing the wrong
thread; for example, if you invoked mysql_kill(mysql_thread_id()).

Connection IDs now are permitted to be 64-bit values when possible,
which has these effects:

* Connection IDs are logged correctly to the general query log and slow
  query log.

  Note: This change involves a modification to the log tables, so after
  upgrading to this release, you must run mysql_upgrade and restart the
  server.

* CONNECTION_ID() returns a data type appropriate for values larger
  than 32 bits.

* 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.

* mysql_kill() still cannot handle values larger than 32 bits but to
  guard against killing the wrong thread now returns an error in these
  cases:

  * If given an ID larger than 32 bits, mysql_kill() returns a
    CR_INVALID_CONN_HANDLE error.

  * After the server's internal thread ID counter reaches a value larger
    than 32 bits, it returns an ER_DATA_OUT_OF_RANGE error for any
    mysql_kill() invocation and mysql_kill() fails.

* To avoid problems with mysql_thread_id() and mysql_kill(), you should
  not use them. To get the connection ID, execute a SELECT
  CONNECTION_ID() query and retrieve the result. To kill a thread,
  execute a KILL statement.

Changes were backported to 5.6.9. Noted in 5.6.9 changelog.