Bug 28328

Summary: Editing a record can cause an ISE if data too long for column
Product: Koha Reporter: Nick Clemens (kidclamp) <nick>
Component: CatalogingAssignee: Jonathan Druart <jonathan.druart>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: major    
Priority: P5 - low CC: bibliothek, david, jonathan.druart, m.de.rooy, martin.renvoize, philip.orr
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26518
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30896
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34029
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
Circulation function:
Attachments: Bug 28328: Extend biblioitems.lccn to longtext
Bug 28328: DBIC schema changes
Bug 28328: Adjust tests
Bug 28328: Extend biblioitems.lccn to longtext
Bug 28328: DBIC schema changes
Bug 28328: Adjust tests
Bug 28328: Extend biblioitems.lccn to longtext
Bug 28328: DBIC schema changes
Bug 28328: Adjust tests

Description Nick Clemens (kidclamp) 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 (kidclamp) 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 (kidclamp) 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 (kidclamp) 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 (ashimema) 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 (ashimema) 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 (ashimema) 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 (ashimema) 2023-05-10 16:10:58 UTC
All working as described and fixes the issue.

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

Nice work everyone, thanks!
Comment 15 Martin Renvoize (ashimema) 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. ***