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, We have encountered similar problems with Shibboleth. However, the community has helpfully written an upstream fix for preventing borrowers.updated_on being filled with 0000-00-00 when a new patron is created via LDAP or Shibboleth: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36822 So if you can upgrade from 24.05.03 to 24.05.06 (to get the 36822 fix) you might be able to stop future invalid borrowers.updated_on data being written to the Koha database. I hope this helps! Thanks, Alex On 19/11/24 9:50 am, Michael Kuhn via Koha-devel 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 -- *Alex Buckley (he/him)* Developer, Implementation Lead | Rōpū kohinga *Catalyst.Net Limited - Expert Open Source Solutions*
*Catalyst.Net Limited - a Catalyst IT group company* www.catalyst.net.nz <http://www.catalyst.net.nz> Follow Catalyst Koha on Twitter <https://twitter.com/catalystkoha> | Subscribe to the Catalyst Koha newsletter <https://catalyst.us4.list-manage.com/subscribe?u=62457ff5060d15ee3c07d3fc4&id=b73fbdcac8> Catalyst Logo CONFIDENTIALITY NOTICE: This email is intended for the named recipients only. It may contain privileged, confidential or copyright information. If you are not the named recipient, any use, reliance upon, disclosure or copying of this email or its attachments is unauthorised. If you have received this email in error, please reply via email or call +64 4 499 2267.
Hi Alex Many thanks for your message! You wrote:
We have encountered similar problems with Shibboleth. However, the community has helpfully written an upstream fix for preventing borrowers.updated_on being filled with 0000-00-00 when a new patron is created via LDAP or Shibboleth: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36822
So if you can upgrade from 24.05.03 to 24.05.06 (to get the 36822 fix) you might be able to stop future invalid borrowers.updated_on data being written to the Koha database.
I hope this helps!
Yes, it does! So we will wait for Koha revision 24.05.06 and then install it. Meanwhile we implemented the following workaround in the crontab of Linux user "root" which sets the value of field "borrowers.updated_on" to NULL every minute if the current value is "0000-00-00 00:00:00": * * * * * /usr/bin/mysql <databasename> -e "UPDATE borrowers SET updated_on = NULL WHERE updated_on = '0000-00-00 00:00:00';" &>> /tmp/bug36822-quickfix.log; 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 19/11/24 9:50 am, Michael Kuhn via Koha-devel 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 -- *Alex Buckley (he/him)* Developer, Implementation Lead | Rōpū kohinga *Catalyst.Net Limited - Expert Open Source Solutions*
*Catalyst.Net Limited - a Catalyst IT group company* www.catalyst.net.nz <http://www.catalyst.net.nz>
Follow Catalyst Koha on Twitter <https://twitter.com/catalystkoha> | Subscribe to the Catalyst Koha newsletter <https://catalyst.us4.list-manage.com/subscribe?u=62457ff5060d15ee3c07d3fc4&id=b73fbdcac8>
Catalyst Logo
CONFIDENTIALITY NOTICE: This email is intended for the named recipients only. It may contain privileged, confidential or copyright information. If you are not the named recipient, any use, reliance upon, disclosure or copying of this email or its attachments is unauthorised. If you have received this email in error, please reply via email or call +64 4 499 2267.
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : https://www.koha-community.org/ git : https://git.koha-community.org/ bugs : https://bugs.koha-community.org/
Great, thanks Michael! On 20/11/24 11:31 pm, Michael Kuhn via Koha-devel wrote:
Hi Alex
Many thanks for your message! You wrote:
We have encountered similar problems with Shibboleth. However, the community has helpfully written an upstream fix for preventing borrowers.updated_on being filled with 0000-00-00 when a new patron is created via LDAP or Shibboleth: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36822
So if you can upgrade from 24.05.03 to 24.05.06 (to get the 36822 fix) you might be able to stop future invalid borrowers.updated_on data being written to the Koha database.
I hope this helps!
Yes, it does! So we will wait for Koha revision 24.05.06 and then install it.
Meanwhile we implemented the following workaround in the crontab of Linux user "root" which sets the value of field "borrowers.updated_on" to NULL every minute if the current value is "0000-00-00 00:00:00":
* * * * * /usr/bin/mysql <databasename> -e "UPDATE borrowers SET updated_on = NULL WHERE updated_on = '0000-00-00 00:00:00';" &>> /tmp/bug36822-quickfix.log;
Best wishes: Michael -- *Alex Buckley (he/him)* Developer, Implementation Lead | Rōpū kohinga *Catalyst.Net Limited - Expert Open Source Solutions*
*Catalyst.Net Limited - a Catalyst IT group company* www.catalyst.net.nz <http://www.catalyst.net.nz> Follow Catalyst Koha on Twitter <https://twitter.com/catalystkoha> | Subscribe to the Catalyst Koha newsletter <https://catalyst.us4.list-manage.com/subscribe?u=62457ff5060d15ee3c07d3fc4&id=b73fbdcac8> Catalyst Logo CONFIDENTIALITY NOTICE: This email is intended for the named recipients only. It may contain privileged, confidential or copyright information. If you are not the named recipient, any use, reliance upon, disclosure or copying of this email or its attachments is unauthorised. If you have received this email in error, please reply via email or call +64 4 499 2267.
participants (2)
-
Alex Buckley -
Michael Kuhn