From 1f819670fd8667e31c1c47435b469d25ae906c8a Mon Sep 17 00:00:00 2001 From: Benjamin Rokseth Date: Mon, 16 Apr 2018 11:06:19 +0200 Subject: [PATCH] Bug 20271: atomicupdate - support mysql < 5.7 and address foreign key issues Signed-off-by: Brendan A Gallagher Signed-off-by: Martin Renvoize --- ..._deleteitems_tables_with_their_alive_cousins.perl | 20 +++++++++++++++++--- 1 file changed, 17 insertions(+), 3 deletions(-) diff --git a/installer/data/mysql/atomicupdate/bug_20271_-_merge_deletedbiblio_and_deleteitems_tables_with_their_alive_cousins.perl b/installer/data/mysql/atomicupdate/bug_20271_-_merge_deletedbiblio_and_deleteitems_tables_with_their_alive_cousins.perl index 47a47cc7a8..4098ff65e6 100644 --- a/installer/data/mysql/atomicupdate/bug_20271_-_merge_deletedbiblio_and_deleteitems_tables_with_their_alive_cousins.perl +++ b/installer/data/mysql/atomicupdate/bug_20271_-_merge_deletedbiblio_and_deleteitems_tables_with_their_alive_cousins.perl @@ -9,9 +9,23 @@ if( CheckVersion( $DBversion ) ) { # Bug 17196 introduced a mismatch in foreign keys of deletedbiblio_metadata, so any key must be dropped DropAllForeignKeys('deletedbiblio_metadata'); $dbh->do( "INSERT IGNORE INTO biblio SELECT *, timestamp AS deleted_at FROM deletedbiblio" ) or warn $DBI::errstr; - $dbh->do( "INSERT IGNORE INTO biblioitems SELECT *, timestamp AS deleted_at FROM deletedbiblioitems" ) or warn $DBI::errstr; - $dbh->do( "INSERT IGNORE INTO biblio_metadata SELECT *, timestamp AS deleted_at FROM deletedbiblio_metadata" ) or warn $DBI::errstr; - $dbh->do( "INSERT IGNORE INTO items SELECT *, timestamp AS deleted_at FROM deleteditems" ) or warn $DBI::errstr; + # We also need to make sure foreign keys references are in place, as Mysql < 5.7 aborts on foreign key errors + $dbh->do( "INSERT IGNORE INTO biblioitems ( + SELECT *, timestamp AS deleted_at FROM deletedbiblioitems + WHERE biblionumber IN (SELECT biblionumber FROM biblio) + )" ) or warn $DBI::errstr; + # biblio_metadata needs special handling since there is an extra autoincrement id that cannot be moved + $dbh->do( "INSERT IGNORE INTO biblio_metadata (biblionumber, format, marcflavour, metadata, timestamp, deleted_at) ( + SELECT biblionumber, format, marcflavour, metadata, timestamp, timestamp AS deleted_at FROM deletedbiblio_metadata + WHERE biblionumber IN (SELECT biblionumber FROM biblio) + )" ) or warn $DBI::errstr; + $dbh->do( "INSERT IGNORE INTO items ( + SELECT *, timestamp AS deleted_at FROM deleteditems + WHERE biblioitemnumber IN (SELECT biblioitemnumber FROM biblioitems) + AND homebranch IN (SELECT homebranch FROM branches) + AND holdingbranch IN (SELECT holdingbranch FROM branches) + AND biblionumber IN (SELECT biblionumber FROM biblio) + )" ) or warn $DBI::errstr; # Check if any rows could not be moved, if so, rename table with underscore for checking, otherwise drop them { -- 2.14.2