I finally tracked down the source of this error and thought I would post it in case anyone else runs into it. It occured while running the update-vcl.sql script for updating a VCL 2.2.1 installation to 2.3.
These 2 statements at the end of the section for removing references to the legacy vmware.pm module are causing the error. If I comment them out, the script runs without error. DELETE FROM provisioning WHERE provisioning.moduleid IN (SELECT module.id FROM module WHERE module.perlpackage = 'VCL::Module::Provisioning::vmware'); DELETE FROM module WHERE module.perlpackage = 'VCL::Module::Provisioning::vmware'; The error they generate is: ERROR 1451 (23000) at line 484: Cannot delete or update a parent row: a foreign key constraint fails (`vcl/provisioningOSinstalltype`, CONSTRAINT `provisioningOSinstalltype_ibfk_1` FOREIGN KEY (`provisioningid`) REFERENCES `provisioning` (`id`) ON UPDATE CASCADE) I changed these deletes to selects to see what they were trying to delete. It looks like it wants to delete the "vmware_server_gsx" entry from the provisioning table and the "provisioning_vmware_1x" entry from the module table. I guess it was balking because there's an entry in the provisioningOSinstalltype table for the provisioningid of the "vmware_server_gsx" entry. When I deleted that entry from provisioningOSinstalltype, those 2 delete statements in the update-vcl.sql script worked. Mike Mike Waldron Systems Specialist ITS - Research Computing Center University of North Carolina at Chapel Hill ________________________________________ From: Waldron, Michael H Sent: Thursday, October 11, 2012 3:47 PM To: [email protected] Subject: RE: MySQL error during 2.3 upgrade Dmitri, Thanks for the insight. I'm not aware of any custom mods made to our database. My main issue is trying to track down exactly which statement is causing the error. I thought it might be the following statements, given they are acting on the referenced tables: CALL AddConstraintIfNotExists('provisioningOSinstalltype', 'provisioningid', 'provisioning', 'id', 'both', 'CASCADE'); CALL AddConstraintIfNotExists('provisioningOSinstalltype', 'OSinstalltypeid', 'OSinstalltype', 'id', 'both', 'CASCADE'); However, if I run a copy of the update script running just these statements, there's no error, yet if I run the entire script I get the error. So I guess I haven't found the right statements yet. Mike Mike Waldron Systems Specialist ITS - Research Computing Center University of North Carolina at Chapel Hill ________________________________________ From: [email protected] [[email protected]] Sent: Thursday, October 11, 2012 9:31 AM To: [email protected] Subject: Re: MySQL error during 2.3 upgrade Mike I've had similar error when updated to VCL 2.3. A custom database upgrade (specific to our environment) in 2.2.1 caused this error. In my case if was custom 'name' value in vcl.affiliation table. Update went OK once I edited update-vcl.sql script to match my production database values. I hope it helps. Thanks. On Oct 10, 2012, at 16:34 , "Waldron, Michael H" <[email protected]> wrote: > I am going through the upgrade procedure on my VCL test system from 2.2.1 to > 2.3. > > When I run the mysql update script, I get the following error: > # mysql vcl < mysql/update-vcl.sql > > ERROR 1451 (23000) at line 484: Cannot delete or update a parent row: a > foreign key constraint fails (`vcl/provisioningOSinstalltype`, CONSTRAINT > `provisioningOSinstalltype_ibfk_1` FOREIGN KEY (`provisioningid`) REFERENCES > `provisioning` (`id`) ON UPDATE CASCADE) > > Any ideas what might be wrong here? > > Thanks, > Mike > > Mike Waldron > Systems Specialist > ITS - Research Computing Center > University of North Carolina at Chapel Hill > -- Thank you, Dmitri Chebotarov Virtual Computing Lab Systems Engineer, TSD - Ent Servers & Messaging 223 Aquia Building, Ffx, MSN: 1B5 Phone: (703) 993-6175 Fax: (703) 993-3404
