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