From f74cae41603de924f5dac13296ccdd56d3fd1cf1 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Tue, 18 Dec 2018 13:52:15 +0000 Subject: [PATCH] Bug 21065: (QA follow-up) Make the update idempotent Signed-off-by: Martin Renvoize https://bugs.koha-community.org/show_bug.cgi?id=21065 Signed-off-by: Nick Clemens (cherry picked from commit 6d44f9dac8235dfe0259ea5b935c5a8908d2e441) Signed-off-by: Martin Renvoize (cherry picked from commit b4f55f8019fa89ce80509d7b05bec3082102a80e) Signed-off-by: Lucas Gass --- .../data/mysql/atomicupdate/bug_21065.perl | 22 +++++++++++-------- installer/data/mysql/kohastructure.sql | 2 +- 2 files changed, 14 insertions(+), 10 deletions(-) diff --git a/installer/data/mysql/atomicupdate/bug_21065.perl b/installer/data/mysql/atomicupdate/bug_21065.perl index 8c732da964..443877b08f 100644 --- a/installer/data/mysql/atomicupdate/bug_21065.perl +++ b/installer/data/mysql/atomicupdate/bug_21065.perl @@ -1,15 +1,19 @@ $DBversion = 'XXX'; # will be replaced by the RM if( CheckVersion( $DBversion ) ) { - $dbh->do(q| - ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_1; - |); - $dbh->do(q| - ALTER TABLE accountlines CHANGE COLUMN borrowernumber borrowernumber INT(11) DEFAULT NULL; - |); - $dbh->do(q| - ALTER TABLE accountlines ADD CONSTRAINT accountlines_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE; - |); + my $sth = $dbh->prepare(q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='accountlines_ibfk_1'|); + $sth->execute; + if ($sth->fetchrow_hashref) { + $dbh->do(q| + ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_1; + |); + $dbh->do(q| + ALTER TABLE accountlines CHANGE COLUMN borrowernumber borrowernumber INT(11) DEFAULT NULL; + |); + $dbh->do(q| + ALTER TABLE accountlines ADD CONSTRAINT accountlines_ibfk_borrowers FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE; + |); + } SetVersion( $DBversion ); print "Upgrade to $DBversion done (Bug 21065 - Set ON DELETE SET NULL on accountlines.borrowernumber)\n"; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 6cf40e86e3..36787406eb 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2716,7 +2716,7 @@ CREATE TABLE `accountlines` ( KEY `acctsborridx` (`borrowernumber`), KEY `timeidx` (`timestamp`), KEY `itemnumber` (`itemnumber`), - CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 2.39.5