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

Database migration to V2 schema too lengthy for preexisting databases and production environments

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.0.1
    • Fix Version/s: 2.0.3rc1, 2.1.0
    • Component/s: Installation (I)
    • Environment:

      Description

      Have taken a copy of the production database (14G) and successfully migrated it to the 1.8 schema.

      On migrating it to the 2.0 schema, the script reaches this section of SQL in upgrade.sql

      SELECT e1.eventid,(SELECT e2.value
      FROM events e2
      WHERE e2.source=e1.source
      AND e2.object=e1.object
      AND e2.objectid=e1.objectid
      AND (e2.clock<e1.clock OR (e2.clock=e1.clock AND e2.eventid<e1.eventid))
      AND e2.value<2
      ORDER BY e2.source DESC,
      e2.object DESC,
      e2.objectid DESC,
      e2.clock DESC,
      e2.eventid DESC,
      e2.value DESC
      LIMIT 1) AS prev_value
      FROM events e1
      WHERE e1.source=0
      AND e1.object=0
      AND e1.value=0
      HAVING prev_value IS NULL OR prev_value = 1

      At that point the process takes an eternity to run. I have let it run for 5 days and abandoned it. I have heard of others (see the Forum) running this bit of SQL for 11days.

      This length of database migration is not practical for a production environment. See my comments in the users forum. (http://www.zabbix.com/forum/showthread.php?t=27486)

      I see three potential resolutions:-

      (1) Revise the code so it runs in a realistic time on sensible hardware.
      (2) Have a two stage migration where the core configuration of Hosts/Host Groups/Users/Templates etc etc are migrated fairly rapidly and then a second stage where the history is migrated and re-integrated into the database.
      (3) Could be a by product of (2) - accept that it is not practical to migrate history and abandon that data and limit the migration to Hosts/Host Groups/Users/Templates etc etc

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                kens Ken Smith
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: