From 2ba498a53b9bd348542e8cb8d1ab68ef2f849e0e Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 27 Jan 2021 09:43:02 +0100 Subject: [PATCH] Bug 27534: Add missing ON UPDATE/DELETE clause to message_transport_type_fk Bug 9016 (3.15.00.039) added message_transport_type_fk but the ON DELETE CASCADE ON UPDATE CASCADE clause was missing from the updatedatabase entry. If we are upgrading from an old version, 20.06.00.058 will fail on updating the message_transport_type, with "Cannot delete or update a parent row: a foreign key constraint fails". --- installer/data/mysql/updatedatabase.pl | 12 +++++++++++- 1 file changed, 11 insertions(+), 1 deletion(-) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 8ef697a7a4..10d985aacd 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -8300,7 +8300,7 @@ if ( CheckVersion($DBversion) ) { } ); $dbh->do( q{ - ALTER TABLE letter ADD CONSTRAINT message_transport_type_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types(message_transport_type); + ALTER TABLE letter ADD CONSTRAINT message_transport_type_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types(message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE } ); $dbh->do( q{ @@ -23189,6 +23189,16 @@ if( CheckVersion( $DBversion ) ) { $DBversion = '20.06.00.058'; if( CheckVersion( $DBversion ) ) { + + # Adding the ON DELETE CASCASE ON UPDATE CASCADE, in case it's missing (from 9016 - 3.15.00.039) + $dbh->do( q{ + ALTER TABLE letter DROP FOREIGN KEY message_transport_type_fk + } ); + + $dbh->do( q{ + ALTER TABLE letter ADD CONSTRAINT message_transport_type_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types(message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE + } ); + $dbh->do(q{ UPDATE message_transport_types SET message_transport_type = "itiva" WHERE message_transport_type = "phone" }); -- 2.20.1