MySQL 5.7 show_compatibility_56
Recently, I decided to get a VPS server and host my own forums as we grew too big for your average shared hosting platform. I thought it would be easy to setup and get going, and it has been for the most part, but I have hit a brick wall with a hellish MySQL error.
Basically, I am trying to upload my old database inside PHPMYADMIN, but I keep getting the same error bounce back. I have followed some excellent guides, such as those from Digital Ocean, but there doesn't seem to be much on this error, and the information is out there isn't working for me.
Here is the error: Error SQL query: 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 have tried adding show_compatibility_56 = ON inside my config file /etc/mysql/my.cnf, I get the following error:
"Job for mysql.service failed because the control process exited with error code." add when I go to PHPMYADMIN it says "Connection for controluser as defined in your configuration failed." and " #2002 - No such file or directory
The server is not responding (or the local server's socket is not correctly configured)."
I can tell you show_compatibility_56 is turned off as it shows it is turned off inside PHPMYADMIN.
Can anyone tell me how to turn it on?
I am using Ubuntu 16.04 with LAMP
Answer
This is the information I found: 1. Problem description:
When performing a MySQL database backup, the following error message is received in the backup report:
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE / FROM GLOBAL_STATUS': The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled: see the documentation for 'show_compatibility_56' (3167)
- Cause:
show_compatibility_56 is deprecated
- Resolution:
Run the following query in your MySQL database:
set @@global.show_compatibility_56=ON;
So it looks like it was a very simple fix after all. I ran the code in the MYSQL console inside PHPMYADMIN.
Credit for the fix 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-