Bug 23309 - Can't add new subfields to bibliographic frameworks in strict mode
Summary: Can't add new subfields to bibliographic frameworks in strict mode
Status: Pushed to oldstable
Alias: None
Product: Koha
Classification: Unclassified
Component: System Administration (show other bugs)
Version: master
Hardware: All All
: P5 - low major (vote)
Assignee: Jonathan Druart
QA Contact: Tomás Cohen Arazi
URL:
Keywords:
Depends on:
Blocks: 17258 23709
  Show dependency treegraph
 
Reported: 2019-07-12 15:04 UTC by Katrin Fischer
Modified: 2019-12-10 14:32 UTC (History)
8 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Who signed the patch off:
Text to go in the release notes:
Version(s) released in:
19.11.00,19.05.04


Attachments
Bug 23309: Fix insert of new subfields with SQL strict modes (1.18 KB, patch)
2019-08-05 16:32 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 23309: Update DB (1.66 KB, patch)
2019-08-08 01:18 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 23309: Use Koha::MarcSubfieldStructures to create to subfields (4.25 KB, patch)
2019-08-08 01:18 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 23309: Use Koha::MarcSubfieldStructures to create to subfields (4.31 KB, patch)
2019-08-13 06:22 UTC, Katrin Fischer
Details | Diff | Splinter Review
Bug 23309: Update DB (1.77 KB, patch)
2019-08-14 14:41 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 23309: Use Koha::MarcSubfieldStructures to create to subfields (4.35 KB, patch)
2019-08-14 14:41 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Katrin Fischer 2019-07-12 15:04:07 UTC
When I try to add a new a new subfield to a tag in the bibliographic MARC frameworks, it doesn't save.

Tested on a sandbox, found this in the logs:

DBD::mysql::st execute failed: Incorrect integer value: '' for column 'hidden' at row 1 [for Statement "
        insert into marc_subfield_structure (tagfield,tagsubfield,liblibrarian,libopac,repeatable,mandatory,kohafield,tab,seealso,authorised_value,authtypecode,value_builder,hidden,isurl,frameworkcode, link,defaultvalue,maxlength)
        values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    " with ParamValues: 0="922", 1="n", 2="k", 3="k", 4=0, 5=0, 6="", 7="-1", 8=undef, 9="", 10="", 11="", 12="", 13=0, 14="", 15="", 16="", 17="9999"] at /kohadevbox/koha/admin/marc_subfields_structure.pl line 316.
DBD::mysql::st execute failed: Incorrect integer value: '' for column 'hidden' at row 1 [for Statement "
        insert into marc_subfield_structure (tagfield,tagsubfield,liblibrarian,libopac,repeatable,mandatory,kohafield,tab,seealso,authorised_value,authtypecode,value_builder,hidden,isurl,frameworkcode, link,defaultvalue,maxlength)
        values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    " with ParamValues: 0="941", 1="k", 2="k", 3="k", 4=0, 5=0, 6="", 7="-1", 8=undef, 9="", 10="", 11="", 12="", 13=0, 14="", 15="", 16="", 17="9999"] at /kohadevbox/koha/admin/marc_subfields_structure.pl line 316.
Comment 1 Jonathan Druart 2019-08-05 16:32:55 UTC
Created attachment 92017 [details] [review]
Bug 23309: Fix insert of new subfields with SQL strict modes

DBD::mysql::st execute failed: Incorrect integer value: '' for column 'hidden' at row 1 [for Statement "

Test plan:
- Turn the config strict_sql_modes ON
- Add a new subfield to a field
Comment 2 Nick Clemens 2019-08-07 18:00:14 UTC
(In reply to Jonathan Druart from comment #1)
> Created attachment 92017 [details] [review] [review]
> Bug 23309: Fix insert of new subfields with SQL strict modes
> 
> DBD::mysql::st execute failed: Incorrect integer value: '' for column
> 'hidden' at row 1 [for Statement "
> 
> Test plan:
> - Turn the config strict_sql_modes ON
> - Add a new subfield to a field

Should it be 8 and not 0? All unchecked is '8' if you save with this patch you get Opac Intranet and Editor checked which is equivalent to 0
Comment 3 Jonathan Druart 2019-08-08 00:50:50 UTC
Indeed Nick, good catch!
So maybe we should have this default value at DB value?
Comment 4 Jonathan Druart 2019-08-08 01:18:07 UTC
Created attachment 92054 [details] [review]
Bug 23309: Update DB
Comment 5 Jonathan Druart 2019-08-08 01:18:12 UTC
Created attachment 92055 [details] [review]
Bug 23309: Use Koha::MarcSubfieldStructures to create to subfields

It will fix the insert of new subfields with SQL strict modes

DBD::mysql::st execute failed: Incorrect integer value: '' for column
'hidden' at row 1 [for Statement "

Test plan:
- Turn the config strict_sql_modes ON
- Add a new subfield to a field
Comment 6 Katrin Fischer 2019-08-13 06:19:48 UTC
Should we make the same changes to auth_subfield_structure?
Comment 7 Katrin Fischer 2019-08-13 06:22:26 UTC
Created attachment 92171 [details] [review]
Bug 23309: Use Koha::MarcSubfieldStructures to create to subfields

It will fix the insert of new subfields with SQL strict modes

DBD::mysql::st execute failed: Incorrect integer value: '' for column
'hidden' at row 1 [for Statement "

Test plan:
- Turn the config strict_sql_modes ON
- Add a new subfield to a field

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 8 Katrin Fischer 2019-08-13 06:22:42 UTC
(In reply to Katrin Fischer from comment #6)
> Should we make the same changes to auth_subfield_structure?

The bug doesn't appear to exist for authorities.
Comment 9 Katrin Fischer 2019-08-14 14:13:53 UTC
Why the change to assigned?
Comment 10 Tomás Cohen Arazi 2019-08-14 14:18:38 UTC
(In reply to Katrin Fischer from comment #9)
> Why the change to assigned?

Because it needs a followup I'm writing.
Comment 11 Tomás Cohen Arazi 2019-08-14 14:40:25 UTC
(In reply to Tomás Cohen Arazi from comment #10)
> (In reply to Katrin Fischer from comment #9)
> > Why the change to assigned?
> 
> Because it needs a followup I'm writing.

It doesn't need the follow-up. The test plan says nothing about updating the DBIC schema (and I forgot!).
Comment 12 Tomás Cohen Arazi 2019-08-14 14:41:45 UTC
Created attachment 92189 [details] [review]
Bug 23309: Update DB

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 13 Tomás Cohen Arazi 2019-08-14 14:41:50 UTC
Created attachment 92190 [details] [review]
Bug 23309: Use Koha::MarcSubfieldStructures to create to subfields

It will fix the insert of new subfields with SQL strict modes

DBD::mysql::st execute failed: Incorrect integer value: '' for column
'hidden' at row 1 [for Statement "

Test plan:
- Turn the config strict_sql_modes ON
- Add a new subfield to a field

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 14 Martin Renvoize 2019-08-15 08:24:00 UTC
Nice work!

Pushed to master for 19.11.00
Comment 15 Fridolin SOMERS 2019-09-02 14:36:12 UTC
Pushed to 19.05.x for 19.05.04
Comment 16 Lucas Gass 2019-09-17 20:25:34 UTC
backported to 18.11.x for 18.11.10
Comment 17 Lucas Gass 2019-09-17 20:25:49 UTC
backported to 18.11.x for 18.11.10
Comment 18 Corinne Hayet 2019-09-19 11:53:05 UTC
Hello everybody,

We apply the last commit on Koha 18.11 including this patch and it doesn't work.

We've got two errors

 1. A foreign key constraint on authorised_value that fires because the value "" is passed instead of NULL:

DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`koha`.`marc_subfield_structure`, CONSTRAINT `marc_subfield_structure_ibfk_1` FOREIGN KEY (`authorised_value`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE SET NULL ON ) [for Statement "
        update marc_subfield_structure set tagfield=?, tagsubfield=?, liblibrarian=?, libopac=?, repeatable=?, mandatory=?, kohafield=?, tab=?, seealso=?, authorised_value=?, authtypecode=?, value_builder=?, hidden=?, isurl=?, frameworkcode=?,  link=?, defaultvalue=?, maxlength=?
        where tagfield=? and tagsubfield=? and frameworkcode=?
    " with ParamValues: 0="225", 1="a", 2="titre de la collection", 3="", 4=0, 5=0, 6="biblioitems.collectiontitle", 7="0", 8=undef, 9="", 10="", 11="unimarc_field_225a.pl", 12="0", 13=0, 14="FA", 15="", 16="", 17="9999", 18="225", 19="a", 20="FA"] at /usr/share/koha/intranet/cgi-bin/admin/marc_subfields_structure.pl line 276.


2. The hidden field is explicitly set to null, so it does not change to its default value:

DBD::mysql::st execute failed: Column 'hidden' cannot be null [for Statement "INSERT INTO `marc_subfield_structure` ( `authorised_value`, `authtypecode`, `defaultvalue`, `frameworkcode`, `hidden`, `isurl`, `kohafield`, `liblibrarian`, `libopac`, `link`, `mandatory`, `maxlength`, `repeatable`, `seealso`, `tab`, `tagfield`, `tagsubfield`, `value_builder`) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" with ParamValues: 0="", 1="", 2="", 3="FA", 4=undef, 5=0, 6='', 7="test", 8="test", 9="", 10=0, 11="9999", 12=0, 13=undef, 14="-1", 15="225", 16="z", 17=""] at /usr/local/share/perl5/DBIx/Class/Storage/DBI.pm line 1836.

Best regards,

Corinne
Comment 20 Lucas Gass 2019-09-20 17:08:44 UTC
Sorry about this error, I have fixed it with this commit: 

9c1720d7d7b299812bc7faa6a2ae9d23075d2a0d
Comment 21 Corinne Hayet 2019-09-30 12:38:19 UTC
Sorry but it still does not work. It only works if the subfield is connected to an Authorized values list.

I created a subfield 099$m with a link with COUNTRY authorized values list and it's OK

But, if I try to create a simple subfield 012$z and I've got the following error in logs : 

DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`koha`.`marc_subfield_structure`,
CONSTRAINT `marc_subfield_structure_ibfk_1` FOREIGN KEY
(`authorised_value`) REFERENCES `authorised_value_categories`
(`category_name`) ON DELETE SET NULL ON ) [for Statement "INSERT INTO `marc_subfield_structure` ( `authorised_value`, `authtypecode`, `defaultvalue`, `frameworkcode`, `hidden`, `isurl`, `kohafield`, `liblibrarian`, `libopac`, `link`, `mandatory`, `maxlength`, `repeatable`, `seealso`, `tab`, `tagfield`, `tagsubfield`,
`value_builder`) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" with ParamValues: 0="", 1="", 2="", 3="", 4="0", 5=0, 6="", 7="test", 8="test", 9="", 10=0, 11="9999", 12=0, 13=undef, 14="0", 15="013", 16="x", 17=""] at /usr/local/share/perl5/DBIx/Class/Storage/DBI.pm line 1836.

Best regards,
Corinne
Comment 22 Katrin Fischer 2019-09-30 12:41:59 UTC
(In reply to Corinne Hayet from comment #21)
> Sorry but it still does not work. It only works if the subfield is connected
> to an Authorized values list.
> 
> I created a subfield 099$m with a link with COUNTRY authorized values list
> and it's OK
> 
> But, if I try to create a simple subfield 012$z and I've got the following
> error in logs : 
> 
> DBD::mysql::st execute failed: Cannot add or update a child row: a foreign
> key constraint fails (`koha`.`marc_subfield_structure`,
> CONSTRAINT `marc_subfield_structure_ibfk_1` FOREIGN KEY
> (`authorised_value`) REFERENCES `authorised_value_categories`
> (`category_name`) ON DELETE SET NULL ON ) [for Statement "INSERT INTO
> `marc_subfield_structure` ( `authorised_value`, `authtypecode`,
> `defaultvalue`, `frameworkcode`, `hidden`, `isurl`, `kohafield`,
> `liblibrarian`, `libopac`, `link`, `mandatory`, `maxlength`, `repeatable`,
> `seealso`, `tab`, `tagfield`, `tagsubfield`,
> `value_builder`) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
> ? )" with ParamValues: 0="", 1="", 2="", 3="", 4="0", 5=0, 6="", 7="test",
> 8="test", 9="", 10=0, 11="9999", 12=0, 13=undef, 14="0", 15="013", 16="x",
> 17=""] at /usr/local/share/perl5/DBIx/Class/Storage/DBI.pm line 1836.
> 
> Best regards,
> Corinne

Hi Corinne, thx for being persistent! As this is already in stable, would you mind filing a new bug and linking to this one? (Depends on). We will have to treat it as a new bug and take a look into fixing it.