MySQL Bugs: #71057: Please expose session variables for all threads to enhance config management

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

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.

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.

See also bug#27508

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)

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.