[#ZBX-4693] mysql database upgrade scripts cause: Cannot add or update a child row: a foreign key constraint fails...

[ZBX-4693] mysql database upgrade scripts cause: Cannot add or update a child row: a foreign key constraint fails... Created: 2012 Feb 24 Updated: 2017 May 30 Resolved: 2012 Feb 25
Status:	Closed
Project:	ZABBIX BUGS AND ISSUES
Component/s:	Installation (I)
Affects Version/s:	1.9.9 (beta)
Fix Version/s:	2.0.0rc1

Type:

Incident report

Priority:

Major

Reporter:

Robert Jerzak

Assignee:

Unassigned

Resolution:

Fixed

Votes:

Labels:

None

Remaining Estimate:

Not Specified

Time Spent:

Not Specified

Original Estimate:

Not Specified

Attachments:

patch.sql

Description

I am not sure if this kind of issue fits bug tracker.

I am upgrading mysql database from zabbix 1.8.10 to 1.9.9 with script patch.sql. My mysql database is size about 50GB.

During an upgrade I get error:
ERROR 1452 (23000) at line 886: Cannot add or update a child row: a foreign key constraint fails (`zabbix`.<result 2 when explaining filename '#sql-2621_6'>, CONSTRAINT `c_hosts_2` FOREIGN KEY (`maintenanceid`) REFERENCES `maintenances` (`maintenanceid`))

My second try:
ERROR 1452 (23000) at line 886: Cannot add or update a child row: a foreign key constraint fails (`zabbix`.<result 2 when explaining filename '#sql-396b_2'>, CONSTRAINT `c_hosts_2` FOREIGN KEY (`maintenanceid`) REFERENCES `maintenances` (`maintenanceid`))

Temporary directory for mysql is about 65G free.

Any sugestion how can I workaround this issue and convert all database?

Comments

Comment by dimir [ 2012 Feb 24 ]

Could you attach the patch.sql that was used?

Comment by Robert Jerzak [ 2012 Feb 24 ]

This is script from zabbix-1.9.9.

Comment by dimir [ 2012 Feb 24 ]

Oh, right.

Comment by dimir [ 2012 Feb 24 ]

Please give me the output of these 2 sql queries:

select maintenanceid from hosts where maintenanceid is not null;
select maintenanceid from maintenances;

I think some host is referencing (is in maintenance status of) some non-existing maintenance.

Comment by Robert Jerzak [ 2012 Feb 24 ]

> select maintenanceid from maintenances;
---------------

maintenanceid

---------------

528
686
793
918
919
921
922
923
924

---------------
9 rows in set (0.00 sec)

> select maintenanceid from hosts where maintenanceid is not null;
---------------

maintenanceid

---------------

0
0
0
0
0
792
0
0
0
0
0
181
0
0
0
0
0
746
747
747
747
747
751
0
0
0
0
0
751
0
751
0
0
0
0
0
0
0
751
752
752
753
0
0
0
0
0
762
0
0
0
0
747
746
0
0
0
751
752
0
0
0

[…]

0
743
752
754
0
0
0
0
0
0
0
740
0
0
0

[…]

792

[…]

---------------
1688 rows in set (0.00 sec)

There is a lot of (~1600) rows with 'maintenanceid' = 0.

Comment by dimir [ 2012 Feb 25 ]

Please run next command before running an upgrade script:

UPDATE hosts
SET maintenanceid=NULL,
maintenance_status=0,
maintenance_type=0,
maintenance_from=0
WHERE maintenanceid=0
OR maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);

Comment by dimir [ 2012 Feb 25 ]

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

Comment by Robert Jerzak [ 2012 Feb 27 ]

It seems that your solution helped - script passes that section.

I don't know if script has completed without error because it is still running - for about 31h (50 GB database):

root

localhost

zabbix

Query

112165

Sending data

INSERT INTO tmp_events_eventid (eventid,prev_value)
(
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
)

But I dont think you can help me in this matter?

<dimir> Could be some DB lock issue. It shouldn't be running for that long. You mean it's stuck on that query?

Comment by Alexander Vladishev [ 2012 Feb 27 ]

(1) please review my changes in r25619.

<dimir> Perfect, tested with every DB! CLOSED

Comment by dimir [ 2012 Feb 27 ]

Fixed in pre-1.9.10 r25648.

During db upgrade invalid maintenance references are set to NULL in hosts table.

Comment by Robert Jerzak [ 2012 Feb 27 ]

> <dimir> Could be some DB lock issue. It shouldn't be running for that long. You mean it's stuck on that query?

We can say that it is stuck, this query is running for about 37h. One processor core is utilized in 100% by mysqld process with this query, IO utilization is 0 percent - looks like there is no disk involved in this operation.

Do you have any suggestion?

Comment by richlv [ 2012 Mar 02 ]

so... is the query still running ?

Comment by Robert Jerzak [ 2012 Mar 03 ]

I have killed the process after 57h of running. I found a way to speed up an upgrade - "truncate events" and upgrade lasted almost 4h. In the events table I have almost 46000000 records - it is very time consuming. Events are less important for me than history and trends.

Generated at Fri Apr 19 17:08:58 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.

[ZBX-4693] mysql database upgrade scripts cause: Cannot add or update a child row: a foreign key constraint fails... Created: 2012 Feb 24 Updated: 2017 May 30 Resolved: 2012 Feb 25