Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.1Mb
PDF (A4) - 35.2Mb
Man Pages (TGZ) - 256.4Kb
Man Pages (Zip) - 361.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  / MySQL Performance Schema  /  Migrating to Performance Schema System and Status Variable Tables

25.20 Migrating to Performance Schema System and Status Variable Tables

TheINFORMATION_SCHEMA has tables that contain system and status variable information (seeSection 24.3.11, “The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables”, andSection 24.3.10, “The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables”). The Performance Schema also contains system and status variable tables (seeSection 25.12.13, “Performance Schema System Variable Tables”, andSection 25.12.14, “Performance Schema Status Variable Tables”). The Performance Schema tables are intended to replace theINFORMATION_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 theINFORMATION_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 theINFORMATION_SCHEMA tables become deprecated and eventually are removed.

MySQL 5.6

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

SHOW VARIABLESSHOW STATUS

And from theseINFORMATION_SCHEMA tables:

INFORMATION_SCHEMA.GLOBAL_VARIABLESINFORMATION_SCHEMA.SESSION_VARIABLESINFORMATION_SCHEMA.GLOBAL_STATUSINFORMATION_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_variablesperformance_schema.session_variablesperformance_schema.variables_by_threadperformance_schema.global_statusperformance_schema.session_statusperformance_schema.status_by_threadperformance_schema.status_by_accountperformance_schema.status_by_hostperformance_schema.status_by_user

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

Whenshow_compatibility_56 isON, 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 theINFORMATION_SCHEMA tables.

  • In MySQL 5.7.6 and 5.7.7, a deprecation warning is raised when using aWHERE clause with theSHOW statements. This behavior does not occur as of MySQL 5.7.8.

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

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

    Before MySQL 5.7.9, selecting from theINFORMATION_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 thatshow_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 Schemasession_variables andsession_status 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 theSHOW 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 aWHERE clause with theSHOW statements produces an error.

  • TheseSlave_xxx status variables become unavailable throughSHOW STATUS:

    Slave_heartbeat_periodSlave_last_heartbeatSlave_received_heartbeatsSlave_retried_transactionsSlave_running

    Applications that use these status variables should be revised to obtain this information using the replication-related Performance Schema tables. For details, seeEffect of show_compatibility_56 on Slave Status Variables.

  • The Performance Schema does not collect statistics forCom_xxx status variables in the status variable tables. To obtain global and per-session statement execution counts, use theevents_statements_summary_global_by_event_name andevents_statements_summary_by_thread_by_event_name 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 theSELECT privilege, just as for other Performance Schema tables. However, this had the consequence that whenshow_compatibility_56=OFF, theSHOW VARIABLES andSHOW STATUS statements also required theSELECT privilege: With compatibility disabled, output for those statements was taken from the Performance Schemaglobal_variables,session_variables,global_status, andsession_status tables.

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

Beyond MySQL 5.7

In a MySQL 8.0, theINFORMATION_SCHEMA variable tables and theshow_compatibility_56 system variable are removed, and output from theSHOW statements is always based on the underlying Performance Schema tables.

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