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

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;

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

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.

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

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);

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?

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

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

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.

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

Great! Successfully tested!

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

New bugs in the upgrade script

(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

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

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.

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

(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

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

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
...

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

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

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

Changes successfully tested.

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