MySQL教程

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

Command-Line Format --show-compatibility-56[={OFF|ON}]
Deprecated Yes
System Variable show_compatibility_56
Scope Global
Dynamic Yes
Type Boolean
Default Value OFF

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_
    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_
    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_
    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_
    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_
    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_
    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.


数据运维技术 » MySQL Variables show_compatibility_56 数据库 参数变量解释及正确配置使用
分享到：

相关推荐