Detailed solution to The INFORMATION_SCHEMA.xxx feature is disabled; see the documentation for show_compatibility_56

Article directory

1. Reproducing the error
2. Analysis errors
3. Solve the problem

1. Reproduction error

Today, when using MySQL to query global variables, the following error was reported:

mysql> select * from information_schema.global_variables;

ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'

That is, The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'.

At the same time, when using MySQL to query the global status, the following error is reported:

mysql> select * from information_schema.global_status;

ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56'

That is, The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56'.

2. Analysis error

Translate The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56' into Chinese, which is 'INFORMATION_SCHEMA.GLOBAL_VARIABLES" feature is disabled; see the documentation for 'show_compatibility_56' Documentation for “show_compatibility_56”.

Likewise, the INFORMATION_SCHEMA.GLOBAL_STATUS feature is disabled.

INFORMATION_SCHEMA.GLOBAL_VARIABLES and INFORMATION_SCHEMA.GLOBAL_STATUS. Why these two tables are disabled depends on the version of MySQL.

These two tables provide information related to system variables.

Starting from MySQL 5.7.6, these two tables have been abandoned and will be removed in subsequent versions. Use the following sql to query my MySQL code>Version:

mysql> select version();

```dart
 + ------------ +
| version() |
 + ------------ +
| 5.7.16-log |
 + ------------ +
1 row in set (0.00 sec)

This feature is controlled by the show_compatibility_56 parameter, which controls both system variables and status variables. This parameter defaults to OFF starting from 5.7.8.

My `MySQL` version is `5.7.16`, therefore, `INFORMATION_SCHEMA.GLOBAL_VARIABLES` and `INFORMATION_SCHEMA.GLOBAL_STATUS` are disabled (obsolete), but the information can be queried in the `Performance_schema` database:

- `performance_schema.global_variables`

```sql
mysql> select * from performance_schema.global_variables;
 +----------------------------- +----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
 +----------------------------- +----------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| avoid_temporal_upgrade | OFF |
| ...... | ...... |
 +----------------------------- +----------------+

491 rows in set (0.00 sec)

performance_schema.global_status

mysql> select * from performance_schema.global_status;
 + ------------------------------ + ----------------- +
| VARIABLE_NAME | VARIABLE_VALUE |
 + ------------------------------ + ----------------- +
| Aborted_clients | 8 |
| Aborted_connects | 1 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| ...... | ...... |
 + ------------------------------ + ----------------- +
206 rows in set (0.00 sec)

Of course, in addition to using performance_schema, system variables and status variables after MySQL 5.6 can also be obtained using the show command:

SHOW VARIABLES , as shown below:

SHOW STATUS, as shown below:

The data for SHOW VARIABLES and SHOW STATUS comes from the following table

INFORMATION_SCHEMA.GLOBAL_VARIABLES
INFORMATION_SCHEMA.SESSION_VARIABLES
INFORMATION_SCHEMA.GLOBAL_STATUS
INFORMATION_SCHEMA.SESSION_STATUS

After MySQL 5.7.6 started, it was changed to obtain it from the following table:

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

Starting from this version, MySQL introduced the show_compatibility_56 parameter to control where the show command gets the value from.

When show_compatibility_56 is ON, it means that the compatibility of MySQL 5.6 is enabled. At this time, it remains the same as MySQL5.6.

If show_compatibility_56 is OFF, it means that compatibility is disabled. At this time, the table information is obtained through peformance_schema, and then the information_schame< is directly queried. /code> will report the above error.


Use the following code to check show_compatibility_56 to see if it is turned off:
mysql> show variables like '%show_compatibility_56%';
 + ----------------------- + ------- +
| Variable_name | Value |
 + ----------------------- + ------- +
| show_compatibility_56 | OFF |
 + ----------------------- + ------- +
1 row in set, 1 warning (0.00 sec)

3. Solve the problem
 You can use the following methods to solve this problem:


Use performance_schema.global_variables and performance_schema.global_status to query system variables, as shown in the above code.

mysql> select * from performance_schema.global_variables where variable_name like '%timeout%';
 +----------------------------- +----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
 +----------------------------- +----------------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
 +----------------------------- +----------------+
13 rows in set, 1 warning (0.00 sec)


Use show variables and show status to query system variables

mysql> show variables like '%%timeout';
 + -------------------------- + ---------- +
| Variable_name | Value |
 + -------------------------- + ---------- +
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
 + -------------------------- + ---------- +
13 rows in set, 1 warning (0.00 sec)


Set show_compatibility_56 to on for compatibility with previous versions:

mysql> set global show_compatibility_56 = ON;
Query OK, 0 rows affected (0.00 sec)

Check whether the setting is successful:
mysql> show variables like '%show_compatibility_56%';
 + ----------------------- + ------- +
| Variable_name | Value |
 + ----------------------- + ------- +
| show_compatibility_56 | ON |
 + ----------------------- + ------- +
1 row in set (0.00 sec)

As follows, you can query data in the information_schema.global_variables table:

mysql> select * from information_schema.global_variables where variable_name like '%timeout%' ;
 +----------------------------- +----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
 +----------------------------- +----------------+
| INNODB_FLUSH_LOG_AT_TIMEOUT | 1 |
| RPL_STOP_SLAVE_TIMEOUT | 31536000 |
| INNODB_ROLLBACK_ON_TIMEOUT | OFF |
| DELAYED_INSERT_TIMEOUT | 300 |
| CONNECT_TIMEOUT | 10 |
| SLAVE_NET_TIMEOUT | 60 |
| NET_WRITE_TIMEOUT | 60 |
| INNODB_LOCK_WAIT_TIMEOUT | 50 |
| LOCK_WAIT_TIMEOUT | 31536000 |
| HAVE_STATEMENT_TIMEOUT | YES |
| NET_READ_TIMEOUT | 30 |
| INTERACTIVE_TIMEOUT | 28800 |
| WAIT_TIMEOUT | 28800 |
 +----------------------------- +----------------+
13 rows in set, 1 warning (0.00 sec)