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

Problems with upgrade DB to latest shema (trunk 1.9)

    XMLWordPrintable

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.9.0 (alpha)
    • Fix Version/s: 1.9.6 (beta)
    • Component/s: Installation (I)
    • Labels:
      None
    • Environment:
      last trunk rev 14502
      FreeBSD 8.1-RELEASE #0
      mysql-server-5.5.5_1

      Description

      ? ??????? ??? ???? ??????? ????? ???????? ?? ???????
      ????? ????????? ??? ? ?? ????? ????? ? ??, ?? ??????? ??? ? ??????? ???? ? ????? ????????.
      ?? ?????? ? ????? "items.sql" ????? ?????????? ????????? ?????? ? DOS-style. ????? ?? ????? ??? ????.

      ?? ????, ? ? ??? ???? ????? ?????? http://www.zabbix.com/forum/showthread.php?t=19276 ???c ????????? ?? ? ????????? ???? ?? ??????? ??????. ????????? ??????? ????? ? ??? ?????? ???????? ????? ???????? ? ? ???? ?? ??? ?????????
      ???? ??????? ???? sysmaps.sql (??????? ??? ????????? ??????? ??? ??????):

      ALTER TABLE sysmaps MODIFY sysmapid bigint unsigned NOT NULL,
      MODIFY width integer DEFAULT '600' NOT NULL,
      MODIFY height integer DEFAULT '400' NOT NULL,
      MODIFY backgroundid bigint unsigned NULL,
      MODIFY label_type integer DEFAULT '2' NOT NULL,
      MODIFY label_location integer DEFAULT '3' NOT NULL,
      ADD expandproblem INTEGER DEFAULT '1' NOT NULL,
      ADD markelements INTEGER DEFAULT '0' NOT NULL,
      ADD show_unack INTEGER DEFAULT '0' NOT NULL;
      UPDATE sysmaps SET backgroundid=NULL WHERE backgroundid=0;
      UPDATE sysmaps SET show_unack=1 WHERE highlight>7 AND highlight<16;
      UPDATE sysmaps SET show_unack=2 WHERE highlight>23;
      UPDATE sysmaps SET highlight=(highlight-16) WHERE highlight>15;
      UPDATE sysmaps SET highlight=(highlight-8) WHERE highlight>7;
      UPDATE sysmaps SET markelements=1 WHERE highlight>3 AND highlight<8;
      UPDATE sysmaps SET highlight=(highlight-4) WHERE highlight>3;
      UPDATE sysmaps SET expandproblem=0 WHERE highlight>1 AND highlight<4;
      UPDATE sysmaps SET highlight=(highlight-2) WHERE highlight>1;
      ALTER TABLE sysmaps ADD CONSTRAINT c_sysmaps_1 FOREIGN KEY (backgroundid) REFERENCES images (imageid);

      ? ?????? (????????? ??????? ?????) ???????? ??? ??? ???????:
      ADD expandproblem INTEGER DEFAULT '1' NOT NULL,
      ADD markelements INTEGER DEFAULT '0' NOT NULL,
      ADD show_unack INTEGER DEFAULT '0' NOT NULL;
      ??????? ?????? ?????? SQL-?????? ?? ????? ? ???? ?? ?????????? (?????? ???????? ????????).

      ? ????? ????? ?? ??????????? ??? ??? ????? ??????? ???????:
      MODIFY backgroundid bigint unsigned NULL,

      ? - ????? ? ???? ??????? ?? ?????????? ??? ???? ??????:
      UPDATE sysmaps SET backgroundid=NULL WHERE backgroundid=0;

      - ???????? ? ???? ??????? ?? ?????????? ??? ???? ??????:
      ALTER TABLE sysmaps ADD CONSTRAINT c_sysmaps_1 FOREIGN KEY (backgroundid) REFERENCES images (imageid);

      ? ??????? ??? ????? ??????:
      #1452 - Cannot add or update a child row: a foreign key constraint fails (`zabbix`.<result 2 when explaining filename '#sql-4441_56dc'>, CONSTRAINT `c_sysmaps_1` FOREIGN KEY (`backgroundid`) REFERENCES `images` (`imageid`))

      ? ???? ? ??? ?? ????? ???? ? ???? ??????????, ???? ?? ?????? ????? ???????????? ????? ? ??????????? ????? ??????.
      ????????? ?????? - ????? ???? ?????? ??????? ??????? ? ??????? ? ??????? ?????????????????? ??? ??????? ? ??? ?????, ?? ???????? ?? ????? ????? ????????? !!! ? ??????? ?? ?? ?????????????? ??? ?????????.
      ? ??????????????? ????? ??? ???????? ? 1.6 ?? 1.8 - ??? ??? ?? ?????? ?????, ? ??????? ???????????? ??????? ??????? !!! ?? ???? ?? ????????? ? ????? ??????? ?????? ?????? ??? ?? ???????? ?? 9 ????????? ???????? ? ? ???? ?? ???? ?? ??????? ??????? (? ??? ????? ??? ? ??????? ? ????? ?? ??????).
      ??? ??????? ??? ?????????????? ????? ???? ?????????? ? ??? ???, ??? ????? ?????? ??????? ?????????? ????? ??? ?????? ?????? 2.0 ????? ??????? ????? ???? ????? ??????? ? ????? ???? ?????? ????????? ????????.
      ????? ???? ????????????? ????? ?? ????? ??????.

      ?????? ?????. ???? ?????????? ???? "config.sql":
      ALTER TABLE config MODIFY configid bigint unsigned NOT NULL,
      MODIFY alert_usrgrpid bigint unsigned NULL,
      MODIFY discovery_groupid bigint unsigned NOT NULL,
      ADD ns_support integer DEFAULT '0' NOT NULL;
      UPDATE config SET alert_usrgrpid=NULL WHERE NOT alert_usrgrpid IN (SELECT usrgrpid FROM usrgrp);
      UPDATE config SET discovery_groupid=NULL WHERE NOT discovery_groupid IN (SELECT groupid FROM groups);
      ALTER TABLE config ADD CONSTRAINT c_config_1 FOREIGN KEY (alert_usrgrpid) REFERENCES usrgrp (usrgrpid);
      ALTER TABLE config ADD CONSTRAINT c_config_2 FOREIGN KEY (discovery_groupid) REFERENCES groups (groupid);

      ??? ?? ???????? ??????:
      UPDATE config SET alert_usrgrpid=NULL WHERE NOT alert_usrgrpid IN (SELECT usrgrpid FROM usrgrp);

      ? ???? ?????????? ??? ????? ???????
      ALTER TABLE config ADD CONSTRAINT c_config_2 FOREIGN KEY (discovery_groupid) REFERENCES groups (groupid);
      ? ??????? ??? ???????? ??? ??????
      #1452 - Cannot add or update a child row: a foreign key constraint fails (`zabbix`.<result 2 when explaining filename '#sql-4441_5783'>, CONSTRAINT `c_config_2` FOREIGN KEY (`discovery_groupid`) REFERENCES `groups` (`groupid`))

      ???????? ? ??? ?????? ????, ?? ??????? ????????? ??????????? ??? ??? ?? ???????, ??????? ??? ?????????? ????? ? ???? ?????????? (discovery_groupid=0), ? ?? ?????????? (MODIFY discovery_groupid bigint unsigned NOT NULL) ?? ?????????? ????????? ?????? ?? ????? ??? ? ??? ???????.
      ????? ? ?? ?????? ???? ?? ? ??? ??? ??????, ?? ???????? ??? ???? ??? ???????? ? ???? ?????.

      ? ?????????. ? ?????? ???????????. ?????? ?? ?????? 1.8, ???????? ??? ????? ?? ?????????? ??????. ????? ?????? ?? ?????? 1.9 ?? ?????? ????? ?? ?????????? ??????.
      ????????????? ??? ?? ? ????????? ????? (?????? ????? ??? ????? ??????) ? ??????? ??. ???? ???????? ????????? ???????? ? ????? ??, ???? ???? ? ???????? ?? ?????? ?? ?????.
      ... ???, ?????? ?? ???? ??????? ???????. ??? ?????? ?????? ? ?????? 1.8 ?????????? ?????????? ?????? `node_cksum_cksum_1`, ??? ??? ? 1.8.1 ??? ????????? ?????? `node_cksum_1`. ?? ???? ?????? ?? ?? ????????? ?? ???????? ??????? ??????? ?????? ? 1.8 ? ??????????? ???? ???????? ??????.
      ?? ??? ?? DIFF ???? ???????:
      — E:/18 patchedto19 schema.sql Tue Sep 21 00:23:44 2010
      +++ E:/19 last trunk clear schema.sql Tue Sep 21 00:31:09 2010
      @@ -3,7 +3,7 @@
      http://www.phpmyadmin.net

      – Host: 10.10.10.10
      — Generation Time: Sep 21, 2010 at 12:23 AM
      +-- Generation Time: Sep 21, 2010 at 12:30 AM
      – Server version: 5.5.5
      – PHP Version: 5.3.3

      @@ -16,10 +16,10 @@
      /*!40101 SET NAMES utf8 */;


      — Database: `zabbix18`
      +-- Database: `zabbix19`

      -CREATE DATABASE `zabbix18` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
      -USE `zabbix18`;
      +CREATE DATABASE `zabbix19` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
      +USE `zabbix19`;

      – --------------------------------------------------------

      @@ -681,8 +681,8 @@
      KEY `hosts_1` (`host`),
      KEY `hosts_2` (`status`),
      KEY `hosts_3` (`proxy_hostid`),

      • KEY `c_hosts_2` (`maintenanceid`),
      • KEY `hosts_4` (`ip`)
        + KEY `hosts_4` (`ip`),
        + KEY `c_hosts_2` (`maintenanceid`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      – --------------------------------------------------------
      @@ -1131,7 +1131,7 @@
      `cksumtype` int(11) NOT NULL DEFAULT '0',
      `cksum` text NOT NULL,
      `sync` char(128) NOT NULL DEFAULT '',

      • KEY `node_cksum_cksum_1` (`nodeid`,`tablename`,`recordid`,`cksumtype`)
        + KEY `node_cksum_1` (`nodeid`,`cksumtype`,`tablename`,`recordid`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      – --------------------------------------------------------

      ?.?. ??????? ? ?? ??? ???????? 6 ?????

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            zalex_ua Oleksii Zagorskyi
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: