Bug #71057 Please expose session variables for all threads to enhance config management
Submitted: 3 Dec 2013 12:24 Modified: 23 Mar 2015 20:57
Reporter: Simon Mudd (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S4 (Feature request)
Version:5.7 and earlier OS:Any
Assigned to: Marc Alff CPU Architecture:Any

[3 Dec 2013 12:24] Simon Mudd
Description:
Related to: http://bugs.mysql.com/bug.php?id=68451

It is not currently possible to see the configuration of other threads and their session variables.

This may make a difference especially for replication threads as settings like max_allowed_packet, the tx_isolation and a few other variables may get changed globally but not affect the current replication, or event threads. This may or may not be a problem but you can not see the settings of threads and under the circumstances listed above this would be useful.

How to repeat:
See above.

Suggested fix:
It would be convenient to have a I_S or P_S table SESSION_VARIABLES which would contain the information of ALL threads, so basically similar to the GLOBAL VARIABLES table / output but with perhaps a thread_id.
[3 Dec 2013 12:26] Simon Mudd
Current reason for wanting to do this is I am changing the tx_isolation settings for a replication chain.
It's possible to change the global settings but not to properly verify if the SQL thread has been adjusted to take the new value, something which requires knowing that it has been restarted.
[3 Dec 2013 12:27] Simon Mudd
See also bug#27508
[10 Mar 2015 10:07] Marc Alff
This feature is now implemented in MySQL 5.7.6

See the table:
performance_schema.variables_by_thread

root@localhost [performance_schema] > set global show_compatibility_56=off;
Query OK, 0 rows affected (0.00 sec)

root@localhost [performance_schema] > describe 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.00 sec)

root@localhost [performance_schema] > select * from variables_by_thread where VARIABLE_NAME like "tx%";
+-----------+---------------+-----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE  |
+-----------+---------------+-----------------+
|        20 | tx_isolation  | REPEATABLE-READ |
|        20 | tx_read_only  | OFF             |
|        21 | tx_isolation  | READ-COMMITTED  |
|        21 | tx_read_only  | ON              |
+-----------+---------------+-----------------+
4 rows in set, 2 warnings (0.00 sec)
[23 Mar 2015 20:57] Paul Dubois
Implemented in 5.7.6. It is now possible to see session variables for all sessions using the variables_by_thread table:

http://dev.mysql.com/doc/refman/5.7/en/performance-schema-system-variable-tables.html

Access to this table requires the SELECT privilege.