[Bug 5379] New: import_borrowers.pl fails with db insert/update errors
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 Summary: import_borrowers.pl fails with db insert/update errors Change sponsored?: --- Product: Koha Version: HEAD Platform: All OS/Version: All Status: NEW Severity: critical Priority: P5 Component: Tools AssignedTo: gmcharlt@gmail.com ReportedBy: cnighswonger@foundations.edu QAContact: koha-bugs@lists.koha-community.org Estimated Hours: 0.0 I'm not sure what is going on here exactly; the gonenoaddress, lost, debarred, guarantorid, and flags db fields default to NULL, but for some reason when the insert/update occurs, DBD returns an error like "Incorrect integer value: '' for column 'foo'..." It appears save to default these to zero for now. This patch also adds more logging of errors and a notice to the user to check the logs for errors when they occur. -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 Chris Nighswonger <cnighswonger@foundations.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |ASSIGNED Change sponsored?|--- |Sponsored AssignedTo|gmcharlt@gmail.com |cnighswonger@foundations.ed | |u -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 Chris Nighswonger <cnighswonger@foundations.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- Priority|P5 |PATCH-Sent --- Comment #1 from Chris Nighswonger <cnighswonger@foundations.edu> 2010-11-09 15:38:42 UTC --- Patch can be had here: http://git.koha-community.org/gitweb/?p=wip/koha-fbc.git;a=shortlog;h=refs/h... -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 Galen Charlton <gmcharlt@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |gmcharlt@gmail.com --- Comment #2 from Galen Charlton <gmcharlt@gmail.com> 2010-11-12 12:45:10 UTC --- Regarding gonenoaddress, lost, etc., not being set to a default value of zero, I have not been able to reproduce that problem. Are you testing with MariaDB by any chance? -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 --- Comment #3 from Chris Nighswonger <cnighswonger@foundations.edu> 2010-11-12 13:20:26 UTC --- (In reply to comment #2)
Regarding gonenoaddress, lost, etc., not being set to a default value of zero, I have not been able to reproduce that problem. Are you testing with MariaDB by any chance?
You are right. I will break out that part of this patch and submit it under bug 5366. I am also attaching a copy of the CSV causing errors to this bug to help ensure proper replication of the problem. -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 --- Comment #4 from Chris Nighswonger <cnighswonger@foundations.edu> 2010-11-12 13:21:13 UTC --- Created attachment 2742 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=2742 Example of a CSV which causes db errors -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 --- Comment #5 from Chris Nighswonger <cnighswonger@foundations.edu> 2010-11-12 13:32:32 UTC --- (In reply to comment #0)
I'm not sure what is going on here exactly; the gonenoaddress, lost, debarred, guarantorid, and flags db fields default to NULL, but for some reason when the insert/update occurs, DBD returns an error like "Incorrect integer value: '' for column 'foo'..." It appears save to default these to zero for now.
This patch also adds more logging of errors and a notice to the user to check the logs for errors when they occur.
DB errors are also thrown for extended patron attributes when entered in the form: "ATTRIB1:value1", "ATTRIB2:value2", etc. This is due to a problem introduced by smart quotes. -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 Chris Cormack <chris@bigballofwax.co.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |chris@bigballofwax.co.nz --- Comment #6 from Chris Cormack <chris@bigballofwax.co.nz> 2010-11-14 20:44:20 UTC --- Patches pushed to new/awaiting_qa/bug_5379, which is a merge onto master of the k_bug_5379 branch of Chris Nighswongers repository. Will continue to push any patches to pull requests to this branch. -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |katrin.fischer@bsz-bw.de --- Comment #7 from Katrin Fischer <katrin.fischer@bsz-bw.de> 2010-11-15 09:01:52 UTC --- Not sure I tested the right thing, but here are my results ;) I tested on current HEAD and on the new branch new/awaiting_qa/bug_5379 with different test cases for 2 patron attributes. The file I used was loaded successfully before in 3.01.61. I used a unique patron attribute as matching key during import, overwriting matching records, replacing all patron attributes. "BORROWER#:222,SECOND_ID:222" Overwriting to existing patrons with attributes works without problems. --- "BORROWER#:223,SECOND_ID:222" For testing I changed one of the matching keys, so that one patron would be added as new to the database. I got following error message for this patron: 1 not imported because they are not in the expected format (last...) I think the error is due to the already existing cardnumber in my database. After I changed the cardnubmer too, the patron loaded without problems. A better error message would be good. --- "BORROWER#:222","SECOND_ID:224" I changed my patron attributes in an attempt to follow the help instructions. This resulted in deletion of my second patron attribute from the overwritten patron record. No error message was displayed, although the number of fields was not matching my header row. The help seems not correct here or is misleading? --- "BORROWER#:222,'SECOND_ID:224, test'" Deleted both patron attributes from the existing patron. --- "'BORROWER#:222','SECOND_ID:224, test'" 1 not imported because they are not in the expected format .. What is the right syntax to import more than 1 patron attribute with patron attributes containing commas? -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 --- Comment #8 from Chris Nighswonger <cnighswonger@foundations.edu> 2010-11-15 13:15:10 UTC --- (In reply to comment #7)
Not sure I tested the right thing, but here are my results ;)
<snip>
What is the right syntax to import more than 1 patron attribute with patron attributes containing commas?
Take a look at the csv attached to this bug. The record in it has two patron attributes, the first of which contains a comma in the date format. I agree that the help on this screen is confusing. I'll follow up with a patch to hopefully help clarify it later this week. -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 --- Comment #9 from Chris Nighswonger <cnighswonger@foundations.edu> 2010-11-15 13:19:37 UTC --- (In reply to comment #7)
Not sure I tested the right thing, but here are my results ;)
<snip>
"BORROWER#:222","SECOND_ID:224" I changed my patron attributes in an attempt to follow the help instructions.
This resulted in deletion of my second patron attribute from the overwritten patron record. No error message was displayed, although the number of fields was not matching my header row.
The help seems not correct here or is misleading?
What where the "Patron Attributes" settings (near the bottom of the import tool screen) set to? -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 --- Comment #10 from Katrin Fischer <katrin.fischer@bsz-bw.de> 2010-11-15 13:27:17 UTC --- I used these settings for all of my tests: - overwrite matching records - replace all patron attributes -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 --- Comment #11 from Katrin Fischer <katrin.fischer@bsz-bw.de> 2010-11-15 13:58:56 UTC --- Created attachment 2755 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=2755 csv file with example record File attached will have following results: "BORROWER#:222","SECOND_ID:224" - patron already exists: overwrite and replace all will delete second_id from the records - patron is new: only the first patron_attribute will be created Help file should be changed. I wondered why the program didn't complain about the difference between header and provided fields. -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 --- Comment #12 from Katrin Fischer <katrin.fischer@bsz-bw.de> 2010-11-15 14:18:59 UTC --- Ok, I have to correct my former report: the patron attribute is not deleted but written into the next field of the header row. More tests with small changes to the provided csv file: "'BORROWER#:222','SECOND_ID:224'" 1 not imported because they are not in the expected format (last was Fischer / AddMember) '"BORROWER#:222","SECOND_ID:224"' 1 not imported because they are not in the expected format And one last change, to match the format in chris_n's example file: "“STARTDATE:January 1, 2010”,”TRACK:Day”" changed to: "“BORROWER#:222”,”SECOND_ID:222, 444”" And that works. -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 --- Comment #13 from Chris Nighswonger <cnighswonger@foundations.edu> 2010-11-16 14:49:29 UTC --- Created attachment 2760 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=2760 Corrected CSV -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 --- Comment #14 from Chris Nighswonger <cnighswonger@foundations.edu> 2010-11-16 14:55:46 UTC --- The problem is most certainly due to poorly worded help. According to RFC-4180: If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote. For example: "aaa","b""bb","ccc" We are essentially embedding a CSV inside of a single field of another CSV, so we must properly escape our double quotes. This means in a plain text editor our patron attribute field should follow this form: """ATTRIB_1:VALUE_1"",""ATTRIB_2:VALUE_2"",""ATTRIB_3:VALUE_3"",... ""ATTRIB_N:VALUE_N""" In a spreadsheet editor this would be: ""ATTRIB_1:VALUE_1"",""ATTRIB_2:VALUE_2"",""ATTRIB_3:VALUE_3"",... ""ATTRIB_N:VALUE_N"" My testing shows that this syntax works. NOTE: Excel *does not* double quote fields afaict, so it may be necessary to follow the first example when using Excel. This needs to be tested. I will follow up with a patch to clarify the help in this tool. -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 --- Comment #15 from Chris Nighswonger <cnighswonger@foundations.edu> 2010-11-16 15:08:26 UTC --- Adding a link to RFC-4180 http://www.rfc-editor.org/rfc/rfc4180.txt -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 --- Comment #16 from Katrin Fischer <katrin.fischer@bsz-bw.de> 2010-11-22 12:19:47 UTC --- I tested the uploaded csv file. New import of record and update of existing record worked correctly. It's also possible to import patron attributes containing commas. Example: """BORROWER#:222"",""SECOND_ID:224, test""" The help file and the hints on the page should be very clear about the right syntax, so people know exactly how the file has to look like. -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 --- Comment #17 from MJ Ray (software.coop) <mjr@ttllp.co.uk> 2010-12-09 15:10:44 UTC --- This bug is mentioned in: [Bug 5379] Pull Request http://lists.koha-community.org/pipermail/koha-patches/2010-November/012867.... [Bug 5379] Pull Request http://lists.koha-community.org/pipermail/koha-patches/2010-November/012871.... [Bug 5379] Pull Request http://lists.koha-community.org/pipermail/koha-patches/2010-November/012917.... [Bug 5379] Pull Request http://lists.koha-community.org/pipermail/koha-patches/2010-November/012919.... [Bug 5379] Pull Request http://lists.koha-community.org/pipermail/koha-patches/2010-November/012921.... [Bug 5379] Pull Request http://lists.koha-community.org/pipermail/koha-patches/2010-November/012922.... [Bug 5379] Pull Request http://lists.koha-community.org/pipermail/koha-patches/2010-November/012924.... [Bug 5379] Pull Request http://lists.koha-community.org/pipermail/koha-patches/2010-November/012926.... -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 Chris Cormack <chris@bigballofwax.co.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch Status|--- |Needs Signoff -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 --- Comment #18 from Liz Rea <wizzyrea@gmail.com> 2011-06-15 16:55:06 UTC --- Created attachment 4486 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=4486 [SIGNED OFF] Bug 5379 - import_borrowers.pl fails with db insert/update errors Some spreadsheet programs use smart quotes which causes the db to throw an error when an insert/update is attempted due to improper processing of the CSV file. This patch adds code to check for smart quotes and change them to "dumb" quotes. This patch also adds more logging of errors and a notice to the user to check the logs for errors when they occur. -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA Contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 Liz Rea <wizzyrea@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |wizzyrea@gmail.com Patch Status|Needs Signoff |Signed Off -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA Contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 Chris Cormack <chris@bigballofwax.co.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch Status|Signed Off |Patch Pushed --- Comment #19 from Chris Cormack <chris@bigballofwax.co.nz> 2011-07-04 23:16:20 UTC --- Works as designed, pushing -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA Contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5379 Chris Nighswonger <cnighswonger@foundations.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |RESOLVED Resolution| |FIXED -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA Contact for the bug.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org