Tuesday, August 04, 2015

Yet another MySQL 5.7 silent change

When a new version of MySQL appears, the first source of information for the brave experimenter is a page in the manual named What is new in MySQL X.X, also known as MySQL in a nutshell. For MySQL 5.7, the in-a-nutshell page lists quite a lot of changes. In that page, the list of removed features is enough to send a chill down the spine of most any DBA. Some of the items in the deprecation section are removals in disguise, as they require immediate, rather than delayed, action to use the new version with existing application (SET PASSWORD comes to mind immediately.)

In all that abundance of changes, there is at least one that may cause huge discomfort to many users:

mysql [localhost] {msandbox} (information_schema) > select @@version;  
+-----------+  
| @@version |  
+-----------+  
| 5.7.8-rc  |  
+-----------+  
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (information_schema) > select * from GLOBAL_STATUS;  
Empty set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (information_schema) > show warnings\G  
*************************** 1. row ***************************  
  Level: Warning  
   Code: 1287  
Message: 'INFORMATION_SCHEMA.GLOBAL_STATUS' is deprecated and will be removed in  
a future release. Please use performance_schema.global_status instead  
1 row in set (0.00 sec)



Did I miss a deprecation information in MySQL 5.6? Apparently not. The manual says that information_schema.GLOBAL_STATUS is deprecated as of 5.7.6 (and so are GLOBAL_VARIABLES, SESSION_STATUS, and SESSION_VARIABLES).

Until recently, a deprecation notice used to mean that the feature would be removed in a future version, but it remained in place, so I could get organized to adapt my procedures to the recommended changes. Therefore I would expect that a deprecation in 5.7 would become a removal in 5.8, and a removal in 5.7 would mean that the feature had been deprecated in a previous release. But in this case, the deprecation is effectively killing the feature today. It’s no use to me if the information_schema.GLOBAL_VARIABLES is still there when it does not return results. This change can break lots of procedures that check the %_STATUS and %_VARIABLES tables to see if a given variable is available or not. For example, after enabling the semi-sync replication plugin, we want to check if the procedure was successful:

master [localhost] {msandbox} ((none)) > select version();  
+------------+  
| version()  |  
+------------+  
| 5.6.25-log |  
+------------+  
1 row in set (0.00 sec)

master [localhost] {msandbox} ((none)) > select * from information_schema.global_variables where variable_name='rpl_semi_sync_master_enabled';  
+------------------------------+----------------+  
| VARIABLE_NAME                | VARIABLE_VALUE |  
+------------------------------+----------------+  
| RPL_SEMI_SYNC_MASTER_ENABLED | ON             |  
+------------------------------+----------------+  
1 row in set (0.00 sec)

The same query does not give us what we expect in MySQL 5.7.8.

master [localhost] {msandbox} ((none)) > select * from information_schema.global_variables where variable_name='rpl_semi_sync_master_enabled';  
Empty set, 1 warning (0.00 sec)

master [localhost] {msandbox} ((none)) > show warnings\G  
*************************** 1. row ***************************  
  Level: Warning  
   Code: 1287  
Message: '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)

When we query the new table, we get it.

master [localhost] {msandbox} ((none)) > select * from performance_schema.global_variables where variable_name='rpl_semi_sync_master_enabled';  
+------------------------------+----------------+  
| VARIABLE_NAME                | VARIABLE_VALUE |  
+------------------------------+----------------+  
| rpl_semi_sync_master_enabled | ON             |  
+------------------------------+----------------+  
1 row in set (0.00 sec)

However, if we had an automated procedure that was querying the information_schema table for this variable, the result of the operation would suggest that the plugin had not been installed yet or that the installation had failed.

If this is a true deprecation, the old table should continue working. If it is a change like removing the password column or changing the syntax of SET PASSWORD, users would be better off with a complete removal of the offending table. In that case, at least they would be notified of the problem and fix it. As it is now, they may waste time trying to find the cause of incomprehensible failures such as Bug#77732.

2 comments:

Ivan said...

Hi GIuseppe,

Thanks for this post, I am still catching up with the latest and this kind of articles help me a lot.

I am really confused by the whole story.

Googling a bit I found this:
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_show_compatibility_56

i.e. a variable that should be enabled by default and, as far as I understand, does not change the server behaviour (meaning the INFORMATION_SCHEMA will show the same info as in 5.6). Unfortunately I do not have a 5.7.X at hand to test it right now, but it looks like you should not have the problem you mentioned.

That said, the funny part of the story is that the variable has been introduced in 5.7.6 _and_ deprecated in 5.7.6... weird.

On top of it, I am not sure system variables are part of a performance schema, but as I said, I missed a lot of CPU cycles, so there must be a good reason for it.
The picky part of me would also argue that a variable with "show_" as a prefix that sets something, is pretty confusing.


Thanks again and hope to see you soon!
-ivan

Giuseppe Maxia said...

@Ivan,
The variable show_compatibility_56 was enabled by default in MySQL 5.7.6 and 5.7.7. However, in MySQL 5.7.8 it is disabled.

The behavior is demonstrated and there is at least one bug that shows the side effects of this change.

If you install both MySQL 5.7.7 and 5.7.8 without any additional options, and try "SELECT variable_name from information_schema.global_variables limit 1" in both versions, you will see the difference.

System variables are now part of performance_schema. I was as surprised as you are when I saw them. If you haven't had any contact with MySQL 5.7 and plan to use in the next future, you should really have a serious look at the new features and changes.

Back to the main point: enabling show_compatibility_56 in MySQL 5.7.8 would solve the problem temporarily, but it will hide the rest of the compatibility problems, which will hit you when the deprecation will cause the features to disappear for good.