Bug 27968 - MARC framework CSV and ODS import incomplete or corrupted
Summary: MARC framework CSV and ODS import incomplete or corrupted
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: System Administration (show other bugs)
Version: master
Hardware: All All
: P5 - low normal (vote)
Assignee: Jonathan Druart
QA Contact: Marcel de Rooy
URL:
Keywords:
Depends on:
Blocks: 17258 28199
  Show dependency treegraph
 
Reported: 2021-03-16 17:53 UTC by Victor Grousset/tuxayo
Modified: 2021-12-13 21:11 UTC (History)
7 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Trivial patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
21.05.00,20.11.06,20.05.12,19.11.18


Attachments
Bug 27968: Fix framework import if isurl has an incorrect value (3.19 KB, patch)
2021-03-17 08:22 UTC, Jonathan Druart
Details | Diff | Splinter Review
Default-framework-export-resaved-libreoffice (335.96 KB, text/csv)
2021-03-18 18:52 UTC, Victor Grousset/tuxayo
Details
Bug 27968: Fix framework import if isurl has an incorrect value (3.25 KB, patch)
2021-04-23 00:18 UTC, Victor Grousset/tuxayo
Details | Diff | Splinter Review
Bug 27968: Fix framework import if isurl has an incorrect value (3.35 KB, patch)
2021-04-23 07:16 UTC, Marcel de Rooy
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Victor Grousset/tuxayo 2021-03-16 17:53:12 UTC
1. CSV
   1. Export default MARC framework in CSV
   2. Create a new framework
   3. import the CSV
   4. See MARC structure of the new framework
   5. View subfield of tag 240
   6. Compare with default framework
   7. Subfields are missing
2. ODS
   1. Export default MARC framework in .ods
   2. Open the .ods with LibreOffice
   3. Save it
   4. Create a new framework
   5. import the .ods
   6. See MARC structure of the new framework
   7. View subfield of tag 240
   8. Compare with default framework
   9. See Also: 3, and Link:3 have appeared (the code of my new framework code is 3)

For ODS, it seems a cell is offset and the framework code ends up in other columns. The raw ODS from Koha and the one saved by LibreOffice don't seem to differ on the 240 subfields.

Both issues happen on master and on 19.11 (with bug 27569 applied)

Not sure about severity.
Comment 1 Jonathan Druart 2021-03-17 08:17:49 UTC
[2021/03/17 07:47:02] [WARN] C4::ImportExportFramework::_processRow_DB(): DBI Exception: DBD::mysql::st execute failed: Incorrect integer value: '' for column 'isurl' at row 1 [for Statement "INSERT INTO marc_subfield_structure (tagfield,tagsubfield,liblibrarian,libopac,repeatable,mandatory,important,kohafield,tab,authorised_value,authtypecode,value_builder,isurl,hidden,frameworkcode,seealso,link,defaultvalue,maxlength,display_order) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE tagfield=?,tagsubfield=?,liblibrarian=?,libopac=?,repeatable=?,mandatory=?,important=?,kohafield=?,tab=?,authorised_value=?,authtypecode=?,value_builder=?,isurl=?,hidden=?,frameworkcode=?,seealso=?,link=?,defaultvalue=?,maxlength=?,display_order=?" with ParamValues: 0='999', 1='c', 2='Koha biblionumber', 3='Koha biblionumber', 4='0', 5='0', 6='0', 7='biblio.biblionumber', 8='-1', 9='', 10='', 11='', 12='', 13='-5', 14="X", 15='', 16='', 17='', 18='9999', 19='0', 20='999', 21='c', 22='Koha biblionumber', 23='Koha biblionumber', 24='0', 25='0', 26='0', 27='biblio.biblionumber', 28='-1', 29='', 30='', 31='', 32='', 33='-5', 34="X", 35='', 36='', 37='', 38='9999', 39='0'] at /kohadevbox/koha/C4/ImportExportFramework.pm line 986
Comment 2 Jonathan Druart 2021-03-17 08:19:38 UTC
Only happens when SQL strict mode is on.
Comment 3 Jonathan Druart 2021-03-17 08:22:08 UTC
Created attachment 118357 [details] [review]
Bug 27968: Fix framework import if isurl has an incorrect value

If isurl is set to undef in the DB, it will be exported as an empty
string, which is an invalid value for isurl (int)

Incorrect integer value: '' for column 'isurl'

Test plan:
Export framework structure in CSV and ODS, then reimport it and check that
all the subfields are important correctly
Comment 4 Jonathan Druart 2021-03-17 08:24:28 UTC
MariaDB [koha_kohadev]> select tagsubfield, isurl from marc_subfield_structure where tagfield=240 and frameworkcode="" limit 6;
+-------------+-------+
| tagsubfield | isurl |
+-------------+-------+
| 0           |  NULL |
| 1           |  NULL |
| 2           |  NULL |
| 6           |     0 |
| 8           |     0 |
| 9           |     0 |
+-------------+-------+
6 rows in set (0.01 sec)
Comment 5 Victor Grousset/tuxayo 2021-03-17 20:23:29 UTC
Likely not related but, is that expected that
1. Create new framework
2. Marc structure
3. Internal server error

CGI::Compile::ROOT::kohadevbox_koha_admin_marctagstructure_2epl::marctagstructure_2epl(): DBI Exception: DBD::mysql::st execute failed: 'koha_kohadev.biblio_framework.frameworktext' isn't in GROUP BY [for Statement "select count(*),marc_tag_structure.frameworkcode,frameworktext from marc_tag_structure,biblio_framework where biblio_framework.frameworkcode=marc_tag_structure.frameworkcode group by marc_tag_structure.frameworkcode"] at /usr/share/perl5/CGI/Emulate/PSGI.pm line 30
 at /usr/share/perl5/DBIx/Class/Exception.pm line 77
Comment 6 Victor Grousset/tuxayo 2021-03-17 21:08:18 UTC
Found issue, test scenario 1 passes but not if I add a save with LibreOffice

1. Export default MARC framework in CSV
2. Open the .csv with LibreOffice and save it
3. Create a new framework
4. import the CSV
5. See MARC structure of the new framework
6. Tags are missing the trailing zeros (10 instead of 010)
7. View subfields of tag 10/010
8. Compare with default framework
9. Subfields are missing
     note, why does on the default framework, these two URLs don't yield the same result?
     /koha/admin/marc_subfields_structure.pl?tagfield=010&frameworkcode=
     /koha/admin/marc_subfields_structure.pl?tagfield=10&frameworkcode=3
     It would also make sense that 10 doesn't yield anything.
10. Same with 031/31
      default framework still has different output for 31 and 031


ODS issue when saved by LibreOffice is still there

1. Export default MARC framework in .ods
2. Open the .ods with LibreOffice
3. Save it
4. Create a new framework
5. import the .ods
6. See MARC structure of the new framework
7. View subfield of tag 010
8. Compare with default framework
9. See Also: 4, and Link:4 have appeared (the code of my new framework code is 4)


Testing notes:
Tags with a lot or diverse subfields:
010
100
240
995

031
110
600
700
Comment 7 Jonathan Druart 2021-03-18 09:27:05 UTC
(In reply to Victor Grousset/tuxayo from comment #6)
> Found issue, test scenario 1 passes but not if I add a save with LibreOffice
> 
> 1. Export default MARC framework in CSV
> 2. Open the .csv with LibreOffice and save it
> 3. Create a new framework
> 4. import the CSV
> 5. See MARC structure of the new framework
> 6. Tags are missing the trailing zeros (10 instead of 010)

I don't recreate that.
Certainly a setting in the libreoffice config.

> 7. View subfields of tag 10/010
> 8. Compare with default framework
> 9. Subfields are missing
>      note, why does on the default framework, these two URLs don't yield the
> same result?
>      /koha/admin/marc_subfields_structure.pl?tagfield=010&frameworkcode=
>      /koha/admin/marc_subfields_structure.pl?tagfield=10&frameworkcode=3
>      It would also make sense that 10 doesn't yield anything.
> 10. Same with 031/31
>       default framework still has different output for 31 and 031

10 picked the 100's subfields, that's definitely a bug, should be reported separately.

> ODS issue when saved by LibreOffice is still there
> 
> 1. Export default MARC framework in .ods
> 2. Open the .ods with LibreOffice
> 3. Save it
> 4. Create a new framework
> 5. import the .ods
> 6. See MARC structure of the new framework
> 7. View subfield of tag 010
> 8. Compare with default framework
> 9. See Also: 4, and Link:4 have appeared (the code of my new framework code
> is 4)

Does it work if you don't open/save it?
Comment 8 Victor Grousset/tuxayo 2021-03-18 17:17:09 UTC
(In reply to Jonathan Druart from comment #7)
> (In reply to Victor Grousset/tuxayo from comment #6)
> > 1. Export default MARC framework in CSV
> > 2. Open the .csv with LibreOffice and save it
> > 3. Create a new framework
> > 4. import the CSV
> > 5. See MARC structure of the new framework
> > 6. Tags are missing the trailing zeros (10 instead of 010)
> 
> I don't recreate that.
> Certainly a setting in the libreoffice config.

Next is attached the CSV saved by LibreOffice, 010 => 10 happens there.
When opening the CSV, one of the options is "Format quoted fields as text.", it's off by default. Hence 010 => 10.
Should the Koha import handle the spreadsheet software removing the leading zeros? Since one of the common spreadsheet software does this by default, a fair share of usage of this feature will be with this data.

> > 7. View subfields of tag 10/010
> > 8. Compare with default framework
> > 9. Subfields are missing
> >      note, why does on the default framework, these two URLs don't yield the
> > same result?
> >      /koha/admin/marc_subfields_structure.pl?tagfield=010&frameworkcode=
> >      /koha/admin/marc_subfields_structure.pl?tagfield=10&frameworkcode=3
> >      It would also make sense that 10 doesn't yield anything.
> > 10. Same with 031/31
> >       default framework still has different output for 31 and 031
> 
> 10 picked the 100's subfields, that's definitely a bug, should be reported
> separately.

Ok, see bug 27989.

About the subfields missing it's related to the above issue. Because when reexporting they are in the CSV even if I can't find a way to see them in the interface.

> > ODS issue when saved by LibreOffice is still there
> > 
> > 1. Export default MARC framework in .ods
> > 2. Open the .ods with LibreOffice
> > 3. Save it
> > 4. Create a new framework
> > 5. import the .ods
> > 6. See MARC structure of the new framework
> > 7. View subfield of tag 010
> > 8. Compare with default framework
> > 9. See Also: 4, and Link:4 have appeared (the code of my new framework code
> > is 4)
> 
> Does it work if you don't open/save it?

Yes, if the ODS export is directly imported, no issues
Comment 9 Victor Grousset/tuxayo 2021-03-18 18:52:18 UTC
Created attachment 118470 [details]
Default-framework-export-resaved-libreoffice
Comment 10 Victor Grousset/tuxayo 2021-04-22 13:09:10 UTC
While there are still issues, this patch solves part of the problem and makes a few workflows work again.

Shall the ticket be split for the remaining failing cases so the current patch can be merged?
Comment 11 Jonathan Druart 2021-04-22 13:22:05 UTC
Yes, I think we should go ahead with this patch and open a separate bug report for the other bug.
Comment 12 Victor Grousset/tuxayo 2021-04-23 00:18:15 UTC
Created attachment 120036 [details] [review]
Bug 27968: Fix framework import if isurl has an incorrect value

If isurl is set to undef in the DB, it will be exported as an empty
string, which is an invalid value for isurl (int)

Incorrect integer value: '' for column 'isurl'

Test plan:
Export framework structure in CSV and ODS, then reimport it and check that
all the subfields are important correctly

Signed-off-by: Victor Grousset/tuxayo <victor@tuxayo.net>
Comment 13 Victor Grousset/tuxayo 2021-04-23 00:21:34 UTC
It works. Tested successfully CSV and ODS without going through LibreOffice.
Comment 14 Marcel de Rooy 2021-04-23 07:08:35 UTC
Looking here
Comment 15 Marcel de Rooy 2021-04-23 07:16:26 UTC
Created attachment 120038 [details] [review]
Bug 27968: Fix framework import if isurl has an incorrect value

If isurl is set to undef in the DB, it will be exported as an empty
string, which is an invalid value for isurl (int)

Incorrect integer value: '' for column 'isurl'

Test plan:
Export framework structure in CSV and ODS, then reimport it and check that
all the subfields are important correctly

Signed-off-by: Victor Grousset/tuxayo <victor@tuxayo.net>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 16 Marcel de Rooy 2021-04-23 07:17:17 UTC
Passed QA
Note that I would not call this a major bug. Devaluates severity ;)
Comment 17 Jonathan Druart 2021-04-23 09:55:43 UTC
Pushed to master for 21.05, thanks to everybody involved!
Comment 18 Fridolin Somers 2021-04-29 14:31:46 UTC
Pushed to 20.11.x for 20.11.06
Comment 19 Sarah Cornell 2021-05-07 14:24:21 UTC
What are our chances of having this pushed to 20.05?
Comment 20 Andrew Fuerste-Henry 2021-05-24 17:08:27 UTC
Pushed to 20.05.x for 20.05.12
Comment 21 Victor Grousset/tuxayo 2021-05-24 18:37:16 UTC
Backported: Pushed to 19.11.x branch for 19.11.18