Centos 7, 5.5.64-MariaDB

btw, this is 4.4.6, not 4.4.6rc1, just installed updates from http://repo.zabbix.com/

And from  server log:

7335:20200222:141954.258 current database version (mandatory/optional): 04040000/04040001
7335:20200222:141954.258 required mandatory version: 04040000
7335:20200222:141954.902 character set name or collation name that is not supported by Zabbix found in 263 column(s) of database "zabbix"
7335:20200222:141954.902 only character set "utf8" and collation "utf8_bin" should be used in database

Looks this is not frontend problem, but upgrade process problem.

4.4.5 did not complain about this...

Yes, it is new check for Zabbix tables.

Well, then why this checker do not fix this?

It is quite easy, right?

We wouldn't go and just change the schema of your database, that'd be too harsh.

And for the record, this is explained here: https://www.zabbix.com/documentation/4.4/manual/introduction/whatsnew446

There is no explanation why you can't on this link.

btw, just for the record © 

https://www.zabbix.com/ru/download_sources

there is no 4.4.6 and no link to release notes

https://www.zabbix.com/documentation/current/manual/appendix/install/db_scripts#mysql

I also get the message:

"Unsupported charset or collation for tables: host_discovery, history_str, media_type_param, graphs_items, media, httpstep_field, application_prototype, hostmacro, history_log, proxy_history, tag_filter, expressions, httpstep, interface, problem, globalmacro, sysmap_shape, acknowledges, group_prototype, icon_map, item_preproc, sysmap_url, hosts, httptest, profiles, history_text, services, maintenance_tag, ids, sessions, httptest_field, opconditions, proxy_dhistory, functions, sysmaps_link_triggers, users, graphs, auditlog_details, config_autoreg_tls, scripts, config, application_discovery, alerts, triggers, sysmap_element_url, sysmaps, dchecks, images, media_type, graph_theme, sysmaps_links, screens_items, corr_condition_tag, corr_condition_tagpair, task_remote_command_result, usrgrp, housekeeper, item_condition, slideshows, drules, items, item_rtdata, problem_tag, correlation, proxy_autoreg_host, trigger_tag, icon_mapping, actions, maintenances, host_inventory, services_times, screens, item_discovery, event_tag, corr_condition_tagvalue, operations, lld_macro_path, conditions, events, regexps, group_discovery, sysmaps_elements, mappings, widget_field, task_remote_command, opcommand, opmessage, slides, autoreg_host, host_tag, dashboard, valuemaps, dservices, applications, auditlog, hstgrp, widget."

At the bottom of the "System information" Dashboard.

After updating Debian 10 buster this morning, I updated zabbix to 4.4.6, but that didnt solve the issue.

Rene, you have to change tables collation manually, because "it is to harsh" for zabbix developers.

dimir , I installed my database about 15 years ago, it is utf8, but some tables do not have utf8_bin collation for some reason, zabbix changed scheme several times, but not collation.

Your link is useless, you have to document way to change tables collation

We also have an old instance. Can You please send me an example, how-to do the change of the tables collation?

After a reboot, zabbix doesn't start anymore. We get:

"Zabbix supports only "utf8_bin" collation. Database "zabbix" has default collation "utf8_general_ci" character set name or collation name that is not supported by Zabbix found in 408 column(s) of database "zabbix" only character set "utf8" and collation "utf8_bin" should be used in database"

That started with the actual debian updates, installed this morning (done last time mid january). Then we updated zabbix from 4.4.4 to 4.4.6, but that doesnt make a difference.

This was caused by ZBXNEXT-5603 

I agree that the procedure for users to fix the issue would be useful.

The following queries generate the commands to solve the issue in mysql:

SELECT CONCAT('ALTER TABLE ',
              table_schema,
              '.',
              table_name,
              ' COLLATE=utf8_bin;') AS cmd 
FROM   (SELECT table_schema, 
               table_name, 
               table_collation 
        FROM   information_schema.tables t 
        WHERE  table_schema = 'zabbix' 
               AND table_collation != 'utf8_bin') AS tables 
ORDER  BY cmd;

SELECT CONCAT('ALTER TABLE ',
              table_schema,
              '.',
              table_name,
              ' MODIFY COLUMN ',
              column_name,
              ' ',
              column_type,
              ' CHARACTER SET utf8 COLLATE utf8_bin',
              defaultval,
              nullable,
              ';') AS cmd
FROM   (SELECT table_schema, 
               table_name, 
               column_name, 
               collation_name, 
               character_set_name, 
               column_type, 
               column_default, 
               CASE 
                 WHEN column_default IS NULL THEN '' 
                 ELSE CONCAT(' DEFAULT ''', column_default, ''' ') 
               END AS defaultval, 
               CASE 
                 WHEN is_nullable = 'NO' THEN ' NOT NULL ' 
                 ELSE '' 
               END AS nullable 
        FROM   information_schema.columns 
        WHERE  table_schema = 'zabbix' 
               AND ( collation_name != 'utf8_bin' 
                      OR character_set_name != 'utf8' )) AS cols 
ORDER  BY cmd;

Regards,

Carlos Ruiz

Thanks for Your reply.
Following helped me to be able to start zabbix again:

mysql> ALTER DATABASE `zabbix` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

But the error-message still apears while zabbix is running fine ...

@Carlos Ruiz
Worked for me, Thanks.

Rgds

renedubs, you need to change collation,

I did this using info from here

https://confluence.atlassian.com/jirakb/change-column-and-table-collation-to-utf8_bin-in-mysql-601456761.html

Should have been documented in Upgrade Notes as well...

I agree, cyclone.

We are working on providing instructions for MySQL at this point. We should think of many aspects here:

• default DB collation
• default table collation
• column collation

Also there might be some weird collations set and working for a user, changing which to utf8 might break things so we might advise to do backups and check existing collations first.

After a reboot, zabbix doesn't start anymore. We get:

"Zabbix supports only "utf8_bin" collation. Database "zabbix" has default collation "utf8_general_ci" character set name or collation name that is not supported by Zabbix found in 408 column(s) of database "zabbix" only character set "utf8" and collation "utf8_bin" should be used in database"

That started with the actual debian updates, installed this morning (done last time mid january). Then we updated zabbix from 4.4.4 to 4.4.6, but that doesnt make a difference.

renedubs, are you sure Zabbix server won't start after upgrade? It should only give warning but still start.

Yes, after doing the debian-updates this monady morning, zabbix didnt start anymore after a reboot. Later, to avoid the error message @ "System Information - Widget" I tried to fix it by updating zabbix to 4.4.6, but that didn't help. The problem was @debian10 and zabbix 4.4.4. The zabbix installation is old. First it war running at a verion 2.x, some years ago. End december 2019 I updated from debian 6 and zabbix 3.x to debian 10 and zabbix 4.4.4. Since end of december 2019 this installation was running fine, also after a reboot.

Sorry, but this is absolutely not related to this issue.

But both started the same time... and after the update zabbix-server wont start due to ""Zabbix supports only "utf8_bin" collation. Database "zabbix" has default collation "utf8_general_ci"" and later we got the error in the sysinfo-widget. Both seem to be related.

Convert tables to CHARACTER SET utf8 and COLLATE utf8_bin:

ALTER TABLE zabbix.TABLE_NAME CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

to find all tables which contain columns with wrong character set or collate:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME  FROM information_schema.`COLUMNS` WHERE table_schema = "zabbix" AND COLLATION_NAME != "utf8_bin";

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME  FROM information_schema.`COLUMNS` WHERE table_schema = "zabbix" AND CHARACTER_SET_NAME != "utf8";

We did it with following steps:
Adapt the schema of the DB:

ALTER DATABASE `zabbix` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

Create command list:

SELECT CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;") AS ExecuteTheString
 FROM information_schema.`COLUMNS`
 WHERE table_schema = "zabbix" AND COLLATION_NAME = "utf8_general_ci";

 Then execute all individual commands in the way like:

ALTER TABLE auditlog CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

Everything's all right now.

@Carlos Ruiz

Thank you! I was able to change my collation using your MySQL query.

@Carlos Ruiz

Most of the commands that your script generates work, but there are many  like this that do not...

MariaDB [(none)]> ALTER TABLE zabbix.drules MODIFY COLUMN delay varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT ''1h'' NOT NULL ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''' NOT NULL' at line 1

The commands that work have '''' (with no value inserted) after DEFAULT . If a value is inserted then it throws a syntax error. Any idea how to fix that?

MariaDB version: 10.3.22

Zabbix Version: 4.0.18

Thanks! 

Hi @jrdnyquist

I guess your drules default is defined '1h' (with quotes) - instead of 1h.

I suppose the fix is simply to change the double quote by single quote - but I cannot be sure as I don't know why that default was set that way.

Regards,

Carlos Ruiz

@jrdnyquist @carlosruiz_globalqss

I faced the same problem. I can confirm that if you replace the double quotes with single quotes the commands work.

All of our tables are set correctly, but we're still seeing the same notification on the system information screen. Restarts make no difference.

+---------------+------------------------------------------------------------------------+
| Database      | Create Database                                                        |
+---------------+------------------------------------------------------------------------+
| zabbix_server | CREATE DATABASE `zabbix_server` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+---------------+------------------------------------------------------------------------+

 And when we look for incorrect tables:

MariaDB [zabbix_server]> SELECT table_schema, table_name, table_collation FROM information_schema.tables WHERE table_schema = 'zabbix_server' AND table_collation != 'utf8_bin';
Empty set (0.00 sec)

What am I missing?

hey,guys,I have knew the answer.When you create the database,both character set and collation must be defined.for example:create database zabbix character set utf8 collate utf8_bin;   and yours may be exclusive collate utf8_bin.

https://www.zabbix.com/documentation/current/manual/appendix/install/db_scripts

Zabbix 4.0.18 / Centos 7 has the same issue.

Zabbix was installed from RPM's while ago and it really sucks that there need to be additional changes that have to be made manually on DB level.

+---------------------------+---------------------+
|@@character_set_database|@@collation_database|
+---------------------------+---------------------+
|utf8|utf8_bin|
+---------------------------+---------------------+

The help from Sles  was perfect for the changes to apply to the database

https://support.zabbix.com/browse/ZBX-17357?focusedCommentId=409288&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-409288

One thing was missing for me to have the changes made
 SET foreign_key_checks = 0;
 
I just followed what they said and everything worked fine.
In any case don't forget to backup your database before doing this.

This was earlier discussed in ZBX-6323 and there are a few possible approaches described.

ATTENTION TO ALL !!!

Making "ALTER TABLE ... ", as suggested here above, is NOT enough, as there are also EXISTING columns !
EXISTING Columns also have to be fixed!

But applying what was discussed in ZBX-6323 or same link for @Sles and checking columns after that give :
 

MariaDB [(none)]> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME  FROM information_schema.`COLUMNS` WHERE table_schema = "zabbix" AND COLLATION_NAME != "utf8_bin";
Empty set (0.01 sec)

MariaDB [(none)]> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME  FROM information_schema.`COLUMNS` WHERE table_schema = "zabbix" AND CHARACTER_SET_NAME != "utf8";
Empty set (0.00 sec)

Columns where fixed during the process.

Not sure if this is related, but I've stumbled upon this due to my agent on host with zabbix proxy, kept getting AUTO REGISTRARION over and over

found a forum post regarding this, but basicaly pointing to the encoding issue, I've upgraded from 4.4.5 to 4.4.6, fixed the DB encoding and issue is gone
https://www.zabbix.com/forum/zabbix-troubleshooting-and-problems/372544-auto-registration-hell

Same issue is in 4.0.18 so please update 4.0 documentation as well

Already documented in What's new in Zabbix 4.0.18 and Upgrade notes for 4.0.18.

"Changing database character set and collation" will be added soon.

There is the same problem. I updated 4.2.1 to 4.4.8 and first time error "latin1 unsupported, use utf8" and I latin1 charset updated utf8 mysql database and write new error

" Unsupported charset or collation for tables: acknowledges, actions, alerts, application_discovery, application_prototype, applications, auditlog, auditlog_details, autoreg_host, conditions, config, config_autoreg_tls, corr_condition_tag, corr_condition_tagpair, corr_condition_tagvalue, correlation, dashboard, dchecks, drules, dservices, event_tag, events, expressions, functions, globalmacro, graph_theme, graphs, graphs_items, group_discovery, group_prototype, history_log, history_str, history_text, host_discovery, host_inventory, host_tag, hostmacro, hosts, housekeeper, hstgrp, httpstep, httpstep_field, httptest, httptest_field, icon_map, icon_mapping, ids, images, interface, item_condition, item_discovery, item_preproc, item_rtdata, items, lld_macro_path, maintenance_tag, maintenances, mappings, media, media_type, media_type_param, opcommand, opconditions, operations, opmessage, problem, problem_tag, profiles, proxy_autoreg_host, proxy_dhistory, proxy_history, regexps, screens, screens_items, scripts, services, services_times, sessions, slides, slideshows, sysmap_element_url, sysmap_shape, sysmap_url, sysmaps, sysmaps_elements, sysmaps_link_triggers, sysmaps_links, tag_filter, task_remote_command, task_remote_command_result, trigger_tag, triggers, users, usrgrp, valuemaps, widget, widget_field."

The error persists, I used the documentation steps.

tforis, There was incorrect query in documentation for changing charset for columns. It did remove default value of column. Today I will put back correct sql query for charset change for columns.

I have disabled watching this issue yet I keep getting spammed continuously about updates. Like dozens a day. Please fix this.

Yesterday I tried converting my MySQL Database, that was created in Zabbix 2.0 era with the following article https://www.zabbix.com/documentation/4.4/manual/appendix/install/db_charset_coll

After I've done all the steps, I encountered three big problems:

1. I couldn't login anymore. I had to use a backup account, and rename my account twice. First into a dummy name, and then back to it's original name. -> Works again
2. I couldn't add trigger anymore, as I always encountered an error, the the Template doesn't exist. I renamed the Template twice. First into a dummy name, and then back to it's original name. -> Works again
3. Then I discovered, that all my Windows Zabbix Agents have auto-registered a second time. In the Frontend they had the same hostname twice, even the case was the same, but somehow the DB must have allowed a second registration.

All three must be because of the case-sensitive change. And somehow the steps provided did not prevent those issues. Either the steps provided are NOT sufficent for a large existing database, or I did something wrong.

Anyway, I dropped the experiment and reverted back. So currently I still have the error in zabbix_server.log and System information.

I summarized all my issues with the script in ZBX-17745 including screenshots, as I discovered this issue here just today.

I have the strong feeling, that this is becoming a big problem, with the coming upgrades to 5.0.

Is this DB change mandatory for 5.0?

We will look into it and get back to you starko

This topic does not exist yet ? **

Sorry ichiyama it was removed due to bug in it and will be added back once fixed.

It turns out that selecting tables for repair like

        WHERE  table_schema = 'zabbix'
               AND ( collation_name != 'utf8_bin'
                      OR character_set_name != 'utf8' )) 

is not future-proof.
Example: in Zabbix 3.0 table `events` has no text columns, only numeric ones, so this table is not selected for fixing character set and collation:

CREATE TABLE `events` (
    `eventid`          bigint unsigned              NOT NULL,                       
    `source`           integer         DEFAULT '0'  NOT NULL,                       
    `object`           integer         DEFAULT '0'  NOT NULL,                       
    `objectid`         bigint unsigned DEFAULT '0'  NOT NULL,                       
    `clock`            integer         DEFAULT '0'  NOT NULL,                       
    `value`            integer         DEFAULT '0'  NOT NULL,                       
    `acknowledged`     integer         DEFAULT '0'  NOT NULL,                       
    `ns`               integer         DEFAULT '0'  NOT NULL,                       
    PRIMARY KEY (eventid)    
) ENGINE=InnoDB;

Upgrade to 4.0 or newer adds a column of varchar type to this table:

   `name`              varchar(2048)   DEFAULT ''   NOT NULL

....and we get again the old, non-utf8 encoding like 'latin1' and collation like 'latin1_swedish_ci' for it!
Similar cases are table 'operations' (column 'esc_period') and table 'slides' (column 'delay'). Both tables have no text fields in Zabbix 3.0, but upgrading to 4.0 or newer converts both columns from 'integer' to 'varchar' - with non-utf8 encoding and collation.

Today's version:

1. Check DB character set and collation. If they are not utf8 and utf8_bin, then
2. Stop Zabbix.
3. Create a database backup copy!
4. Fix character set and collation on DB level:

    alter database <your DB name> character set utf8 collate utf8_bin;
   

5. generate repair script with

   SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' CHARACTER SET utf8 COLLATE utf8_bin;')
   FROM   information_schema.tables
   WHERE  table_schema = '<your DB name>'        
   ORDER BY table_name;
   
   SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' MODIFY COLUMN ', column_name,
           ' ', column_type, ' CHARACTER SET utf8 COLLATE utf8_bin',  
           case                    
                   when column_default is null then ''
                   else concat(' default ', column_default, ' ')
           end,
           case
                   when is_nullable = 'no' then ' not null '
                   else ''
           end,
           ';')
   FROM information_schema.columns
   WHERE table_schema = '<your DB name>'        
          AND column_type = 'text'
   ORDER BY table_name;
   
   SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;')
   FROM   information_schema.tables
   WHERE  table_schema = '<your DB name>'        
   ORDER BY table_name;
   

   The 1st query deals with fixing table-level character set and collation in ALL tables - even ones without 'varchar' and 'text' fields. Because in future Zabbix upgrades any table can get added a varchar column or some 'integer' column can be turned into 'varchar'.
   The 2nd query handles 'text'-type columns separately - to prevent their max length jumping from 65535 to 16777215 and their type from 'text' to 'mediumtext'.
   The 3rd query is for mass-conversion of all 'varchar' fields into utf8, with max length (in bytes) change.

6. Run the generated repair script. Note that data encoding will be changed on disk. For example, when converting from 'latin1' to 'utf8', characters like Æ, Ñ, Ö will go from 1 byte to 2 bytes. Thus the repaired database may require more space than before.
7. If no errors - you may want to create a database backup copy with repaired DB.
8. Start Zabbix.

This script was tested with MariaDB 10.3.22 on a pristine 2.0 DB, which was fixed from 'latin1'/'latin1_swedish_ci' to 'utf8'/'utf8_bin'. Then Zabbix server 5.0 was started on the repaired 2.0 DB, it was upgraded to 5.0. Comparison with pristine 5.0 DB showed only difference with 'double' columns: upgraded 2.0 used 'double(16,4)' whereas original 5.0 used 'double'.

Possible improvement: reorganize script to do all work on one table then proceed to the next table. It would be better in case or errors.
Please help to improve!

Thanks Rene Dubs, your solution solved the problem for me on Zabbix 4.0.21.

1. Check DB character set and collation. If they are not utf8 and utf8_bin, then
2. Stop Zabbix.
3. Create a database backup copy!
4. Fix character set and collation on DB level:

    alter database <your DB name> character set utf8 collate utf8_bin;
   

5. generate fix script with

   /* create_fix_zbx_charset.sql
   
      Purpose: generate a script for setting character set and collation
               in MySQL and MariaDB databases to values supported by
               Zabbix ('utf8' and 'utf8_bin', respectively).
      Usage:
         1) Edit this script - replace <your DB name> with the name of
            Zabbix database (in 2 places).
         2) Run this script via 'mysql', for example:
             mysql -s -uzabbix -p < create_fix_zbx_charset.sql > fix_zbx_charset.sql
   */
   SELECT command
   FROM
       (/* This 'select' statement deals with 'text' type columns to prevent
           their automatic conversion into 'mediumtext' type.
           The goal is to produce statements like
            ALTER TABLE zabbix.hosts MODIFY COLUMN description text CHARACTER SET utf8 COLLATE utf8_bin not null;
        */
        SELECT table_name AS sort1,
                      'A' AS sort2,
               CONCAT('ALTER TABLE ', table_schema, '.', table_name,
                      ' MODIFY COLUMN ', column_name, ' ', column_type,
                      ' CHARACTER SET utf8 COLLATE utf8_bin',
                   case
                       when column_default is null then ''
                       else concat(' default ', column_default, ' ')
                   end,
                   case
                       when is_nullable = 'no' then ' not null '
                       else ''
                   end,
               ';') AS command
           FROM information_schema.columns
           WHERE table_schema = '<your DB name>'        
              AND column_type = 'text'
       UNION
        /* This 'select' statement deals with setting character set and collation for
           each table and converting varchar fields on a per-table basis.
           It is necessary to process all tables (even those with numeric-only columns)
           otherwise in future Zabbix upgrades text (e.g. varchar) columns may be added
           to these tables or numeric columns can be turned into text ones and
           the old character set/collation can reappear again.
           The goal is to produce statements like
            ALTER TABLE zabbix.hosts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
        */
        SELECT table_name AS sort1,
                      'B' AS sort2,
               CONCAT('ALTER TABLE ', table_schema, '.', table_name,
                      ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;') AS command
           FROM information_schema.tables
           WHERE table_schema = '<your DB name>') s
   /* Sorting is important: 'MODIFY COLUMN' statements should precede 'CONVERT TO' ones
      for each table. */
   ORDER BY sort1, sort2;
   

6. Run the generated fix script. Note that data encoding will be changed on disk. For example, when converting from 'latin1' to 'utf8', characters like Æ, Ñ, Ö will go from 1 byte to 2 bytes. Thus the repaired database may require more space than before.
7. If no errors - you may want to create a database backup copy with fixed DB.
8. Start Zabbix.

> 1. Check DB character set and collation. If they are not utf8 and utf8_bin, then

Provide an additional example SQL query, on how to verify you are affected (additional to the hint from the "System information" Report from Zabbix Frontend).

(1) As suggested would be nice to have also instructions how to check database character set and collation. There are several ways, for example:
use <dbname>
SELECT @@character_set_database, @@collation_database;

Result:

+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| latin1                     | latin1_swedish_ci      |
+----------------------------+------------------------+

2)
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '<dbname>';
Result:

+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| latin1                     | latin1_swedish_ci      |
+----------------------------+------------------------+

Not sure which way is preferable.

andris: Thanks! Included in documentation. CLOSED

(2) The script outputs 'command' in the first line. What is the purpose of it? It gives error on mariadb server, while script appears to be working without it.

andris : I ran the script with "mysql -s ...", then 'command' did not appear. It is in the script comments:

mysql -s -uzabbix -p < create_fix_zbx_charset.sql > fix_zbx_charset.sql

Maybe there is a better way to suppress selected column names.

wiper: Sorry, missed '-s' when copy-pasted the command line.

CLOSED

(3) I have two suggestions to the sql script (depending on how big changes are acceptable)

First what we really must do is to move database name to variable. So user does not have to replace database name in few places in the middle of script:

SET @ZABBIX_DATABASE = 'zbtest';
SELECT command
FROM
    (/* This 'select' statement deals with 'text' type columns to prevent
        their automatic conversion into 'mediumtext' type.
        The goal is to produce statements like
         ALTER TABLE zabbix.hosts MODIFY COLUMN description text CHARACTER SET utf8 COLLATE utf8_bin not null;
     */
     SELECT table_name AS sort1,
                   'A' AS sort2,
            CONCAT('ALTER TABLE ', table_schema, '.', table_name,
                   ' MODIFY COLUMN ', column_name, ' ', column_type,
                   ' CHARACTER SET utf8 COLLATE utf8_bin',
                case
                    when column_default is null then ''
                    else concat(' default ', column_default, ' ')
                end,
                case
                    when is_nullable = 'no' then ' not null '
                    else ''
                end,
            ';') AS command
        FROM information_schema.columns
        WHERE table_schema = @ZABBIX_DATABASE        
           AND column_type = 'text'
    UNION
     /* This 'select' statement deals with setting character set and collation for
        each table and converting varchar fields on a per-table basis.
        It is necessary to process all tables (even those with numeric-only columns)
        otherwise in future Zabbix upgrades text (e.g. varchar) columns may be added
        to these tables or numeric columns can be turned into text ones and
        the old character set/collation can reappear again.
        The goal is to produce statements like
         ALTER TABLE zabbix.hosts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
     */
     SELECT table_name AS sort1,
                   'B' AS sort2,
            CONCAT('ALTER TABLE ', table_schema, '.', table_name,
                   ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;') AS command
        FROM information_schema.tables
        WHERE table_schema = @ZABBIX_DATABASE) s
/* Sorting is important: 'MODIFY COLUMN' statements should precede 'CONVERT TO' ones
   for each table. */
ORDER BY sort1, sort2;

Second - without many changes this script can be updated to make the database alterations (it executes the concatenated statements itself):

SET @ZABBIX_DATABASE = 'zbtest';

DROP PROCEDURE IF EXISTS zbx_convert_utf8;

DELIMITER $$
CREATE PROCEDURE zbx_convert_utf8 (
)

BEGIN
	declare cmd varchar(255) default "";
	declare finished integer default 0;

	declare cur_command cursor for 
		SELECT command
		FROM
		    (/* This 'select' statement deals with 'text' type columns to prevent
		        their automatic conversion into 'mediumtext' type.
		        The goal is to produce statements like
		         ALTER TABLE zabbix.hosts MODIFY COLUMN description text CHARACTER SET utf8 COLLATE utf8_bin not null;
		     */
		     SELECT table_name AS sort1,
		                   'A' AS sort2,
		            CONCAT('ALTER TABLE ', table_schema, '.', table_name,
		                   ' MODIFY COLUMN ', column_name, ' ', column_type,
		                   ' CHARACTER SET utf8 COLLATE utf8_bin',
		                case
		                    when column_default is null then ''
		                    else concat(' default ', column_default, ' ')
		                end,
		                case
		                    when is_nullable = 'no' then ' not null '
		                    else ''
		                end,
		            ';') AS command
		        FROM information_schema.columns
		        WHERE table_schema = @ZABBIX_DATABASE        
		           AND column_type = 'text'
		    UNION
		     /* This 'select' statement deals with setting character set and collation for
		        each table and converting varchar fields on a per-table basis.
		        It is necessary to process all tables (even those with numeric-only columns)
		        otherwise in future Zabbix upgrades text (e.g. varchar) columns may be added
		        to these tables or numeric columns can be turned into text ones and
		        the old character set/collation can reappear again.
		        The goal is to produce statements like
		         ALTER TABLE zabbix.hosts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
		     */
		     SELECT table_name AS sort1,
		                   'B' AS sort2,
		            CONCAT('ALTER TABLE ', table_schema, '.', table_name,
		                   ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;') AS command
		        FROM information_schema.tables
		        WHERE table_schema = @ZABBIX_DATABASE) s
		/* Sorting is important: 'MODIFY COLUMN' statements should precede 'CONVERT TO' ones
		   for each table. */
		ORDER BY sort1, sort2;
	
	declare continue handler for not found set finished = 1;

	open cur_command;
	cmd_loop: loop
		fetch cur_command into cmd;
		if finished = 1 then
			leave cmd_loop;
		end if;
		PREPARE stmt FROM cmd;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
	end loop cmd_loop;
	close cur_command;

END$$

DELIMITER ;

CALL zbx_convert_utf8();

DROP PROCEDURE zbx_convert_utf8;

andris: The 1st idea is definite usability improvement. As of the 2nd idea - I would prefer the simple, linear script as it is now. Statements in script can be re-executed if error happens, order of tables can be changed (e.g. history tables can be postponed). With stored procedure it seems more like "all or nothing". But that is my opinion.

andris: We included your proposed stored procedure in documentation, our support colleagues successfully used it on customer system.  CLOSED

In 4. and 5. replace '<your DB name>' with 'zabbix'.

I have upgraded from 4.4.8 to 5.0.1 on RHEL 7 with MariaDB 5.5.65 and followed all the steps here including @Andris Mednis comment, but still no luck
Has anyone had luck with this issue?

@Jason C: The public visible comment by Andris Mednis from 27 May is only a draft. 

There exists a hidden developer comment in here, that we can’t see by Andris Mednis, which has the final invisible draft in it. Zabbix still tests this. 
This was communicated on different sources for example the English online meetup. But not here. 

But it should be made clear by Zabbix here in this very issue, that the public visible comment is just a draft and the final sql command is a hidden developer comment. 
We have to wait for an official change to the Zabbix Docs. 

Hi! I opened part of comments for all users to get faster to final solution.

When we can expected resolution for this issue?

We are close to the final version. But all important things you can see in comments above.

How to repair Zabbix database character set and collation (for MySQL and MariaDB)

1. Check DB character set and collation.
Example:

mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_general_ci   |
+--------------------------+----------------------+

Here character set is not 'utf8' and collation is not 'utf8_bin', so we need to fix them.

2. Stop Zabbix.
3. Create a database backup copy!
4. Fix character set and collation on DB level:

 alter database <your DB name> character set utf8 collate utf8_bin;

Fixed values:

mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_bin             |
+--------------------------+----------------------+     

5. Load the script utf8_convert.sql to fix character set and collation on table and column level:

  mysql <your DB name> < utf8_convert.sql

6. Execute script:

               SET @ZABBIX_DATABASE = '<your DB name>';
If MariaDB ->  set innodb_strict_mode = OFF;        
               CALL zbx_convert_utf8();
If MariaDB ->  set innodb_strict_mode = ON;   
               drop procedure zbx_convert_utf8;

Note that data encoding will be changed on disk. For example, when converting from 'latin1' to 'utf8', characters like Æ, Ñ, Ö will go from 1 byte to 2 bytes. Thus the repaired database may require more space than before.

7. If no errors - you may want to create a database backup copy with fixed DB.
8. Start Zabbix.

I followed the steps above, downloaded the .sql script from the link in step 5, but I get:

# mysql -u root -p zabbixdb < utf8_convert.sql
Enter password: 
ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cmd;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
        end loop cmd_loop;
        close cur_c' at line 48

I'm using mysql 5.7.31 on FreeBSD 12.1. Any idea what could be wrong with the script code?

Thanks for reporting issues! We are looking into problem. I observed the error on MySQL 8.0.21, but on MariaDB 10.3.24 is is ok.

Apparently this fix

--- utf8_convert.sql.org        2020-09-01 16:57:35.814195380 +0300
+++ utf8_convert.sql    2020-09-03 13:14:51.169278685 +0300
@@ -59,7 +59,8 @@
                if finished = 1 then
                        leave cmd_loop;
                end if;
-               PREPARE stmt FROM cmd;
+               SET @value = cmd;
+               PREPARE stmt FROM @value;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
        end loop cmd_loop;

helps on MySQL 8.0.21.

I have to test it on other MySQL and MariaDB versions.

The script now loads, but I get another error in step 6:

[root@freebsd /home/bart]# mysql -u root -p zabbixdb < utf8_convert.sql 
Enter password: 
[root@freebsd /home/bart]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 62
Server version: 5.7.31-log Source distribution


Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


root@localhost [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zabbixdb           |
+--------------------+
5 rows in set (0.00 sec)


root@localhost [(none)]> SET @ZABBIX_DATABASE = 'zabbixdb';
Query OK, 0 rows affected (0.00 sec)


root@localhost [(none)]> CALL zbx_convert_utf8();
ERROR 1046 (3D000): No database selected

The 1st command

# mysql -p zabbixdb < utf8_convert.sql

specifies DB to operate on - zabbixdb.
The 2nd command

# mysql -u root -p

does not specify which DB you will operate on.
Try

# mysql -p zabbixdb

as the 2nd command.
And you will need

SET @ZABBIX_DATABASE = 'zabbixdb';

too.

Published a new version of utf8_convert.sql script.
Fixed for MySQL. Tested on MySQL 5.6.49, 8.0.21 and MariaDB 10.3.24.

completely forgot the db when logging in

// # mysql -p zabbixdb

this fixed it, ran the script and the db is back online! Thanks for the support.

Thanks for feedback

FWIW, when I run the latest version of utf8_convert.sql over my sessions and events tables it crashes MariaDB 10.2.34 whilst running:

Query (0x7ff7ec097cf8): ALTER TABLE zabbix_prod.events CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin

I'm going to file a bug report with MariaDB and will post back here if I make any progress.

Any news on this jameskirsop ?

andris I tried this today, didn't work. I cloned our prod VM into a test VM, so no prod environments were harmed.

This is what happened, I'm not DBA, so not sure what this means. Should I create a new issue?

Environment: Zabbix 5.2.6 / Debian 10 / MariaDB 10.4

Zabbix_server was stopped

root@monitoring:~# mysql -u root -p zabbix < utf8_convert.sql
root@monitoring:~# mysql -u root -p zabbix
MariaDB [zabbix]>SELECT @@character_set_database, @@collation_database;
MariaDB [zabbix]>alter database zabbix character set utf8 collate utf8_bin;
Query OK, 0 rows affected (0.000 sec)
MariaDB [zabbix]> SET @ZABBIX_DATABASE = 'zabbix';
Query OK, 0 rows affected (0.000 sec)
MariaDB [zabbix]> set innodb_strict_mode = OFF;
Query OK, 0 rows affected (0.000 sec)
MariaDB [zabbix]> CALL zbx_convert_utf8();
ERROR 1878 (HY000): Temporary file write failureone 
MariaDB [zabbix]>

EDIT

So seems like my DBA added a tmpfs for MariaDB which is too small for the conversion:

tmpfs /tmp/mysqltmp tmpfs rw,uid=mysql,gid=mysql,size=4G,nr_inodes=10k,mode=0700 0 0

#RamDisk
tmpdir                          = /tmp/mysqltmp

So I talk to him, where I get more space for the conversion.

EDIT2

Talked to my DBA, he suggests to simply add a temporary dummy vdisk, which is slow but reliable, until the (test-)conversion is done, which I then discard afterwards.

"ERROR 1878 (HY000): Temporary file write failure" - could it be "disk full" ?

andris Yes, see my edits above. It was the small ramdisk.

In the meantime, I finished all my tests on my cloned VM. The conversion took 1h and 41m and the DB size on the HDD (not the actual DB size!) decreased about 25GB. (241GB -> 215GB)

After that I tested what I could test with an offline Zabbix server:

• Creating/Renaming/Deleting hosts
• Creating/Renaming/Deleting items
• Creating/Renaming/Deleting trigger
• Creating/Renaming/Deleting templates
• Creating/Renaming/Deleting actions
• I also tried creating the same host in 4 different cases:
  • dc2016-01
  • DC2016-01
  • Dc2016-01
  • dC2016-01
  • That worked very well, and isn't possible in our prod environment (yet)
  • All of the above failed, with the (very) old conversion script Zabbix offered 1 or 2 years ago, can't remember.

For today evening we have planed a 4 hour downtime, to implement that change in our prod environment. Wish me luck ^^

Only issue I fear, is that the auto-registration might go nuts, due to the now possible fact, that because of the case-sensitive DB, hosts might register a second time, if the hostname in the zabbix_agent.conf differs in case from the host in Zabbix frontend. I will activate the auto-registration E-Mail in the actions, so I hope to catch those double registrations.

Thanks, Marco, for feedback!

I wish You successful DB conversion!

andris Finished successful it seems! I want to share my data with everybody who is interested:

Zabbix 5.2.6 on Debian 10 Buster with MariaDB 10.4

Zabbix DB size was 179,67GB before the conversion, using 241,75GB on the HDD.

Conversion took 2h 28m to finish.

Zabbix DB size is 146,04GB after the conversion, using 226,95GB on the HDD.

Afterwards I used the planed downtime, to upgrade MariaDB from version 10.4 to 10.5, as recommend in the upcoming Zabbix 5.4 docs:
https://www.zabbix.com/documentation/5.4/manual/installation/requirements#required_software

Great! Nice to hear it worked!