From 4c263cb9cf89c16c6521da8d4f42cfa865dee99e Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Mon, 25 Apr 2022 10:29:05 +0000 Subject: [PATCH] Bug 27253: (follow-up) Fix UNIXTIME(0) in db_revs/211200037.pl MariaDB [koha_myclone]> update borrowers set updated_on=COALESCE( NULL, FROM_UNIXTIME(0) ) where borrowernumber=51; ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:00:00' for column `koha_myclone`.`borrowers`.`updated_on` at row 1 MariaDB [koha_myclone]> update borrowers set updated_on=COALESCE( NULL, FROM_UNIXTIME(1) ) where borrowernumber=51; Query OK, 1 row affected (0.008 sec) Rows matched: 1 Changed: 1 Warnings: 0 Signed-off-by: Marcel de Rooy Tested by switching updated_on to datetime. Remove NOT NULL, etc. Copied dbrev to atomicupdate folder. Resulted in: Updated all NULL values of borrowers.updated_on to GREATEST(date_renewed, dateenrolled, lastseen): 51 rows updated Signed-off-by: Fridolin Somers Signed-off-by: Kyle M Hall --- installer/data/mysql/db_revs/211105001.pl | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) diff --git a/installer/data/mysql/db_revs/211105001.pl b/installer/data/mysql/db_revs/211105001.pl index d5c7d7136d..0731c7cde1 100755 --- a/installer/data/mysql/db_revs/211105001.pl +++ b/installer/data/mysql/db_revs/211105001.pl @@ -10,9 +10,9 @@ return { my $rv = $dbh->do(q{ UPDATE borrowers SET updated_on = GREATEST( - COALESCE(date_renewed, FROM_UNIXTIME(0)), - COALESCE(dateenrolled, FROM_UNIXTIME(0)), - COALESCE(lastseen, FROM_UNIXTIME(0)) + COALESCE(date_renewed, FROM_UNIXTIME(1)), + COALESCE(dateenrolled, FROM_UNIXTIME(1)), + COALESCE(lastseen, FROM_UNIXTIME(1)) ) WHERE updated_on IS NULL }); @@ -21,9 +21,9 @@ return { $rv = $dbh->do(q{ UPDATE deletedborrowers SET updated_on = GREATEST( - COALESCE(date_renewed, FROM_UNIXTIME(0)), - COALESCE(dateenrolled, FROM_UNIXTIME(0)), - COALESCE(lastseen, FROM_UNIXTIME(0)) + COALESCE(date_renewed, FROM_UNIXTIME(1)), + COALESCE(dateenrolled, FROM_UNIXTIME(1)), + COALESCE(lastseen, FROM_UNIXTIME(1)) ) WHERE updated_on IS NULL }); -- 2.39.5