Unsupported charset or collation for tables

[aby]

bs"d

Hi guys,

I created a new Zabbix server (version 6) and imported the DB from my old server Zabbix 4.
The steps I performed are:

1. Export the DB from the old server
   1. mysqldump -u zabbix -p zabbix > /tmp/zabbix_db_dump
2. After installing the new server, I created the DB with these commands.
   1. mysql -uroot -p"thepasswd"
   2. create database zabbix;
   3. create user 'zabbix'@'localhost' identified by 'PASSWORD';
   4. use zabbix;
   5. grant all privileges on zabbix.* to 'zabbix'@'localhost';
3. Import the DB to the new server
   1. mysql -u zabbix -p zabbix < zabbix_db_dump

In the Reports --> System information, I have this error.

Unsupported charset or collation for tables: lld_override_optag, service_problem_tag, lld_override_ophistory, auditlog, role, sla_service_tag, lld_override_operation, config_autoreg_tls, sla_excluded_downtime, host_inventory, sysmaps_element_tag, media_type_param, token, item_parameter, report_param, httptest_tag, item_tag, role_rule, valuemap_mapping, lld_override_opperiod, task_data, ha_node, service_tag, item_rtdata, lld_override_optrends, report, maintenance_tag, sla, media_type_message, interface_snmp, lld_override, valuemap, lld_override_condition, dashboard_page, task_result, script_param, lld_macro_path, host_tag, module.

Also these two options

"Database history tables upgraded" and "Database history tables use primary key" set as NO.


Thanks a lot!

[tim.mooney]

You don't say what versions of MySQL/MariaDB are involved on the old Zabbix 4.x system or the new Zabbix 6.x system, but the way you ran the mysqldump command, you're almost certainly preserving the collation and character set for every table, and recreating the tables in the new system with same character set and collation.

Older versions of Zabbix were less strict about character set and collation. The installation instructions (eventually) specified what character set and collation you should use, but it wasn't strictly enforced until Zabbix 5.0.x and later. Your Zabbix 6.0 install is reporting that what you were using before is no longer correct, and needs to be changed.

There's a procedure in the documentation appendix for 6.0.x for converting your database to the correct character set and collation AND for updating to utf8mb4: https://www.zabbix.com/documentation...b_charset_coll
There's an earlier procedure in the 5.0.x appendix for just doing the character set and collation, but since you skipped 5.0.x you should use the updated procedure that also converts to utf8mb4.

Note that I haven't tried either procedure from the appendix, but I believe either one should be more robust than the procedure I used when I fixed my site's character set / collation issues before we upgraded to 5.0.x.

[aby]

Hi tim.mooney
Thanks for your reply! and sorry for my delay.

I cannot know which version of Mysql I had in the Zabbix 4; in the new server I have "mysql Ver 15.1 Distrib 10.11.4-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper"
I went through the link you sent, I use the utf8mb4.

MariaDB [(none)]> SELECT @@character_set_database, @@collation_database;
+--------------------------+--------------------------------------------+
| @@character_set_database | @@collation_database |
+--------------------------+--------------------------------------------+
| utf8mb4 | utf8mb4_general_ci | |
+--------------------------+--------------------------------------------+

Maybe this error is related to the DB schema?

[FrankD]

Hi - i am struggling with same issues in v4 -> v6 upgrade.
I did not manage to get the database dump import to work.

I am now trying to do an export import using zabbix-cli (https://github.com/unioslo/zabbix-cli)
In the process having several issues some described in https://www.zabbix.com/documentation...rade_notes_600

- User macros in item name no longer supported

and
also found issue with v4 having an error setting request_method to 1, should be 0
(procedure described here https://support.zabbix.com/projects/...lter=allissues)
and
change in syntax https://www.zabbix.com/forum/zabbix-...5-and-zabbix-6
- strlen replaced by length among other things, i am now trying to identify and change.

Probably more syntax edit's i still need to identify for my import to work.

"happy hunting"

Hope this helps in the right direction.

[tim.mooney]

Hi - i am struggling with same issues in v4 -> v6 upgrade.
I did not manage to get the database dump import to work.

I am now trying to do an export import using zabbix-cli (https://github.com/unioslo/zabbix-cli)
In the process having several issues some described in https://www.zabbix.com/documentation...rade_notes_600

- User macros in item name no longer supported

and
also found issue with v4 having an error setting request_method to 1, should be 0
(procedure described here https://support.zabbix.com/projects/...lter=allissues)
and
change in syntax https://www.zabbix.com/forum/zabbix-...5-and-zabbix-6
- strlen replaced by length among other things, i am now trying to identify and change.

Probably more syntax edit's i still need to identify for my import to work.

No offense, but that sounds like a lot of manual work-arounds and hacks, and in the end you won't know if your database is in good shape for the next upgrade.

Why not fix the database problems and let the standard upgrade procedure do what it's supposed to do?

You've only said that you didn't get a database dump & import to work, but if you post details about your environment and the actual error(s) you're getting, there's a fair chance someone here can provide some useful recommendations on what to try to get past the error. There's no guarantee that someone will help, but if you make it easier for them by providing all the information they need about your environment, the odds of someone helping definitely improve. I'm happy to offer advice and suggestions (when I have free time and can visit these forums), but I always skip the "it didn't work" super vague questions with no information about the environment or what the actual error message was.

I had to fix a number of minor database problems with my Zabbix database before I went to 5.0.x LTS, but with some research and reading the official "Known Issues" and "Upgrade Notes", all of them were fixable. I have to believe that's the situation for your install too.