Make a way to set and view other user's variables

[Baron Schwartz]

I don't like https://blueprints.launchpad.net/percona-server/+spec/control-global-slow or use_global_long_query_time. What I think we really need is the ability to set any or all variables for any or all users. I would like to be able to do something like this:

SHOW SESSION VARIABLES LIKE <variable> FOR <connection ID>;
SET SESSION <variable> = <value> FOR <connection ID>;
SET SESSION <variable> = <value> FOR ALL;

For example, this:

SET SESSION long_query_time=0 FOR ALL;

That would make it possible to remove use_global_long_query_time completely.

[Baron Schwartz (baron-xaprb)]

It might be even better to build this as a UDF that could be loaded without a new server. Example:

SELECT SET_VARIABLE(<THREAD_ID>, <VARIABLE>, <VALUE>);
SELECT GET_VARIABLE(...........);

If <THREAD_ID> is zero, then SET_VARIABLE() should set the variable for every thread.

[Vadim Tkachenko (vadim-tk)]

Assigned to Alexey to check if it is possible to implement in 5.5

[Alexey Kopytov (akopytov)]

Session variables are implemented as a thread-local memory area which accessed directly, without taking any locks. What is even more important is that there is a lot of implicit and explicit assumptions that session variable values do not change during a statement execution. For example, imagine changing the SQL mode, or connection's character set or some buffer size in the middle of a statement. So it does not look that simple in the general case.

What we can do to circumvent that is to delay the actual update until the end of the current statement execution (if any) in the target thread, by updating a separate per-thread copy of thd->variables, and then replacing the actual session variable values by that copy when the statement completes. That, of course, can only be implemented as a patch, I don't see a safe way to implement it as a UDF.

[Peter Zaitsev (pz-percona)]

Alex,

I think waiting till statement completion is good enough in this case and I
trust it can be done relatively easy
and low overhead, like checking some flag after statement exeution is
completed to see if there are any "patches"
which need to be applied. When you can use the standard code for execution
- setting some variables may trigger
some function execution. I'm not sure if it is used anywhere for per thread
variables but it surely is for globals

On Mon, Jan 3, 2011 at 4:16 AM, Alexey Kopytov <email address hidden> wrote:

> Session variables are implemented as a thread-local memory area which
> accessed directly, without taking any locks. What is even more important
> is that there is a lot of implicit and explicit assumptions that session
> variable values do not change during a statement execution. For example,
> imagine changing the SQL mode, or connection's character set or some
> buffer size in the middle of a statement. So it does not look that
> simple in the general case.
>
> What we can do to circumvent that is to delay the actual update until
> the end of the current statement execution (if any) in the target
> thread, by updating a separate per-thread copy of thd->variables, and
> then replacing the actual session variable values by that copy when the
> statement completes. That, of course, can only be implemented as a
> patch, I don't see a safe way to implement it as a UDF.
>
> --
> You received this bug notification because you are a member of Percona
> developers, which is the registrant for Percona Server.
> https://bugs.launchpad.net/bugs/588854
>
> Title:
> Make a way to set and view other user's variables
>
> Status in Percona Server with XtraDB:
> Confirmed
>
> Bug description:
> I don't like
> https://blueprints.launchpad.net/percona-server/+spec/control-global-slowor use_global_long_query_time. What I think we really need is the ability
> to set any or all variables for any or all users. I would like to be able
> to do something like this:
>
> SHOW SESSION VARIABLES LIKE <variable> FOR <connection ID>;
> SET SESSION <variable> = <value> FOR <connection ID>;
> SET SESSION <variable> = <value> FOR ALL;
>
> For example, this:
>
> SET SESSION long_query_time=0 FOR ALL;
>
> That would make it possible to remove use_global_long_query_time
> completely.
>
>
>

--
Peter Zaitsev, CEO, Percona Inc.
Tel: [phone] ext 501 Skype: peter_zaitsev
24/7 Emergency Line [phone] ext 911

Percona Training Workshops
http://www.percona.com/training/

[Vadim Tkachenko (vadim-tk)]

Alexey,

So how you estimate time and efforts to get it done ?

[Laurynas Biveinis (laurynas-biveinis)]

https://github.com/facebook/mysql-5.6/commit/19f5aad976d8d269110f121c3ab5dee9b730b4df

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2306