25.20 Migrating to Performance Schema System and Status Variable Tables

The 'INFORMATION_SCHEMA' has tables that contain system and status variable information (see note information-schema-variables-table::, and note information-schema-status-table::). The Performance Schema also contains system and status variable tables (see note performance-schema-system-variable-tables::, and note 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:

 SHOW VARIABLES
 SHOW STATUS

And from these 'INFORMATION_SCHEMA' tables:

 INFORMATION_SCHEMA.GLOBAL_VARIABLES
 INFORMATION_SCHEMA.SESSION_VARIABLES

 INFORMATION_SCHEMA.GLOBAL_STATUS
 INFORMATION_SCHEMA.SESSION_STATUS

MySQL 5.7

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

 performance_schema.global_variables
 performance_schema.session_variables
 performance_schema.variables_by_thread

 performance_schema.global_status
 performance_schema.session_status
 performance_schema.status_by_thread
 performance_schema.status_by_account
 performance_schema.status_by_host
 performance_schema.status_by_user

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:

A deprecation warning is raised when selecting from the 'INFORMATION_SCHEMA' tables.
In MySQL 5.7.6 and 5.7.7, a deprecation warning is raised when using a 'WHERE' clause with the 'SHOW' statements. This behavior does not occur as of MySQL 5.7.8.

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:

Selecting from the 'INFORMATION_SCHEMA' tables produces an error. Applications that access the 'INFORMATION_SCHEMA' tables should be revised to use the corresponding Performance Schema tables instead.

Before MySQL 5.7.9, selecting from the 'INFORMATION_SCHEMA' tables produces an empty result set plus a deprecation warning. This was not sufficient notice to signal the need to migrate to the corresponding Performance Schema system and status variable tables for the case that 'show_compatibility_56=OFF'. Producing an error in MySQL 5.7.9 and higher makes it more evident that an application is operating under conditions that require modification, as well as where the problem lies.

In MySQL 5.7.6 and 5.7.7, the Performance Schema note 'session_variables': performance-schema-system-variable-tables. and note 'session_status': performance-schema-status-variable-tables. tables do not fully reflect all variable values in effect for the current session; they include no rows for global variables that have no session counterpart. This is corrected in MySQL 5.7.8.
Output for the 'SHOW' statements is produced using the underlying Performance Schema tables. Applications written to use these statements can still use them, but it is best to use MySQL 5.7.8 or higher. In MySQL 5.7.6 and 5.7.7, the results may differ:
```
* 'SHOW [SESSION] VARIABLES' output does not include global
  variables that have no session counterpart.

* Using a 'WHERE' clause with the 'SHOW' statements produces an
  error.
```
These 'Slave_XXX' status variables become unavailable through *note 'SHOW STATUS': show-status.:
```
  Slave_heartbeat_period
  Slave_last_heartbeat
  Slave_received_heartbeats
  Slave_retried_transactions
  Slave_running
```
Applications that use these status variables should be revised to obtain this information using the replication-related Performance Schema tables. For details, see *note sysvar_show_compatibility_56_slave_status::.
The Performance Schema does not collect statistics for 'Com_XXX' status variables in the status variable tables. To obtain global and per-session statement execution counts, use the note 'events_statements_summary_global_by_event_name': performance-schema-statement-summary-tables. and note 'events_statements_summary_by_thread_by_event_name': performance-schema-statement-summary-tables. tables, respectively.

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 note 'SHOW VARIABLES': show-variables. and note 'SHOW STATUS': show-status. statements also required the 'SELECT' privilege: With compatibility disabled, output for those statements was taken from the Performance Schema note 'global_variables': performance-schema-system-variable-tables, note 'session_variables': performance-schema-system-variable-tables, note 'global_status': performance-schema-status-variable-tables, and note 'session_status': performance-schema-status-variable-tables. tables.

As of MySQL 5.7.9, those Performance Schema tables are world readable and accessible without the 'SELECT' privilege. Consequently, note 'SHOW VARIABLES': show-variables. and note 'SHOW STATUS': 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.

File: manual.info.tmp, Node: performance-schema-restrictions, Prev: performance-schema-variable-table-migration, Up: performance-schema