Using the sys.ps_thread_account() Function to Get the Account for a Given Thread ID in MySQL

by

In MySQL, the sys.ps_thread_account() function returns the account associated with a given thread ID. It returns the account in the form user_name@host_name.

Syntax

The syntax goes like this:

sys.ps_thread_account(in_thread_id)

Where in_thread_id is the thread ID to check. This should match the THREAD_ID column from one of the rows in the performance_schema.threads table.

Example

Here’s an example to demonstrate:

SELECT sys.ps_thread_account( 136 );

Result:

barney@localhost

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 = 136;

Result:

+-----------+---------------------------+----------------+------------------+------------------+
| thread_id | name                      | processlist_id | processlist_user | processlist_host |
+-----------+---------------------------+----------------+------------------+------------------+
|       136 | thread/sql/one_connection |             81 | barney           | localhost        |
+-----------+---------------------------+----------------+------------------+------------------+

Check the Current Connection’s Thread ID

We can pass the ps_current_thread_id() function to check the thread for the current connection:

SELECT sys.ps_thread_account( ps_current_thread_id() );

Result:

root@localhost

Another way to do this is to use the sys.ps_thread_id() and connection_id() functions. However, sys.ps_thread_id() is deprecated as of MySQL 8.0.16, and subject to removal in a future MySQL version. Therefore the built-in ps_current_thread_id() function will usually be our best choice for doing this.

That said, the sys.ps_thread_id() has been superseded by the built-in ps_thread_id() function that does the same thing.

Non-Existent Thread ID

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

SELECT sys.ps_thread_account( 201 );

Result:

NULL

NULL Thread ID

If the argument is NULL, then NULL is returned:

SELECT sys.ps_thread_account( NULL );

Result:

NULL

Passing the Wrong Number of Arguments

Passing the wrong number of arguments results in an error:

SELECT sys.ps_thread_account( 136, 137 );

Result:

ERROR 1318 (42000): Incorrect number of arguments for FUNCTION sys.ps_thread_account; expected 1, got 2

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

SELECT sys.ps_thread_account( );

Result:

ERROR 1318 (42000): Incorrect number of arguments for FUNCTION sys.ps_thread_account; expected 1, got 0