From 114a3efb16c6ee9598bdb56307cc74670b38399e 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 Content-Type: text/plain; charset=utf-8 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 --- installer/data/mysql/db_revs/211200037.pl | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) diff --git a/installer/data/mysql/db_revs/211200037.pl b/installer/data/mysql/db_revs/211200037.pl index d5c7d7136d..0731c7cde1 100755 --- a/installer/data/mysql/db_revs/211200037.pl +++ b/installer/data/mysql/db_revs/211200037.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.20.1