Bug #79370 Change warning/error message regarding show_compatibility_56
Submitted: 21 Nov 2015 8:18 Modified: 9 Oct 2017 8:45
Reporter: Simon Mudd (OCA) Email Updates:
Status: Won't fix Impact on me:
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.7.9 OS:Any
Assigned to: Marc Alff CPU Architecture:Any
Tags: 5.7.9, global_variables, information_schema, performance_schema, show_compatibility_56

[21 Nov 2015 8:18] Simon Mudd
Doing the following on a server I see a known issue:

root@localhost [(none)]> select * from information_schema.global_variables where variable_name = 'XXX';
ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'
root@localhost [(none)]> select * from information_schema.global_variables where variable_name = 'XXX';
Empty set, 1 warning (0.00 sec)

root@localhost [(none)]> show warnings;
| Level   | Code | Message                                                                                                                                             |
| Warning | 1287 | 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' is deprecated and will be removed in a future release. Please use performance_schema.global_variables instead |
1 row in set (0.00 sec)

Technically this is all fine, but fundamentally it is wrong. You are pushing users to use backwards compatibility without actually telling them _first_
how to fix the problem.

How to repeat:
See above.

Suggested fix:
You want to move forward to make people use P_S.GLOBAL_VARIABLES.
For people who have not set SHOW_COMPATIBILITY_56 I suggest you change the error message to:

ERROR 3167 (HY000): 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' is deprecated and will be removed in a future release. Please use performance_schema.global_variables, or set show_compatibility = 1 as a workaround in this version.

End result: You point out the "correct solution", and you provide a away to avoid that as a short-term measure. That's much better than convincing everyone to set SHOW_COMPATIBILITY_56 = 1 on all their 5.7 servers only to find out the effect it has later when you drop this variable.
[21 Nov 2015 8:22] Simon Mudd
I also notice with this setting that it can not be changed at the session level. Probably doing that now is not worth changing but it does mean that if the server administrator has not set SHOW_COMPATIBILITY_56 the user can not fix this by setting up a session specific setting here. It therefore requires them to change the code. For shared servers this may or may not be an issue.
[21 Nov 2015 8:27] Simon Mudd
Hm. Sorry I mispasted. After the first select I did:

root@localhost [(none)]> set GLOBAL show_compatibility_56 = 1;
Query OK, 0 rows affected (0.00 sec)
[21 Nov 2015 13:09] Umesh Shastry
Hello Simon,

Thank you for the report.

[9 Oct 2017 8:44] Marc Alff
Hi Simon,

I totally agree that the warning text and the documentation could have been better for show_compatibility_56, to prevent confusion and provide more guidance.

Given that this setting is transitional (used for the 5.6 -> 5.7 migration),
and is now removed in 8.0, changing this in 5.7 only now will cause more harm and additional confusion than good, so there are no plans to make any change to 5.7 at this point.

Valid report, but changing to "won't fix" as the issue itself is no longer relevant for the 5.7 -> 8.0 migration.