Solved upgrading zabbix 4 to 5. Cannot run mysqldump

[fred974]

Hi,

I have a Zabbix 4 running on FreeBSD and I would like to upgrade to Zabbix 5.
Before doing anything I tried to run
mysqldump -u root --databases zabbix > zabbix44-backup.sql anf got the following error:

mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Table 'performance_schema.session_variables' doesn't exist (1146)

Could anyone please advise on how to resolve this issue?

Thank you

 

[richardtoohey2]

Have you upgraded MySQL already? Maybe there's a bit of a mismatch between client and server?

To work around this specific issue you could try excluding the dump of the performance_schema table - don't think you need to dump & restore that.

--ignore-table=db_name.tbl_name

But if you are trying to just dump the zabbix database, why is it even going to the performance_schema table? Looks like it is trying to dump all databases - is that what you wanted?

Would this work?

mysqldump -u root zabbix > zabbix44_backup.sql

 

[fred974]

Hi,

Yes i did a mysql update.
I ran mysql_upgrade -u root -p --force to resolved the issue

 

[richardtoohey2]

I've hit (almost) exactly the same error (during an upgrade from MySQL 5.6 to 5.7), but just from doing:

mysql> show variables like '%query%';
ERROR 1682 (HY000): Native table 'performance_schema'.'session_variables' has the wrong structure

but for me the missing link was to restart MySQL after the mysql_upgrade step:

[ERROR]Native table performance schema has the wrong structure

150814 9:09:14 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure 150814 9:09:14 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong

stackoverflow.com

So steps in MySQL 5.6 to 5.7 migration (assuming you want to do "in-place"):

Backup, backup, backup, check backup has everything (including VIEWs, TRIGGERs, etc!) And maybe another backup!
Stop MySQL 5.6.
Remove MySQL 5.6.
Install MySQL 5.7.
Start MySQL 5.7.
Run mysql_upgrade
Restart MySQL 5.7 <!-- the important part!

Once I'd done the restart the show variables worked fine.

And (cough) that's well-documented in the MySQL 5.7 manual:

MySQL :: MySQL 5.7 Reference Manual :: 2.10.3 Changes in MySQL 5.7

dev.mysql.com