25

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

the
  • 21,007
  • 11
  • 68
  • 101
CarlM24
  • 1,251
  • 1
  • 9
  • 8
  • Can you define "breaks MYSQL"? – ceejayoz Aug 03 '17 at 23:53
  • Yes, when I add "show_compatibility_56 = ON" to the config file and then try to restart it, it says "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)."     – CarlM24 Aug 04 '17 at 00:07
  • Put that information in your question, not buried in a comment. It's part of the question, and should have been there in the first place. You can find the [edit] link below the tags. – Ken White Aug 04 '17 at 00:09
  • https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_show_compatibility_56 – Ken White Aug 04 '17 at 00:12
  • I appreciate your help, Ken, but the link that you sent me doesn't help fix my problem. It only tells me information about the show_compatibility_56 variable. – CarlM24 Aug 04 '17 at 00:21

3 Answers3

90

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)

  1. Cause:

show_compatibility_56 is deprecated

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

CarlM24
  • 1,251
  • 1
  • 9
  • 8
6

In mysqld.cnf under [mysqld] section add this line:

show_compatibility_56 = On

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

AndreyP
  • 2,510
  • 1
  • 29
  • 17
1

If you come here due to getting this error while trying to use any of:

INFORMATION_SCHEMA.GLOBAL_VARIABLES
INFORMATION_SCHEMA.SESSION_VARIABLES

INFORMATION_SCHEMA.GLOBAL_STATUS
INFORMATION_SCHEMA.SESSION_STATUS

Then you should probably change to use performance_schema instead, see: https://dev.mysql.com/doc/refman/5.7/en/performance-schema-variable-table-migration.html

NiKiZe
  • 1,256
  • 10
  • 26