[Bug 30449] New: Missing FK constraint on borrower_attribute_types
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Bug ID: 30449 Summary: Missing FK constraint on borrower_attribute_types Change sponsored?: --- Product: Koha Version: unspecified Hardware: All OS: All Status: NEW Severity: minor Priority: P5 - low Component: Database Assignee: koha-bugs@lists.koha-community.org Reporter: m.de.rooy@rijksmuseum.nl QA Contact: testopia@bugs.koha-community.org Not in kohastructure, but present for a long time already in updatedatabase: $DBversion = "3.07.00.034"; $dbh->do("ALTER TABLE borrower_attribute_types ADD CONSTRAINT category_code_fk FOREIGN KEY (category_code) REFERENCES categories(categorycode)"); -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff Patch complexity|--- |Small patch -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 --- Comment #1 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 132965 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=132965&action=edit Bug 30449: Add missing FK constraint on borrower_attribute_types Old Koha databases probably have it, newer ones might not. See also BZ description. Test plan: [1} Check SHOW CREATE TABLE borrower_attribute_types to see if you have any index and FK constraint on category_code. [2] Run updatedatabase. [3] If you had category_code_fk, it should be replaced. [4] Remove index and constraint again using things like: alter table borrower_attribute_types drop constraint `borrower_attribute_types_ibfk_1`; alter table borrower_attribute_types drop index category_code; [5] Run updatedatabase. [6] You should have KEY category_code and FK borrower_attribute_types_ibfk_1. [7] Run updatedatabase. Idempotent, no changes. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |m.de.rooy@rijksmuseum.nl |ity.org | -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Version|unspecified |master -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #132965|0 |1 is obsolete| | --- Comment #2 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 132966 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=132966&action=edit Bug 30449: Add missing FK constraint on borrower_attribute_types Old Koha databases probably have it, newer ones might not. See also BZ description. Test plan: [1} Check SHOW CREATE TABLE borrower_attribute_types to see if you have any index and FK constraint on category_code. [2] Run updatedatabase. [3] If you had category_code_fk, it should be replaced. [4] Remove index and constraint again using things like: alter table borrower_attribute_types drop constraint `borrower_attribute_types_ibfk_1`; alter table borrower_attribute_types drop index category_code; [5] Run updatedatabase. [6] You should have KEY category_code and FK borrower_attribute_types_ibfk_1. [7] Run updatedatabase. Idempotent, no changes. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |30486 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30486 [Bug 30486] [OMNIBUS] Synchronize database schema with (older) database revisions -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |tomascohen@gmail.com |y.org | CC| |tomascohen@gmail.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|tomascohen@gmail.com |testopia@bugs.koha-communit | |y.org -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 --- Comment #3 from Tomás Cohen Arazi <tomascohen@gmail.com> --- I've checked on 3.0.X original Koha deployments I have, (already in 21.05) and the constraint is there! Good catch, Marcel! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 --- Comment #4 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- I am wondering how DBIx::RunSQL handles our /*4xxx SET FOREIGN KEY CHECKS OFF*/ line. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #132966|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 --- Comment #5 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Created attachment 133777 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=133777&action=edit Bug 30449: Add missing FK constraint on borrower_attribute_types Old Koha databases probably have it, newer ones might not. See also BZ description. Test plan: [1} Check SHOW CREATE TABLE borrower_attribute_types to see if you have any index and FK constraint on category_code. [2] Run updatedatabase. [3] If you had category_code_fk, it should be replaced. [4] Remove index and constraint again using things like: alter table borrower_attribute_types drop constraint `borrower_attribute_types_ibfk_1`; alter table borrower_attribute_types drop index category_code; [5] Run updatedatabase. [6] You should have KEY category_code and FK borrower_attribute_types_ibfk_1. [7] Run updatedatabase. Idempotent, no changes. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 --- Comment #6 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Created attachment 133778 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=133778&action=edit Bug 30449: DBIC updates Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 --- Comment #7 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Created attachment 133779 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=133779&action=edit Bug 30446: (QA follow-up) Report back on problematic situations Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 --- Comment #8 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Marcel de Rooy from comment #4)
I am wondering how DBIx::RunSQL handles our /*4xxx SET FOREIGN KEY CHECKS OFF*/ line.
Well, quite simple. It does ignore such lines. The following commit added these checks in Installer: Bug 26947: (follow-up) Set key checks to disabled for kohastructure But note that DBIx::RunSQL was already added on bug 13669 ;) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 --- Comment #9 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Anyway, we do not need to move the (re)definition of borrower_attribute_types below categories now. You should however when you backport this patch BELOW 21.05 ! Or add these lines too in Installer: + # Disable checks before load + $self->{'dbh'}->do(q{SET NAMES utf8mb4}); + $self->{'dbh'}->do(q{SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0}); + $self->{'dbh'}->do(q{SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0}); + $self->{'dbh'}->do(q{SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'}); + $self->{'dbh'}->do(q{SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0}); + + # Load kohastructure my $error = $self->load_sql("$datadir/kohastructure.sql"); + + # Re-enable checks after load + $self->{'dbh'}->do(q{SET SQL_MODE=@OLD_SQL_MODE}); + $self->{'dbh'}->do(q{SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS}); + $self->{'dbh'}->do(q{SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS}); + $self->{'dbh'}->do(q{SET SQL_NOTES=@OLD_SQL_NOTES}); -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #133777|0 |1 is obsolete| | --- Comment #10 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 133883 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=133883&action=edit Bug 30449: Add missing FK constraint on borrower_attribute_types Old Koha databases probably have it, newer ones might not. See also BZ description. Test plan: [1} Check SHOW CREATE TABLE borrower_attribute_types to see if you have any index and FK constraint on category_code. [2] Run updatedatabase. [3] If you had category_code_fk, it should be replaced. [4] Remove index and constraint again using things like: alter table borrower_attribute_types drop constraint `borrower_attribute_types_ibfk_1`; alter table borrower_attribute_types drop index category_code; [5] Run updatedatabase. [6] You should have KEY category_code and FK borrower_attribute_types_ibfk_1. [7] Run updatedatabase. Idempotent, no changes. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #133778|0 |1 is obsolete| | --- Comment #11 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 133884 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=133884&action=edit Bug 30449: DBIC updates Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 --- Comment #12 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 133885 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=133885&action=edit Bug 30449: (QA follow-up) Report back on problematic situations Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |martin.renvoize@ptfs-europe | |.com Attachment #133779|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA --- Comment #13 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Working well, a good job resyncing. Thanks PQA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |22.05.00 released in| | Status|Passed QA |Pushed to master -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 --- Comment #14 from Fridolin Somers <fridolin.somers@biblibre.com> --- Pushed to master for 22.05, thanks to everybody involved 🦄 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to master |Pushed to stable CC| |kyle@bywatersolutions.com Version(s)|22.05.00 |22.05.00,21.11.06 released in| | --- Comment #15 from Kyle M Hall <kyle@bywatersolutions.com> --- Pushed to 21.11.x for 21.11.06 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |30899 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30899 [Bug 30899] Upgrade sometimes fails at "Upgrade to 21.11.05.004" -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to stable |RESOLVED Resolution|--- |FIXED --- Comment #16 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- See bug 30899. Patch forthcoming on that report. No need to push this further down. Closing the report to prevent that. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Victor Grousset/tuxayo <victor@tuxayo.net> changed: What |Removed |Added ---------------------------------------------------------------------------- Resolution|FIXED |--- Status|RESOLVED |Needs documenting CC| |victor@tuxayo.net -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs documenting |RESOLVED Documentation| |Martin Renvoize contact| | Resolution|--- |FIXED --- Comment #17 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Architectural change, no documentation update required. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30449 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart+koha@gmail. | |com Blocks| |31037 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31037 [Bug 31037] 'borrower_attribute_types' table contains references to invalid category codes -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org