Zabbix database character set error

[kola]

So I upgraded Zabbix server from 4.2.1 to 4.4.6 yesterday. Everything seems to be working fine except a few errors which i have been able to fix. However it seems the database cahracter set was initially set as latin1 unfortunately now an error shows up under system information on the web page as follows
"Incorrect default charset for Zabbix database: "latin1" instead "UTF8". It seems i have to change the database to UTF8. How do i go about doing this?

[Nothing497]

I've got something similar after upgrading from 4.0.6 to 4.6 :

Unsupported charset or collation for tables: config_autoreg_tls, host_inventory, host_tag, item_rtdata, lld_macro_path, media_type_param.

How can we find the default charset of a specific table ?

EDIT : I found this documentation but I'm not able to find it for 4.4 version : https://zabbix.org/wiki/Docs/DB_schema/4.0

kola you can find here how to change the default charset of the database, be careful however cause I did it and now some tables have some problems ^^

https://mediatemple.net/community/pr...-and-collation

[sancho]

Hi,

I solved it by changing the schema default collation to utf8 and the tables to utf8_bin.

On this website they indicate how to make the changes, although the example they set is for change from utf8 to utf8mb4. The web is in Spanish.

Before making any changes remember to make a backup of the database.

[tim.mooney]

First, to be clear, everyone should understand that character set and collation are two distinct (but related) things.

The collation requirement is covered in the "Case-sensitive MySQL Database" section of the 3.2.0 upgrade notes.

Just in case you're surprised after fixing the collation: usernames become case sensitive after the collation change. The Zabbix administrative user is actually "Admin", so if you've been using the wrong collation prior to 3.2.x and you change the collation to utf8_bin, you can't use "admin" as the login name.

I really think the developers should expand that section of the upgrade notes, to describe how to change your database collation. It's not exactly trivial. Because my site has paid Zabbix support, I had support provide me with all the ALTER statements to adjust the collation. Our production Zabbix install originally started at Zabbix 2.0.2 and has been upgraded through various versions since then. I believe that the original 2.0.2 database creation documentation didn't use 'utf8_bin' for collation.

Keep in mind that changing the collation is probably easier than changing the character set, but that depends on how compatible the original and final character sets are. If they are not completely compatible, you run the risk experiencing one of my favorite technology-related words: Mojibake

For my site, our character set has always been 'utf8', so that was definitely the recommended setting at 2.0.x when we did our original production install.

If you're not familiar with MySQL's weird character set history, MySQL's utf8 is not "true utf-8". I really hope the Zabbix developers don't discover that they need full UTF8 one day, as we'll all be forced to convert everything from 'utf8' to 'utf8mb4'.

[kola]

Thanks for the reply everyone.

To see the database character set
MariaDB [(none)]> show create database mysql;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+ 1 row in set (0.00 sec)


My plan is to stop the Zabbix service and then run the following commands on the zabbix database to change the character set
ALTER DATABASE zabbix CHARACTER SET utf8 collate utf8_bin; Then restart the service. And yes i will be doing a database dump before running the command. The only thing i am not sure of is would i need to change the character set for the individual tables also?

[derli1988]

Thanks, dude... That was done... Issue solved

[tim.mooney]

When Zabbix support had me change the collation for my site's install before I upgraded to 3.2.x, the ALTER statements they provided didn't just ALTER the database; they altered tables AND columns too. Keep in mind that my site's character set was correct (utf8), it was our original collation that was no longer what Zabbix 3.2.x needed.

I would expect that the same would be true in your case, for changing character set.

Also, as I hinted at in my previous post, changing character set might be more difficult, if the final character set isn't a complete superset of the original character set. It's been a while since I looked, but is MySQL's 'latin1' a compatible subset of MySQL's 'utf8'? If it's not, you may be in for problems if your database contains latin1 characters that are not valid (MySQL) utf8.

[kola]

I think i may have broken the tables in the database. I stopped the Zabbix server service and then ran the alter command on the zabbix database unfortunately seems like the tables now need to be changed also as i am getting the following error on the web page

"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."


I tired running the same alter command on some of the tables but they are still showing up in the error message. Monitoring to see what is broken

[tim.mooney]

Yes, if 'latin1' was your old default for the database, then tables and columns have probably inherited that in some places.

There are lots of articles on the web about converting the character set on an existing database to a new one. There are fewer articles that also talk about changing collation, but they exist too. You probably want to spend some time reading about what others have done when they've faced this situation.

Also, if you can review the mysqldump output from your zabbix database, it will be very informative. Assuming that's what you use to back up your database, look through one of your recent backup mysqldump SQL files. Look for places in the file where either COLLATE or DEFAULT CHARSET appear. Those are places that your ALTER statements will need to adjust, if they're not currently set to the correct character set and collation.

[Nothing497]

Hi there,

I tried to restore a previous version of my Zabbix. I re-run the upgrade procedure from 4.0.6 to 4.4 without editing any database setting. I manage to connect to my database freshly updated and I saw the charset of several tables that have issues in my production environment :

For example, the table "config_autoreg_tls" is setup with the collation "latin1_swedish_ci" by default after upgrading. I tried to change it in my production environment with this SQL command :

alter table config_autoreg_tls character set latin1 collate latin1_swedish_ci;

Restarted zabbix & mariadb, but the error messages are still there ... If anyone comes with a solution, that would be greatly appreciated by me and kola I think ^^

EDIT : The error doesn't seem to show up anywhere else that in the frontend web ... I can't find any error in zabbix_server.log nor mariadb.log
EDIT 2 : That could be related to the default charset define in the web server configuration (AddDefaultCharset UTF-8)

Regards,

[stesko]

Just wanted to leave my 2 cents on this.

Just upgrade from 4.4.5 to 4.4.6 and got the newly added message "Incorrect default charset for Zabbix database: "latin1" instead "UTF8".

Checked the db and we were indeed using latin1.

Steps to reconcile:

- Shut down zabbix-server service
- Backup db!
- Snapshot server (virtual server)
- Change charset and collation for db with:

ALTER DATABASE zabbixdb CHARACTER SET = utf8 COLLATE = utf8_bin;

- Next, create commands to update all tables:

SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;  ')
AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'zabbixdb';

- Take all the commands generated from above and run (total 146 rows for me), I did 10 at a time to not run into paste buffer problems. This step took about 1:30h for our small db (~20GB).

Notable tables that took longer time to convert were:
zabbixdb.history
zabbixdb.history_uint
zabbixdb.trends
zabbixdb.trends_uint

Now, I'm not saying this is the solution for everyone, but it got us running without errors

[tim.mooney]

Sorry to say but I don't think what you've done is complete. The steps so far are I think correct, but they may not be enough.

You've addressed the database and the tables, but *not* the existing columns. Please read my earlier comments about doing a mysqldump and looking at every place where CHARSET or COLLATE appears in the dump output. You need to make certain that you've fixed any columns that are using the wrong collation.

[stesko]

Running CONVERT to character set should also apply to all columns in each table, should it not?
I will do a dump tomorrow and search for charset/collation as you suggest.

[Nothing497]

Great solution, my problem is gone ! I ran the command only on the tables that had the issue and I don't have any error message anymore !
Cheers !

[kola]

Just wanted to leave my 2 cents on this.

Just upgrade from 4.4.5 to 4.4.6 and got the newly added message "Incorrect default charset for Zabbix database: "latin1" instead "UTF8".

Checked the db and we were indeed using latin1.

Steps to reconcile:

- Shut down zabbix-server service
- Backup db!
- Snapshot server (virtual server)
- Change charset and collation for db with:

ALTER DATABASE zabbixdb CHARACTER SET = utf8 COLLATE = utf8_bin;

- Next, create commands to update all tables:

SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; ')
AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'zabbixdb';

- Take all the commands generated from above and run (total 146 rows for me), I did 10 at a time to not run into paste buffer problems. This step took about 1:30h for our small db (~20GB).

Notable tables that took longer time to convert were:
zabbixdb.history
zabbixdb.history_uint
zabbixdb.trends
zabbixdb.trends_uint

Now, I'm not saying this is the solution for everyone, but it got us running without errors

Hi Stesko

I followed the same steps as yours on difference was i found a shell script to do the table conversion so instead of your step to create commands to update all tables i used the following shell script created by Petr Stastny on https://stackoverflow.com/questions/1294117/how-to-change-collation-of-database-table-column
#!/bin/bash
# mycollate.sh <database> [<charset> <collation>]
# changes MySQL/MariaDB charset and collation for one database - all tables and
# all columns in all tables
DB="$1"
CHARSET="$2"
COLL="$3"
[ -n "$DB" ] || exit 1
[ -n "$CHARSET" ] || CHARSET="utf8mb4"
[ -n "$COLL" ] || COLL="utf8mb4_general_ci"
echo $DB
echo "ALTER DATABASE $DB CHARACTER SET $CHARSET COLLATE $COLL;" | mysql
echo "USE $DB; SHOW TABLES;" | mysql -s | (
while read TABLE; do
echo $DB.$TABLE
echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql $DB
done
)

This worked for me and i got the same long time to convert for zabbix.history, zabbixdb.history_uint, zabbixdb.trends and zabbixdb.trends_uint. All in all the script ran for about 2 hours. So advise for anyone going through this process patience is required. Thank you all for the responses.

[gofree]

Is this a bug ? happens to me also when I upgrade zabbix containers from 4.4.5 to 4.4.6

there is something mentioned in the 4.4.6 upgrade nodes - does it mean that the db was created during 4.4.5 installation in a wrong way ?


https://www.zabbix.com/documentation...rade_notes_446

EDIT: I guess I need to look in to DB settings and it has nothing to do with zabbix

[jaida1978]

So I upgraded Zabbix server from 4.2.1 to 4.4.6 yesterday. Everything seems to be working fine except a few errors which i have been able to fix. However it seems the database cahracter set was initially set as latin1 unfortunately now an error shows up under system information on the web page as follows rufus
"Incorrect default charset for Zabbix database: "latin1" instead "UTF8". It seems i have to change the database to UTF8. How do i go about doing this?

I solved it by changing the schema default collation to utf8 and the tables to utf8_bin.

[gofree]

Solved, had to add args with proper settings - now the message is gone.

[alientm]

What file need i edit to add this args ?

[gofree]

i did add those lines to my deployment file , specifically in mysql section

[Starko]

I just stumbled upon this notification in my Zabbix Server Log

only character set "utf8" and collation "utf8_bin" should be used in database

and then found this discussion here. It seems like people are not sure how to handle this, when there is in fact an official guideline available hidden in the documentation:
https://www.zabbix.com/documentation...b_charset_coll
I thought I post it here, as many other will come and ask the same question.
I haven't tried it yet, but will soon.

EDIT: There is also a big discussion with much more details over here:

https://support.zabbix.com/browse/ZBX-17357

[AvocadoSupreme]

Starko, the first link you posted (https://www.zabbix.com/documentation...b_charset_coll) takes me to a page with no content. Are you seeing something different? The Official Guideline for this topic appears to be empty.