[ZBX-6323] instructions should call for case sensitive mysql database Created: 2013 Feb 26  Updated: 2024 Apr 10  Resolved: 2017 Feb 17

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Documentation (D)
Affects Version/s: None
Fix Version/s: None

Type: Incident report Priority: Major
Reporter: richlv Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: casesensitivity, mysql, upgrade
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
is duplicated by ZBX-4516 Autoregistration hostname case-sensit... Closed
is duplicated by ZBX-7216 Zabbix Frontent 2.0.7 login case sens... Closed
is duplicated by ZBX-7958 Duplicate Applications can't be assig... Closed
Team: Team A
Sprint: Sprint 1

 Description   

(1) various problems arise from mysql db being case insensitive for most users.

zabbix documentation should advise for mysql database to be case sensitive.
do we want to do this for 2.2 docs only, or also for 2.0 ?

a) installation instructions :
https://www.zabbix.com/documentation/2.2/manual/appendix/install/db_scripts (and maybe corresponding 2.0 page)
"create database zabbix character set utf8;" should be changed to
"create database zabbix character set utf8 collate utf8_bin;"

b) upgrade notes for 2.2 should suggest this in https://www.zabbix.com/documentation/2.2/manual/installation/upgrade_notes_220 (and maybe corresponding 2.0 page)
they should suggest both changing db collation & for all tables.

ALTER DATABASE zabbix DEFAULT CHARACTER SET utf8 COLLATE utf8mb4_bin;
ALTER TABLE <table name> COLLATE utf8_bin;

apparently, the second query above also adds utf8 charset for columns, but not collate. then default collate for that charset is used, not the one set for the table (as par http://dev.mysql.com/doc/refman/5.0/en/charset-column.html). the following works, but shows collate for all columns. that seems to be only displaying collate which is not the default for used charset (as opposed to it being different to the table collation).

ALTER TABLE <table name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

it could be a shellscript like :
for table in $(echo "show tables;" | mysql -N zabbix); do echo "ALTER TABLE $table CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;" | mysql -N zabbix; done
and there's also a way to do that purely in mysql : http://serverfault.com/questions/124797/alter-table-for-all-tables-in-a-database

(2) mysql will ignore trailing spaces for most operations, which can be a problem for zabbix (for example, ipmi sensors may have trailing space). using 'like' instead of '=' will do exact match.
this would require code changes. do we want to do that ?

 Comments   
Comment by Martins Valkovskis [ 2013 Feb 27 ]

I'd say both 2.0 and 2.2 instructions should be changed, unless there are some good reasons against touching it in 2.0.

<richlv> agreed, case insensitivity is causing various problems - but _bin collation should be extensively tested with zabbix

zalex_ua I'd suggest to not touch 2.0 doc.
Why?
2.2 expected to be soon (I hope), we will remember that this change should appear in 2.2 doc only - it will be easier.
In any case users will apply such changes probably only for 2.2

Comment by Oleksii Zagorskyi [ 2013 Feb 27 ]

ZBX-6019 contains some discussion around this topic.
ZBX-5781 is also related.

Comment by richlv [ 2013 Mar 18 ]

for the record, i've been using several of my test installs with utf8_bin for several weeks now, haven't seen any issue with that so far

Comment by Antonio Salazar [ 2013 May 11 ]

So far the only issue I've seen after the conversion to utf8_bin was that users could not login, probably because the user aliases were mixed-case.
As a workaround the table users was kept as utf8_general_ci.
We're using Zabbix 2.0.6

Comment by richlv [ 2014 Oct 24 ]

installation notes have been updated, but suggestion to change was not added to upgrade notes. now it should probably added to upgrade notes for 2.2, 2.4 and 3.0

Comment by richlv [ 2015 Feb 13 ]

it has been mentioned that adding everything from "b)" to upgrade notes would be too much - and that is true. a short version should be used instead. what exactly - any suggestions ?

Comment by richlv [ 2016 Mar 06 ]

...now it should probably added to upgrade notes for 2.2, 2.4, 3.0 and 3.2

Comment by Oleksii Zagorskyi [ 2016 Apr 28 ]

https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldbcompare.html

Comment by Martins Valkovskis [ 2017 Feb 16 ]

I have added a basic note to the upgrade notes for 2.0, 2.2, 2.4 and 3.0.

2.0 because that's the version we changed the database creation instructions in. It could be argued that having one note in 2.0 is enough. I think it's good to repeat it for a few versions, but we can't go on repeating this info infinitely either.

Comment by richlv [ 2017 Feb 16 ]

thanks, looks good to me. as for the versions, it might still be good to add to 3.2 and 3.4 upgrade notes. reasoning :

  • this is still a common misconfiguration
  • the note has been added some time after the 3.2 release, thus it is quite likely that users of up to and including 3.2 would have this misconfiguration

not suggesting to repeat it forever, 3.4 would be the last version to warn about this

Comment by Martins Valkovskis [ 2017 Feb 17 ]

Agreed, added to the 3.2 and 3.4 upgrade notes as well.

Comment by richlv [ 2017 Feb 17 ]

great, looks good - thank you

Comment by Satchel [ 2017 Apr 28 ]

Not sure if this is the appropriate place, but I was able to follow along with the docs here: https://confluence.atlassian.com/jirakb/how-to-change-all-columns-and-tables-collation-to-utf8_bin-in-mysql-601456761.html and get my medium-to-large sized installation migrated. This is an instance that's been upgraded from 2.0->2.4->3.0 and now 3.2 with success, origianlly created with utf8_general_ci.

Comment by Joris Willems [ 2018 Jun 21 ]

@Satchel Thanks for the link, the instructions work perfectly and fast compared to other methods. I migrated from latin1 without issues.

Comment by Oleksii Zagorskyi [ 2020 Apr 23 ]

ZBX-17357 contains addition details

Generated at Tue Jul 22 08:36:41 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.