From 114a3efb16c6ee9598bdb56307cc74670b38399e Mon Sep 17 00:00:00 2001
From: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
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 <m.de.rooy@rijksmuseum.nl>
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