Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-17740

Wrong SQL Query in Docs: Changing database character set and collation

    XMLWordPrintable

    Details

    • Type: Problem report
    • Status: Closed
    • Priority: Trivial
    • Resolution: Cannot Reproduce
    • Affects Version/s: 4.4.8
    • Fix Version/s: None
    • Component/s: Documentation (D)
    • Labels:
      None
    • Environment:
      Debian 10 Buster
      MariaDB 10.4

      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
      3. 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
      1. Try to repair according to:
        https://www.zabbix.com/documentation/current/manual/appendix/install/db_charset_coll
      2. 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' );

       

       

        Attachments

          Activity

            People

            Assignee:
            kpavars Kristians Pavars
            Reporter:
            starko Marco Hofmann
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: