MySQL 5.7 and SHOW VARIABLES

[lem9]

A new behavior in MySQL 5.7 might cause some problems. Indeed, it seems like many (all?) SHOW VARIABLES statements are disabled by default for unprivileged users, unless show_compatibility_56 is set to ON. Tested on MySQL 5.7.8.

See http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_show_compatibility_56_show

[lem9]

See a workaround:
c6a3671

[madhuracj]

Reading about this there are two related major changes in 5.7

1. INFORMATION_SCHEMA.(GLOBAL|SESSION)_(VARIABLES|STATUS) tables are now deprecated and empty. These have been replaced by performance_schema.(global|session)_(variables|status) tables. However, this wouldn't matter since SHOW VARIABLES commands would handle this internally.
2. Privileges are required on the new performance_schema.(global|session)_(variables|status) tables. This what cause this bug. Even though this looks to be security related, as Marc pointed out, a command like SELECT @@version; still works.

Since, we do not have control over show_compatibility_56, using SELECT @@ seems to be the best workaround for now.

See http://planet.mysql.com/entry/?id=5991741