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.
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
(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
Indeed Nick, good catch! So maybe we should have this default value at DB value?
Created attachment 92054 [details] [review] Bug 23309: Update DB
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
Should we make the same changes to auth_subfield_structure?
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>
(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.
Why the change to assigned?
(In reply to Katrin Fischer from comment #9) > Why the change to assigned? Because it needs a followup I'm writing.
(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!).
Created attachment 92189 [details] [review] Bug 23309: Update DB Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
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>
Nice work! Pushed to master for 19.11.00
Pushed to 19.05.x for 19.05.04
backported to 18.11.x for 18.11.10
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
Oh indeed. @Lucas : I think its a backport error of "Bug 23309: DBRev 18.11.09.002" : You impact only installer/data/mysql/updatedatabase.pl : http://git.koha-community.org/gitweb/?p=koha.git;a=commitdiff;h=105e3c9ce04d2958d9e0749c579f73fece94d641 Compare with master : http://git.koha-community.org/gitweb/?p=koha.git;a=commitdiff;h=347dc9589aa75edb1c64c21e5d057373dfd3ed84 And 19.05.x : http://git.koha-community.org/gitweb/?p=koha.git;a=commitdiff;h=f14d94e565c81f8afa25fea7e1a032f33c93ad82
Sorry about this error, I have fixed it with this commit: 9c1720d7d7b299812bc7faa6a2ae9d23075d2a0d
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
(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.