can't upgrade mysql with zabbix

[majed17]

sudo mysql_upgrade -uroot -p --force
Enter password:
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.gtid_slave_pos
Error : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status : Operation failed
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.innodb_index_stats
Error : Table 'mysql.innodb_index_stats' doesn't exist in engine
status : Operation failed
mysql.innodb_table_stats
Error : Table 'mysql.innodb_table_stats' doesn't exist in engine
status : Operation failed
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.transaction_registry
Error : Table 'mysql.transaction_registry' doesn't exist in engine
status : Operation failed
mysql.user OK

Repairing tables
mysql.gtid_slave_pos
Error : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status : Operation failed
mysql.innodb_index_stats
Error : Table 'mysql.innodb_index_stats' doesn't exist in engine
status : Operation failed
mysql.innodb_table_stats
Error : Table 'mysql.innodb_table_stats' doesn't exist in engine
status : Operation failed
mysql.transaction_registry
Error : Table 'mysql.transaction_registry' doesn't exist in engine
status : Operation failed
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1932 (42S02) at line 635: Table 'mysql.innodb_index_stats' doesn't exist in engine
ERROR 1243 (HY000) at line 636: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1932 (42S02) at line 638: Table 'mysql.innodb_table_stats' doesn't exist in engine
ERROR 1243 (HY000) at line 639: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1932 (42S02) at line 643: Table 'mysql.innodb_index_stats' doesn't exist in engine
ERROR 1932 (42S02) at line 647: Table 'mysql.innodb_table_stats' doesn't exist in engine
ERROR 1932 (42S02) at line 650: Table 'mysql.innodb_table_stats' doesn't exist in engine
FATAL ERROR: Upgrade failed


I tried dropping database zabbix and schema, deleting the idb files in mysql directry, restarting mysql, restoring the database, and then upgrading, with no effect!
this is happening on centos 8 with zabbix 5

[yurtesen]

This seems to be a bug in MySQL
https://stackoverflow.com/questions/...s-doesnt-exist

I would recommend using PostgreSQL in future. It has better performance and you can even use TimescaleDB etc.

[majed17]

now i get:

sudo mysql_upgrade -uroot -p --force
Enter password:
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1813 (HY000) at line 77: Tablespace for table '`mysql`.`innodb_table_stats`' exists. Please DISCARD the tablespace before IMPORT
ERROR 1813 (HY000) at line 81: Tablespace for table '`mysql`.`innodb_index_stats`' exists. Please DISCARD the tablespace before IMPORT
ERROR 1813 (HY000) at line 85: Tablespace for table '`mysql`.`transaction_registry`' exists. Please DISCARD the tablespace before IMPORT
ERROR 1813 (HY000) at line 153: Tablespace for table '`mysql`.`gtid_slave_pos`' exists. Please DISCARD the tablespace before IMPORT
ERROR 1146 (42S02) at line 635: Table 'mysql.innodb_index_stats' doesn't exist
ERROR 1243 (HY000) at line 636: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1146 (42S02) at line 638: Table 'mysql.innodb_table_stats' doesn't exist
ERROR 1243 (HY000) at line 639: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1146 (42S02) at line 643: Table 'mysql.innodb_index_stats' doesn't exist
ERROR 1146 (42S02) at line 647: Table 'mysql.innodb_table_stats' doesn't exist
ERROR 1146 (42S02) at line 650: Table 'mysql.innodb_table_stats' doesn't exist
FATAL ERROR: Upgrade failed

i am considering learning posgresql on a separate server, i really those zabbix is the problem as cacti with mysql causes no such problem..

[yurtesen]

This is not a problem related to zabbix. The errors are not even in related to zabbix. There are errors are related to mysql itself. The fact that another mysql installation did not exhibit same problem is probably coincidental. The errors mention mysql's own internal mysql database, nothing to do with zabbix database. It has problems processing mysql.* tables...

You may have better luck at a mysql forum.

[majed17]

since then i installed a new posgresql server with the same numbers of hosts but now the database is 23G, how to limit that?