ZABBIX BUGS AND ISSUES

Incorrect query in upgrade script to 2.0

Details

  • Zabbix ID:
    RTD

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

Vote (2)
Watch (7)

Dates

  • Created:
    Updated:
    Resolved: