Bug 4002 - Issues with borrowernumber set as NULL
Summary: Issues with borrowernumber set as NULL
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Circulation (show other bugs)
Version: 3.6
Hardware: All All
: PATCH-Sent (DO NOT USE) normal (vote)
Assignee: Srdjan Jankovic
QA Contact: Bugs List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-12-24 01:40 UTC by Nahuel Angelinetti
Modified: 2013-12-05 19:53 UTC (History)
4 users (show)

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


Attachments
patch (4.20 KB, patch)
2011-04-13 05:36 UTC, Srdjan Jankovic
Details | Diff | Splinter Review
patch (5.93 KB, patch)
2011-04-14 00:48 UTC, Srdjan Jankovic
Details | Diff | Splinter Review
Signed off Patch (6.13 KB, patch)
2011-05-16 07:30 UTC, Chris Cormack
Details | Diff | Splinter Review
patch (5.92 KB, patch)
2011-05-17 00:26 UTC, Srdjan Jankovic
Details | Diff | Splinter Review
bug_4002: issues referential integrity (5.98 KB, patch)
2011-06-14 20:45 UTC, Chris Cormack
Details | Diff | Splinter Review
proposed fix signed-off (6.46 KB, patch)
2011-08-03 09:24 UTC, Paul Poulain
Details | Diff | Splinter Review
proposed fix signed-off (6.46 KB, patch)
2011-08-03 09:30 UTC, Paul Poulain
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Chris Cormack 2010-05-21 01:22:00 UTC


---- Reported by nahuel.angelinetti@biblibre.com 2009-12-24 13:40:10 ----

If an issues have a borrowernumber set as NULL, all checkouts/checkins of the item will broke circulation for this item.
When a document is returned, all issues for itself should be moved to old_issues.



--- Bug imported by chris@bigballofwax.co.nz 2010-05-21 01:22 UTC  ---

This bug was previously known as _bug_ 4002 at http://bugs.koha.org/cgi-bin/bugzilla3/show_bug.cgi?id=4002

Actual time not defined. Setting to 0.0
Setting qa contact to the default for this product.
   This bug either had no qa contact or an invalid one.

Comment 1 Owen Leonard 2010-06-29 18:33:29 UTC
I see two patch submissions with this Bug number:

http://lists.koha-community.org/pipermail/koha-patches/2009-December/010764.html
http://lists.koha-community.org/pipermail/koha-patches/2009-December/010761.html

...and reference to one of these having been pushed to 3.0.x.
Comment 2 Srdjan Jankovic 2011-04-13 05:36:15 UTC Comment hidden (obsolete)
Comment 3 Srdjan Jankovic 2011-04-14 00:48:17 UTC Comment hidden (obsolete)
Comment 4 Chris Cormack 2011-05-16 07:30:52 UTC Comment hidden (obsolete)
Comment 5 Chris Cormack 2011-05-16 17:12:15 UTC
+    $dbh->do("INSERT INTO old_issues (borrowernumber, itemnumber, date_due,                                  
branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals,                                   
timestamp, issuedate)                                                                                         
+              SELECT borrowernumber, itemnumber, date_due, branchcode,                                       
issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp,                                    
issuedate FROM issues WHERE NOT EXISTS (SELECT * FROM borrowers WHERE                                         
itemnumber = issues.itemnumber)");                                                                            
+    $dbh->do("DELETE FROM issues WHERE NOT EXISTS (SELECT * FROM items WHERE                                 
itemnumber = issues.itemnumber)");
Comment 6 Srdjan Jankovic 2011-05-17 00:26:46 UTC Comment hidden (obsolete)
Comment 7 Chris Cormack 2011-05-17 00:29:06 UTC
Yep, totally caught me too, luckily Mike Hafen spotted it. Many eyes make bugs shallow and all that :)
Comment 8 Chris Cormack 2011-06-14 20:45:41 UTC Comment hidden (obsolete)
Comment 9 Paul Poulain 2011-08-03 09:23:58 UTC
The patch works fine for fresh install.

There is a problem with updates: the change to NOT NULL of some columns requires the contraints to be removed, otherwise, mySQL complains (and don't do anything)

The lines 
    $dbh->do("ALTER TABLE issues ALTER COLUMN borrowernumber DROP DEFAULT");
    $dbh->do("ALTER TABLE issues ALTER COLUMN itemnumber DROP DEFAULT");
    $dbh->do("ALTER TABLE issues MODIFY COLUMN borrowernumber int(11) NOT NULL");
    $dbh->do("ALTER TABLE issues MODIFY COLUMN itemnumber int(11) NOT NULL");
will issue a message (modify failed).

I've moved the DROP FOREIGN KEY just before the ALTER TABLE, and it works fine now.

Marking as passed QA, but chris, it's worth a complementary check...
Comment 10 Paul Poulain 2011-08-03 09:24:22 UTC Comment hidden (obsolete)
Comment 11 Paul Poulain 2011-08-03 09:30:11 UTC
Created attachment 4815 [details] [review]
proposed fix signed-off

oups, uploaded a wrong patch, sorry. This one is correct
    $dbh->do("ALTER TABLE issues DROP FOREIGN KEY `issues_ibfk_1`");
    $dbh->do("ALTER TABLE issues DROP FOREIGN KEY `issues_ibfk_2`");
now before :
    $dbh->do("ALTER TABLE issues ALTER COLUMN borrowernumber DROP DEFAULT");
    $dbh->do("ALTER TABLE issues ALTER COLUMN itemnumber DROP DEFAULT");
    $dbh->do("ALTER TABLE issues MODIFY COLUMN borrowernumber int(11) NOT NULL");
    $dbh->do("ALTER TABLE issues MODIFY COLUMN itemnumber int(11) NOT NULL");
Comment 12 Chris Cormack 2011-08-16 22:49:36 UTC
Pushed, please test
Comment 13 Jared Camins-Esakov 2012-05-23 12:10:56 UTC
This appears to be working fine. Closing.