Bugzilla – Attachment 117428 Details for
Bug 26997
Database Mysql Version 8.0.22 failed to Update During Upgrade
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 26997: CAST AS CHAR to prevent the error
Bug-26997-CAST-AS-CHAR-to-prevent-the-error.patch (text/plain), 3.86 KB, created by
Jonathan Druart
on 2021-03-01 09:33:32 UTC
(
hide
)
Description:
Bug 26997: CAST AS CHAR to prevent the error
Filename:
MIME Type:
Creator:
Jonathan Druart
Created:
2021-03-01 09:33:32 UTC
Size:
3.86 KB
patch
obsolete
>From 4735fa2bebc0831753e3431f18a1f54d91a47dd6 Mon Sep 17 00:00:00 2001 >From: Jonathan Druart <jonathan.druart@bugs.koha-community.org> >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 <martin.renvoize@ptfs-europe.com> > >Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> > >Bug 26997: (QA follow-up) Fix Typo > >Feel free to squash this ;) > >Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> > >Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> > >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
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 26997
:
113495
|
113600
|
114526
|
114527
|
117038
|
117178
|
117179
|
117180
|
117181
| 117428