Details

      Description

      cat upgrades/dbpatches/2.0/mysql/patch.sql | mysql zabbix
      the following error accures:
      ERROR 1452 (23000) at line 104: Cannot add or update a child row: a foreign key constraint fails (`zabbix`.<result 2 when explaining filename '#sql-7d6d_2'>, CONSTRAINT `c_config_2` FOREIGN KEY (`discovery_groupid`) REFERENCES `groups` (`groupid`))

      Whereas "discovery_groupid" column modified to NOT NULL, then executes the next query:
      UPDATE config SET discovery_groupid=NULL WHERE NOT discovery_groupid IN (SELECT groupid FROM groups);

      The "discovery_groupid" column can not be NULL.

      The problem may happen when discovery_groupid references to non exist groupid.

        Issue Links

          Activity

          Hide
          Gertjan Awater added a comment -

          almost the same here:
          ERROR 1452 (23000) at line 104: Cannot add or update a child row: a foreign key constraint fails (`zabbix_db`.<result 2 when explaining filename '#sql-4a17_25'>, CONSTRAINT `c_config_2` FOREIGN KEY (`discovery_groupid`) REFERENCES `groups` (`groupid`))
          it's a testenvironment, I can execute any command you'd like

          Show
          Gertjan Awater added a comment - almost the same here: ERROR 1452 (23000) at line 104: Cannot add or update a child row: a foreign key constraint fails (`zabbix_db`.<result 2 when explaining filename '#sql-4a17_25'>, CONSTRAINT `c_config_2` FOREIGN KEY (`discovery_groupid`) REFERENCES `groups` (`groupid`)) it's a testenvironment, I can execute any command you'd like
          Hide
          Alexey Pustovalov added a comment -

          Second problem:
          We try create unique index:
          CREATE UNIQUE INDEX hosts_groups_1 ON hosts_groups (hostid,groupid);
          but we don't check duplicates!

          This is should help:
          CREATE TABLE hosts_groups_tmp LIKE hosts_groups;
          INSERT INTO hosts_groups_tmp SELECT * FROM hosts_groups;
          DELETE FROM hosts_groups WHERE hostgroupid NOT IN (SELECT min(hostgroupid) FROM hosts_groups_tmp GROUP BY hostid,groupid);
          DROP TABLE hosts_groups_tmp;

          Show
          Alexey Pustovalov added a comment - Second problem: We try create unique index: CREATE UNIQUE INDEX hosts_groups_1 ON hosts_groups (hostid,groupid); but we don't check duplicates! This is should help: CREATE TABLE hosts_groups_tmp LIKE hosts_groups; INSERT INTO hosts_groups_tmp SELECT * FROM hosts_groups; DELETE FROM hosts_groups WHERE hostgroupid NOT IN (SELECT min(hostgroupid) FROM hosts_groups_tmp GROUP BY hostid,groupid); DROP TABLE hosts_groups_tmp;
          Hide
          Gertjan Awater added a comment -

          almost the same error as before:
          ERROR 1452 (23000) at line 104: Cannot add or update a child row: a foreign key constraint fails (`zabbix_db`.<result 2 when explaining filename '#sql-610_171'>, CONSTRAINT `c_config_2` FOREIGN KEY (`discovery_groupid`) REFERENCES `groups` (`groupid`))

          Executed your four rows of sql code prior to running ./zabbix-2.0.0/upgrades/dbpatches/2.0/mysql/patch.sql
          If you would like, I can give access to our database on an ftp server?
          If it helps: the zabbix installation is an old one, upgraded from 1.? -> 1.4.4 -> 1.8.? -> 1.8.6 now

          Show
          Gertjan Awater added a comment - almost the same error as before: ERROR 1452 (23000) at line 104: Cannot add or update a child row: a foreign key constraint fails (`zabbix_db`.<result 2 when explaining filename '#sql-610_171'>, CONSTRAINT `c_config_2` FOREIGN KEY (`discovery_groupid`) REFERENCES `groups` (`groupid`)) Executed your four rows of sql code prior to running ./zabbix-2.0.0/upgrades/dbpatches/2.0/mysql/patch.sql If you would like, I can give access to our database on an ftp server? If it helps: the zabbix installation is an old one, upgraded from 1.? -> 1.4.4 -> 1.8.? -> 1.8.6 now
          Hide
          Alexey Pustovalov added a comment -

          also we can use IGNORE syntax for creating UNIQUE indexes. For example:
          ALTER IGNORE TABLE items_applications ADD UNIQUE INDEX items_applications_1 (applicationid,itemid);

          IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

          Show
          Alexey Pustovalov added a comment - also we can use IGNORE syntax for creating UNIQUE indexes. For example: ALTER IGNORE TABLE items_applications ADD UNIQUE INDEX items_applications_1 (applicationid,itemid); IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.
          Hide
          dimir added a comment - - edited

          Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-5125
          r28768 - default discovery group in config table
          r28775 - remove duplicates from hosts_groups to allow unique index

          Show
          dimir added a comment - - edited Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-5125 r28768 - default discovery group in config table r28775 - remove duplicates from hosts_groups to allow unique index
          Hide
          Gertjan Awater added a comment -

          restored 1.8.6 DB, tried again. Upgrade still crashes on line 104, which looks like this now:
          ALTER IGNORE TABLE config ADD CONSTRAINT c_config_2 FOREIGN KEY (discovery_groupid) REFERENCES groups (groupid);

          Show
          Gertjan Awater added a comment - restored 1.8.6 DB, tried again. Upgrade still crashes on line 104, which looks like this now: ALTER IGNORE TABLE config ADD CONSTRAINT c_config_2 FOREIGN KEY (discovery_groupid) REFERENCES groups (groupid);
          Hide
          dimir added a comment -

          Gertjan, could you take the development branch when upgrading to 2.0:

          svn co svn://svn.zabbix.com/branches/dev/ZBX-5125

          And try to upgrade using that?

          Show
          dimir added a comment - Gertjan, could you take the development branch when upgrading to 2.0: svn co svn://svn.zabbix.com/branches/dev/ZBX-5125 And try to upgrade using that?
          Hide
          Gertjan Awater added a comment -

          That did the trick...script has been running for hours now, so I guess it's past line 104
          Hope it's ready before zabcon 2012

          Show
          Gertjan Awater added a comment - That did the trick...script has been running for hours now, so I guess it's past line 104 Hope it's ready before zabcon 2012
          Hide
          dimir added a comment -

          Has it been also that long before too? Or there was no "before" ? How big is your DB?

          Show
          dimir added a comment - Has it been also that long before too? Or there was no "before" ? How big is your DB?
          Hide
          Gertjan Awater added a comment -

          patch.sql used to crash within 5 minutes on line 104. DB is 6GB, running conversion on a rather small sized VM. mysqld runnin 100% cpu. It's OK, no rush...no direct need for 2.0 here. Would just be nice to be able to stand up at zabconf when the question "who is running 2.0?" comes
          Restoring 1.8.6 db (8GB) took 40 minutes.

          Show
          Gertjan Awater added a comment - patch.sql used to crash within 5 minutes on line 104. DB is 6GB, running conversion on a rather small sized VM. mysqld runnin 100% cpu. It's OK, no rush...no direct need for 2.0 here. Would just be nice to be able to stand up at zabconf when the question "who is running 2.0?" comes Restoring 1.8.6 db (8GB) took 40 minutes.
          Hide
          dimir added a comment -

          Oh, thanks for all the info. It's obvious then it takes that long. See you on zabconf! :-D

          Show
          dimir added a comment - Oh, thanks for all the info. It's obvious then it takes that long. See you on zabconf! :-D
          Hide
          Alexander Vladishev added a comment -

          Great! Successfully tested!

          Show
          Alexander Vladishev added a comment - Great! Successfully tested!
          Hide
          dimir added a comment -

          Fixed in pre-2.0.2 r28925, pre-2.1.0 r28926.

          Show
          dimir added a comment - Fixed in pre-2.0.2 r28925, pre-2.1.0 r28926.
          Hide
          Alexey Pustovalov added a comment -

          New bugs in the upgrade script

          Show
          Alexey Pustovalov added a comment - New bugs in the upgrade script
          Hide
          Alexey Pustovalov added a comment - - edited

          (1) CREATE UNIQUE INDEX hostmacro_1 ON hostmacro (hostid,macro);
          ERROR 1062 (23000) at line 280: Duplicate entry '15585-{$SOMESTING}' for key 'hostmacro_1'
          (2) ALTER TABLE services ADD CONSTRAINT c_services_1 FOREIGN KEY (triggerid) REFERENCES triggers (triggerid) ON DELETE CASCADE;
          Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`#sql-470e_12`, CONSTRAINT `c_services_1` FOREIGN KEY (`triggerid`) REFERENCES `triggers` (`triggerid`) ON DELETE CASCADE)
          It can help with solve the (2) problem:
          UPDATE services SET triggerid = NULL WHERE triggerid = 0;
          CREATE TABLE services_tmp (
          serviceid bigint unsigned NOT NULL,
          triggerid bigint unsigned
          ) ENGINE = InnoDB;
          INSERT INTO services_tmp (serviceid, triggerid) SELECT DISTINCT s.serviceid, s.triggerid FROM services s LEFT JOIN triggers t ON t.triggerid = s.triggerid WHERE t.triggerid IS NULL AND s.triggerid IS NOT NULL;
          UPDATE services s, services_tmp st SET s.triggerid = NULL WHERE st.serviceid = s.serviceid AND s.triggerid = st.triggerid;
          DROP TABLE services_tmp;

          <dimir> I believe this was resolved in r29350. CLOSED

          Show
          Alexey Pustovalov added a comment - - edited (1) CREATE UNIQUE INDEX hostmacro_1 ON hostmacro (hostid,macro); ERROR 1062 (23000) at line 280: Duplicate entry '15585-{$SOMESTING}' for key 'hostmacro_1' (2) ALTER TABLE services ADD CONSTRAINT c_services_1 FOREIGN KEY (triggerid) REFERENCES triggers (triggerid) ON DELETE CASCADE; Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`#sql-470e_12`, CONSTRAINT `c_services_1` FOREIGN KEY (`triggerid`) REFERENCES `triggers` (`triggerid`) ON DELETE CASCADE) It can help with solve the (2) problem: UPDATE services SET triggerid = NULL WHERE triggerid = 0; CREATE TABLE services_tmp ( serviceid bigint unsigned NOT NULL, triggerid bigint unsigned ) ENGINE = InnoDB; INSERT INTO services_tmp (serviceid, triggerid) SELECT DISTINCT s.serviceid, s.triggerid FROM services s LEFT JOIN triggers t ON t.triggerid = s.triggerid WHERE t.triggerid IS NULL AND s.triggerid IS NOT NULL; UPDATE services s, services_tmp st SET s.triggerid = NULL WHERE st.serviceid = s.serviceid AND s.triggerid = st.triggerid; DROP TABLE services_tmp; <dimir> I believe this was resolved in r29350. CLOSED
          Hide
          Alexander Vladishev added a comment -

          Fixed in the development branch svn://svn.zabbix.com/branches/dev/ZBX-5125

          Show
          Alexander Vladishev added a comment - Fixed in the development branch svn://svn.zabbix.com/branches/dev/ZBX-5125
          Hide
          dimir added a comment -

          Successfully tested.

          Alexey, thank you for you suggestions!

          However, we have used a bit different approach. We delete the duplicate rows leaving the one with the lowest ID by executing something like this:

          DELETE FROM hostmacro
          WHERE hostmacroid IN (
          SELECT hm1.hostmacroid
          FROM hostmacro hm1
          LEFT OUTER JOIN (
          SELECT MIN(hm2.hostmacroid) AS hostmacroid
          FROM hostmacro hm2
          GROUP BY hm2.hostid,hm2.macro
          ) keep_rows ON
          hm1.hostmacroid=keep_rows.hostmacroid
          WHERE keep_rows.hostmacroid IS NULL
          );

          Similar for the problem with hosts_groups table.

          Show
          dimir added a comment - Successfully tested. Alexey, thank you for you suggestions! However, we have used a bit different approach. We delete the duplicate rows leaving the one with the lowest ID by executing something like this: DELETE FROM hostmacro WHERE hostmacroid IN ( SELECT hm1.hostmacroid FROM hostmacro hm1 LEFT OUTER JOIN ( SELECT MIN(hm2.hostmacroid) AS hostmacroid FROM hostmacro hm2 GROUP BY hm2.hostid,hm2.macro ) keep_rows ON hm1.hostmacroid=keep_rows.hostmacroid WHERE keep_rows.hostmacroid IS NULL ); Similar for the problem with hosts_groups table.
          Hide
          Alexander Vladishev added a comment -

          Fixed in pre-2.0.3 r29350 and pre-2.1.0 r29351.

          Show
          Alexander Vladishev added a comment - Fixed in pre-2.0.3 r29350 and pre-2.1.0 r29351.
          Hide
          Alexey Pustovalov added a comment - - edited

          (3) ERROR 1062 (23000) at line 1752: Duplicate entry '63377-63378' for key 'trigger_depends_1'
          Failed to patch Zabbix database. Restore from backup

          The problem can appear when the table contains rows with identical triggerid_down and triggerid_up pairs.

          <dimir> RESOLVED in r30344

          <Sasha> Successfully tested! TESTED

          dimir CLOSED

          Show
          Alexey Pustovalov added a comment - - edited (3) ERROR 1062 (23000) at line 1752: Duplicate entry '63377-63378' for key 'trigger_depends_1' Failed to patch Zabbix database. Restore from backup The problem can appear when the table contains rows with identical triggerid_down and triggerid_up pairs. <dimir> RESOLVED in r30344 <Sasha> Successfully tested! TESTED dimir CLOSED
          Hide
          Alexey Pustovalov added a comment - - edited

          Also

          DELETE FROM hostmacro
          WHERE hostmacroid IN (
          SELECT hm1.hostmacroid
          FROM hostmacro hm1
          LEFT OUTER JOIN (
          SELECT MIN(hm2.hostmacroid) AS hostmacroid
          FROM hostmacro hm2
          GROUP BY hm2.hostid,hm2.macro
          ) keep_rows ON
          hm1.hostmacroid=keep_rows.hostmacroid
          WHERE keep_rows.hostmacroid IS NULL
          );

          Will not work. Because MySQL does not support queries like this: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause

          mysql> DELETE FROM hostmacro
          -> WHERE hostmacroid IN (
          -> SELECT hm1.hostmacroid
          -> FROM hostmacro hm1
          -> LEFT OUTER JOIN (
          -> SELECT MIN(hm2.hostmacroid) AS hostmacroid
          -> FROM hostmacro hm2
          -> GROUP BY hm2.hostid,hm2.macro
          -> ) keep_rows ON
          -> hm1.hostmacroid=keep_rows.hostmacroid
          -> WHERE keep_rows.hostmacroid IS NULL
          -> );
          ERROR 1093 (HY000): You can't specify target table 'hostmacro' for update in FROM clause
          mysql>

          <Sasha> It happens on MySQL 5.5. MySQL 5.0.51 works without errors.

          <Sasha> RESOLVED r30430

          dimir Looks great, CLOSED

          Show
          Alexey Pustovalov added a comment - - edited Also DELETE FROM hostmacro WHERE hostmacroid IN ( SELECT hm1.hostmacroid FROM hostmacro hm1 LEFT OUTER JOIN ( SELECT MIN(hm2.hostmacroid) AS hostmacroid FROM hostmacro hm2 GROUP BY hm2.hostid,hm2.macro ) keep_rows ON hm1.hostmacroid=keep_rows.hostmacroid WHERE keep_rows.hostmacroid IS NULL ); Will not work. Because MySQL does not support queries like this: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause mysql> DELETE FROM hostmacro -> WHERE hostmacroid IN ( -> SELECT hm1.hostmacroid -> FROM hostmacro hm1 -> LEFT OUTER JOIN ( -> SELECT MIN(hm2.hostmacroid) AS hostmacroid -> FROM hostmacro hm2 -> GROUP BY hm2.hostid,hm2.macro -> ) keep_rows ON -> hm1.hostmacroid=keep_rows.hostmacroid -> WHERE keep_rows.hostmacroid IS NULL -> ); ERROR 1093 (HY000): You can't specify target table 'hostmacro' for update in FROM clause mysql> <Sasha> It happens on MySQL 5.5. MySQL 5.0.51 works without errors. <Sasha> RESOLVED r30430 dimir Looks great, CLOSED
          Hide
          dimir added a comment -

          I did not say we used this query for MySQL. Please check files:

          upgrades/dbpatches/2.0/mysql/patch/hostmacro.sql
          upgrades/dbpatches/2.0/postgresql/patch/hostmacro.sql
          ...

          Show
          dimir added a comment - I did not say we used this query for MySQL. Please check files: upgrades/dbpatches/2.0/mysql/patch/hostmacro.sql upgrades/dbpatches/2.0/postgresql/patch/hostmacro.sql ...
          Hide
          Alexey Pustovalov added a comment - - edited

          A new error:
          CREATE UNIQUE INDEX users_groups_1 ON users_groups (usrgrpid,userid);
          ERROR 1062 (23000) at Line 1914: Duplicate entry '8-2' for key 'users_groups_1'

          <Sasha> RESOLVED in r30754

          dimir Great, CLOSED

          Show
          Alexey Pustovalov added a comment - - edited A new error: CREATE UNIQUE INDEX users_groups_1 ON users_groups (usrgrpid,userid); ERROR 1062 (23000) at Line 1914: Duplicate entry '8-2' for key 'users_groups_1' <Sasha> RESOLVED in r30754 dimir Great, CLOSED
          Hide
          Alexey Pustovalov added a comment -

          More simple examples of queries:

          DELETE hm FROM hostmacro hm 
                        JOIN (SELECT MAX(hostmacroid) AS hostmacroid FROM hostmacro hm 
                                   GROUP BY hm.hostid, hm.macro HAVING COUNT(*) > 1) hm2 
                          USING(hostmacroid);
          
          DELETE hm1 FROM hostmacro as hm1,hostmacro as hm2 
                       WHERE hm1.hostid = hm2.hostid AND hm1.macro = hm2.macro AND hm1.hostmacroid>hm2.hostmacroid
          
          Show
          Alexey Pustovalov added a comment - More simple examples of queries: DELETE hm FROM hostmacro hm JOIN (SELECT MAX(hostmacroid) AS hostmacroid FROM hostmacro hm GROUP BY hm.hostid, hm.macro HAVING COUNT(*) > 1) hm2 USING(hostmacroid); DELETE hm1 FROM hostmacro as hm1,hostmacro as hm2 WHERE hm1.hostid = hm2.hostid AND hm1.macro = hm2.macro AND hm1.hostmacroid>hm2.hostmacroid
          Hide
          Alexey Pustovalov added a comment - - edited

          The new one:

          mysql> ALTER TABLE items ADD CONSTRAINT c_items_2 FOREIGN KEY (templateid) REFERENCES items (itemid) ON DELETE CASCADE;
          ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test_zbx`.<result 2 when explaining filename '#sql-ca8_77'>, CONSTRAINT `c_items_2` FOREIGN KEY (`templateid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE)
          

          We does not check that templateid which not null may does not equal any itemid. But we should non-remove corrupted templated items. we should mark it as host items.

          <Sasha> We already check item existence from a template. Alexey, please re-reproduce the issue.
          MySQL:

          CREATE TEMPORARY TABLE tmp_items_itemid (itemid bigint unsigned PRIMARY KEY);
          INSERT INTO tmp_items_itemid (itemid) (SELECT itemid FROM items);
          UPDATE items
                  SET templateid=NULL
                  WHERE templateid=0
                          OR templateid NOT IN (SELECT itemid FROM tmp_items_itemid);
          DROP TABLE tmp_items_itemid;
          

          IBM DB2, PostgreSQL, Oracle:

          UPDATE items
                  SET templateid=NULL
                  WHERE templateid=0
                          OR templateid NOT IN (SELECT itemid FROM items);
          

          <dotneft> Sorry. It is my mistake.

          <Sasha> CLOSED

          Show
          Alexey Pustovalov added a comment - - edited The new one: mysql> ALTER TABLE items ADD CONSTRAINT c_items_2 FOREIGN KEY (templateid) REFERENCES items (itemid) ON DELETE CASCADE; ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test_zbx`.<result 2 when explaining filename '#sql-ca8_77'>, CONSTRAINT `c_items_2` FOREIGN KEY (`templateid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE) We does not check that templateid which not null may does not equal any itemid. But we should non-remove corrupted templated items. we should mark it as host items. <Sasha> We already check item existence from a template. Alexey, please re-reproduce the issue. MySQL: CREATE TEMPORARY TABLE tmp_items_itemid (itemid bigint unsigned PRIMARY KEY); INSERT INTO tmp_items_itemid (itemid) (SELECT itemid FROM items); UPDATE items SET templateid=NULL WHERE templateid=0 OR templateid NOT IN (SELECT itemid FROM tmp_items_itemid); DROP TABLE tmp_items_itemid; IBM DB2, PostgreSQL, Oracle: UPDATE items SET templateid=NULL WHERE templateid=0 OR templateid NOT IN (SELECT itemid FROM items); <dotneft> Sorry. It is my mistake. <Sasha> CLOSED
          Hide
          dimir added a comment -

          Changes successfully tested.

          Show
          dimir added a comment - Changes successfully tested.
          Hide
          Alexander Vladishev added a comment -

          Fixed in version pre-2.0.4 r31167 and pre-2.1.0 (trunk) r31168.

          Show
          Alexander Vladishev added a comment - Fixed in version pre-2.0.4 r31167 and pre-2.1.0 (trunk) r31168.

            People

            • Assignee:
              Unassigned
              Reporter:
              Alexey Pustovalov
            • Votes:
              2 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: