[Bug 8002] New: Can't add patron attribute type in newer installation
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Priority: P5 - low Change sponsored?: --- Bug ID: 8002 CC: gmcharlt@gmail.com Assignee: paul.poulain@biblibre.com Summary: Can't add patron attribute type in newer installation QA Contact: koha.sekjal@gmail.com Severity: major Classification: Unclassified OS: All Reporter: oleonard@myacpl.org Hardware: All Status: NEW Version: master Component: System Administration Product: Koha The patch for Bug 7154 (Group borrower attributes and link between patron category and attribute) contains this in updatedatabase.pl: $dbh->do("ALTER TABLE borrower_attribute_types ADD COLUMN category_code VARCHAR(10) NULL DEFAULT NULL AFTER `display_checkout`"); ...which allows borrower_attribute_types.category_code to be NULL. However, the addition to kohastructure.sql includes: `category_code` VARCHAR(1) NOT NULL DEFAULT '',-- defines a category for an attribute_type ...which requires that it /not/ be NULL. Users of new installations will find that they cannot add patron attribute types if they don't limit it to a patron category. After trying to add a record without a patron category limit the user will be told the operation was successful but the entry will not be saved. An error appears in the log: patron-attr-types.pl: DBD::mysql::st execute failed: Column 'category_code' cannot be null at /home/oleonard/kohaclone/C4/Members/AttributeTypes.pm line 220 -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Owen Leonard <oleonard@myacpl.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |ASSIGNED Assignee|paul.poulain@biblibre.com |oleonard@myacpl.org -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 --- Comment #1 from Owen Leonard <oleonard@myacpl.org> --- Created attachment 9287 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=9287&action=edit Bug 8002 - Can't add patron attribute type in newer installation Correcting patron_attributes_types definition in kohastructure.sql to allow NULL entries for category_code, matching the definition specified in updatedatabase.pl. To test: Back up your Koha database, drop it, and recreate it. Load the staff interface and go through the web installer. After recreating the database enable ExtendedPatronAttributes and try adding a new patron attribute type with no patron category specified. The operation should be successful. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Owen Leonard <oleonard@myacpl.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Owen Leonard <oleonard@myacpl.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |ASSIGNED -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Owen Leonard <oleonard@myacpl.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #9287|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 --- Comment #2 from Owen Leonard <oleonard@myacpl.org> --- Created attachment 9288 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=9288&action=edit Bug 8002 [REVISED] Can't add patron attribute type in newer installation Correcting patron_attributes_types definition in kohastructure.sql to allow NULL entries for category_code, matching the definition specified in updatedatabase.pl. Adds an entry to updatedatabase.pl to correct existing installations with the incorrect table structure. After running the updated updatedatabase.pl the category_code column in borrower_attribute_types should show that it allows NULL entries. Adding a new patron attribute type with no patron category specified should work correctly. To test the update to kohastructure.sql: Back up your Koha database, drop it, and recreate it. Load the staff interface and go through the web installer. After recreating the database enable ExtendedPatronAttributes and try adding a new patron attribute type with no patron category specified. The operation should be successful. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Owen Leonard <oleonard@myacpl.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Jonathan Druart <jonathan.druart@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #9288|0 |1 is obsolete| | --- Comment #3 from Jonathan Druart <jonathan.druart@biblibre.com> --- Created attachment 9311 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=9311&action=edit Bug 8002 [SIGNED-OFF][REVISED] Can't add patron attribute type in newer installation -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Jonathan Druart <jonathan.druart@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off CC| |jonathan.druart@biblibre.co | |m -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Ian Walls <koha.sekjal@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Failed QA --- Comment #4 from Ian Walls <koha.sekjal@gmail.com> --- Sets DB rev number starting with 3.08 instead of 3.09. Also uses CHANGE instead of MODIFY, and since we're not renaming the column, better to use what we've used in the past to do this job. Small things, I know, but marking Failed QA until they're resolved. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 --- Comment #5 from Paul Poulain <paul.poulain@biblibre.com> --- Created attachment 9367 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=9367&action=edit Bug 8002 follow-up to use 3.09 and MODIFY instead of 3.08 and CHANGE see Ian comment 4 (http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002) -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Paul Poulain <paul.poulain@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Passed QA CC| |paul.poulain@biblibre.com --- Comment #6 from Paul Poulain <paul.poulain@biblibre.com> --- (In reply to comment #4)
Sets DB rev number starting with 3.08 instead of 3.09. Also uses CHANGE instead of MODIFY, and since we're not renaming the column, better to use what we've used in the past to do this job.
Small things, I know, but marking Failed QA until they're resolved.
oups... seems I made a mistake, by missing Ian (perfectly correct) QA comment. As I already had pushed the bugfix provided, I also have pushed a follow-up to fix this QA comment. Ian, please check everything is OK, and sorry again (i'm back from holiday, maybe my brain is still not 100% efficient ;-) ) -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Paul Poulain <paul.poulain@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to Master --- Comment #7 from Paul Poulain <paul.poulain@biblibre.com> --- patch and follow-up pushed -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Chris Cormack <chris@bigballofwax.co.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to Master |Pushed to Stable CC| |chris@bigballofwax.co.nz -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Frédéric Demians <frederic@tamil.fr> changed: What |Removed |Added ---------------------------------------------------------------------------- Priority|P5 - low |P2 CC| |frederic@tamil.fr --- Comment #8 from Frédéric Demians <frederic@tamil.fr> --- WARNING: This patch borrower_attribute_types by truncating category_code content. Those lines: `category_code` VARCHAR(1) NULL DEFAULT NULL,-- defines a category for an attribute_type $DBversion = "3.08.00.XXX"; if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { $dbh->do("ALTER TABLE borrower_attribute_types CHANGE category_code category_code VARCHAR( 1 ) NULL DEFAULT NULL"); print "Upgrade to $DBversion done. (Bug 8002: Update patron attribute types table to allow NULL category_code)\n"; SetVersion($DBversion); } are wrong. category_code length is 10 not 1! It means that after update, attributes by category don't work anymore, and configuration is lost. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 --- Comment #9 from Paul Poulain <paul.poulain@biblibre.com> --- mmm... The patch is: - `category_code` VARCHAR(1) NOT NULL DEFAULT '',-- defines a category for an attribute_type + `category_code` VARCHAR(1) NULL DEFAULT NULL,-- defines a category for an attribute_type so category_code is already set to varchar(1) before this patch. This patch just set NULL instead of NOT NULL Digging kohastructure git history now -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 --- Comment #10 from Paul Poulain <paul.poulain@biblibre.com> --- The patch introducing this column is: bb35b608282e670e1bb07bf4cea324fc16b6ec90 Auteur: Jonathan Druart <jonathan.druart@biblibre.com> 2012-02-10 09:57:35 Bug 7154: Modification in borrower attributes * group some attributes for a more friendly display * allow a link between a borrower category and an attribute to display it only for the specified category * Attr classes filled with AV 'PA_CLASS' category (you have to create AV with a new category 'PA_CLASS' to fill class list) this patch add 2 fields in borrower_attribute_types : category_code class Signed-off-by: Julien Sicot <julien.sicot@gmail.com> Signed-off-by: Paul Poulain <paul.poulain@biblibre.com> I just tried to add a filtered attribute = DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`lecannetdesmaures`.`borrower_attribute_types`, CONSTRAINT `category_code_fk` FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`)) at /home/paul/koha.dev/koha-community/C4/Members/AttributeTypes.pm line 220. !!! Just spoke with jonathan, and, unfortunately, in his test database, he only has 1 digit categories, so it work fine !!! Patch coming ;-) -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Jonathan Druart <jonathan.druart@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #9311|0 |1 is obsolete| | Attachment #9367|0 |1 is obsolete| | --- Comment #11 from Jonathan Druart <jonathan.druart@biblibre.com> --- Created attachment 12011 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=12011&action=edit Bug 8002: FIX Change datatype from varchar 1 to 10 for borrower_attribute_types.category_code -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Jonathan Druart <jonathan.druart@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to Stable |ASSIGNED -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Jonathan Druart <jonathan.druart@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Jonathan Druart <jonathan.druart@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|major |critical -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 --- Comment #12 from Frédéric Demians <frederic@tamil.fr> --- Thanks a lot for fixing it so quickly! I will sign-off the patch. Don't you think a warning, somewhere, must be addressed to Koha sysadmin? I propose: Warning to Koha System Administrators: If you use borrower attributes defined by borrower categories, you have to check your configuration. A bug may have removed your attribute links to borrower categories. Please check, and fix it if necessary. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Frédéric Demians <frederic@tamil.fr> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #12011|0 |1 is obsolete| | --- Comment #13 from Frédéric Demians <frederic@tamil.fr> --- Created attachment 12012 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=12012&action=edit [SIGNED-OFF] FIX Change datatype from varchar 1 to 10 -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Frédéric Demians <frederic@tamil.fr> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 --- Comment #14 from Jonathan Druart <jonathan.druart@biblibre.com> --- (In reply to comment #12)
Don't you think a warning, somewhere, must be addressed to Koha sysadmin?
I think it is not necessary given that informations were not updated (or inserted). Moreover the issue exists only for a new install (not updated install). -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Frédéric Demians <frederic@tamil.fr> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |In Discussion --- Comment #15 from Frédéric Demians <frederic@tamil.fr> ---
I think it is not necessary given that informations were not updated (or inserted). Moreover the issue exists only for a new install (not updated install).
In updatedatabase.pl, on HEAD branch, line 5218, an ALTER TABLE truncate category_code field from 10 to 1 character. Same issue, same line in 3.8.x, for 3.08.00.001 version. So we need a patch for the two branches. And a warning message. I confirm that I have a library on 3.8 which has had truncated its attributes by borrower categories. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Frédéric Demians <frederic@tamil.fr> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #12012|0 |1 is obsolete| | --- Comment #16 from Frédéric Demians <frederic@tamil.fr> --- Comment on attachment 12012 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=12012 [SIGNED-OFF] FIX Change datatype from varchar 1 to 10 updatedatabase.pl must also be fixed. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 --- Comment #17 from Jonathan Druart <jonathan.druart@biblibre.com> --- Yes I have just seen that ! But why mysql breaks a constraint ?? -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 --- Comment #18 from Frédéric Demians <frederic@tamil.fr> ---
Yes I have just seen that ! But why mysql breaks a constraint ??
When category_code is truncated to 1 character, it is no more possible to link borrower attribute types to borrower categories which are still 10 characters length. And if 2 attributes have been linked to 2 distinct borrower categories like ENFANT and ETUDIANT, category_code will contain E after ALTER TABLE for both attributes, and the link won't work anymore. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 --- Comment #19 from Jonathan Druart <jonathan.druart@biblibre.com> --- Created attachment 12033 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=12033&action=edit Bug 8002: FIX Change datatype from varchar 1 to 10 for borrower_attribute_types.category_code -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 --- Comment #20 from Jonathan Druart <jonathan.druart@biblibre.com> --- Created attachment 12034 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=12034&action=edit [3.8.X] Bug 8002: FIX Change datatype from varchar 1 to 10 for borrower_attribute_types.category_code -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Jonathan Druart <jonathan.druart@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|In Discussion |Needs Signoff --- Comment #21 from Jonathan Druart <jonathan.druart@biblibre.com> --- (In reply to comment #18)
Yes I have just seen that ! But why mysql breaks a constraint ??
When category_code is truncated to 1 character, it is no more possible to link borrower attribute types to borrower categories which are still 10 characters length. And if 2 attributes have been linked to 2 distinct borrower categories like ENFANT and ETUDIANT, category_code will contain E after ALTER TABLE for both attributes, and the link won't work anymore.
Hi Frédéric, Yes, I has understand. I would said it is strange that MySQL does not keep integrity and raise an error when we want to reduce data. Besides But it is normal MySQL does what we ask him :) I proposed 2 patchs (same as previous). 1 for master, 1 for 3.8.x A warning message is displayed on the updatedatabase page. I hope it is what you wanted. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 --- Comment #22 from Paul Poulain <paul.poulain@biblibre.com> --- (In reply to comment #21)
But it is normal MySQL does what we ask him :)
Definetly it's not normal, and it's a bug ! When you don't have a constraint and try to add one, if there are data that would not respect the constraint, you get a message, and the constraint is not set. Seel update22to30.pl to see how I handled this case in the past = I throw a clear warning, with the query to run to see which data was wrong, so the library could fix it and put the constraint. I'm expecting from ALTER TABLE that an existing constraint is checked before the table is altered ! Also note that, iirc, you can't put a constraint on 2 columns with different type/size. That could be easily checked by mysql when you ALTER TABLE ! -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Chris Cormack <chris@bigballofwax.co.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #12033|0 |1 is obsolete| | --- Comment #23 from Chris Cormack <chris@bigballofwax.co.nz> --- Created attachment 12206 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=12206&action=edit Bug 8002: FIX Change datatype from varchar 1 to 10 for borrower_attribute_types.category_code Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz> -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Chris Cormack <chris@bigballofwax.co.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Mason James <mtj@kohaaloha.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA CC| |mtj@kohaaloha.com --- Comment #24 from Mason James <mtj@kohaaloha.com> --- (In reply to comment #23)
Created attachment 12206 [details] Bug 8002: FIX Change datatype from varchar 1 to 10 for borrower_attribute_types.category_code
Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>
passing QA on patch against master $ koha-qa.pl testing 1 commit(s) (applied to commit 60790d0) * 5599413 Bug 8002: FIX Change datatype from varchar 1 to 10 for borrower_a installer/data/mysql/kohastructure.sql installer/data/mysql/updatedatabase.pl * installer/data/mysql/updatedatabase.pl OK -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Paul Poulain <paul.poulain@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to Master Version|master |rel_3_8 --- Comment #25 from Paul Poulain <paul.poulain@biblibre.com> --- Patch pushed to master Patch pushed on master branch directly, as the new/bug_8002 is old -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8002 Chris Cormack <chris@bigballofwax.co.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to Master |Pushed to Stable --- Comment #26 from Chris Cormack <chris@bigballofwax.co.nz> --- Pushed to 3.8.x, will be in 3.8.5 -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org