You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@vcl.apache.org by "Waldron, Michael H" <mw...@email.unc.edu> on 2012/10/10 22:34:49 UTC
MySQL error during 2.3 upgrade
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
RE: MySQL error during 2.3 upgrade
Posted by "Waldron, Michael H" <mw...@email.unc.edu>.
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: dev@vcl.apache.org
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: dchebota@gmu.edu [dchebota@gmu.edu]
Sent: Thursday, October 11, 2012 9:31 AM
To: dev@vcl.apache.org
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" <mw...@email.unc.edu> 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
RE: MySQL error during 2.3 upgrade
Posted by "Waldron, Michael H" <mw...@email.unc.edu>.
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: dev@vcl.apache.org
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: dchebota@gmu.edu [dchebota@gmu.edu]
Sent: Thursday, October 11, 2012 9:31 AM
To: dev@vcl.apache.org
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" <mw...@email.unc.edu> 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
RE: MySQL error during 2.3 upgrade
Posted by "Waldron, Michael H" <mw...@email.unc.edu>.
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: dchebota@gmu.edu [dchebota@gmu.edu]
Sent: Thursday, October 11, 2012 9:31 AM
To: dev@vcl.apache.org
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" <mw...@email.unc.edu> 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
Re: MySQL error during 2.3 upgrade
Posted by Dmitri Chebotarov <dc...@gmu.edu>.
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" <mw...@email.unc.edu> 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