WL#6884: PERFORMANCE SCHEMA, USER VARIABLES
Affects: Server-5.7 — Status: Complete — Priority: Medium
In order to get information about the session state, several pieces of information is needed. One of these pieces of information is knowing if there are any user-defined variables with values, and what values they have. The information should be presented in such a way that the output can be fed into the server to re-establish this part of the session state. For example, if a result set where the output consists of two columns with values such that the following code works would be ideal. for var, val in cursor.fetchall(): new_conn.cmd_query("SET @`" + var + "` = %s", val) User Documentation ================== http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-5.html http://dev.mysql.com/doc/refman/5.7/en/performance-schema-connection-attribute- tables.html
REQUIREMENTS ============ CURRENT-VERSION := 5.7 PREVIOUS-VERSION := 5.6 Install ------- Func-Req (1): A fresh MySQL installation of CURRENT-VERSION must create the following tables: - 1.1, TABLE performance_schema.user_variables_by_thread Upgrade ------- Func-Req (2): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must create the following tables: - 2.1, TABLE performance_schema.user_variables_by_thread User variables names -------------------- Func-Req (3): When defining a user variable, the name given to the user variable: - (3.1) must have a minimum length of 1 - (3.2) must not end with trailing spaces - (3.3) must have a maximum length of 64 Setting user variables with illegal names results in an error.
Changes to user variables ========================= Currently, user variables in the server do not have any limitation on the length of the variable name. A new limit for the length of the variable name is enforced, which matches how identifiers are used in MySQL in general: an identifier is limited to 64 characters. Statements such as SET @very_long_name_that_exceeds_64_characters = ... now fail with an error. TABLE performance_schema.user_variables_by_thread ================================================= New table This table exposes user variables associated with each thread. Columns `THREAD_ID` bigint(20) unsigned NOT NULL, `VARIABLE_NAME` varchar(64) NOT NULL, `VARIABLE_VALUE` longblob Note: "BLOB" are limited to 16 bits, using a "LONGBLOB" here, to store up to 32 bits values (4 Gb). The real limit on user variables size may be lower, due to constraints related to max_allowed_packet. Operations: Only SELECT is supported, this is a read only table. Semantic for each columns When a user variable in thread TID is defined with set @foo = bar a row exposes the variable as follows: - THREAD_ID = TID - VARIABLE_NAME = foo - VARIABLE_VALUE = bar
Copyright (c) 2000, 2019, Oracle Corporation and/or its affiliates. All rights reserved.