Bug #15684 system variables cannot be SELECTed (e.g. @@version_comment)
Submitted: 12 Dec 2005 13:57 Modified: 2 May 2006 1:20
Reporter: Carsten Segieth Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.18-bk OS:Any (All)
Assigned to: Jim Winstead

[12 Dec 2005 13:57] Carsten Segieth
Description:
It is not possible to retrieve the value of all system variables that are shown with "show variables like 'vers%';". 
Only some of them can be retrieved using "SELECT @@<variablename>;". Others are rejected with an error like: ERROR 1193 (HY000): Unknown system variable 'version_comment'

mysql> show variables like 'vers%';
+-------------------------+-----------------------+
| Variable_name           | Value                 |
+-------------------------+-----------------------+
| version                 | 5.0.16-pro-nt-log     |
| version_comment         | Official MySQL binary |
| version_compile_machine | ia32                  |
| version_compile_os      | Win32                 |
+-------------------------+-----------------------+
4 rows in set (0.00 sec)

mysql> select @@version; select @@version_comment; select @@version_compile_machine; select @@version_compile_os;
+-------------------+
| @@version         |
+-------------------+
| 5.0.16-pro-nt-log |
+-------------------+
1 row in set (0.00 sec)

ERROR 1193 (HY000): Unknown system variable 'version_comment'
ERROR 1193 (HY000): Unknown system variable 'version_compile_machine'
+----------------------+
| @@version_compile_os |
+----------------------+
| Win32                |
+----------------------+
1 row in set (0.00 sec)

How to repeat:
show variables like 'vers%';
select @@version; 
select @@version_bdb; 
select @@version_comment; 
select @@version_compile_machine; 
select @@version_compile_os;

Suggested fix:
allow all system variables to be SELECTed.
[12 Dec 2005 14:11] Valerii Kravchuk
Thank you for a bug report. Verified just as described on Linux with 5.0.18-BK (ChangeSet@1.1968, 2005-12-11 22:21:19-08:00):

[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'vers%';
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| version                 | 5.0.18              |
| version_comment         | Source distribution |
| version_compile_machine | i686                |
| version_compile_os      | redhat-linux-gnu    |
+-------------------------+---------------------+
4 rows in set (0,01 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.0.18    |
+-----------+
1 row in set (0,03 sec)

mysql> select @@version_bdb;
ERROR 1193 (HY000): Unknown system variable 'version_bdb'
mysql> select @@version_comment;
ERROR 1193 (HY000): Unknown system variable 'version_comment'
mysql> select @@version_compile_machine;
ERROR 1193 (HY000): Unknown system variable 'version_compile_machine'
mysql> select @@version_compile_os;
+----------------------+
| @@version_compile_os |
+----------------------+
| redhat-linux-gnu     |
+----------------------+
1 row in set (0,03 sec)
[20 Dec 2005 11:04] Carsten Segieth
similar (but other variables touched) to http://bugs.mysql.com/bug.php?id=12792
[21 Apr 2006 4:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5247
[21 Apr 2006 15:59] Jim Winstead
I filed a new bug, Bug #19263, for cleaning up variables.test to not leave any variable changes behind.
[26 Apr 2006 18:36] Jim Winstead
Second review was from Ian.
[1 May 2006 16:03] Jim Winstead
Fixed in 5.0.22 and 5.1.10.
[2 May 2006 1:20] Paul Dubois
Noted in 5.0.22, 5.1.10 changelogs.

The <literal>system_time_zone</literal> and
<literal>version_*</literal> system variables could not be
accessed via <literal>SELECT
@@<replaceable>var_name</replaceable></literal> syntax. (Bug
#12792, Bug #15684)