Invalid value "0000-00-00 00:00:00" in field "borrowers.updated_on" with LDAP
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@adminkuhn.ch · W www.adminkuhn.ch
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@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@adminkuhn.ch · W www.adminkuhn.ch _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Hi Chris Many thanks for your message! You wrote:
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.
I now just implemeted the command in the crontab. It's about the same idea I alread use on my Koha demo installation to prevent some wise guys to change the password of the supreuser so they have the whole installation for themselves - there I reset the password every minute to the original one every minute. As Alex Buckley pointed out in mailinglist "koha-devel" the error is probably fixed with https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36822 in Koha 24.05.06. 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@adminkuhn.ch · W www.adminkuhn.ch
On Mon, Nov 18, 2024 at 8:51 PM Michael Kuhn <mik@adminkuhn.ch <mailto:mik@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 <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@adminkuhn.ch <mailto:mik@adminkuhn.ch> · W www.adminkuhn.ch <http://www.adminkuhn.ch> _______________________________________________
Koha mailing list http://koha-community.org <http://koha-community.org> Koha@lists.katipo.co.nz <mailto:Koha@lists.katipo.co.nz> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha <https://lists.katipo.co.nz/mailman/listinfo/koha>
participants (2)
-
City & Guilds of London Art School Admin -
Michael Kuhn