Bug 28328 - Editing a record can cause an ISE if data too long for column
Summary: Editing a record can cause an ISE if data too long for column
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Cataloging (show other bugs)
Version: Main
Hardware: All All
: P5 - low major (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
: 30896 (view as bug list)
Depends on:
Blocks:
 
Reported: 2021-05-12 11:46 UTC by Nick Clemens
Modified: 2023-12-28 20:47 UTC (History)
6 users (show)

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


Attachments
Bug 28328: Extend biblioitems.lccn to longtext (2.99 KB, patch)
2023-05-03 13:42 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 28328: DBIC schema changes (2.68 KB, patch)
2023-05-03 13:42 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 28328: Adjust tests (3.04 KB, patch)
2023-05-03 13:42 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 28328: Extend biblioitems.lccn to longtext (3.04 KB, patch)
2023-05-03 13:55 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 28328: DBIC schema changes (2.73 KB, patch)
2023-05-03 13:55 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 28328: Adjust tests (3.09 KB, patch)
2023-05-03 13:55 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 28328: Extend biblioitems.lccn to longtext (3.10 KB, patch)
2023-05-10 16:10 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 28328: DBIC schema changes (2.79 KB, patch)
2023-05-10 16:10 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 28328: Adjust tests (3.16 KB, patch)
2023-05-10 16:10 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 Nick Clemens 2021-05-12 11:46:26 UTC
Like bug 26518, but for ModBiblio

To recreate:
1 - Edit a record in the staff client
2 - In MARC21 edit the 010a/LCCN field wih:
ThisisgoingtobetoomanycharactersfortheLCCNfield
3 - Save the record
4 - ISE:
C4::Biblio::_koha_modify_biblioitem_nonmarc(): DBI Exception: DBD::mysql::st execute failed: Data too long for column 'lccn' at row 1 [for Statement "UPDATE biblioitems 
    SET biblionumber    = ?,
        volume          = ?,
        number          = ?,
        itemtype        = ?,
        isbn            = ?,
        issn            = ?,
        publicationyear = ?,
        publishercode   = ?,
        volumedate      = ?,
        volumedesc      = ?,
        collectiontitle = ?,
        collectionissn  = ?,
        collectionvolume= ?,
        editionstatement= ?,
        editionresponsibility = ?,
        illus           = ?,
        pages           = ?,
        notes           = ?,
        size            = ?,
        place           = ?,
        lccn            = ?,
        url             = ?,
        cn_source       = ?,
        cn_class        = ?,
        cn_item         = ?,
        cn_suffix       = ?,
        cn_sort         = ?,
        totalissues     = ?,
        ean             = ?,
        agerestriction  = ?
        where biblioitemnumber = ?
        " with ParamValues: 0="4239", 1=undef, 2=undef, 3="BK", 4="0887080189", 5=undef, 6=undef, 7="Neugebauer Press USA ; | Distributed by Alphabet Press,", 8=undef, 9=undef, 10=undef, 11=undef, 12=undef, 13=undef, 14=undef, 15="col. ill. ;", 16="[30] p. :", 17=undef, 18="30 cm.", 19="[S.l.] : | Natick, MA :", 20="83181009sdhgaethq45y245y24thsadhgahq", 21=undef, 22="ddc", 23=undef, 24=undef, 25=undef, 26='', 27=undef, 28=undef, 29=undef, 30='4239'] at /kohadevbox/koha/C4/Biblio.pm line 384
 at /usr/share/perl5/DBIx/Class/Exception.pm line 77

This also kills the linker script if a bib with too long lccn is encountered
Comment 1 Katrin Fischer 2021-06-03 23:13:29 UTC
What would be the best solution here? 

I think we could limit the max size of 010a via the frameworks. 

But if the LCCN really can be longer than we give them space for now, we should probably change the data type of the column?
Comment 2 Philip Orr 2023-03-29 10:29:41 UTC
I got the same error in the OPAC when adding a purchase suggestion from an already existing title.
To reproduce:
1 - Open a record in the OPAC that has too many characters in the ISBN field, e.g. "Ruby cookbook"
2 - Click on "Suggest for purchase"
3 - Click on "Submit your suggestion"
4 - ISE: DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Data too long for column 'isbn' at row 1 at /kohadevbox/koha/Koha/Object.pm line 170
 at /usr/share/perl5/DBIx/Class/Exception.pm line 77
Comment 3 Jan Kissig 2023-04-12 08:41:05 UTC
The same problem for columns publishercode or place obtained from MARC 264$b and $a or 260$a and $b


to reproduce 
1 - edit or add new biblio
2 - in 264$a enter f.e. "1averylongtextaverylongtextaverylongtext42averylongtextaverylongtextaverylongtext84 | 2averylongtextaverylongtextaverylongtext42averylongtextaverylongtextaverylongtext84 | 3averylongtextaverylongtextaverylongtext42averylongtextaverylongtextaverylongtext847"
3 - fill in other mandatory fields
4 - save


/var/log/koha/kohadev/plack-intranet-error.log says
[2023/04/12 08:27:52] [WARN] DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Data too long for column 'place' at row 1 at /kohadevbox/koha/Koha/Object.pm line 176

As 260/264 $a and $c are repeatable fields, which get joined, it is not uncommon that they exceed the maximal length of 256 in some of my records
Comment 4 Jonathan Druart 2023-05-03 13:42:40 UTC
Created attachment 150583 [details] [review]
Bug 28328: Extend biblioitems.lccn to longtext

Same sa issn and isbn, we want lccn to allow longer values.

Test plan:
Run the updatedatabase script to modify the DB structure
restart_all
Edit a bibliographic record and enter a long (more than 25 chars) lccn
Save
Comment 5 Jonathan Druart 2023-05-03 13:42:42 UTC
Created attachment 150584 [details] [review]
Bug 28328: DBIC schema changes
Comment 6 Jonathan Druart 2023-05-03 13:42:45 UTC
Created attachment 150585 [details] [review]
Bug 28328: Adjust tests
Comment 7 Nick Clemens 2023-05-03 13:55:25 UTC
Created attachment 150586 [details] [review]
Bug 28328: Extend biblioitems.lccn to longtext

Same sa issn and isbn, we want lccn to allow longer values.

Test plan:
Run the updatedatabase script to modify the DB structure
restart_all
Edit a bibliographic record and enter a long (more than 25 chars) lccn
Save

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 8 Nick Clemens 2023-05-03 13:55:27 UTC
Created attachment 150587 [details] [review]
Bug 28328: DBIC schema changes

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 9 Nick Clemens 2023-05-03 13:55:29 UTC
Created attachment 150588 [details] [review]
Bug 28328: Adjust tests

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 10 Martin Renvoize 2023-05-10 16:10:32 UTC
Created attachment 151020 [details] [review]
Bug 28328: Extend biblioitems.lccn to longtext

Same sa issn and isbn, we want lccn to allow longer values.

Test plan:
Run the updatedatabase script to modify the DB structure
restart_all
Edit a bibliographic record and enter a long (more than 25 chars) lccn
Save

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 11 Martin Renvoize 2023-05-10 16:10:35 UTC
Created attachment 151021 [details] [review]
Bug 28328: DBIC schema changes

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 12 Martin Renvoize 2023-05-10 16:10:39 UTC
Created attachment 151022 [details] [review]
Bug 28328: Adjust tests

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 13 Martin Renvoize 2023-05-10 16:10:58 UTC
All working as described and fixes the issue.

Passing QA
Comment 14 Tomás Cohen Arazi 2023-05-10 18:28:28 UTC
Pushed to master for 23.05.

Nice work everyone, thanks!
Comment 15 Martin Renvoize 2023-05-15 18:43:01 UTC
Many hands makes light work, thankyou everyone!

Pushed to 22.11.x for the next release
Comment 16 Katrin Fischer 2023-06-16 11:15:22 UTC
(In reply to Jan Kissig from comment #3)
> The same problem for columns publishercode or place obtained from MARC 264$b
> and $a or 260$a and $b
> 
> 
> to reproduce 
> 1 - edit or add new biblio
> 2 - in 264$a enter f.e.
> "1averylongtextaverylongtextaverylongtext42averylongtextaverylongtextaverylon
> gtext84 |
> 2averylongtextaverylongtextaverylongtext42averylongtextaverylongtextaverylong
> text84 |
> 3averylongtextaverylongtextaverylongtext42averylongtextaverylongtextaverylong
> text847"
> 3 - fill in other mandatory fields
> 4 - save
> 
> 
> /var/log/koha/kohadev/plack-intranet-error.log says
> [2023/04/12 08:27:52] [WARN] DBIx::Class::Storage::DBI::_dbh_execute(): DBI
> Exception: DBD::mysql::st execute failed: Data too long for column 'place'
> at row 1 at /kohadevbox/koha/Koha/Object.pm line 176
> 
> As 260/264 $a and $c are repeatable fields, which get joined, it is not
> uncommon that they exceed the maximal length of 256 in some of my records

Hi Jan, please see bug 34029. Trying to get rid of those problems for good.
Comment 17 Katrin Fischer 2023-06-18 13:10:06 UTC
*** Bug 30896 has been marked as a duplicate of this bug. ***