INFORMATION_SCHEMA has tables that contain
system and status variable information (see
Section 24.10, “The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables”, and
Section 24.9, “The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables”). The Performance Schema also
contains system and status variable tables (see
Section 25.11.13, “Performance Schema System Variable Tables”, and
Section 25.11.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 will be removed in a future MySQL release.
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
become deprecated and eventually are removed.
In MySQL 5.6, system and status variable information is available
SHOW VARIABLES SHOW STATUS
And from these
INFORMATION_SCHEMA.GLOBAL_VARIABLES INFORMATION_SCHEMA.SESSION_VARIABLES INFORMATION_SCHEMA.GLOBAL_STATUS INFORMATION_SCHEMA.SESSION_STATUS
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
variable to control how the server makes system and status
variable information available.
ON, compatibility with MySQL 5.6 is enabled.
The older system and status variable sources
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
In MySQL 5.7.6 and 5.7.7, a deprecation warning is raised when using a
WHEREclause with the
SHOWstatements. This behavior does not occur as of MySQL 5.7.8.
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_SCHEMAtables produces an error. Applications that access the
INFORMATION_SCHEMAtables should be revised to use the corresponding Performance Schema tables instead.
Before MySQL 5.7.9, selecting from the
INFORMATION_SCHEMAtables 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
session_statustables 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
SHOWstatements 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] VARIABLESoutput does not include global variables that have no session counterpart.
WHEREclause with the
SHOWstatements produces an error.
Slave_status variables become unavailable through
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 Effect of show_compatibility_56 on Slave Status Variables.
Migration and Privileges
Initially, with the introduction of Performance Schema system and
status variable tables in MySQL 5.7.6, access to those tables
SELECT privilege, just
as for other Performance Schema tables. However, this had the
consequence that when
SHOW VARIABLES and
SHOW STATUS statements also
SELECT privilege: With
compatibility disabled, output for those statements was taken from
the Performance Schema
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
Beyond MySQL 5.7
In a MySQL 8.0, the
tables and the
variable are removed, and output from the
statements is always based on the underlying Performance Schema
Applications that have been revised to work in MySQL 5.7 when
work without further changes, except that it will not be possible
to test or set
show_compatibility_56 because it
will not exist.