@@ -, +, @@ - Before applying the patch - Add a subscription and select a notice template for patron notification - Go to the OPAC and subscribe to the email alert - Log in as any other patron (not yourself) and subscribe with that patron too - Verfiy on the subscription detail page, that the patrons are subscribed - Note the borrowernumber of your patrons - Delete the second patron - Note borrowernumber and delete patron - Run: SELECT * from alert; in a report - Despite the patron being deleted, you will see entries for both - Apply patch - Run updatedatabase - verify no error message - Run updatedatabase again - verify still no error message - There should no longer be an entry with the deleted borrowernumber, but only the entry for your own patron account - Start fresh with the web installer, verify there are no errors on creating the database tables --- .../mysql/atomicupdate/bug_13535_alert_constraint.perl | 16 ++++++++++++++++ installer/data/mysql/kohastructure.sql | 3 ++- 2 files changed, 18 insertions(+), 1 deletion(-) create mode 100644 installer/data/mysql/atomicupdate/bug_13535_alert_constraint.perl --- a/installer/data/mysql/atomicupdate/bug_13535_alert_constraint.perl +++ a/installer/data/mysql/atomicupdate/bug_13535_alert_constraint.perl @@ -0,0 +1,16 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + unless ( foreign_key_exists( 'alert', 'alert_ibfk_1' ) ) { + $dbh->do(q| + DELETE a FROM alert a + LEFT JOIN borrowers b ON a.borrowernumber=b.borrowernumber + WHERE b.borrowernumber IS NULL + |); + $dbh->do( + qq{ + ALTER TABLE alert ADD CONSTRAINT alert_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON UPDATE CASCADE ON DELETE CASCADE + } + ); + } + NewVersion( $DBversion, 13535, "Add FK constraint on borrowernumber to alert table"); +} --- a/installer/data/mysql/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -2830,7 +2830,8 @@ CREATE TABLE `alert` ( `externalid` varchar(20) NOT NULL default '', PRIMARY KEY (`alertid`), KEY `borrowernumber` (`borrowernumber`), - KEY `type` (`type`,`externalid`) + KEY `type` (`type`,`externalid`), + CONSTRAINT alert_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --