MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6

24.20 Migrating to Performance Schema System and Status Variable Tables

The INFORMATION_SCHEMA has tables that contain system and status variable information (see Section 23.11, “The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables”, and Section 23.10, “The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables”). The Performance Schema also contains system and status variable tables (see Section 24.12.13, “Performance Schema System Variable Tables”, and Section 24.12.14, “Performance Schema Status Variable Tables”). The Performance Schema tables are intended to replace the INFORMATION_SCHEMA tables, which are deprecated as of MySQL 5.7.6 and are removed in MySQL 8.0.

This section describes the intended migration path away from the INFORMATION_SCHEMA system and status variable tables to the corresponding Performance Schema tables. Application developers should use this information as guidance regarding the changes required to access system and status variables in MySQL 5.7.6 and up as the INFORMATION_SCHEMA tables become deprecated and eventually are removed.

MySQL 5.6

In MySQL 5.6, system and status variable information is available from these SHOW statements:


And from these INFORMATION_SCHEMA tables:



MySQL 5.7

As of MySQL 5.7.6, the Performance Schema includes these tables as new sources of system and status variable information:



MySQL 5.7.6 also adds a show_compatibility_56 system variable to control how the server makes system and status variable information available.

When show_compatibility_56 is ON, compatibility with MySQL 5.6 is enabled. The older system and status variable sources (SHOW statements, INFORMATION_SCHEMA tables) are available with semantics identical to MySQL 5.6. Applications should run as is, with no code changes, and should see the same variable names and values as in MySQL 5.6. Warnings occur under these circumstances:

When show_compatibility_56 is OFF, compatibility with MySQL 5.6 is disabled and several changes result. Applications must be revised as follows to run properly:

Migration and Privileges

Initially, with the introduction of Performance Schema system and status variable tables in MySQL 5.7.6, access to those tables required the SELECT privilege, just as for other Performance Schema tables. However, this had the consequence that when show_compatibility_56=OFF, the SHOW VARIABLES and SHOW STATUS statements also required the SELECT privilege: With compatibility disabled, output for those statements was taken from the Performance Schema global_variables, session_variables, global_status, and session_status tables.

As of MySQL 5.7.9, those Performance Schema tables are world readable and accessible without the SELECT privilege. Consequently, SHOW VARIABLES and SHOW STATUS do not require privileges on the underlying Performance Schema tables from which their output is produced when show_compatibility_56=OFF.

Beyond MySQL 5.7

In a MySQL 8.0, the INFORMATION_SCHEMA variable tables and the show_compatibility_56 system variable are removed, and output from the SHOW statements is always based on the underlying Performance Schema tables.

Applications that have been revised to work in MySQL 5.7 when show_compatibility_56=OFF should work without further changes, except that it is not possible to test or set show_compatibility_56 because it does not exist.