Mysql – Can you see other users’ session variables in MySQL

configurationMySQLsession

In MySQL, Using SHOW GLOBAL VARIABLES gives the ability to see global variables.
SHOW SESSION VARIABLES are the variable settings for that session.

Is there a way to see the SESSION variables of other active, connected users, from a superuser connection? For example, is there a query or view that would show me which active sessions have variables set differently from the global variables?

Best Answer

Question #1

Is there a way to see the SESSION variables of other active, connected users, from a superuser connection ?

No, there is no way.

Question #2

is there a query or view that would show me which active sessions have variables set differently from the global variables?

No, there is no way.

CAVEAT

From your individual connection you can run this

SELECT A.variable_name VarName,
A.variable_value Global_Value,
B.variable_value Session_Value FROM
information_schema.global_variables A INNER JOIN
information_schema.session_variables B USING (variable_name)
WHERE A.variable_value <> B.variable_value;

to see which session settings differ from the global settings.

For example, here is what I am running

mysql> SHOW VARIABLES LIKE 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.15                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

mysql>

Note the output of the query

mysql> SELECT A.variable_name VarName,
    -> A.variable_value Global_Value,
    -> B.variable_value Session_Value FROM
    -> information_schema.global_variables A INNER JOIN
    -> information_schema.session_variables B USING (variable_name)
    -> WHERE A.variable_value <> B.variable_value;
+--------------------------+-------------------+------------------+
| VarName                  | Global_Value      | Session_Value    |
+--------------------------+-------------------+------------------+
| CHARACTER_SET_CONNECTION | latin1            | cp850            |
| CHARACTER_SET_RESULTS    | latin1            | cp850            |
| CHARACTER_SET_DATABASE   | latin1            | utf8             |
| COLLATION_DATABASE       | latin1_swedish_ci | utf8_general_ci  |
| CHARACTER_SET_CLIENT     | latin1            | cp850            |
| COLLATION_CONNECTION     | latin1_swedish_ci | cp850_general_ci |
+--------------------------+-------------------+------------------+
6 rows in set (0.08 sec)

If you want to find all the active connections that have this difference, perhaps you could have the application dump the output of that query to a text file along with the connection ID (using the CONNECTION_ID() function) and parse it as you wish.

Related Question