MySQL 5.7 introduces a change in the way we query for global variables and status variables: the INFORMATION_SCHEMA.(GLOBAL|SESSION)_(VARIABLES|STATUS) tables are now deprecated and empty. Instead, we are to use the respective performance_schema.(global|session)_(variables|status) tables.
But the change goes farther than that; there is also a security change. Oracle created a pitfall of 2 changes at the same time:
- Variables/status moved to a different table
- Privileges required on said table
As an example, my non-root user gets:
mysql> show session variables like 'tx_isolation'; ERROR 1142 (42000): SELECT command denied to user 'normal_user'@'my_host' for table 'session_variables'
Who gets affected by this? Nearly everyone and everything.
- Your Nagios will not be able to read status variables
- Your ORM will not be able to determine session variables
- Your replication user will fail connecting (see this post by Giuseppe)
- And most everyone else.
The problem with the above is that involves two unrelated changes to your setup, which are not entirely simple to coordinate:
- Change your app code to choose the correct schema (information_schema vs. performance_schema)
- GRANT the permissions on your database
Perhaps at this point you still do not consider this to be a problem. You may be thinking: well, let’s first prepare by creating the GRANTs, and once that is in place, we can, at our leisure, modify the code.
Not so fast. Can you really that simply create those GRANTs? Continue reading » “Baffling 5.7 global/status variables issues, unclean migration path”