From 5047073121369e0b4f6d30b448ec6ee6c1cde444 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". Signed-off-by: Martin Renvoize Signed-off-by: Jonathan Druart (cherry picked from commit 57a15eb474b5a8a6a199df1d324c30b7df0544d7) Signed-off-by: Fridolin Somers --- 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 5a3a4881fa..40d681016c 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -8305,7 +8305,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{ @@ -23173,6 +23173,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.39.5