MySQL 5.7 show_compatibility_56

Question

Recently, I decided to get a VPS server and host my own forums, as we have become too large for your medium platform for general hosting. I thought it would be easy to set up and get started, and that was for the most part, but I hit a brick wall with a hellish MySQL error.

Basically, I'm trying to load my old database into PHPMYADMIN, but I still return the same error. I followed great tutorials like Digital Ocean, but it doesn't seem to be that much and the information does not work for me.

Here is the error: SQL query error: documentation

/*!50112 SELECT COUNT(*) INTO @is_rocksdb_supported FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='rocksdb_bulk_load' */ MySQL said: Documentation #3167 - The INFORMATION_SCHEMA.SESSION_VARIABLES feature is disabled; see the documentation for show_compatibility_56 

I tried to add show_compatibility_56 = ON inside my /etc/mysql/my.cnf configuration file, I get the following error:

"The operation for mysql.service failed because the management process failed with an error code." add, when I go to PHPMYADMIN, it says: "The connection for controluser, as defined in your configuration, failed." and "# 2002 - There is no such file or directory
Server is not responding (or local server socket is not configured correctly).

I can say that show_compatibility_56 is turned off because it shows that it is turned off inside PHPMYADMIN.

Can someone tell me how to turn it on?

I am using Ubuntu 16.04 with LAMP

Answer 1

This is the information I found: 1. Description of the problem:

When backing up a MySQL database, the following error message appears in the backup report:

mysqldump: Failed to execute "SELECT / *! 40001 SQL_NO_CACHE / FROM GLOBAL_STATUS ": the function "INFORMATION_SCHEMA.GLOBAL_STATUS" is disabled: see the documentation for "show_compatibility_56" (3167)

2. Cause:

show_compatibility_56 is deprecated

3. Resolution:

Run the following query in the MySQL database:

 set @@global.show_compatibility_56=ON; 

So it looks like it was a very simple solution. I ran the code in the MYSQL console inside PHPMYADMIN.

Correction credit goes to this page: https://support.servosity.com/hc/en-us/articles/221071687-ISSUE-The-INFORMATION-SCHEMA-GLOBAL-STATUS-feature-is-disabled-see-the- documentation-for-show-compatibility-56-

Answer 2

In mysqld.cnf in the [mysqld] section, add this line:

 show_compatibility_56 = On 

and restart mysqld (for ubuntu its sudo service mysql restart )