11

I needed to set up the system variable "show_compatibility_56" to ON in MySQL. So, I run the command set global show_compatibility_56 = on;, and it worked However, I noticed that whenever I stop and then start the MySQL server, this variable goes back to OFF. Any hints on how to keep it always ON even if I restart the server?

the
  • 21,007
  • 11
  • 68
  • 101
Dhoha
  • 369
  • 3
  • 6
  • 17

5 Answers5

23

I'm using a Laravel Homestead (Vagrant) box (MySql Ver 14.14 Distrib 5.7.17).

I needed to SSH into Homestead and then run:

echo "[mysqld]
show_compatibility_56 = ON
performance_schema" | sudo tee -a /etc/mysql/conf.d/mysql.cnf  >/dev/null    
sudo service mysql restart

(Thanks to Mark Reed for showing how to skip opening vim.)

Older version:

sudo vim /etc/mysql/conf.d/mysql.cnf

Then I added this section:

[mysqld]
show_compatibility_56 = ON
performance_schema

I was surprised that other answers here and elsewhere on the web didn't specify that it needed to be under [mysqld] instead of [mysql] and also that you must restart the MySql service:

sudo service mysql restart
Ryan
  • 22,332
  • 31
  • 176
  • 357
  • 2
    exactly, nobody gave a shit about [group] where to put that stupid configuration option – Marecky Aug 13 '17 at 23:21
  • More help is `SHOW SESSION VARIABLES where Variable_name like '%show_compatibility%'` . It helps to test from SQL editor is it good yet or not – Marecky Aug 13 '17 at 23:27
  • 2
    Ha, Google took me to my own answer 3 months later now that I'm facing this problem *again*. Win! – Ryan Aug 28 '17 at 20:04
  • If you've installed mysql with homebrew and have homebrew services installed, restart mysql with `brew services restart mysql`. – nickcoxdotme Mar 02 '18 at 20:26
3

you need to save this variable setting in your configuration file my.cnf for linux and my.ini for windows.

Zafar Malik
  • 6,734
  • 2
  • 19
  • 30
  • Can you tell me please where can I find the my.ini file (I mean the path)? – Dhoha Nov 16 '15 at 08:22
  • As you are asking my.ini, so you can check at "C:\Program Files (x86)\MySQL\MySQL Server 5.5\my.ini" pathg just change version if you have different version and also there should be different program files (x86) as per your windows version. – Zafar Malik Nov 16 '15 at 08:54
  • I found this file 'my-default.ini' and I think this is the file that you are talking about. I added this line to it show_compatibility_56= on. However, it still not working properly and this property is set up to off once I stop and start the server.. Any hints? – Dhoha Nov 16 '15 at 10:46
  • make this file one more copy with name my.ini on same location and update your configuration in this file and then restart your mysql service. – Zafar Malik Nov 16 '15 at 11:15
  • Still it is not working although I have the my.ini file... Is adding the line show_compatibility_56= on is correct. Or I should write something else? – Dhoha Nov 17 '15 at 06:11
  • 1
    try with "show_compatibility_56 = 1", don't forgot to restart mysql service. – Zafar Malik Nov 17 '15 at 07:01
  • your mysql version...check by followig command if you are getting this variable "SHOW VARIABLES LIKE 'show_compatibility_56';" as this variable introduces from 5.7.6 – Zafar Malik Nov 17 '15 at 11:16
  • Yes, I am getting the record, but I couldn't make the changes of this system variable 'show_compatibility_56' permanent. Whenever, I restart the server, it is assigned again OFF... – Dhoha Nov 18 '15 at 08:47
  • It is a bit long for a comment. Can I get ur email @, so I will email it to you? – Dhoha Nov 18 '15 at 13:01
  • This late reply just for future users, if someone is using a code from information_schema like "SELECT DATE_SUB(NOW(), INTERVAL variable_value SECOND) started_at FROM information_schema.global_status WHERE variable_name='Uptime';" then no need to change this variable just change information_schema to performance_schema like "SELECT DATE_SUB(NOW(), INTERVAL variable_value SECOND) started_at FROM performance_schema.global_status WHERE variable_name='Uptime';"....Even if some one using such type queries in program/script (which should not be a case) and don't want to change in script then do it. – Zafar Malik Sep 05 '16 at 11:00
2

To make it permanent, you need to add this variable in configuration file of MySQL like we did for all other variables as:

show_compatibility_56 = ON

For Linux based system: File name is my.cnf and default location is /etc/my.cnf

For Windows based system: File name is my.ini and default location is your windows mysql data directory that you can check via below command:

show variables like 'datadir';

Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81
1

If you've installed MySQL through Hombrew on a Mac, there isn't a my.cnf by default. I created one in /etc/my.cnf, added the text from @Ryan's answer:

[mysqld]
show_compatibility_56 = ON
performance_schema

... and then restarted MySQL, with (I'm using the older 5.7 version):

$ brew services restart mysql@5.7

This worked for me.

Elliot Larson
  • 10,669
  • 5
  • 38
  • 57
0

As Zafar has already pointed you can set the variable in the configuration file to save the value.

Also note that this is now deprecated. The manual says:

Note:

show_compatibility_56 is deprecated because its only purpose is to permit control over deprecated system and status variable information sources that will be removed in a future MySQL release. When those sources are removed, show_compatibility_56 will have no purpose and will be removed as well.

Community
  • 1
  • 1
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331