Zabbix database character set error

[tim.mooney]

The ZBX issue that Starko linked, especially the last post by Andris Mednis, is something that everyone facing this issue should read carefully.

A lot of the "how to change your collation" guides I found while researching this issue are incomplete or just plain wrong. They leave you with some columns with the wrong collation or cause other problems.

[Starko]

But you have to be careful! The post from Andris Mednis is still a draft. There is a hidden developer comment with the final draft, waiting for approval. So there is still no final official version.

[Starko]

The draft seems to be part of the official docs now:

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

[mca2]

but I have problems with the .sql script in step 5. Any idea what is wrong with this script: https://support.zabbix.com/secure/at...f8_convert.sql

# mysql -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

[mbadkoobe]

Hi
i have a problem about charset
i installed a zabbix-server with oracle database( zabbix 5.0.10 and oracle 19.c)
the zabbix is up now and send data to database( the size of history table is become bigger) but in frontend in setup.php, in database section when i fill up oracle setting, a error is shown and say :
can not connect to database; incorrect parameter NLS_NCHAR_CHARACTERSET value: AL16UTF16 instead of AL32UTF8, UTF8
i try lots of changes in php.ini, httpd.conf, php-oci.ini but no one works
i’ll be appreciated if someone help me!!

[Hasabadusa23]

The draft seems to be part of the official docs now:
https://www.zabbix.com/documentation...b_charset_coll

Hi of course the documentation is not working because it doesnt inherit the changes to the tables.

I had to run a script to change every table

#!/bin/bash

# Verbindungsparameter für MySQL
MYSQL_USER="USERNAME"
MYSQL_PASSWORD="PASSWORD"
MYSQL_DATABASE="zabbix"

# SQL-Abfrage, um die 'ALTER TABLE'-Anweisungen zu generieren
SQL_QUERY=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -Nse "SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;') FROM information_schema.tables WHERE table_schema = '$MYSQL_DATABASE';")

# Ausführung der generierten 'ALTER TABLE'-Anweisungen
echo "$SQL_QUERY" | mysql -u$MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE

[Starko]

I can't confirm that. I used those instructions twice without any issues. First, I converted everything to utf8_bin while 5.4 was current. -> Source https://www.zabbix.com/documentation...b_charset_coll
Then I converted everything again to utf8mb4_bin for Zabbix 6.0+ (can't remember exactly, with the following guide -> https://www.zabbix.com/documentation...b_charset_coll

I don't see the reason to create something yourself, when there is the script by Zabbix, linked in the article?

[Hasabadusa23]

I used the script and did every step in the tutorial but it didn't worked. I used the newest .ovf file as an appliance https://cdn.zabbix.com/zabbix/applia....10-ovf.tar.gz

The appliance has also other error that are maybe related to my errors, for example is the zabbix user table empty so you have to reinitiliase the whole database after the appliance started.


I really love Zabbix and I am using it since Zabbix 3 but the installation, everytime, is like in that Linkin Park Song:

"Every step that I take is another mistake to youuuuuuuuuuuuuuu, I'VE BECOME SO NUMB...."