Understanding the PS_THREAD_ID() Function in MySQL

by

In MySQL, we can use the PS_THREAD_ID() function to get the Performance Schema thread ID assigned to a given connection ID. This can be useful whenever we use a function that takes the thread ID as its parameter, but we only know the connection ID.

If no thread ID exists for the connection, then NULL is returned.

The built-in PS_THREAD_ID() function does the same thing as the now deprecated sys.ps_thread_id() function. Therefore, we can call the function without needing to qualify it with sys or making sys our current schema.

Syntax

The syntax goes like this:

PS_THREAD_ID(connection_id)

Example

Here’s an example to demonstrate:

SELECT PS_THREAD_ID( 83 );

Result:

138

This tells us that the connection with a connection ID of 83 has a thread ID of 138.

We can check the performance_schema.threads table for the actual thread:

SELECT
    thread_id,
    name,
    processlist_id,
    processlist_user,
    processlist_host
FROM performance_schema.threads
WHERE thread_id = 138;

Result:

+-----------+---------------------------+----------------+------------------+------------------+
| thread_id | name                      | processlist_id | processlist_user | processlist_host |
+-----------+---------------------------+----------------+------------------+------------------+
|       138 | thread/sql/one_connection |             83 | barney           | localhost        |
+-----------+---------------------------+----------------+------------------+------------------+

Non-Existent Connection ID

If the connection ID doesn’t exist, NULL is returned:

SELECT PS_THREAD_ID( 201 );

Result:

NULL

NULL Connection ID

If the argument is NULL, then NULL is returned:

SELECT PS_THREAD_ID( NULL );

Result:

NULL

Passing the Wrong Number of Arguments

Passing the wrong number of arguments results in an error:

SELECT PS_THREAD_ID( 136, 137 );

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'PS_THREAD_ID'

The same applies when we don’t pass any arguments:

SELECT PS_THREAD_ID( );

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'PS_THREAD_ID'

Check the Current Connection’s Thread ID

Although we could always pass the CONNECTION_ID() function to the PS_THREAD_ID() function to get the thread ID of the current connection, there’s actually a separate function called PS_CURRENT_THREAD_ID() that does the whole thing for us:

SELECT 
    PS_THREAD_ID(CONNECTION_ID()),
    PS_CURRENT_THREAD_ID();

Result:

+-------------------------------+------------------------+
| PS_THREAD_ID(CONNECTION_ID()) | PS_CURRENT_THREAD_ID() |
+-------------------------------+------------------------+
|                           137 |                    137 |
+-------------------------------+------------------------+