Re: [Koha-devel] [Koha] Invalid value "0000-00-00 00:00:00" in field "borrowers.updated_on" with LDAP
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>
Just wanted to mention that we got this message, but it was not the borrower's date. For some reason, an item's date acquired field had bad data, and the holdings table wouldn't load, giving the "500: Internal Server Error. Month out of range." message. Elaine VWML <https://www.efdss.org/vwml> On Wed, Nov 20, 2024 at 2:38 AM Michael Kuhn <mik@adminkuhn.ch> wrote:
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>
_______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
participants (2)
-
Elaine Bradtke -
Michael Kuhn