Greetings friends, there may be some bugs with anonymize circulation history when it is run while items are checked out. I find that items appear on overdues reports after they have been returned. Items cannot be deleted because Koha thinks they are checked out. Items also continue to show up on overdue reports even when they have been reissued and have a new due-date. What can we do so that our data is accurate? Some screen shots are at the following link: http://home.earthlink.net/~rhollis/Koha/buggyanon.pdf The steps taken to get there are at: http://home.earthlink.net/~rhollis/Koha/anonymize.pdf We had about 700 items checked out when I ran the anonymize circulation report with a poorly selected date. We will be checking out about another 700 items in the next few days; it seems any of the items that had been checked out and are reissued will be affected. I'd be happy to revert to a backup but we've not been very good about backing up the data. My geek will be here on campus Thursday and I'll be happy to pass on anything that will help us in this situation. Other than confirming that I'd buggered it up, she didn't have any ideas immediately. We're running Koha 3.0 on Debian. Rachel Hollis, librarian Stevens-Henager College, Boise Idaho Campus ________________________________ This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.
Greetings friends, my geek (who has graduated and is available less) checked out the situation last week and found that circulation history tied to patrons is completely gone; the table column show item numbers and the patron column is empty. The damage occurred when I ran anonymize circulation history (4/7) while items were checked out. Fortunately the only items affected were those currently checked out. Perhaps "damage" is too strong a word: 1. Affected items appear on overdue reports without patron data. 2. When I click on "download file of all overdues," the CSV file contains no data. 3. Affected checkouts and due-dates post-anonymize circulation history display with patrons but not with items. 4. Affected items checked-in are "not checked out" - they cannot be returned so are forever overdue? is this because they are separated from patron data? 5. Affected items appear on overdue reports even after they have been returned or reissued. 6. Affected items cannot be deleted because they are checked out. We are considering: a) Update each affected item's status to Withdrawn, add second item - affected items will remain on overdue lists. b) "Migrate" to an earlier version of Koha (anonymous is new in Koha 3?) c) "Migrate" our biblios minus affected items to another Koha 3.0. d) A combination of the above: make Withdrawn, export biblios/items, remove Withdrawn, reload My geek says that I tend to have a hammer to the fly approach and I sincerely hope/believe there's a better way to approach this situation. Is there a better way to correct this situation? Because pictures communicate more, there are screen shots are at the following link: http://home.earthlink.net/~rhollis/Koha/anonymize.pdf Rachel Hollis, librarian Stevens-Henager College, Boise Idaho Campus ________________________________ This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.
Hi Rachel, 2009/5/7 Rachel Hollis <Rachel.Hollis@stevenshenager.edu>:
4. Affected items checked-in are “not checked out” - they cannot be returned so are forever overdue? is this because they are separated from patron data?
Yes. There is a way to get rid of them, which will require use of SQL. With this sort of SQL update, it a good idea (nay, required!) to do a complete backup beforehand, perform the update outside of normal library hours, check the results right away, and be prepared to restore from backup. The process would looks like this: 1. Do your backup. 2. Find out exactly how many loans are now ghosts: SELECT COUNT(*) FROM issues WHERE borrowernumber IS NULL; 2. Copy those loans that were incorrectly anonymized to the issue history table using the following SQL: INSERT INTO old_issues SELECT * FROM issues WHERE borrowernumber IS NULL; 3. Actually delete those ghost loans: DELETE FROM issues WHERE borrowernumber IS NULL; 4. Clear the item record's copy of the due date: UPDATE items SET onloan = null WHERE itemnumber NOT IN (SELECT itemnumber FROM issues); After you do this, those items will no longer be on loan. Because of how Koha stores item information, and specifically the due date, you may see a couple spots, probably in search results, where it appears that the item still has a due date, but that won't prevent the item from circulating. This procedure will *most likely* allow these items to be circulated again, although without a backup it is impossible to restore the original borrowernumbers. Although I try not to overburden messages with disclaimers, one definitely applies here: although I am happy to answer further questions, actually running the update you would do at your own risk - please be sure that your are certain that you can backup and restore your database before trying this. A fix for the underlying bug (null borrower numbers in the issues table) is being worked on. Regards, Galen -- Galen Charlton VP, Research & Development, LibLime galen.charlton@liblime.com p: 1-888-564-2457 x709 skype: gmcharlt
Would it be less dangerous to use the SQL to local the records but instead of insert into old.issues replace the NULL value with a patron record that you created for this purpose - I'd suggest a patron type with no fies :-)? That way if you had the items in hand you could check them in, staff could look for the items on the shelves to check in any that are already shelved and you'd then know what items where still checked out. The normal check in would correct any that were returned by patrons later. something like: mysql> update issues set borrowernumber = (your dummy patrons id) where borrowernumber is NULL; Mark (at LibLime) used this to fix records for us that had NULL borrowers. Susan Bennett ILS System Administrator Geauga County Public Library 440 286-6811 x 125 440 286-7419 FAX On Thu, May 7, 2009 at 4:28 PM, Galen Charlton <galen.charlton@liblime.com>wrote:
Hi Rachel,
2009/5/7 Rachel Hollis <Rachel.Hollis@stevenshenager.edu>:
4. Affected items checked-in are “not checked out” - they cannot be returned so are forever overdue? is this because they are separated from patron data?
Yes. There is a way to get rid of them, which will require use of SQL. With this sort of SQL update, it a good idea (nay, required!) to do a complete backup beforehand, perform the update outside of normal library hours, check the results right away, and be prepared to restore from backup.
The process would looks like this:
1. Do your backup.
2. Find out exactly how many loans are now ghosts:
SELECT COUNT(*) FROM issues WHERE borrowernumber IS NULL;
2. Copy those loans that were incorrectly anonymized to the issue history table using the following SQL:
INSERT INTO old_issues SELECT * FROM issues WHERE borrowernumber IS NULL;
3. Actually delete those ghost loans:
DELETE FROM issues WHERE borrowernumber IS NULL;
4. Clear the item record's copy of the due date:
UPDATE items SET onloan = null WHERE itemnumber NOT IN (SELECT itemnumber FROM issues);
After you do this, those items will no longer be on loan. Because of how Koha stores item information, and specifically the due date, you may see a couple spots, probably in search results, where it appears that the item still has a due date, but that won't prevent the item from circulating.
This procedure will *most likely* allow these items to be circulated again, although without a backup it is impossible to restore the original borrowernumbers. Although I try not to overburden messages with disclaimers, one definitely applies here: although I am happy to answer further questions, actually running the update you would do at your own risk - please be sure that your are certain that you can backup and restore your database before trying this.
A fix for the underlying bug (null borrower numbers in the issues table) is being worked on.
Regards,
Galen -- Galen Charlton VP, Research & Development, LibLime galen.charlton@liblime.com p: 1-888-564-2457 x709 skype: gmcharlt _______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Hi Susan, On Thu, May 7, 2009 at 4:53 PM, Susan Bennett <susan.bennett@geaugalibrary.info> wrote:
Would it be less dangerous to use the SQL to local the records but instead of insert into old.issues replace the NULL value with a patron record that you created for this purpose - I'd suggest a patron type with no fies :-)? That way if you had the items in hand you could check them in, staff could look for the items on the shelves to check in any that are already shelved and you'd then know what items where still checked out. The normal check in would correct any that were returned by patrons later.
That's an excellent suggestion. Regards, Galen -- Galen Charlton VP, Research & Development, LibLime galen.charlton@liblime.com p: 1-888-564-2457 x709 skype: gmcharlt
You guys are fantastic! Fortunately we have a test database that is a near mirror (fubar records and all) of our production database. If we can fix the test database, we’ll be golden! Oh sure, some data will be irretrievably lost but that is a minor inconvenience compared to our current situation. As I thought about this throughout the day, I realized that not only will our items be able to circulate normally again, we’ll be able to de-accession (delete) the items that are on permanent ;-) loan. Question: how would items that have been reissued be affected? Those items are on the overdues list, appear overdue in the item record attached to the biblio but on the patron record display the actual due date. I’m not too worried about my library but the other library has a larger circulation and many of those fubar items have been reissued. Perhaps we run the SQL again after those items are returned (they are all due at the same time) in two weeks? Rachel Hollis, librarian Stevens-Henager College, Boise Idaho Campus This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.
Greetings colleagues, we recently created a test database for our other campus' library staff to trial. Unfortunately, the data that was loaded into the test database was the same messed up data. Fortunately, after making a backup, my geek was able to run both SQL solutions, one on each database. I could then view the results and pick the outcome that I liked best. I wish I had thought to get screenshots to better show the differences with the two SQLs as applied to our situation. If we had not reissued so many items with anonymous checkout information (NULL values), the other shorter SQL would have been better. For our particular situation, Galen's SQL had better results. We reissued about 150 items with anonymous checkout information (NULL values) and Galen's SQL linked reissued items to their current borrowers. The other SQL resulted in every item with a NULL to be issued to a fictitious borrower; this affected overdue items and well as items that had been returned or reissued. Galen's SQL resulted in overdue items with NULL values showing as available in the catalog, those items are no longer on overdue lists and doing an inventory will be beneficial. Informal evidence indicates that there are a small number of items on "permanent loan" and while these overdue items appear as available, they can be updated or removed now. While some data is lost, it is a minor inconvenience compared to what we had. An interesting discovery after running the SQL is that searches resulting in more than one record where there is a formerly NULL item, the item displays as checked out but the detail view shows the item as available. Screen shots are at: http://home.earthlink.net/~rhollis/Koha/afterSQL.pdf Rachel Hollis, librarian Stevens-Henager College, Boise Idaho Campus ________________________________ This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.
participants (3)
-
Galen Charlton -
Rachel Hollis -
Susan Bennett