MySQL Bugs: #17811: Provide means of assigning the value of a GLOBAL variable to an user var

Bug #17811	Provide means of assigning the value of a GLOBAL variable to an user var
Submitted:	28 Feb 2006 23:45	Modified:	14 May 2006 13:22
Reporter:	Andrey Hristov	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.1.8-debug	OS:	Linux (SuSE 9.3)
Assigned to:		CPU Architecture:	Any

Description:
set @val:=(SHOW GLOBAL VARIABLES LIKE 'long_query_time');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'show global variables like 'long_query_time')' at line 1

There is hidden INFORMATION_SCHEMA.VARIABLES table but it only shows the session variables. Which one can select from with @@ like
set @val:=@@long_query_time;
This returns the session value.

Quite nice will be to have the I_S.VARIABLES table not hidden with 3 columns - var_name, session value, global value. The workaround I found is to use a function that opens a cursor, but it's kind of slow, though it works

How to repeat:
create function get_value(var_n CHAR(128))
  returns CHAR(255)
  deterministic
begin
  DECLARE var_name CHAR(255);
  DECLARE var_val CHAR(255);
  DECLARE done INT DEFAULT 0;
  DECLARE cur1 CURSOR FOR SHOW GLOBAL VARIABLES;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  OPEN cur1;
  REPEAT
    FETCH cur1 INTO var_name, var_val;
    IF NOT done THEN
      IF var_n = var_name THEN
        CLOSE cur1;
        RETURN var_val;
      END IF;
    END IF;
  UNTIL done END REPEAT;
  CLOSE cur1;
  RETURN NULL;
end|

Suggested fix:
Have  I_S.VARIABLES table not hidden with 3 columns - var_name, session value, global value

Thank you for a reasonable feature request (and workaround).

What's wrong with - documented - SELECT @@global.long_query_time ?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".