Bug #71059 Make I_S.GLOBAL_VARIABLES and SHOW GLOBAL VARIABLES consistent
Submitted: 3 Dec 2013 13:01 Modified: 3 Dec 2013 13:33
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.6.14 and 5.7.3 OS:Any
Assigned to: Marc Alff CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[3 Dec 2013 13:01] Simon Mudd
Description:
Why is the output of SHOW GLOBAL VARIABLES and SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES different?

How to repeat:
The column names are different and in a different case.
The variable names are in a different case from one command to the other.

mysql> select * from INFORMATION_SCHEMA.GLOBAL_VARIABLES LIMIT 1;
+-------------------------+----------------+
| VARIABLE_NAME           | VARIABLE_VALUE |
+-------------------------+----------------+
| MAX_PREPARED_STMT_COUNT | 16382          |
+-------------------------+----------------+
1 row in set (0.01 sec)

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

Suggested fix:
Make the output of the 2 commands consistent as if nothing else that should allow you to use common code to generate both outputs.  Maybe you do that already?

In any case the 2 commands currently provide different output and thus are not exchangeable.
That makes no sense at all.
[3 Dec 2013 13:33] Miguel Solorzano
Thank you for the bug report.

Last login: Tue Dec  3 08:55:56 on console
[11:30:37][pochita:]~ miguel$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> select * from INFORMATION_SCHEMA.GLOBAL_VARIABLES LIMIT 1;
+-------------------------+----------------+
| VARIABLE_NAME           | VARIABLE_VALUE |
+-------------------------+----------------+
| MAX_PREPARED_STMT_COUNT | 16382          |
+-------------------------+----------------+
1 row in set (0.03 sec)

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

mysql>
[3 Dec 2013 14:54] Hartmut Holzgraefe
Might also be a good opportunity to finally handle http://bugs.mysql.com/bug.php?id=363 "SHOW SESSION VARIABLES LIKE 'x' != SELECT @@SESSION.x"

(the oldest active server bug right now as far as i can tell)