zabbix upgrade database error Z3005

[voler]

Hi, when I try upgrade. I get error

using configuration file: /etc/zabbix/zabbix_server.conf
3628:20200604:082411.132 current database version (mandatory/optional): 04030004/04030004
3628:20200604:082411.132 required mandatory version: 04040000
3628:20200604:082411.132 optional patches were found
3628:20200604:082411.132 starting automatic database upgrade
3628:20200604:082411.144 [Z3005] query failed: [1366] Incorrect string value: '\xD0\x97\xD0\xB0\xD0\xB3...' for column 'error' at row 56 [insert into item_rtdata (iteate,i.mtime,i.error from items i join hosts h on i.hostid=h.hostid where h.status in (0,1) and i.flags<>2]
3628:20200604:082411.159 database upgrade failed

How I can fix it, but not lost data?

[tim.mooney]

What database (MySQL, MariaDB, PostgreSQL, etc.) and version is your database?

What character set and collation is your database using?

[voler]

What database - Mysql 5.7.30
What character character_set_connection - utf8, character_set_database - latin, character_set_system - utf8, collation_connection - utf8_general_ci, collation_database - latin1_swedish_ci, collation_server - latin1_swedish_ci

[voler]

4030004 what frontend use for this databases?

[tim.mooney]

Going back as far as Zabbix 2.0, the recommendation has been to use 'utf8' as the character set for the zabbix database. Keep in mind that with MySQL and MariaDB and Percona, 'utf8' is not full UTF-8 support, but that's not related to this problem.

As far as I can tell, the Zabbix docs and the initial database create statements were less specific about which collation to use, at least back in the 1.8 and 2.0 product versions.

With Zabbix 3.2.x, zabbix became more specific about its database character set and collation requirements . See: https://www.zabbix.com/documentation...rade_notes_320 , in particular, the note about "Case-sensitive MySQL database". Note that even the upgrade documentation uses the wrong terminology and confuses the character set with the collation.

At some point in the Zabbix 4.4 series (I think it's 4.4.6, it might be 4.4.7) they added checks so that the product will complain if your character set or collation is incorrect for any table. If you search these forums, you can probably find examples of people that ran into that issue.

If the information you posted in #3 for 'character_set_database' and 'collation_database' is true for your zabbix database, then your zabbix database has both incorrect character set and incorrect collation. Before you're going to be able to use any of the Zabbix 4.4.x or 5.x versions, you're going to need to fix both of those issues. In addition, since it appears that whatever character string your current 'latin' character set database holds is not compatible with utf8, you may also have to do a character set conversion of some of your data.

If you want more background information about character set incompatibilities, this article (not related to Zabbix) has some good information in it: https://nicj.net/mysql-converting-an...olumn-to-utf8/

If you want more information on how you might safely alter your database to convert it to character set utf8 and collation utf8_bin, you may want to review: https://support.zabbix.com/browse/ZBX-17357