-
Problem report
-
Resolution: Cannot Reproduce
-
Trivial
-
None
-
4.4.8
-
None
-
Debian 10 Buster
MariaDB 10.4
Steps to reproduce:
- Have an old Zabbix Database on MySQL or MariaDB from pre 3.0 era
- Patch to 4.4.4
- Check Zabbix server log and receive the following error:
character set name or collation name that is not supported by Zabbix found in 270 column(s) of database "zabbix" only character set "utf8" and collation "utf8_bin" should be used in database
- Try to repair according to:
https://www.zabbix.com/documentation/current/manual/appendix/install/db_charset_coll - Receive wrong "alter table" querys.
Result:
alter table zabbix.actions modify column esc_period varchar(255) character set utf8 collate utf8_bin default ''1h'' not null ; alter table zabbix.actions modify column def_shortdata varchar(255) character set utf8 collate utf8_bin default '''' not null ;
Expected:
alter table zabbix.actions modify column esc_period varchar(255) character set utf8 collate utf8_bin default '1h' not null ; alter table zabbix.actions modify column def_shortdata varchar(255) character set utf8 collate utf8_bin default '' not null ;
Solution:
I modified the query as following, then everything worked out:
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 = '<yourDB>' and ( collation_name != 'utf8_bin' OR character_set_name != 'utf8' );
