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

"mediumtext" columns instead of "text" sometimes appear in zabbix DB schema after v1.6

    Details

      Description

      Goal of creation this issue report first of all is a some place where to describe everything.

      Probably we could add some notes to documentation but we will discuss this in comments.

      In v1.6 DB schema has 5 columns with "text" type:

      node_cksum.cksum
      history_log.value
      history_text.value
      proxy_history.value
      items.params

      Just a note - v1.8 schema has the same 5 "text" columns, v2.0 has much more "text" columns.

      If perform conversion to UFT8 (designed in upgrade 1.6->1.8) they all will get new "mediumtext" type.
      See a nice article http://www.mysqlperformanceblog.com/2009/03/17/converting-character-sets/

      After applying 1.6->1.8 SQL patch there will remain only 4 such columns:

      history_log.value
      history_text.value
      proxy_history.value
      items.params

      Then after applying 1.8->2.0 SQL patch there will remain only 3 such columns:

      history_log.value
      history_text.value
      items.params

      What a problem is here? See:

      Sometimes people need to check DB schema to make sure that all is correct.
      And if such inconsistencies appear then we have a question - what is that?, when and why it appeared ?
      To make sure that all is fine we need to know exact reason of this inconsistency and fix it.
      It's NOT very easy to figure out pure reason.

      Some real scenarios:
      #1 user started to use v1.6 and in V2.0 got 3 "mediumtext" columns.
      #2 user skipped UTF8 conversion (during 1.6->1.8) for some reason and did fix it now and got:

      • 4 columns - if convert in v1.8 a DB created in 1.6;
      • 5 columns - if convert in v1.8 a DB created in 1.8;
      • a lot of columns if convert in v2.0
        #3 user created v1.8+ database accidentally without specifying "... character set utf8". then see scenario #2

      How to fix.
      for scenario #1, #2:
      If you have up to 5 columns with the "mediumtext", here is ready SQLs (if currently used schema is v2.0):

      ALTER TABLE node_cksum MODIFY value cksum NOT NULL;
      ALTER TABLE history_log MODIFY value text NOT NULL;
      ALTER TABLE history_text MODIFY value text NOT NULL;
      ALTER TABLE proxy_history MODIFY value text NOT NULL;
      ALTER TABLE items MODIFY params text NOT NULL;

      for scenario #2, #3:
      Need to convert all tables, repeat this SQL for all tables:

      ALTER TABLE <tablename_here> CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

      and then check and change "mediumtext" to "text" as shown above for ALL appeared "mediumtext" columns.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              zalex_ua Oleksiy Zagorskyi
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: