Could be task described in more details?

What variables to track?
What to do with new variables?
What memory and speed penalty is acceptable?

Please define the task properly.

What variables to track?

session user variables

What to do with new variables?

using session track mechanism to inform proxy which user variables changed and the value; the info store in ok packet just like MDEV-8931 tracking system variables;

What memory and speed penalty is acceptable?

just a little, maybe more bandwidth consumption

Do you need to track all session variable, individual list of variables, report only existence of some changes of some variables, report list of all changed variables, report list of variables with its new values, report list of all changed variables with old and new value?
so, what to track?

(after defining what to track question about penalty will be more clear)

Report list of changed variables with its new values will be enough , I think the penalty should be negligible；

You have not answered if it is all user variables or variables by some list.

About penalty, not sure. It will be memory penalty for sure (name and values storing) also it is in times more complex then just report if there was some changes.

BTW do you realise that you still will not have info about global variables (which are not session nor user).?

Also could you collect and put all requirements in the task description.

If I understand this correctly, the idea is to return all changed user variables in the OK packet. This means that the following SQL statement:

SET @a = 1;

Would return the key a and value 1. This would allow the client to know what user variables have changed after calling something with a side-effect e.g. a stored procedure.

This would mainly benefit proxies and other systems that intend to use the values of these variables for other purposes. In the case of MaxScale, it would propagate these changes to other servers when executed on one server (see MXS-1756).

serg, please review patch for this task https://github.com/MariaDB/server/commit/fd4bac9c5e23443d059d6e65ffe8eda319ea25d9

robertbindar, please review https://github.com/MariaDB/server/commit/69b05a7d7a3c3630232fa12a5131d86ec80afdd4, it is reimplementation of contributed patch.

there is no support in client, no real test (and support in mysqltest.cc), no documentation

The patch appered to be non working (no surprise because it has no tests).

Current implementation forgot about:

• NULL
• variables types
• charset for string and precision for numbers

I'd propose to return variable tracking as:

• the variable name
• the variable value expression

So the proxy will reconstruct a SET statement using this simple function:

spnrintf(buff, "SET @%s=%s", name, expr);

The server will put all necessary metadata into the expression itself.
For example, in case of a string data, it would send the expression in this format:

_latin1'str' COLLATE latin1_german2_ci

Note, we will eventually support user defined types in user variables. An arbitrary data type has an arbitrary amount of data type attributes.
So sending the basic metadata (e.g, length, precision, scale), like we do it in the result set in the client-server protocol, won't always be enough: this metadata is incomplete.

i suppose the proxy does not need to parse the expression. It only needs to get the variable name and its value from one backend and transparently transfer this exact value to another backend. Using the expression format looks the best for me.

We already use the same approach in binary log by replacing user variables and SP variables with NAME_CONST() expressions:

INSERT INTO t2 VALUES ( NAME_CONST('rec1.a',10),  NAME_CONST('rec1.b',_latin1'b10' COLLATE 'latin1_swedish_ci'))

Notice, the expression contains all metadata:

• in case of a number it's just 10 - an integer literal. It does not need any additional metatada.
• In case of a string it's more complex: the expression, among the value itself, contains the character set and the collation

This approach proved to be functional. Why not reuse it for variable tracking?

Some more binlog examples for other data types:

INSERT INTO t1 VALUES ( NAME_CONST('b8',_binary'a' COLLATE 'binary')=0x61)

INSERT INTO t1 VALUES ( NAME_CONST('t0',TIME'01:01:01')=10101)

INSERT INTO t1 VALUES ( NAME_CONST('dt0',TIMESTAMP'2001-01-01 01:01:01')=20010101010101)

Notice, every data type encodes its values in its own way. In the way that the server will re-parse it back without any data loss.

There is a good advantage in this approach:
we can reuse a lot of code in the server for variable tracking.

So a new data type will have to implement the functionality (to print a variable value as an expression) only one time, and it will be reused both for binary logging and variable tracking.

I think Bar's proposal would be pretty good as it'd still allow the admin tool to see the value with a simple SELECT <insert constant here> AS <variable name>. This removes all client-side requirements for the feature to be used. it'd involve an extra round-trip but it's probably not super critical when it's used by interactive applications and I think doing things in SQL is more portable than implementing it in the connectors.