[Koha] Invalid value "0000-00-00 00:00:00" in field "borrowers.updated_on" with LDAP

[City & Guilds of London Art School Admin]

Hi Michael,

My far from optimal "workaround" (yeah... permanent workaround :)  ) for
this issue was to cron a shellscript to run every minute during library
hours which does this:

/usr/bin/mysql -u *db user* -D "*library database name*" -e "update
borrowers set updated_on = NULL where updated_on = '0000-00-00 00:00:00';"
&>> /home/*user*/quickfix.log;

Anything bookended by *'s is redacted. The log catches any errors or
messages.

I keep meaning to experiment with inserting a NULL value from the xml LDAP
config, but am yet to find a quiet moment for that. Also, not sure if it's
any better a solution, since much as it pains me to run a polling-type
script every minute, it fixes the issue when it's there but otherwise does
nothing with little overhead, whereas re-inserting a NULL value only has
the benefit of being a non-polling approach.

Anyway, that's one way to do it.

All the best,

Chris

On Mon, Nov 18, 2024 at 8:51 PM Michael Kuhn <mik at adminkuhn.ch> wrote:

> Hi
>
> In an installation of Koha 24.05.03 I am successfully authenticating
> against an LDAP server which is writing the data of the authenticating
> user into the Koha database. But as soon as I search for the added user
> in the staff client I get the message: "500: Internal Server Error.
> Month out of range." This is because table field "borrowers.updated_on"
> is automatically filled with the value "0000-00-00 00:00:00".
>
> I am aware of bug
> https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31143 (We
> should attempt to fix/identify all cases where '0000-00-00' may still
> remain in the database) but of course it is not possible to manually
> change the value of field "borrowers.updated_on" from "0000-00-00
> 00:00:00" to NULL every time a user successfully logs into the OPAC.
>
> So I tried to set the value of "borrowers.updated_on" by using lines
> like the following, but unfortunately they all resulted in the invalid
> value "0000-00-00 00:00:00".
>
>   <updated_on   is="">NULL</updated_on>
>   <updated_on   is="potato"></updated_on>
>   <updated_on   is="">2024-11-18 00:00:00</updated_on>
>
> How can I prevent Koha filling in this invalid value when authenticating
> against LDAP and instead generate a NULL value?
>
> Best wishes: Michael
> --
> Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis
> Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz
> T 0041 (0)61 261 55 61 · E mik at adminkuhn.ch · W www.adminkuhn.ch
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>