MySQL Variables show_compatibility_56 数据库 参数变量解释及正确配置使用

本站中文解释

设置Mysql参数变量show_compatibility_56用于支持MySQL 5.6的兼容性，可以更好地支持旧版本MySQL 5.6中使用的一些语法和函数。

此参数可以在变量范围中设置，可以使用以下命令来设置：

SET GLOBAL show_compatibility_56 = 1;

或者

SET SESSION show_compatibility_56 = 1;

如果需要禁用参数，可以将值设置为：

SET GLOBAL show_compatibility_56 = 0;

或者

SET SESSION show_compatibility_56 = 0;

官方英文解释

show_compatibility_56

The INFORMATION_SCHEMA has tables that
contain system and status variable information (see
Section 24.3.11, “The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables”, and
Section 24.3.10, “The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables”). As of
MySQL 5.7.6, the Performance Schema also contains system and
status variable tables (see
Section 25.12.13, “Performance Schema System Variable Tables”,
and
Section 25.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.

For advice on migrating away from the
INFORMATION_SCHEMA tables to the
Performance Schema tables, see
Section 25.20, “Migrating to Performance Schema System and Status Variable Tables”.
To assist in the migration, you can use the
show_compatibility_56 system
variable, which affects whether MySQL 5.6 compatibility is
enabled with respect to how system and status variable
information is provided by the
INFORMATION_SCHEMA and Performance Schema
tables, and also by the SHOW
VARIABLES and SHOW
STATUS statements.

Note

show_compatibility_56 is
deprecated because its only purpose is to permit control
over deprecated system and status variable information
sources which you can expect to be removed in a future
release of MySQL. When those sources are removed,
show_compatibility_56 no
longer has any purpose, and you can expect it be removed as
well.

The following discussion describes the effects of
show_compatibility_56:

• Overview of show_compatibility_56 Effects

• Effect of show_compatibility_56 on SHOW Statements

• Effect of show_compatibility_56 on INFORMATION_SCHEMA Tables

• Effect of show_compatibility_56 on Performance Schema Tables

• Effect of show_compatibility_56 on Slave Status Variables

• Effect of show_compatibility_56 on FLUSH STATUS

For better understanding, it is strongly recommended that you
also read these sections:

• Section 25.12.13, “Performance Schema System Variable Tables”

• Section 25.12.14, “Performance Schema Status Variable Tables”

• Section 25.12.15.10, “Status Variable Summary Tables”

Overview of show_compatibility_56 Effects

The show_compatibility_56
system variable affects these aspects of server operation
regarding system and status variables:

• Information available from the SHOW
VARIABLES and SHOW
STATUS statements

• Information available from the
  INFORMATION_SCHEMA tables that provide
  system and status variable information

• Information available from the Performance Schema tables
  that provide system and status variable information

• The effect of the FLUSH
STATUS statement on status variables

This list summarizes the effects of
show_compatibility_56, with
additional details given later:

• When
  show_compatibility_56 is
  ON, compatibility with MySQL 5.6 is
  enabled. Older variable information sources
  (SHOW statements,
  INFORMATION_SCHEMA tables) produce the
  same output as in MySQL 5.6.

• When
  show_compatibility_56 is
  OFF, compatibility with MySQL 5.6 is
  disabled. Selecting from the
  INFORMATION_SCHEMA tables produces an
  error because the Performance Schema tables are intended
  to replace them. The INFORMATION_SCHEMA
  tables are deprecated as of MySQL 5.7.6 and are removed in
  MySQL 8.0.

  To obtain system and status variable information When
  show_compatibility_56=OFF,
  use the Performance Schema tables or the
  SHOW statements.

  Note

  When
  show_compatibility_56=OFF,
  the SHOW VARIABLES and
  SHOW STATUS statements
  display rows from the Performance Schema
  global_variables,
  session_variables,
  global_status, and
  session_status tables.

  As of MySQL 5.7.9, those tables are world readable and
  accessible without the
  SELECT privilege, which
  means that SELECT is not
  needed to use the SHOW statements,
  either. Before MySQL 5.7.9, the
  SELECT privilege is
  required to access those Performance Schema tables,
  either directly, or indirectly through the
  SHOW statements.

• Several
  Slave_xxx
  status variables are available from
  SHOW STATUS when
  show_compatibility_56 is
  ON. When
  show_compatibility_56 is
  OFF, some of those variables are not
  exposed to SHOW STATUS. The
  information they provide is available in
  replication-related Performance Schema tables, as
  described later.

• show_compatibility_56 has
  no effect on system variable access using
  @@ notation:
  @@GLOBAL.var_name,
  @@SESSION.var_name,
  @@var_name.

• show_compatibility_56 has
  no effect for the embedded server, which produces
  5.6-compatible output in all cases.

The following descriptions detail the effect of setting
show_compatibility_56 to
ON or OFF in the
contexts in which this variable applies.

Effect of show_compatibility_56 on SHOW Statements

SHOW GLOBAL
VARIABLES statement:

• ON: MySQL 5.6 output.

• OFF: Output displays rows from the
  Performance Schema
  global_variables table.

SHOW [SESSION
| LOCAL] VARIABLES statement:

• ON: MySQL 5.6 output.

• OFF: Output displays rows from the
  Performance Schema
  session_variables table. (In
  MySQL 5.7.6 and 5.7.7, OFF output does
  not fully reflect all system variable values in effect for
  the current session; it includes no rows for global
  variables that have no session counterpart. This is
  corrected in MySQL 5.7.8.)

SHOW GLOBAL
STATUS statement:

• ON: MySQL 5.6 output.

• OFF: Output displays rows from the
  Performance Schema
  global_status table, plus the
  Com_xxx
  statement execution counters.

  OFF output includes no rows for session
  variables that have no global counterpart, unlike
  ON output.

SHOW [SESSION |
LOCAL] STATUS statement:

• ON: MySQL 5.6 output.

• OFF: Output displays rows from the
  Performance Schema
  session_status table, plus
  the Com_xxx
  statement execution counters. (In MySQL 5.7.6 and 5.7.7,
  OFF output does not fully reflect all
  status variable values in effect for the current session;
  it includes no rows for global variables that have no
  session counterpart. This is corrected in MySQL 5.7.8.)

In MySQL 5.7.6 and 5.7.7, for each of the
SHOW statements just described, use of a
WHERE clause produces a warning when
show_compatibility_56=ON and an error when
show_compatibility_56=OFF. (This applies to
WHERE clauses that are not optimized away.
For example, WHERE 1 is trivially true, is
optimized away, and thus produces no warning or error.) This
behavior does not occur as of MySQL 5.7.8;
WHERE is supported as before 5.7.6.

Effect of show_compatibility_56 on INFORMATION_SCHEMA Tables

INFORMATION_SCHEMA tables
(GLOBAL_VARIABLES,
SESSION_VARIABLES,
GLOBAL_STATUS, and
SESSION_STATUS):

• ON: MySQL 5.6 output, with a
  deprecation warning.

• OFF: Selecting from these tables
  produces an error. (Before 5.7.9, selecting from these
  tables produces no output, with a deprecation warning.)

Effect of show_compatibility_56 on Performance Schema Tables

Performance Schema system variable tables:

• OFF:

  • global_variables: Global
    system variables only.

  • session_variables: System
    variables in effect for the current session: A row for
    each session variable, and a row for each global
    variable that has no session counterpart.

  • variables_by_thread:
    Session system variables only, for each active
    session.

• ON: Same output as for
  OFF. (Before 5.7.8, these tables
  produce no output.)

Performance Schema status variable tables:

• OFF:

  • global_status: Global
    status variables only.

  • session_status: Status
    variables in effect the current session: A row for
    each session variable, and a row for each global
    variable that has no session counterpart.

  • status_by_account Session
    status variables only, aggregated per account.

  • status_by_host: Session
    status variables only, aggregated per host name.

  • status_by_thread: Session
    status variables only, for each active session.

  • status_by_user: Session
    status variables only, aggregated per user name.

  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
  events_statements_summary_global_by_event_name
  and
  events_statements_summary_by_thread_by_event_name
  tables, respectively.

• ON: Same output as for
  OFF. (Before 5.7.9, these tables
  produce no output.)

Effect of show_compatibility_56 on Slave Status Variables

Replica status variables:

• ON: Several
  Slave_xxx
  status variables are available from
  SHOW STATUS.

• OFF: Some of those replica variables
  are not exposed to SHOW
STATUS or the Performance Schema status variable
  tables. The information they provide is available in
  replication-related Performance Schema tables. The
  following table shows which
  Slave_xxx
  status variables become unavailable in
  SHOW STATUS and their
  locations in Performance Schema replication tables.

  Status Variable	Performance Schema Location
  Slave_heartbeat_period	replication_connection_configuration table, HEARTBEAT_INTERVAL column
  Slave_last_heartbeat	replication_connection_status table, LAST_HEARTBEAT_TIMESTAMP column
  Slave_received_heartbeats	replication_connection_status table, COUNT_RECEIVED_HEARTBEATS column
  Slave_retried_transactions	replication_applier_status table, COUNT_TRANSACTIONS_RETRIES column
  Slave_running	replication_connection_status and replication_applier_status tables, SERVICE_STATE column

Effect of show_compatibility_56 on FLUSH STATUS

FLUSH STATUS statement:

• ON: This statement produces MySQL 5.6
  behavior. It adds the current thread’s session status
  variable values to the global values and resets the
  session values to zero. Some global variables may be reset
  to zero as well. It also resets the counters for key
  caches (default and named) to zero and sets
  Max_used_connections to
  the current number of open connections.

• OFF: This statement adds the session
  status from all active sessions to the global status
  variables, resets the status of all active sessions, and
  resets account, host, and user status values aggregated
  from disconnected sessions.