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

instructions should call for case sensitive mysql database

    XMLWordPrintable

Details

    • Team A
    • 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 ?

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              richlv richlv
              Votes:
              0 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: