[ZBX-21437] Cannot set MySQL character set to "utf8mb3" Created: 2022 Aug 05  Updated: 2022 Aug 15  Resolved: 2022 Aug 15

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 6.0.7
Fix Version/s: None

Type: Problem report Priority: Trivial
Reporter: Mikhail Grigorev Assignee: Zabbix Development Team
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Debian 10, MariaDB 10.5.16

Issue Links:
Duplicate
is duplicated by ZBX-21385 utf8mb3 error message in zabbix serve... Closed
Sub-task
depends on ZBX-21440 Cannot set MySQL character set to "ut... Closed

 Description   

Steps to reproduce:

  1. Upgrade zabbix 6.0.6 to 6.0.7
  2. In log file message "cannot set MySQL character set to "utf8mb3""

Result:
See log file...

 25504:20220805:085105.104 Zabbix Server stopped. Zabbix 6.0.6 (revision 3f7597e3ea3).
 14290:20220805:085105.174 Starting Zabbix Server. Zabbix 6.0.7 (revision 442920c8f69).
 14290:20220805:085105.174 ****** Enabled features ******
 14290:20220805:085105.174 SNMP monitoring:           YES
 14290:20220805:085105.174 IPMI monitoring:           YES
 14290:20220805:085105.174 Web monitoring:            YES
 14290:20220805:085105.174 VMware monitoring:         YES
 14290:20220805:085105.174 SMTP authentication:       YES
 14290:20220805:085105.174 ODBC:                      YES
 14290:20220805:085105.174 SSH support:               YES
 14290:20220805:085105.175 IPv6 support:              YES
 14290:20220805:085105.175 TLS support:               YES
 14290:20220805:085105.175 ******************************
 14290:20220805:085105.175 using configuration file: /etc/zabbix/zabbix_server.conf
 14290:20220805:085105.189 cannot set MySQL character set to "utf8mb3"
 14290:20220805:085105.189 cannot set MySQL character set to "utf8mb3"
 14290:20220805:085105.191 current database version (mandatory/optional): 06000000/06000004
 14290:20220805:085105.191 required mandatory version: 06000000
 14290:20220805:085105.191 cannot set MySQL character set to "utf8mb3"
 14290:20220805:085105.192 database could be upgraded to use primary keys in history tables
 14290:20220805:085105.193 cannot set MySQL character set to "utf8mb3"
 14290:20220805:085105.195 cannot set MySQL character set to "utf8mb3"
 14291:20220805:085105.196 starting HA manager
 14291:20220805:085105.197 cannot set MySQL character set to "utf8mb3"
 14291:20220805:085105.199 HA manager started in active mode
 14290:20220805:085105.200 server #0 started [main process]
 14293:20220805:085105.201 server #2 started [configuration syncer #1]
 14292:20220805:085105.202 server #1 started [service manager #1]
 14293:20220805:085105.202 cannot set MySQL character set to "utf8mb3"
 14292:20220805:085105.203 cannot set MySQL character set to "utf8mb3"
 14290:20220805:085105.294 cannot set MySQL character set to "utf8mb3"
 14304:20220805:085105.299 server #12 started [lld worker #1]
 14305:20220805:085105.300 server #13 started [lld worker #2] 
...

See ZBX-21301 for change default character set.

Additions info:

 

MariaDB [(none)]> SELECT version();
+----------------------------------------+
| version()                              |
+----------------------------------------+
| 10.5.16-MariaDB-1:10.5.16+maria~buster |
+----------------------------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE '%chara%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.002 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE '%colla%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.003 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE '%init_conn%';
+---------------+----------------------------------------------+
| Variable_name | Value                                        |
+---------------+----------------------------------------------+
| init_connect  | SET NAMES utf8mb4 collate utf8mb4_general_ci |
+---------------+----------------------------------------------+
1 row in set (0.002 sec) 

MariaDB [zabbix]> SHOW CREATE DATABASE zabbix;
+----------+----------------------------------------------------------------------------------+
| Database | Create Database                                                                  |
+----------+----------------------------------------------------------------------------------+
| zabbix   | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |
+----------+----------------------------------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [zabbix]> SHOW COLLATION LIKE 'utf8%';
+------------------------------+---------+------+---------+----------+---------+
| Collation                    | Charset | Id   | Default | Compiled | Sortlen |
+------------------------------+---------+------+---------+----------+---------+
| utf8_general_ci              | utf8    |   33 | Yes     | Yes      |       1 |
| utf8_bin                     | utf8    |   83 |         | Yes      |       1 |
| utf8_unicode_ci              | utf8    |  192 |         | Yes      |       8 |
| utf8_icelandic_ci            | utf8    |  193 |         | Yes      |       8 |
| utf8_latvian_ci              | utf8    |  194 |         | Yes      |       8 |
| utf8_romanian_ci             | utf8    |  195 |         | Yes      |       8 |
| utf8_slovenian_ci            | utf8    |  196 |         | Yes      |       8 |
| utf8_polish_ci               | utf8    |  197 |         | Yes      |       8 |
| utf8_estonian_ci             | utf8    |  198 |         | Yes      |       8 |
| utf8_spanish_ci              | utf8    |  199 |         | Yes      |       8 |
| utf8_swedish_ci              | utf8    |  200 |         | Yes      |       8 |
| utf8_turkish_ci              | utf8    |  201 |         | Yes      |       8 |
| utf8_czech_ci                | utf8    |  202 |         | Yes      |       8 |
| utf8_danish_ci               | utf8    |  203 |         | Yes      |       8 |
| utf8_lithuanian_ci           | utf8    |  204 |         | Yes      |       8 |
| utf8_slovak_ci               | utf8    |  205 |         | Yes      |       8 |
| utf8_spanish2_ci             | utf8    |  206 |         | Yes      |       8 |
| utf8_roman_ci                | utf8    |  207 |         | Yes      |       8 |
| utf8_persian_ci              | utf8    |  208 |         | Yes      |       8 |
| utf8_esperanto_ci            | utf8    |  209 |         | Yes      |       8 |
| utf8_hungarian_ci            | utf8    |  210 |         | Yes      |       8 |
| utf8_sinhala_ci              | utf8    |  211 |         | Yes      |       8 |
| utf8_german2_ci              | utf8    |  212 |         | Yes      |       8 |
| utf8_croatian_mysql561_ci    | utf8    |  213 |         | Yes      |       8 |
| utf8_unicode_520_ci          | utf8    |  214 |         | Yes      |       8 |
| utf8_vietnamese_ci           | utf8    |  215 |         | Yes      |       8 |
| utf8_general_mysql500_ci     | utf8    |  223 |         | Yes      |       1 |
| utf8_croatian_ci             | utf8    |  576 |         | Yes      |       8 |
| utf8_myanmar_ci              | utf8    |  577 |         | Yes      |       8 |
| utf8_thai_520_w2             | utf8    |  578 |         | Yes      |       4 |
| utf8_general_nopad_ci        | utf8    | 1057 |         | Yes      |       1 |
| utf8_nopad_bin               | utf8    | 1107 |         | Yes      |       1 |
| utf8_unicode_nopad_ci        | utf8    | 1216 |         | Yes      |       8 |
| utf8_unicode_520_nopad_ci    | utf8    | 1238 |         | Yes      |       8 |
| utf8mb4_general_ci           | utf8mb4 |   45 | Yes     | Yes      |       1 |
| utf8mb4_bin                  | utf8mb4 |   46 |         | Yes      |       1 |
| utf8mb4_unicode_ci           | utf8mb4 |  224 |         | Yes      |       8 |
| utf8mb4_icelandic_ci         | utf8mb4 |  225 |         | Yes      |       8 |
| utf8mb4_latvian_ci           | utf8mb4 |  226 |         | Yes      |       8 |
| utf8mb4_romanian_ci          | utf8mb4 |  227 |         | Yes      |       8 |
| utf8mb4_slovenian_ci         | utf8mb4 |  228 |         | Yes      |       8 |
| utf8mb4_polish_ci            | utf8mb4 |  229 |         | Yes      |       8 |
| utf8mb4_estonian_ci          | utf8mb4 |  230 |         | Yes      |       8 |
| utf8mb4_spanish_ci           | utf8mb4 |  231 |         | Yes      |       8 |
| utf8mb4_swedish_ci           | utf8mb4 |  232 |         | Yes      |       8 |
| utf8mb4_turkish_ci           | utf8mb4 |  233 |         | Yes      |       8 |
| utf8mb4_czech_ci             | utf8mb4 |  234 |         | Yes      |       8 |
| utf8mb4_danish_ci            | utf8mb4 |  235 |         | Yes      |       8 |
| utf8mb4_lithuanian_ci        | utf8mb4 |  236 |         | Yes      |       8 |
| utf8mb4_slovak_ci            | utf8mb4 |  237 |         | Yes      |       8 |
| utf8mb4_spanish2_ci          | utf8mb4 |  238 |         | Yes      |       8 |
| utf8mb4_roman_ci             | utf8mb4 |  239 |         | Yes      |       8 |
| utf8mb4_persian_ci           | utf8mb4 |  240 |         | Yes      |       8 |
| utf8mb4_esperanto_ci         | utf8mb4 |  241 |         | Yes      |       8 |
| utf8mb4_hungarian_ci         | utf8mb4 |  242 |         | Yes      |       8 |
| utf8mb4_sinhala_ci           | utf8mb4 |  243 |         | Yes      |       8 |
| utf8mb4_german2_ci           | utf8mb4 |  244 |         | Yes      |       8 |
| utf8mb4_croatian_mysql561_ci | utf8mb4 |  245 |         | Yes      |       8 |
| utf8mb4_unicode_520_ci       | utf8mb4 |  246 |         | Yes      |       8 |
| utf8mb4_vietnamese_ci        | utf8mb4 |  247 |         | Yes      |       8 |
| utf8mb4_croatian_ci          | utf8mb4 |  608 |         | Yes      |       8 |
| utf8mb4_myanmar_ci           | utf8mb4 |  609 |         | Yes      |       8 |
| utf8mb4_thai_520_w2          | utf8mb4 |  610 |         | Yes      |       4 |
| utf8mb4_general_nopad_ci     | utf8mb4 | 1069 |         | Yes      |       1 |
| utf8mb4_nopad_bin            | utf8mb4 | 1070 |         | Yes      |       1 |
| utf8mb4_unicode_nopad_ci     | utf8mb4 | 1248 |         | Yes      |       8 |
| utf8mb4_unicode_520_nopad_ci | utf8mb4 | 1270 |         | Yes      |       8 |
+------------------------------+---------+------+---------+----------+---------+
67 rows in set (0.001 sec)
MariaDB [zabbix]> SHOW CHARACTER SET LIKE 'utf%';
+---------+------------------+--------------------+--------+
| Charset | Description      | Default collation  | Maxlen |
+---------+------------------+--------------------+--------+
| utf8    | UTF-8 Unicode    | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode    | utf8mb4_general_ci |      4 |
| utf16   | UTF-16 Unicode   | utf16_general_ci   |      4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci |      4 |
| utf32   | UTF-32 Unicode   | utf32_general_ci   |      4 |
+---------+------------------+--------------------+--------+
5 rows in set (0.001 sec)

 

 Comments   
Comment by Mikhail Grigorev [ 2022 Aug 05 ]

In documentation recommend create database is character set utf8mb4 collate utf8mb4_bin

Why then did ZBX-21301 use utf8mb3 encoding?

Comment by Mikhail Grigorev [ 2022 Aug 05 ]

Also, to install MariaDB 10.6, you need to know the feature that utf8 will be an alias for utf8mb3, see official documentation 
It turns out that it makes no sense to set the encoding to utf8mb3, it's easier to use utf8 as it was before.

Comment by Vladislavs Sokurenko [ 2022 Aug 08 ]

Detection should be improved when checking for "utf8,utf8mb3" to check separately instead and set corresponding value instead of using common value "utf8mb3" for both.

Comment by Lukáš Malý [ 2022 Aug 09 ]

How to fix CHARSET and COLLATE for Zabbix 6.0.7 ... (Debian 11.4 and MariaDB 10.5.15)

Convert database schema:

ALTER DATABASE zabbix CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;

Convert all database zabbix tables:

example:

#!/bin/bash
DATABASE=zabbix
TABLES=$(echo SHOW TABLES | mysql -s $DATABASE)
for TABLE in $TABLES ; do
   echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;"
   echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;" | mysql $DATABASE
done
Comment by Mikhail Grigorev [ 2022 Aug 10 ]

@lukas.maly

Thanks, i know how to convert the database and table to the desired encoding.

Here the problem is that the utf8mb3 encoding is set in the zabbix server source code (see ZBX-21301, see commmit). But for MariaDB 10.5 the default encoding is utf8mb4, and for MariaDB 10.6 the default encoding is already utf8mb3. That is, for different versions of MariaDB, the encodings are different and you cannot hardcode the utf8mb3 encoding in the server code, it would be more correct to leave the utf8 encoding

Comment by Mikhail Grigorev [ 2022 Aug 10 ]

Му database output, this query use to zabbix server code

MariaDB [(none)]> select default_character_set_name,default_collation_name from information_schema.SCHEMATA where schema_name='zabbix';
+----------------------------+------------------------+
| default_character_set_name | default_collation_name |
+----------------------------+------------------------+
| utf8                       | utf8_bin               |
+----------------------------+------------------------+
1 row in set (0.002 sec)

The first if statement is SUCCEED (see code), but in MariaDB 10.5 there is no utf8mb3 encoding and the next code throws an error setting a non-existent encoding.

Comment by Alexander Vladishev [ 2022 Aug 15 ]

Closed as duplicate of ZBX-21385.

Generated at Wed May 14 07:52:23 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.