Bug 15246 - Adding new patron triggers foreign key error if branchcode is hidden
Summary: Adding new patron triggers foreign key error if branchcode is hidden
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Patrons (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-11-24 00:55 UTC by Laura
Modified: 2023-12-28 20:42 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Laura 2015-11-24 00:55:45 UTC
I volunteer at a small library that wants to make available it's rare books. Another volunteer has taken the time to enter our collection. I am tasked with adding our patrons, but unable to do so.

For context, our koha installation predates me, so I don't know the history of it.

Steps to reproduce:

1. Go to patrons, choose "New Patron", and select "Guest/Casual User"
2. Fill out all required fields in the patron form
3. Submit the form

Then I end up on: `/members/moremember.pl?borrowernumber=0`
And see this message: "This patron does not exist."

Just to double check, when I go back to review the patrons list for the patron I attempted to create, I can't find them.

Steps I have taken: 

1. updated to the (latest) package in Precise (Ubuntu 12.04) "last" repository (Koha V 3.18.12).

I inherited this installation, so I can't say if this function ever worked for our library. 

Here is a dump of the relevant log entries from `intranet-error.log`

```
[Mon Nov 23 16:38:17 2015] [error] [client 192.168.0.5] [Mon Nov 23 16:38:17 2015] memberentry.pl: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`koha_library`.`borrowers`, CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)) at /usr/share/koha/lib/C4/SQLHelper.pm line 184., referer: http://192.168.0.19:8080/cgi-bin/koha/members/memberentry.pl?op=add&categorycode=DAY
[Mon Nov 23 16:38:17 2015] [error] [client 192.168.0.5] [Mon Nov 23 16:38:17 2015] memberentry.pl: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`koha_library`.`borrower_message_preferences`, CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UP) at /usr/share/koha/lib/C4/Members/Messaging.pm line 169., referer: http://192.168.0.19:8080/cgi-bin/koha/members/memberentry.pl?op=add&categorycode=DAY
[Mon Nov 23 16:38:17 2015] [error] [client 192.168.0.5] [Mon Nov 23 16:38:17 2015] memberentry.pl: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`koha_library`.`borrower_message_preferences`, CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UP) at /usr/share/koha/lib/C4/Members/Messaging.pm line 169., referer: http://192.168.0.19:8080/cgi-bin/koha/members/memberentry.pl?op=add&categorycode=DAY
[Mon Nov 23 16:38:17 2015] [error] [client 192.168.0.5] [Mon Nov 23 16:38:17 2015] memberentry.pl: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`koha_library`.`borrower_message_preferences`, CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UP) at /usr/share/koha/lib/C4/Members/Messaging.pm line 169., referer: http://192.168.0.19:8080/cgi-bin/koha/members/memberentry.pl?op=add&categorycode=DAY
[Mon Nov 23 16:38:17 2015] [error] [client 192.168.0.5] [Mon Nov 23 16:38:17 2015] memberentry.pl: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`koha_library`.`borrower_message_transport_preferences`, CONSTRAINT `borrower_message_transport_preferences_ibfk_1` FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `borrower_message_) at /usr/share/koha/lib/C4/Members/Messaging.pm line 185., referer: http://192.168.0.19:8080/cgi-bin/koha/members/memberentry.pl?op=add&categorycode=DAY
[Mon Nov 23 16:38:17 2015] [error] [client 192.168.0.5] [Mon Nov 23 16:38:17 2015] memberentry.pl: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`koha_library`.`borrower_message_preferences`, CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UP) at /usr/share/koha/lib/C4/Members/Messaging.pm line 169., referer: http://192.168.0.19:8080/cgi-bin/koha/members/memberentry.pl?op=add&categorycode=DAY
[Mon Nov 23 16:38:17 2015] [error] [client 192.168.0.5] [Mon Nov 23 16:38:17 2015] memberentry.pl: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`koha_library`.`borrower_message_preferences`, CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UP) at /usr/share/koha/lib/C4/Members/Messaging.pm line 169., referer: http://192.168.0.19:8080/cgi-bin/koha/members/memberentry.pl?op=add&categorycode=DAY
```
Comment 1 Laura 2015-11-24 01:52:43 UTC
I was able to get past this by changing my Home > Administration > System Preferences > Patrons.

I noticed that the "BorrowerUnwantedField was set to "title|dateofbirth|branchcode"

This was causing the field not to render (even as a hidden field). By removing branchcode from BorrowerUnwantedField, the branchcode field now renders, and chooses as it's only option our library branch code (our koha instance only has one libary).

As for solutions, instead of getting the database error and having to chase through mysql logs, I would have ideally been met with a more understandable "Branch code is required" or better yet, the branch code, being a required field, should be rendered as a hidden input or computed server side.
Comment 2 Owen Leonard 2016-06-21 14:09:51 UTC
(In reply to Laura from comment #1)

> the branch code, being a required
> field, should be rendered as a hidden input or computed server side.

Agreed. I think that if branchcode has been hidden from the form, the logged-in branch should automatically be selected for new patrons.
Comment 3 Michael Kuhn 2017-05-09 17:00:06 UTC
Under the described circumstances (Koha system preference "BorrowerUnwantedField" containing "branchcode") almost the same problem occurrs when trying to add new patrons.

In this case:

1. Environment: Debian GNU/Linux 8 with Koha 16.11.04 and Plack activated.

2. Trying to add a new patron using the staff client will result in the URL ".../cgi-bin/koha/members/memberentry.pl" showing just a white page saying "Internal Server error".

3. The log file "plack-error.log" will show the following error (just as an example):

DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`koha_phsh`.`borrowers`, CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)) [for Statement "INSERT INTO `borrowers` ( `address`, `address2`, `altcontactaddress1`, `altcontactaddress2`, `altcontactaddress3`, `altcontactcountry`, `altcontactfirstname`, `altcontactphone`, `altcontactstate`, `altcontactsurname`, `altcontactzipcode`, `borrowernotes`, `cardnumber`, `categorycode`, `city`, `contactnote`, `country`, `dateenrolled`, `dateexpiry`, `dateofbirth`, `debarred`, `email`, `emailpro`, `firstname`, `mobile`, `opacnote`, `othernames`, `password`, `phone`, `phonepro`, `privacy`, `privacy_guarantor_checkouts`, `sex`, `sms_provider_id`, `sort1`, `sort2`, `state`, `streetnumber`, `surname`, `title`, `userid`, `zipcode`) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" with ParamValues: 0="", 1="", 2="", 3="", 4="", 5="", 6="", 7="", 8="", 9="", 10="", 11="", 12="11111111111", 13="13", 14="", 15="", 16="", 17='2017-05-09', 18='2100-08-09T00:00:00', 19=undef, 20=undef, 21="", 22="", 23="", 24="", 25="", 26="", 27='!', 28="", 29="", 30=0, 31=0, 32="", 33=undef, 34="", 35="", 36="", 37="", 38="XXXX", 39="", 40='xxxx', 41=""] at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1832.
DBIx::Class::Storage::DBI::_dbh_execute(): Cannot add or update a child row: a foreign key constraint fails (`koha_phsh`.`borrowers`, CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)) at /usr/share/koha/lib/Koha/Object.pm line 120

4. The problem can be solved by removing "branchcode" from Koha system preference "BorrowerUnwantedField".

5. As Laura has suggested it may be a good idea to add a hint to the preference "BorrowerUnwantedField" concerning field "branchcode" - so most people wouldn't even try to include it. Or, as Owen Leonard has suggested, to automatically select the logged-in branch for new patrons; even if the field "branchcode" is hidden; although this may not always be the correct choice, if the library has more than one branch. So maybe it would be safest to just forbid adding the mandatory field "branchcode" to Koha system preference "BorrowerUnwantedField".
Comment 4 Katrin Fischer 2023-02-26 12:31:33 UTC
In current versions the branchcode can no longer be hidden using BorrowerUnwantedField.