View Issue Details

IDProjectCategoryView StatusLast Update
1321RackTablesdefaultpublic2015-08-09 22:12
Reporterflaminidavid Assigned Toinfrastation  
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
PlatformLinux 3.8.0-35-generic on x86_64OSUbuntu LinuxOS Version13.04
Product Version0.20.8 
Summary1321: ALTER TABLE PortInterfaceCompat ADD CONSTRAINT query failed during upgrade
DescriptionCurrent status Data version: 0.20.6
Code version: 0.20.8

Upgrade path 0.20.6 ? 0.20.7 ? 0.20.8

(...)

Executing batch '0.20.7' done

(...)


Executing batch '0.20.8' The following queries failed:
ALTER TABLE PortInterfaceCompat ADD CONSTRAINT `PortInterfaceCompat-FK-oif_id` FOREIGN KEY (oif_id) REFERENCES PortOuterInterface (id) -- Cannot add or update a child row: a foreign key constraint fails (`racktables`.`#sql-432_312a`, CONSTRAINT `PortInterfaceCompat-FK-oif_id` FOREIGN KEY (`oif_id`) REFERENCES `PortOuterInterface` (`id`))

Executing batch 'dictionary' done


Summary Upgrade complete, it is Ok to enter the system.
Steps To ReproduceOccurs when upgrading data from 0.20.6 to 0.20.8, I followed the standard procedure: Install the code, drop 0.20.8 database, load 0.20.6 database, run web upgrade.
TagsNo tags attached.
Attached Files
OBJECT LOG.csv (141 bytes)

Relationships

related to 1463 closedadoom42 Upgrade from 0.19.11 to 0.20.10 fails "Executing batch '0.20.8'" 

Activities

flaminidavid

flaminidavid

2014-08-25 21:22

reporter   ~0002471

Data Integrity Reports:

Missing Foreign Keys (1)

Table Key
PortInterfaceCompat PortInterfaceCompat-FK-oif_id
infrastation

infrastation

2014-09-08 04:55

administrator   ~0002479

Do the following two queries fix the problem?

DELETE FROM PortInterfaceCompat WHERE oif_id NOT IN (SELECT id FROM PortOuterInterface);
ALTER TABLE PortInterfaceCompat ADD CONSTRAINT `PortInterfaceCompat-FK-oif_id` FOREIGN KEY (oif_id) REFERENCES PortOuterInterface (id);
flaminidavid

flaminidavid

2014-09-08 17:24

reporter   ~0002483

Hi, no, it doesn't, first query fails with:

 DELETE FROM PortInterfaceCompat WHERE oif_id NOT IN (SELECT id FROM PortOuterInterface);

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`racktables`.`Port`, CONSTRAINT `Port-FK-iif-oif` FOREIGN KEY (`iif_id`, `type`) REFERENCES `PortInterfaceCompat` (`iif_id`, `oif_id`))
infrastation

infrastation

2014-09-08 23:18

administrator   ~0002487

SELECT * FROM Port WHERE type NOT IN (SELECT id FROM PortOuterInterface);
flaminidavid

flaminidavid

2014-09-09 00:07

reporter   ~0002489

Attached
infrastation

infrastation

2014-09-09 09:30

administrator   ~0002491

INSERT INTO PortOuterInterface (id, oif_name) VALUES (3000, 'SONET');
ALTER TABLE PortInterfaceCompat ADD CONSTRAINT `PortInterfaceCompat-FK-oif_id` FOREIGN KEY (oif_id) REFERENCES PortOuterInterface (id);
infrastation

infrastation

2014-09-11 21:53

administrator   ~0002497

Have this resolved the issue?
flaminidavid

flaminidavid

2014-09-17 18:21

reporter   ~0002509

Sorry for the delay. I've been working against the clock lately.

Yes, it solved the issue!

Thanks a lot.
infrastation

infrastation

2014-09-19 20:18

administrator   ~0002511

Fine. So far this seems to be a rare edge case hence I don't intend to retrofit the fix into the upgrade script, which normally doesn't try to change the behaviour of a past release. Closing. Thanks for reporting this!

Issue History

Date Modified Username Field Change
2014-08-25 21:19 flaminidavid New Issue
2014-08-25 21:22 flaminidavid Note Added: 0002471
2014-08-25 22:46 adoom42 Assigned To => infrastation
2014-08-25 22:46 adoom42 Status new => assigned
2014-09-08 04:55 infrastation Note Added: 0002479
2014-09-08 17:24 flaminidavid Note Added: 0002483
2014-09-08 23:18 infrastation Note Added: 0002487
2014-09-09 00:06 flaminidavid Tag Attached: C:\Users\David\Desktop\racktables\OBJECT LOG.csv
2014-09-09 00:06 flaminidavid Tag Detached: C:\Users\David\Desktop\racktables\OBJECT LOG.csv
2014-09-09 00:06 flaminidavid File Added: OBJECT LOG.csv
2014-09-09 00:07 flaminidavid Note Added: 0002489
2014-09-09 09:30 infrastation Note Added: 0002491
2014-09-11 21:53 infrastation Note Added: 0002497
2014-09-11 21:53 infrastation Status assigned => feedback
2014-09-17 18:21 flaminidavid Note Added: 0002509
2014-09-17 18:21 flaminidavid Status feedback => assigned
2014-09-19 20:18 infrastation Note Added: 0002511
2014-09-19 20:18 infrastation Status assigned => closed
2014-09-19 20:18 infrastation Resolution open => fixed
2015-08-09 22:12 adoom42 Relationship added related to 1463