From 7a6ccf426f7084b244576fc3174be388a119e830 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 --- 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 4de16f8078..98929e3468 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{ @@ -23191,6 +23191,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