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)");
Created attachment 132965 [details] [review] 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.
Created attachment 132966 [details] [review] 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>
I've checked on 3.0.X original Koha deployments I have, (already in 21.05) and the constraint is there! Good catch, Marcel!
I am wondering how DBIx::RunSQL handles our /*4xxx SET FOREIGN KEY CHECKS OFF*/ line.
Created attachment 133777 [details] [review] 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>
Created attachment 133778 [details] [review] Bug 30449: DBIC updates Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Created attachment 133779 [details] [review] Bug 30446: (QA follow-up) Report back on problematic situations Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
(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 ;)
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});
Created attachment 133883 [details] [review] 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>
Created attachment 133884 [details] [review] Bug 30449: DBIC updates Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Created attachment 133885 [details] [review] 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>
Working well, a good job resyncing. Thanks PQA
Pushed to master for 22.05, thanks to everybody involved [U+1F984]
Pushed to 21.11.x for 21.11.06
See bug 30899. Patch forthcoming on that report. No need to push this further down. Closing the report to prevent that.
Architectural change, no documentation update required.