Bug 26853 - Data lost due to "Data too long for column" errors during MARC import
Summary: Data lost due to "Data too long for column" errors during MARC import
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: MARC Bibliographic record staging/import (show other bugs)
Version: master
Hardware: All All
: P5 - low critical (vote)
Assignee: David Cook
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-10-29 01:55 UTC by David Cook
Modified: 2020-11-17 23:16 UTC (History)
4 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Text to go in the release notes:
Version(s) released in:
20.11.00, 20.05.06


Attachments
Bug 26853: Throw a fatal error if import_biblios insert fails (929 bytes, patch)
2020-10-29 05:23 UTC, David Cook
Details | Diff | Splinter Review
Bug 26853: Harmonize import_biblios with biblio and biblioitems (3.17 KB, patch)
2020-10-29 05:24 UTC, David Cook
Details | Diff | Splinter Review
Bug 26853: Throw a fatal error if import_biblios insert fails (929 bytes, patch)
2020-10-29 05:24 UTC, David Cook
Details | Diff | Splinter Review
Record with long title (1.61 KB, text/plain)
2020-11-03 00:37 UTC, David Cook
Details
Bug 26853: Harmonize import_biblios with biblio and biblioitems (3.22 KB, patch)
2020-11-03 02:52 UTC, David Nind
Details | Diff | Splinter Review
Bug 26853: Throw a fatal error if import_biblios insert fails (977 bytes, patch)
2020-11-03 02:52 UTC, David Nind
Details | Diff | Splinter Review
Bug 26853: Harmonize import_biblios with biblio and biblioitems (3.26 KB, patch)
2020-11-03 07:59 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 26853: Throw a fatal error if import_biblios insert fails (1.02 KB, patch)
2020-11-03 07:59 UTC, Martin Renvoize
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description David Cook 2020-10-29 01:55:43 UTC
I'm noticing "Data too long for column 'title'" and "Data too long for column 'issn'" database errors in the logs.

These are preventing import_biblios rows from being added to the system, although import_records rows are still being added. 

On koha-testing-docker, these errors are being silently ignored, which is not good.
Comment 1 David Cook 2020-10-29 01:58:14 UTC
One option is to try to match the import_biblios columns with the biblio and biblioitems tables...

I notice import_biblios has an index on title, so I don't know if that will truncate the title for the index, or if it will just fail.

If people don't clean their import_biblios table, this could also be a massive database update later, which would not be fun...
Comment 2 David Cook 2020-10-29 02:01:15 UTC
Another option would be to truncate the data before trying to insert it into the database. 

But considering that things like Koha::MetaSearcher use those fields... maybe we should fix them.
Comment 3 David Cook 2020-10-29 05:23:56 UTC
Created attachment 112656 [details] [review]
Bug 26853: Throw a fatal error if import_biblios insert fails
Comment 4 David Cook 2020-10-29 05:24:16 UTC
Created attachment 112657 [details] [review]
Bug 26853: Harmonize import_biblios with biblio and biblioitems

This patch harmonizes the column datatypes of import_biblios
with biblio and biblioitems to prevent database errors which cause
staged MARC uploads to sometimes fail to update 100% correctly.

To test:
1. Go to http://localhost:8081/cgi-bin/koha/tools/stage-marc-import.pl
2. Upload a file with MARC records with titles longer than 128
characters long
3. Click "Stage for import"
4. Monitor /var/log/koha/kohadev/intranet-error.log for
database errors
5. Note that there are no database errors
Comment 5 David Cook 2020-10-29 05:24:19 UTC
Created attachment 112658 [details] [review]
Bug 26853: Throw a fatal error if import_biblios insert fails
Comment 6 David Nind 2020-11-02 19:27:18 UTC
Hi David.

Are you able to provide a sample file for import with records that will trigger this?

I tried by editing a record and making the title longer, then exporting; but it still worked.

Otherwise, I will have another go this evening.

David
Comment 7 David Cook 2020-11-02 22:03:45 UTC
(In reply to David Nind from comment #6)
> Hi David.
> 
> Are you able to provide a sample file for import with records that will
> trigger this?
> 
> I tried by editing a record and making the title longer, then exporting; but
> it still worked.
> 
> Otherwise, I will have another go this evening.
> 
> David

Were you testing the patch or trying to reproduce the error?

The problem is really difficult to spot, but I'll look at putting together a sample set of data.
Comment 8 David Cook 2020-11-03 00:37:14 UTC
Created attachment 112884 [details]
Record with long title
Comment 9 David Cook 2020-11-03 00:38:38 UTC
I've added a record with a long title. Without the patch, when I go to view the import batch (at /cgi-bin/koha/tools/manage-marc-import.pl?import_batch_id=1), I see that the "Citation" says "null".

If I go into the database, I can see 1 record in import_records, but there are 0 records in import_biblios.
Comment 10 David Nind 2020-11-03 02:52:47 UTC
Created attachment 112885 [details] [review]
Bug 26853: Harmonize import_biblios with biblio and biblioitems

This patch harmonizes the column datatypes of import_biblios
with biblio and biblioitems to prevent database errors which cause
staged MARC uploads to sometimes fail to update 100% correctly.

To test:
1. Go to http://localhost:8081/cgi-bin/koha/tools/stage-marc-import.pl
2. Upload a file with MARC records with titles longer than 128
characters long
3. Click "Stage for import"
4. Monitor /var/log/koha/kohadev/intranet-error.log for
database errors
5. Note that there are no database errors

Signed-off-by: David Nind <david@davidnind.com>
Comment 11 David Nind 2020-11-03 02:52:51 UTC
Created attachment 112886 [details] [review]
Bug 26853: Throw a fatal error if import_biblios insert fails

Signed-off-by: David Nind <david@davidnind.com>
Comment 12 David Nind 2020-11-03 03:00:25 UTC
Thanks David!

I had tried to recreate the error first, but I had no luck.

The record you provided really helped!

I hadn't spotted that a database was required as well.

Everything appears to work now for me so I have signed off.

I did notice another error in the database - it happened both before and after the patch was applied, but I don't think this is related to this bug:

[Tue Nov 03 02:57:55.761624 2020] [cgi:error] [pid 3980] [client 172.18.0.1:    58304] AH01215: [Tue Nov  3 02:57:55 2020] stage-marc-import.pl: Filehandle     STDOUT reopened as $fh only for input at /usr/share/perl5/DateTime/TimeZone/    Local/Unix.pm line 146.: /kohadevbox/koha/tools/stage-marc-import.pl, refere    r: http://127.0.0.1:8081/cgi-bin/koha/tools/stage-marc-import.pl


David
Comment 13 David Cook 2020-11-03 04:34:33 UTC
(In reply to David Nind from comment #12)
> 
> Everything appears to work now for me so I have signed off.
 
Thanks, David. Much appreciated! I think is a really nasty bug that is easy to miss, especially when RaiseError is turned off. When Bug 25026 is pushed, it will make issues like this more obvious. 

> I did notice another error in the database - it happened both before and
> after the patch was applied, but I don't think this is related to this bug:
> 
> [Tue Nov 03 02:57:55.761624 2020] [cgi:error] [pid 3980] [client 172.18.0.1:
> 58304] AH01215: [Tue Nov  3 02:57:55 2020] stage-marc-import.pl: Filehandle 
> STDOUT reopened as $fh only for input at /usr/share/perl5/DateTime/TimeZone/
> Local/Unix.pm line 146.: /kohadevbox/koha/tools/stage-marc-import.pl, refere
> r: http://127.0.0.1:8081/cgi-bin/koha/tools/stage-marc-import.pl
 
That's more of a warning than an error. It's not related to this bug, but I have been thinking of looking into it more.
Comment 14 Martin Renvoize 2020-11-03 07:59:07 UTC
Created attachment 112888 [details] [review]
Bug 26853: Harmonize import_biblios with biblio and biblioitems

This patch harmonizes the column datatypes of import_biblios
with biblio and biblioitems to prevent database errors which cause
staged MARC uploads to sometimes fail to update 100% correctly.

To test:
1. Go to http://localhost:8081/cgi-bin/koha/tools/stage-marc-import.pl
2. Upload a file with MARC records with titles longer than 128
characters long
3. Click "Stage for import"
4. Monitor /var/log/koha/kohadev/intranet-error.log for
database errors
5. Note that there are no database errors

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 15 Martin Renvoize 2020-11-03 07:59:11 UTC
Created attachment 112889 [details] [review]
Bug 26853: Throw a fatal error if import_biblios insert fails

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 16 Martin Renvoize 2020-11-03 07:59:53 UTC
It makes sense for these tables to be harmonized and I'm happy with the error being thrown on failures.

Passing QA
Comment 17 Jonathan Druart 2020-11-03 09:59:56 UTC
Pushed to master for 20.11, thanks to everybody involved!
Comment 18 Lucas Gass 2020-11-16 19:20:34 UTC
backported to 20.05.x for 20.05.06
Comment 19 Aleisha Amohia 2020-11-17 05:21:45 UTC
I can't reproduce the errors in the logs in 19.11.x. not backported
Comment 20 David Cook 2020-11-17 23:16:16 UTC
(In reply to Aleisha Amohia from comment #19)
> I can't reproduce the errors in the logs in 19.11.x. not backported

That's interesting since 19.11.something is where I discovered the bug.