Mysql – How to get connection details for another connection open to database

connectionsMySQL

How to get connection details as e.g. collation_connection used for another connection open to database?

To give an example. Let's say I have this activity on my Database

mysql> show processlist;
+------+------+--------------------------------+------+---------+------+-------+------------------+
| Id   | User | Host                           | db   | Command | Time | State | Info             |
+------+------+--------------------------------+------+---------+------+-------+------------------+
| 6649 | xxxx | server1.example.com:56300      | mydb | Sleep   | 4123 |       | NULL             |
| 6651 | xxxx | server1.example.com:56301      | mydb | Sleep   |   28 |       | NULL

Now I need to know the collation of connection with ID 6649 from processlist.

Best Answer

What you are asking for is only available in MySQL 5.7. It's a new thing I have not played with yet.

It is in the performance_schema database (a.k.a. Performance Schema System Variable Tables)

  • session_variables : System variables for the current session. An application that wants all system variable values for its own session should use this table. It includes the session variables for its session, as well as the values of global variables that have no session counterpart.
  • variables_by_thread : Session system variables for each active session. An application that wants to know the session variable values for specific sessions should use this table. It includes session variables only, identified by thread ID.

It looks like this

mysql> desc performance_schema.variables_by_thread;
+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| THREAD_ID      | bigint(20) unsigned | NO   |     | NULL    |       |
| VARIABLE_NAME  | varchar(64)         | NO   |     | NULL    |       |
| VARIABLE_VALUE | varchar(1024)       | YES  |     | NULL    |       |
+----------------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

To address the question, you would need to run somthing like

SELECT * FROM performance_schema.variables_by_thread WHERE thread_id = 6649;

If you want that kind of access to other connections, you must upgrade to MySQL 5.7.

Sorry, there is nothing else available in MySQL 5.6 and prior.

Related Question