Dear All,
I am running a CentOS 7 three node XtraDB cluster ususally with yum-cron enabled and usually with no issues.
The repo now contains 5.7.17-27.20.2.el7.x86_64 packages. After yum-cron installed them no two of the nodes, nagios showed “ERROR 1682 (HY000) at line 1: Native table ‘performance_schema’.‘global_variables’ has the wrong structure” on these two.
I disabled yum-cron, downgraded the packages on the nodes in question, rebooted these nodes and things were OK again without the above error. After that, I also ran mysql_upgrade to be on the safe side.
Next step was that I manually upgraded the nodes and ran mysql_upgrade thereafter. That did not make a difference, unfortunately. The error did remain on the same two nodes.
Then, I downgraded the packages again. Unfortunately, the error does now stay. The cluster does seem to run, but nagios output is no longer really useful. Only the single node which was not touched does show cluster size and flow controll (both OK), while the others just show the error in place of these values.
Can someone please shed some light on how to deal with this? Release notes do not seem to be on the website, yet.
Regards,
Michael Schefczyk
Hello,
Indeed this system table gets changed with the last version upgrade, so after I tested upgrade from PXC 5.7.16-10-57 to PXC 5.7.17-11-57, this error occurs:
mysql> show global variables like 'version%';
ERROR 1682 (HY000): Native table 'performance_schema'.'global_variables' has the wrong structure
mysql> select @@version,@@version_comment;
+--------------+-------------------------------------------------------------------------------------------------+
| @@version | @@version_comment |
+--------------+-------------------------------------------------------------------------------------------------+
| 5.7.17-11-57 | Percona XtraDB Cluster (GPL), Release rel11, Revision e2a7fdd, WSREP version 27.20, wsrep_27.20 |
+--------------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Solution to that is running mysql_upgrade script on upgraded node, and later restarting it. Restart is needed since this is performance_schema table.
If you have the same error after downgrading, it seems that this table definition was already upgraded, so you should either run mysql_upgrade again, or upgrade back.
Old table definition was:
mysql> show global variables like 'version';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| version | 5.7.16-10-57 |
+---------------+--------------+
1 row in set (0.01 sec)
mysql> show create table performance_schema.global_variables\G
*************************** 1. row ***************************
Table: global_variables
Create Table: CREATE TABLE `global_variables` (
`VARIABLE_NAME` varchar(64) NOT NULL,
`VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
New one is:
mysql> show global variables like 'version';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| version | 5.7.17-11-57 |
+---------------+--------------+
1 row in set (0.01 sec)
mysql> show create table performance_schema.global_variables\G
*************************** 1. row ***************************
Table: global_variables
Create Table: CREATE TABLE `global_variables` (
`VARIABLE_NAME` varchar(64) NOT NULL,
`VARIABLE_VALUE` varchar(2048) DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Dear Przemek,
Thank you very much! As I was not certain about the outcome, I did backup /var/lib/mysql/ on all nodes while they were stopped. Then, I upgraded all of them (which I did not dare to previously given the errors which do not go away quickly after upgrading (yum upgrade and mysql_upgrade) just one or two nodes), ran mysql_upgrade -uroot -p --force on all of them and then rebooted. After a short while, the error was gone.
Regards & greetings from Dresden,
Michael