5

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?

2

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.

7

As of MySQL 5.7 the performance_schema can provide this information.

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

You can get these from performance_schema.variables_by_thread.

You will need to know the Thread ID. The ID from SHOW PROCESSLIST is Process ID. Knowing the Process ID you can then look up the Thread ID from performance_schema.threads.

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

You sure can.

SELECT VARIABLE_NAME, gv.VARIABLE_VALUE, tv.VARIABLE_VALUE
FROM performance_schema.global_variables gv
  JOIN performance_schema.variables_by_thread tv USING (VARIABLE_NAME)
WHERE gv.VARIABLE_VALUE <> tv.VARIABLE_VALUE
  AND THREAD_ID = ?;
0

By normal ways isn't possible... We know something about current SESSION AS "show status like '%compress%'" but not yet from other SESSIONS. Well, ON MySQL Community is possible but very dificult, you need to change the source code and add this function to MySQL, so you can build a new custumized executable. Example: You can create a new table on INFORMATION_SCHEME with data of each SESSION when each session starts, so you can know p.e. the SESSION variable COMPRESS of each ID PROCESS. Other idea, if you have only one APP that access your MySQL, you can write some code into your APP to write informations about that session into your tables, so you can access the information of all connectios from your APP.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.