Bug #55287 SHOW VARIABLES and SELECT display different variable value
Submitted: 15 Jul 2010 14:35 Modified: 30 Jul 2010 14:12
Reporter: Paul DuBois Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.1.49 OS:Any (Mac OS X)
Assigned to: CPU Architecture:Any

[15 Jul 2010 14:35] Paul DuBois
Description:
I was checking the default value of max_binlog_cache_size, and discovered an anomaly that SHOW GLOBAL VARIABLES and SELECT @@global.var_name display different values, one of which is clearly incorrect:

mysql> show global variables like 'max_binlog_cache_size';
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| max_binlog_cache_size | 18446744073709547520 |
+-----------------------+----------------------+
1 row in set (0.00 sec)

mysql> select @@global.max_binlog_cache_size;
+--------------------------------+
| @@global.max_binlog_cache_size |
+--------------------------------+
|                          -4096 |
+--------------------------------+
1 row in set (0.00 sec)

I tested this on Mac OS X 64-bit builds (because for 32-bit builds
the default is only 4294963200 and this problem does not occur).
I see the problem in 5.1.x, but not in 5.0.92 or 5.5.x.

I don't know whether the problem is specific to Mac OS X, though.

How to repeat:
show global variables like 'max_binlog_cache_size';
select @@global.max_binlog_cache_size;
[15 Jul 2010 14:42] Valeriy Kravchuk
Verified just as described:

valeriy-kravchuks-macbook-pro:mysql openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.48 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show global variables like 'max_binlog_cache_size';
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| max_binlog_cache_size | 18446744073709547520 |
+-----------------------+----------------------+
1 row in set (0.00 sec)

mysql>  select @@global.max_binlog_cache_size;
+--------------------------------+
| @@global.max_binlog_cache_size |
+--------------------------------+
|                          -4096 |
+--------------------------------+
1 row in set (0.00 sec)
[30 Jul 2010 14:13] Omer Barnir
Problem addressed in 5.5