From 4735fa2bebc0831753e3431f18a1f54d91a47dd6 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 19 Feb 2021 10:52:45 +0100 Subject: [PATCH] Bug 26997: CAST AS CHAR to prevent the error To prevent the DBMS to fail on 0000-00-00 we can cast it as a char and compare. This patch also moves to a subroutine, in case we need to adjust (or reuse) it later. Signed-off-by: Martin Renvoize Signed-off-by: Jonathan Druart Bug 26997: (QA follow-up) Fix Typo Feel free to squash this ;) Signed-off-by: Martin Renvoize Signed-off-by: Jonathan Druart JD Amended patch - remove changes prior to 3.20 --- installer/data/mysql/updatedatabase.pl | 68 ++++++++++---------------- 1 file changed, 25 insertions(+), 43 deletions(-) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 11ca229949..452a2d1148 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -10635,14 +10635,14 @@ if ( CheckVersion($DBversion) ) { $DBversion = "3.21.00.009"; if ( CheckVersion($DBversion) ) { - eval { - local $dbh->{PrintError} = 0; - $dbh->do(q| - UPDATE aqorders SET orderstatus='cancelled' - WHERE (datecancellationprinted IS NOT NULL OR - datecancellationprinted<>'0000-00-00'); - |); - }; + + sanitize_zero_date('aqorders', 'datecancellationprinted'); + + $dbh->do(q| + UPDATE aqorders SET orderstatus='cancelled' + WHERE (datecancellationprinted IS NOT NULL) + |); + print "Upgrade to $DBversion done (Bug 13993: Correct orderstatus for transferred orders)\n"; SetVersion($DBversion); } @@ -10873,21 +10873,11 @@ if ( CheckVersion($DBversion) ) { $DBversion = "3.21.00.023"; if ( CheckVersion($DBversion) ) { - 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' - }); - }; + + sanitize_zero_date('borrowers', 'debarred'); + sanitize_zero_date('borrowers', 'dateexpiry'); + sanitize_zero_date('borrowers', 'dateofbirth'); + sanitize_zero_date('borrowers', 'dateenrolled'); print "Upgrade to $DBversion done (Bug 14717: Prevent 0000-00-00 dates in patron data)\n"; SetVersion($DBversion); @@ -21697,26 +21687,9 @@ $DBversion = '19.12.00.076'; if( CheckVersion( $DBversion ) ) { my @warnings; - 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 claimdate = NULL - WHERE claimdate = '0000-00-00' - |); - }; + sanitize_zero_date('serial', 'planneddate'); + sanitize_zero_date('serial', 'publisheddate'); + sanitize_zero_date('serial', 'claimdate'); $dbh->do(q| ALTER TABLE serial @@ -23680,4 +23653,13 @@ sub CheckVersion { } } +sub sanitize_zero_date { + my ( $table_name, $column_name ) = @_; + $dbh->do(qq| + UPDATE $table_name + SET $column_name = NULL + WHERE CAST($column_name AS CHAR(10)) = '0000-00-00'; + |); +} + exit; -- 2.20.1