Rhel 7.3, MySQL DB, Zabbix-server 4.0.3

Tried to use such a walkaround:

[client]
character-set-server = utf8
skip-character-set-client-handshake
collation-server = utf8_bin
character_set_connection = utf8
character_set_result = utf8
character_set_client = utf8

No effect at all

Could you please show output of show create table triggers; ?

MariaDB [zabbix]> show create table triggers;

+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| triggers | CREATE TABLE `triggers` (
 `triggerid` bigint(20) unsigned NOT NULL,
 `expression` varchar(2048) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
 `description` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `url` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `status` int(11) NOT NULL DEFAULT '0',
 `value` int(11) NOT NULL DEFAULT '0',
 `priority` int(11) NOT NULL DEFAULT '0',
 `lastchange` int(11) NOT NULL DEFAULT '0',
 `comments` text COLLATE utf8_bin NOT NULL,
 `error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
 `templateid` bigint(20) unsigned DEFAULT NULL,
 `type` int(11) NOT NULL DEFAULT '0',
 `state` int(11) NOT NULL DEFAULT '0',
 `flags` int(11) NOT NULL DEFAULT '0',
 `recovery_mode` int(11) NOT NULL DEFAULT '0',
 `recovery_expression` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
 `correlation_mode` int(11) NOT NULL DEFAULT '0',
 `correlation_tag` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
 `manual_close` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`triggerid`),
 KEY `triggers_1` (`status`),
 KEY `triggers_3` (`templateid`),
 KEY `triggers_2` (`value`,`lastchange`),
 CONSTRAINT `c_triggers_1` FOREIGN KEY (`templateid`) REFERENCES `triggers` (`triggerid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

Could you please show output of "show create table problems;" too?

What version of Zabbix did you use before the upgrade?

Threre is no table named "problems", it's "problem".

Here is the output:

The origin Zabbix server version is 3.2.7. The database was not imported or moved, the clone of the VM was made instead and Zabbix binaries updated through rpm -U, the DB was upgraded through the zabbix server service start.

Threre is no table named "problems", it's "problem".

Sorry, my bad. Table's name is indeed "problem".

It looks like there are some problems with charsets and/or collations. Your "triggers" table has utf8 charset and utf8_bin collation which is expected. Your "problem" table has latin1 charset. That's why trigger configurations are fine, but "Dashboard" and "Problems" views display "???" instead of normal text.

Can you please show output of following queries (replace <dbname> with actual name)?

show create database <dbname>;
select table_name, engine, table_collation from information_schema.tables where table_schema="<dbname>";

If you still have database from Zabbix 3.2.7, it would be good to see output of these queries for that database as well.

Original database on 3.2.7 gives the following replies:

MariaDB [zabbix]> show create database zabbix;
------------------------------------------------------------------------------------------+

------------------------------------------------------------------------------------------+

------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [zabbix]> select table_name, engine, table_collation from information_schema.tables where table_schema="zabbix";
---------------------------------------------------

---------------------------------------------------

---------------------------------------------------
134 rows in set (0.00 sec)

The DB under version 4.0 gives the following:

MariaDB [zabbix]> show create database zabbix;
------------------------------------------------------------------------------------------+

------------------------------------------------------------------------------------------+

------------------------------------------------------------------------------------------+

MariaDB [zabbix]> select table_name, engine, table_collation from information_schema.tables where table_schema="zabbix";
---------------------------------------------------

---------------------------------------------------

---------------------------------------------------

It seems the collation latin1_swedish_ci is the problem, but I can't say why everything works in 3.2.7, and I've got problems in 4.0.3. Probably I can try to change the collation of the "problem" table and retry with zabbix-server?

The likely source of non-ASCII characters are macro values. In 3.2 they are resolved in Problems "on-the-fly" by frontend, in 4.0 they are resolved by server and written into DB, new frontend just shows them as they are in DB. Fixing character set now will only affect new problems, to fix all of them you will probably need to restore the backup, fix character sets and redo the upgrade.

Or, perhaps, you can hack around by inserting ZBX_TM_TASK_UPDATE_EVENTNAMES task manually, this can save you from redoing the upgrade, but I will not guarantee that it will work.

I've doublechecked my vanilla 4.0 deployment and all the tables are in utf8_bin collation. Looks like collation is the real problem.

I will try to restore the DB backup from 3.2.7 server over the DB on 4.0 machine, fix the character sets and redo upgrade. An get back when finished.

Thank you very much for help, I've successfully reloaded a db dump, fixed collation and charset, upgraded and now no problem with question marks!!!

Everyone - THANK YOU VERY MUCH!

I think this feature of 4.0 woth DB char set is worth mentioning in manuals for upgrade. =)

1. Fix char set and collation for tables BEFORE the upgrade
2. Upgrade
3. No issues

(1) [D] Upgrade procedure should contain a warning about the fact that character set utf8 and utf8_bin collation is required for Zabbix server to work properly with MySQL database.

Please add a note in db_scripts:
Character set utf8 and utf8_bin collation is required for Zabbix server to work properly with MySQL database.

Also please change upgrade notes

Case-sensitive MySQL database required for proper server work.

to

Case-sensitive MySQL database required for proper server work.
Character set utf8 and utf8_bin collation is required for Zabbix server to work properly with MySQL database.

martins-v Suggested changes added to Database creation scripts for 4.2 and other supported versions. I think that should be sufficient. RESOLVED

vso CLOSED

There is nothing specific about upgrade. It is documented that

UTF-8 is the only encoding supported by Zabbix.

If the reported had used non-ASCII characters in trigger names outside of macro values, the question marks in problems would be noticed much earlier, while still running 3.2.

I think there is nothing to add to documentation regarding this issue. But it may be a good idea for Zabbix to check database character set (and schema in general) on startup.

It's strange that 3.2 works fine with the same database with incorrect charset for several tables, though the db charset is OK. IMHO it's a good thing to notice that 4.0 has to be upgraded with the revised db.

You are lucky. UTF-8 is backward compatible with ASCII, therefore if you only use code points from U+0000 to U+0127 in the configuration there will be no data loss when saving UTF-8 text as any of extended ASCII character sets.

One more recommendation could be to use STRICT_TRANS_TABLES mode which could have prevented database corruption during upgrade (I think...). See a relevant discussion around here.

(..) which could have prevented database corruption during upgrade (..)

I don't see any corruption in this case. Upgrade added some new tables (all of them have utf8_bin collation), collation of "graph_theme" table changed from "latin1_swedish_ci" to "utf8_bin" for some reason, but collations of other tables were left untouched.

Or was it upgrade process that populated "problem" table with data?

It's unclear why many tables had "latin1_swedish_ci" collation before the upgrade, though.

I don't see any corruption in this case.

Zabbix clearly wasn't writing question marks into problem table during upgrade (see ZBXNEXT-4108 for details). It was writing UTF-8 characters. MySQL couldn't write them into latin1... tables and silently replaced with question marks. Isn't it a corruption?

It was MariaDB, not MySQL. On MySQL this would fail as far as I know.

Depends on sql_mode I guess.