[ZBX-17745] Several errors after "Changing database character set and collation" Created: 2020 May 15  Updated: 2020 Jul 06  Resolved: 2020 Jul 06

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Frontend (F), Server (S)
Affects Version/s: 4.4.8
Fix Version/s: None

Type: Problem report Priority: Trivial
Reporter: Marco Hofmann Assignee: Renats Valiahmetovs (Inactive)
Resolution: Duplicate Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Debian 10 Buster
MariaDB 10.4

Attachments: File 3_alter.sql     PNG File image-2020-05-14-23-16-27-769.png     PNG File image-2020-05-14-23-54-58-962.png    

 Description   

Steps to reproduce:

  1. Have an old Zabbix Database on MySQL or MariaDB from pre 3.0 era
  2. Patch to 4.4.4 or higher
  3. Try to repair according to:
    https://www.zabbix.com/documentation/current/manual/appendix/install/db_charset_coll
  4. Use query from ZBX-17740 instead

Result:

See log file: 3_alter.sql

Trying to login failed. Wrong user name and password. I had to use a backup admin account, a rename my account to function again.

Then I tried to add a trigger, but it says template not found. I had to rename the template twice, first a dummy name and then back to it's original name.After that it worked.

I'm not sure I haven't even discovered every problem, yet.

Expected:
I know that the DB is now case-sensitive and this switch had to be done, but I have now a load of problems.

Did I do anything wrong, is this expected?

 Comments   
Comment by Marco Hofmann [ 2020 May 15 ]

I now have each and every Zabbix Agent with Auto Registration twice. Somehow they seem to be different hosts now:

Comment by Andrei Gushchin (Inactive) [ 2020 May 19 ]

Please provide the ouptut of the command from MySQL cli:

mysql> show create table hosts\G;
Comment by Marco Hofmann [ 2020 May 19 ]

I already dropped the defunct VM and replaced it with a backup from before the changes. We are now running again prod on 4.4.8 with the wrong charset.

If it's really important I could clone the VM and execute the script from the deleted article again, to reproduce the state from the report.

Please tell me how important that would be for Zabbix support.

Comment by Andrei Gushchin (Inactive) [ 2020 May 20 ]

For MySQL collation make sense especially when you have hosts with very close name convention but different case in spelling.
Please provide the current hosts table code as I asked earlier.

Comment by Fernando Collado Permuy [ 2020 May 20 ]

Hi Andrei Gushchin

I have exactly the same problem, here is my show create table hosts;

I had a zabbix 4.4.8 with a latin1 charset and change it using the old doc link.

*************************** 1. row ***************************
       Table: hosts
Create Table: CREATE TABLE `hosts` (
  `hostid` bigint(20) unsigned NOT NULL,
  `proxy_hostid` bigint(20) unsigned DEFAULT NULL,
  `host` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '''',
  `status` int(11) NOT NULL DEFAULT 0,
  `disable_until` int(11) NOT NULL DEFAULT 0,
  `error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '''',
  `available` int(11) NOT NULL DEFAULT 0,
  `errors_from` int(11) NOT NULL DEFAULT 0,
  `lastaccess` int(11) NOT NULL DEFAULT 0,
  `ipmi_authtype` int(11) NOT NULL DEFAULT -1,
  `ipmi_privilege` int(11) NOT NULL DEFAULT 2,
  `ipmi_username` varchar(16) COLLATE utf8_bin NOT NULL DEFAULT '''',
  `ipmi_password` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '''',
  `ipmi_disable_until` int(11) NOT NULL DEFAULT 0,
  `ipmi_available` int(11) NOT NULL DEFAULT 0,
  `snmp_disable_until` int(11) NOT NULL DEFAULT 0,
  `snmp_available` int(11) NOT NULL DEFAULT 0,
  `maintenanceid` bigint(20) unsigned DEFAULT NULL,
  `maintenance_status` int(11) NOT NULL DEFAULT 0,
  `maintenance_type` int(11) NOT NULL DEFAULT 0,
  `maintenance_from` int(11) NOT NULL DEFAULT 0,
  `ipmi_errors_from` int(11) NOT NULL DEFAULT 0,
  `snmp_errors_from` int(11) NOT NULL DEFAULT 0,
  `ipmi_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '''',
  `snmp_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '''',
  `jmx_disable_until` int(11) NOT NULL DEFAULT 0,
  `jmx_available` int(11) NOT NULL DEFAULT 0,
  `jmx_errors_from` int(11) NOT NULL DEFAULT 0,
  `jmx_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '''',
  `name` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '''',
  `flags` int(11) NOT NULL DEFAULT 0,
  `templateid` bigint(20) unsigned DEFAULT NULL,
  `description` text COLLATE utf8_bin NOT NULL,
  `tls_connect` int(11) NOT NULL DEFAULT 1,
  `tls_accept` int(11) NOT NULL DEFAULT 1,
  `tls_issuer` varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT '''',
  `tls_subject` varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT '''',
  `tls_psk_identity` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '''',
  `tls_psk` varchar(512) COLLATE utf8_bin NOT NULL DEFAULT '''',
  `proxy_address` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '''',
  `auto_compress` int(11) NOT NULL DEFAULT 1,
  `discover` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`hostid`),
  KEY `hosts_1` (`host`),
  KEY `hosts_2` (`status`),
  KEY `hosts_3` (`proxy_hostid`),
  KEY `hosts_4` (`name`),
  KEY `hosts_5` (`maintenanceid`),
  KEY `c_hosts_3` (`templateid`),
  CONSTRAINT `c_hosts_1` FOREIGN KEY (`proxy_hostid`) REFERENCES `hosts` (`hostid`),
  CONSTRAINT `c_hosts_2` FOREIGN KEY (`maintenanceid`) REFERENCES `maintenances` (`maintenanceid`),
  CONSTRAINT `c_hosts_3` FOREIGN KEY (`templateid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.000 sec)
Comment by Fernando Collado Permuy [ 2020 May 20 ]

Viewing the differences between the original zabbix create table hosts, and the one who I have, I noticed the differences between the default value

9c10
< `error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
---
> `error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '''',
12c13
< `host` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
---
> `host` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '''',
17c18
< `ipmi_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
---
> `ipmi_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '''',
19c20
< `ipmi_password` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '',
---
> `ipmi_password` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '''',
21c22
< `ipmi_username` varchar(16) COLLATE utf8_bin NOT NULL DEFAULT '',
---
> `ipmi_username` varchar(16) COLLATE utf8_bin NOT NULL DEFAULT '''',
24c25
< `jmx_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
---
> `jmx_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '''',
37c38
< `name` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
---
> `name` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '''',
39c40
< `proxy_address` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
---
> `proxy_address` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '''',
43c44
< `snmp_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
---
> `snmp_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '''',
49,52c50,53
< `tls_issuer` varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT '',
< `tls_psk` varchar(512) COLLATE utf8_bin NOT NULL DEFAULT '',
< `tls_psk_identity` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
< `tls_subject` varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT '',
---
> `tls_issuer` varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT '''',
> `tls_psk` varchar(512) COLLATE utf8_bin NOT NULL DEFAULT '''',
> `tls_psk_identity` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '''',
> `tls_subject` varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT '''',

I changed all the ' ' ' ' values for ' ' and now all seems to work, but I not very confident about this change

 ALTER TABLE zabbix.actions MODIFY COLUMN formula varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.actions MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.alerts MODIFY COLUMN error varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.alerts MODIFY COLUMN sendto varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.alerts MODIFY COLUMN subject varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.applications MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.application_discovery MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.application_prototype MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.auditlog MODIFY COLUMN ip varchar(39) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.auditlog MODIFY COLUMN resourcename varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.auditlog_details MODIFY COLUMN field_name varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.auditlog_details MODIFY COLUMN table_name varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.autoreg_host MODIFY COLUMN host varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.autoreg_host MODIFY COLUMN host_metadata varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.autoreg_host MODIFY COLUMN listen_dns varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.autoreg_host MODIFY COLUMN listen_ip varchar(39) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.conditions MODIFY COLUMN value varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.conditions MODIFY COLUMN value2 varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.config MODIFY COLUMN db_extension varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.config MODIFY COLUMN http_strip_domains varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.config MODIFY COLUMN ldap_base_dn varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.config MODIFY COLUMN ldap_bind_dn varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.config MODIFY COLUMN ldap_bind_password varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.config MODIFY COLUMN ldap_host varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.config MODIFY COLUMN ldap_search_attribute varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.correlation MODIFY COLUMN formula varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.correlation MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.corr_condition_tag MODIFY COLUMN tag varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.corr_condition_tagpair MODIFY COLUMN newtag varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.corr_condition_tagpair MODIFY COLUMN oldtag varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.corr_condition_tagvalue MODIFY COLUMN tag varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.corr_condition_tagvalue MODIFY COLUMN value varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.dchecks MODIFY COLUMN snmpv3_authpassphrase varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.dchecks MODIFY COLUMN snmpv3_contextname varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.dchecks MODIFY COLUMN snmpv3_privpassphrase varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.dchecks MODIFY COLUMN snmpv3_securityname varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.dchecks MODIFY COLUMN snmp_community varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.drules MODIFY COLUMN iprange varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.drules MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.dservices MODIFY COLUMN dns varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.dservices MODIFY COLUMN ip varchar(39) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.dservices MODIFY COLUMN value varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.events MODIFY COLUMN name varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.event_tag MODIFY COLUMN tag varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.event_tag MODIFY COLUMN value varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.expressions MODIFY COLUMN expression varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.expressions MODIFY COLUMN exp_delimiter varchar(1) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.functions MODIFY COLUMN name varchar(12) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.globalmacro MODIFY COLUMN macro varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.globalmacro MODIFY COLUMN value varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.graphs MODIFY COLUMN name varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.group_discovery MODIFY COLUMN name varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.group_prototype MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.history_log MODIFY COLUMN source varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.history_str MODIFY COLUMN value varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.hostmacro MODIFY COLUMN macro varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.hostmacro MODIFY COLUMN value varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.hosts MODIFY COLUMN error varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.hosts MODIFY COLUMN host varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.hosts MODIFY COLUMN ipmi_error varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.hosts MODIFY COLUMN ipmi_password varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.hosts MODIFY COLUMN ipmi_username varchar(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.hosts MODIFY COLUMN jmx_error varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.hosts MODIFY COLUMN name varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.hosts MODIFY COLUMN proxy_address varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.hosts MODIFY COLUMN snmp_error varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.hosts MODIFY COLUMN tls_issuer varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.hosts MODIFY COLUMN tls_psk varchar(512) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.hosts MODIFY COLUMN tls_psk_identity varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.hosts MODIFY COLUMN tls_subject varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.host_discovery MODIFY COLUMN host varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.housekeeper MODIFY COLUMN field varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.housekeeper MODIFY COLUMN tablename varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.hstgrp MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.httpstep MODIFY COLUMN name varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.httpstep MODIFY COLUMN required varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.httpstep MODIFY COLUMN status_codes varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.httpstep MODIFY COLUMN url varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.httpstep_field MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.httptest MODIFY COLUMN http_password varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.httptest MODIFY COLUMN http_proxy varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.httptest MODIFY COLUMN http_user varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.httptest MODIFY COLUMN name varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.httptest MODIFY COLUMN ssl_cert_file varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.httptest MODIFY COLUMN ssl_key_file varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.httptest MODIFY COLUMN ssl_key_password varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.httptest_field MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.icon_map MODIFY COLUMN name varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.icon_mapping MODIFY COLUMN expression varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.ids MODIFY COLUMN field_name varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.ids MODIFY COLUMN table_name varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.interface MODIFY COLUMN dns varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN formula varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN http_proxy varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN ipmi_sensor varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN jmx_endpoint varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN logtimefmt varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN password varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN privatekey varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN publickey varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN query_fields varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN snmp_oid varchar(512) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN ssl_cert_file varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN ssl_key_file varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN ssl_key_password varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN trapper_hosts varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN units varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN url varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.items MODIFY COLUMN username varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.item_condition MODIFY COLUMN macro varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.item_condition MODIFY COLUMN value varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.item_preproc MODIFY COLUMN error_handler_params varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.maintenances MODIFY COLUMN name varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.mappings MODIFY COLUMN newvalue varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.mappings MODIFY COLUMN value varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.media MODIFY COLUMN sendto varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.media_type MODIFY COLUMN event_menu_name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.media_type MODIFY COLUMN event_menu_url varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.media_type MODIFY COLUMN exec_params varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.media_type MODIFY COLUMN exec_path varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.media_type MODIFY COLUMN gsm_modem varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.media_type MODIFY COLUMN name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.media_type MODIFY COLUMN passwd varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.media_type MODIFY COLUMN smtp_email varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.media_type MODIFY COLUMN smtp_helo varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.media_type MODIFY COLUMN smtp_server varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.media_type MODIFY COLUMN username varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.opcommand MODIFY COLUMN password varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.opcommand MODIFY COLUMN port varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.opcommand MODIFY COLUMN privatekey varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.opcommand MODIFY COLUMN publickey varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.opcommand MODIFY COLUMN username varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.opconditions MODIFY COLUMN value varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.opmessage MODIFY COLUMN subject varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.problem MODIFY COLUMN name varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.problem_tag MODIFY COLUMN tag varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.problem_tag MODIFY COLUMN value varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.profiles MODIFY COLUMN idx varchar(96) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.profiles MODIFY COLUMN source varchar(96) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.profiles MODIFY COLUMN value_str varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.proxy_autoreg_host MODIFY COLUMN host varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.proxy_autoreg_host MODIFY COLUMN host_metadata varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.proxy_autoreg_host MODIFY COLUMN listen_dns varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.proxy_autoreg_host MODIFY COLUMN listen_ip varchar(39) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.proxy_dhistory MODIFY COLUMN dns varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.proxy_dhistory MODIFY COLUMN ip varchar(39) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.proxy_dhistory MODIFY COLUMN value varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.proxy_history MODIFY COLUMN source varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.regexps MODIFY COLUMN name varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.screens_items MODIFY COLUMN application varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.screens_items MODIFY COLUMN url varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.scripts MODIFY COLUMN command varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.scripts MODIFY COLUMN confirmation varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.scripts MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.services MODIFY COLUMN name varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.services_times MODIFY COLUMN note varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.sessions MODIFY COLUMN sessionid varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.slideshows MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.sysmaps MODIFY COLUMN label_string_host varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.sysmaps MODIFY COLUMN label_string_hostgroup varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.sysmaps MODIFY COLUMN label_string_image varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.sysmaps MODIFY COLUMN label_string_map varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.sysmaps MODIFY COLUMN label_string_trigger varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.sysmaps MODIFY COLUMN name varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.sysmaps_elements MODIFY COLUMN application varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.sysmaps_elements MODIFY COLUMN label varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.sysmaps_links MODIFY COLUMN label varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.sysmap_element_url MODIFY COLUMN url varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.sysmap_shape MODIFY COLUMN background_color varchar(6) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.sysmap_url MODIFY COLUMN url varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.task_remote_command MODIFY COLUMN password varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.task_remote_command MODIFY COLUMN privatekey varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.task_remote_command MODIFY COLUMN publickey varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.task_remote_command MODIFY COLUMN username varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.triggers MODIFY COLUMN correlation_tag varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.triggers MODIFY COLUMN description varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.triggers MODIFY COLUMN error varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.triggers MODIFY COLUMN expression varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.triggers MODIFY COLUMN opdata varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.triggers MODIFY COLUMN recovery_expression varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.triggers MODIFY COLUMN url varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.trigger_tag MODIFY COLUMN tag varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.trigger_tag MODIFY COLUMN value varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.users MODIFY COLUMN alias varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.users MODIFY COLUMN attempt_ip varchar(39) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.users MODIFY COLUMN name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.users MODIFY COLUMN surname varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.users MODIFY COLUMN url varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.usrgrp MODIFY COLUMN name varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.valuemaps MODIFY COLUMN name varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.widget MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.widget MODIFY COLUMN type varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.widget_field MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; 
 ALTER TABLE zabbix.widget_field MODIFY COLUMN value_str varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ;

 Did you have any advise about it?

Comment by Marco Hofmann [ 2020 May 20 ]

I "corrected" the script in ZBX-17740 but take my script with a grain of salt on your own risk.

I corrected the Single quotes to double quotes. See the attached file "3_Changing_column_character_set_and_collation.sql"

Maybe that would've changed the table differently. I'm currently searching time to clone my also wrong charset DB again, and make more tests, to give Zabbix support feedback. There must be an official way, to solve the warning introduced with 4.4.6.

Comment by Fernando Collado Permuy [ 2020 May 20 ]

Hi Marco
 I "stole" many ideas for the changes from your script (thanks for sharing).

Yes I also wait for an official solution. 

Comment by Renats Valiahmetovs (Inactive) [ 2020 Jul 06 ]

Closing this ticket as duplicate of https://support.zabbix.com/browse/ZBX-17357

 

Best Regards,

Renats

Generated at Sat May 03 06:50:12 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.