From da58f3e2c541bb617ba3ec5ef58d5cf1792caeb7 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 11 Nov 2020 14:22:50 +0100 Subject: [PATCH] Bug 26997: Fix upgrade process under MySQL 8 If you are coming from an old version of Koha, you may face an upgrade error for the SQL query using 0000-00-00. The idea was to fix it, but actually the DBMS will raise an error (not blocking the upgrade process however). We can hide them to not cause confusion Errors are like: DBD::mysql::db do failed: Incorrect date value: '0000-00-00' for column 'suggesteddate' at row 1 [for Statement "UPDATE suggestions SET suggesteddate = '1970-01-01' WHERE suggesteddate = '0000-00-00';"] at /kohadevbox/koha/installer/data/mysql/updatedatabase.pl line 23312. Test plan: Checkout an old version of Koha (3.20, not older) Insert a dump, install Koha Checkout a recent version of Koha, apply the patch (or use master) Execute the upgrade process You should not see the errors Signed-off-by: Martin Renvoize Signed-off-by: Jonathan Druart (cherry picked from commit 3c087bc216eeab8fe0d68a2d33407b554bc57074) Signed-off-by: Fridolin Somers --- installer/data/mysql/updatedatabase.pl | 104 +++++++++++++------------ 1 file changed, 54 insertions(+), 50 deletions(-) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index d8c96a2438..4aa5a04caa 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -10635,11 +10635,14 @@ if ( CheckVersion($DBversion) ) { $DBversion = "3.21.00.009"; if ( CheckVersion($DBversion) ) { - $dbh->do(q| - UPDATE aqorders SET orderstatus='cancelled' - WHERE (datecancellationprinted IS NOT NULL OR - datecancellationprinted<>'0000-00-00'); - |); + eval { + local $dbh->{PrintError} = 0; + $dbh->do(q| + UPDATE aqorders SET orderstatus='cancelled' + WHERE (datecancellationprinted IS NOT NULL OR + datecancellationprinted<>'0000-00-00'); + |); + }; print "Upgrade to $DBversion done (Bug 13993: Correct orderstatus for transferred orders)\n"; SetVersion($DBversion); } @@ -10870,18 +10873,22 @@ if ( CheckVersion($DBversion) ) { $DBversion = "3.21.00.023"; if ( CheckVersion($DBversion) ) { - $dbh->do(q{ - UPDATE borrowers SET debarred=NULL WHERE debarred='0000-00-00' - }); - $dbh->do(q{ - UPDATE borrowers SET dateexpiry=NULL where dateexpiry='0000-00-00' - }); - $dbh->do(q{ - UPDATE borrowers SET dateofbirth=NULL where dateofbirth='0000-00-00' - }); - $dbh->do(q{ - UPDATE borrowers SET dateenrolled=NULL where dateenrolled='0000-00-00' - }); + eval { + local $dbh->{PrintError} = 0; + $dbh->do(q{ + UPDATE borrowers SET debarred=NULL WHERE debarred='0000-00-00' + }); + $dbh->do(q{ + UPDATE borrowers SET dateexpiry=NULL where dateexpiry='0000-00-00' + }); + $dbh->do(q{ + UPDATE borrowers SET dateofbirth=NULL where dateofbirth='0000-00-00' + }); + $dbh->do(q{ + UPDATE borrowers SET dateenrolled=NULL where dateenrolled='0000-00-00' + }); + }; + print "Upgrade to $DBversion done (Bug 14717: Prevent 0000-00-00 dates in patron data)\n"; SetVersion($DBversion); } @@ -17629,15 +17636,18 @@ $DBversion = '18.12.00.024'; if ( CheckVersion($DBversion) ) { # Fixup any pre-existing bad suggestedby, manageddate, accepteddate dates - $dbh->do( - "UPDATE suggestions SET suggesteddate = '1970-01-01' WHERE suggesteddate = '0000-00-00';" - ); - $dbh->do( - "UPDATE suggestions SET manageddate = '1970-01-01' WHERE manageddate = '0000-00-00';" - ); - $dbh->do( - "UPDATE suggestions SET accepteddate = '1970-01-01' WHERE accepteddate = '0000-00-00';" - ); + eval { + local $dbh->{PrintError} = 0; + $dbh->do( + "UPDATE suggestions SET suggesteddate = '1970-01-01' WHERE suggesteddate = '0000-00-00';" + ); + $dbh->do( + "UPDATE suggestions SET manageddate = '1970-01-01' WHERE manageddate = '0000-00-00';" + ); + $dbh->do( + "UPDATE suggestions SET accepteddate = '1970-01-01' WHERE accepteddate = '0000-00-00';" + ); + }; # Add constraint for suggestedby unless ( foreign_key_exists( 'suggestions', 'suggestions_ibfk_suggestedby' ) ) @@ -21687,32 +21697,26 @@ $DBversion = '19.12.00.076'; if( CheckVersion( $DBversion ) ) { my @warnings; - $dbh->do(q| - UPDATE - serial - SET - planneddate = NULL - WHERE - planneddate = '0000-00-00' - |); + eval { + local $dbh->{PrintError} = 0; + $dbh->do(q| + UPDATE serial + SET planneddate = NULL + WHERE planneddate = '0000-00-00' + |); - $dbh->do(q| - UPDATE - serial - SET - publisheddate = NULL - WHERE - publisheddate = '0000-00-00' - |); + $dbh->do(q| + UPDATE serial + SET publisheddate = NULL + WHERE publisheddate = '0000-00-00' + |); - $dbh->do(q| - UPDATE - serial - SET - claimdate = NULL - WHERE - claimdate = '0000-00-00' - |); + $dbh->do(q| + UPDATE serial + SET claimdate = NULL + WHERE claimdate = '0000-00-00' + |); + }; $dbh->do(q| ALTER TABLE serial -- 2.39.5