[Bug 33671] New: Database update 22.06.00.048 breaks when there are borrower_relationships.guarantor_id with NULL
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Bug ID: 33671 Summary: Database update 22.06.00.048 breaks when there are borrower_relationships.guarantor_id with NULL Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Installation and upgrade (web-based installer) Assignee: koha-bugs@lists.koha-community.org Reporter: katrin.fischer@bsz-bw.de QA Contact: testopia@bugs.koha-community.org CC: gmcharlt@gmail.com Depends on: 30472 We've had 2 reports of this database update breaking the update on bug 30472. The database update altered the table without checking/dealing with existing NULL values: PabloAB: Upgrading to 22.11.02-1 from an updated 22.05 on a Debian 11: Upgrade to 22.06.00.048 [18:33:27]: Bug 30472 - borrower_relationships.guarantor_id NOT NULL ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'guarantor_id': used in a foreign key constraint 'r_guarantor' at /usr/share/koha/lib/C4/Installer.pm line 739 Maybe the same issue as on bug 32399? ------ Mona Panchaud: Hello. While upgrading from 22.05 to 22.11 we are running into the following problem that is related to this change: "Upgrade to 22.06.00.048 [20:19:53]: Bug 30472 - borrower_relationships.guarantor_id NOT NULL ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'guarantor_id': used in a foreign key constraint 'r_guarantor' at /usr/share/koha/lib/C4/Installer.pm line 743" Is there any workaround? What can we do to get the upgrade working? Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30472 [Bug 30472] Field borrower_relationships.guarantor_id should be marked as NOT NULL -- 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=33671 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Summary|Database update |Database update |22.06.00.048 breaks when |22.06.00.048 breaks update |there are |process |borrower_relationships.guar | |antor_id with NULL | -- 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=33671 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |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=33671 --- Comment #1 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- To get it working right away, just do this in mysql: DELETE FROM borrower_relationships WHERE guarantor_id IS NULL This is just bad data. You cannot add such relations (anymore). -- 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=33671 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 the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |ASSIGNED --- Comment #2 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Adding a follow-up to update dbrev accordingly. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- 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=33671 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff Patch complexity|--- |Trivial patch -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #3 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 150629 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=150629&action=edit Bug 33671: Fix for 30472 - Remove null-guarantors These guarantors cannot be added. They are of no use. NOTE FOR QA/RM: I do not think that it is needed to add a new db rev for this change since it happens seldom. But if you think that it is, just copy it to atomicupdate/change bugno. Test plan: [1] Check your table definition and modify it: alter table borrower_relationships modify column `guarantor_id` int(11) NULL; [2] Add an empty guarantor: insert into borrower_relationships (guarantee_id,relationship) values (SOME_PATRON_ID,'father'); [3] Run the dbrev and verify that the inserted record has been removed. [4] Check table definition with 'show create table borrower_relationships'. You should see NOT NULL with guarantor_id. 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=33671 --- Comment #4 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 150630 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=150630&action=edit Bug 33671: Update POD, remove unneeded check in relationship->guarantor [1] Update POD for $patron->guarantor_relationships [2] Remove check from Patron::Relationship->guarantor Test plan: Run t/db_dependent/Koha/Patron.t Run t/db_dependent/Patron/Relationships.t 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=33671 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off --- Comment #5 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Trivial: self SO -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Mona Panchaud <m+koha@mpan.ch> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |m+koha@mpan.ch --- Comment #6 from Mona Panchaud <m+koha@mpan.ch> --- Thanks for taking a look at this and adding a fix! Just to be sure and for my understanding: The data that is deleted is not used by anything else? Will this fix get into the 22.11 branch so that we can try the update again in a straightforward way? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #7 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Mona Panchaud from comment #6)
Thanks for taking a look at this and adding a fix!
Just to be sure and for my understanding: The data that is deleted is not used by anything else?
The new data structure won't allow these to be added, which is why you get the complaint in the update. So it should be safe as this type of data can no longer be generated.
Will this fix get into the 22.11 branch so that we can try the update again in a straightforward way?
The fix is now going through the QA process and when everything is ok the goal will be to also include in the stable branches. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #150629|0 |1 is obsolete| | Attachment #150630|0 |1 is obsolete| | --- Comment #8 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Created attachment 150824 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=150824&action=edit Bug 33671: Fix for 30472 - Remove null-guarantors These guarantors cannot be added. They are of no use. NOTE FOR QA/RM: I do not think that it is needed to add a new db rev for this change since it happens seldom. But if you think that it is, just copy it to atomicupdate/change bugno. Test plan: [1] Check your table definition and modify it: alter table borrower_relationships modify column `guarantor_id` int(11) NULL; [2] Add an empty guarantor: insert into borrower_relationships (guarantee_id,relationship) values (SOME_PATRON_ID,'father'); [3] Run the dbrev and verify that the inserted record has been removed. [4] Check table definition with 'show create table borrower_relationships'. You should see NOT NULL with guarantor_id. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #9 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Created attachment 150825 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=150825&action=edit Bug 33671: Update POD, remove unneeded check in relationship->guarantor [1] Update POD for $patron->guarantor_relationships [2] Remove check from Patron::Relationship->guarantor Test plan: Run t/db_dependent/Koha/Patron.t Run t/db_dependent/Patron/Relationships.t Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to master Version(s)| |23.05.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #10 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Pushed to master for 23.05. Nice work everyone, thanks! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |pablo.bianchi@gmail.com --- Comment #11 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- *** Bug 33118 has been marked as a duplicate of this bug. *** -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Matt Blenkinsop <matt.blenkinsop@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to master |Pushed to stable Version(s)|23.05.00 |23.05.00,22.11.06 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #12 from Matt Blenkinsop <matt.blenkinsop@ptfs-europe.com> --- Nice work everyone! Pushed to stable for 22.11.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Resolution|--- |FIXED CC| |lucas@bywatersolutions.com Status|Pushed to stable |RESOLVED --- Comment #13 from Lucas Gass <lucas@bywatersolutions.com> --- Missing dependencies for 22.05.x, no backport. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #14 from Pablo AB <pablo.bianchi@gmail.com> --- Not sure if it's still mandatory to run before the update the `DELETE FROM borrower_relationships WHERE guarantor_id IS NULL`, but the errors still happen on the same upgrade, from 22.05 to 22.11, but on Installer.pm line 741. The procedure continues till the end, resulting in a broken Koha. If I understand Katrin Fischer comment 7 correctly, these issues shouldn't happen. Now rereading Gass comment 13, "Missing dependencies for 22.05.x, no backport". Why this issue is marked as RESOLVED, if aims directly to 22.05? If the DELETE query solve the issue, couldn't the installer do it transparently? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #15 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Pablo AB from comment #14)
Not sure if it's still mandatory to run before the update the `DELETE FROM borrower_relationships WHERE guarantor_id IS NULL`, but the errors still happen on the same upgrade, from 22.05 to 22.11, but on Installer.pm line 741. The procedure continues till the end, resulting in a broken Koha.
If I understand Katrin Fischer comment 7 correctly, these issues shouldn't happen.
Now rereading Gass comment 13, "Missing dependencies for 22.05.x, no backport".
Why this issue is marked as RESOLVED, if aims directly to 22.05? If the DELETE query solve the issue, couldn't the installer do it transparently?
Hi Pablo, We cannot help you if you do not provide more details. Trying to respond to a few of your comments: The DELETE you mention is now part of the dbrev (220600048). This number 2206 tells you that it is part of 22.11. So when you upgrade from 22.05 to 22.11 you should have it. (Note: obviously this depends on your actual 22.11 version too, use the latest.) If you would upgrade from X to 22.05, there is no need for this fix. So it was not backported to 22.05. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #16 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Pablo responded:
If the fix of this issue not apply to 22.05, then there is a confusion right from the start.
This is again a misunderstanding! The fix is part of 22.11. So if you are upgrading from 22.05 to 22.11 you will be affected. You are referring to 22.11.02. Please use the latest 22.11 now.
Maybe worth to mention that that Koha was a fresh installation on 20.05, and never touched directly the db, so if there was a NULL guarantor_id on borrower_relationships, Koha create it. On a few days I will get the VM rollbacked to check, but on a very similar instance SELECT COUNT(*) FROM borrower_relationships WHERE guarantor_id IS NULL; return zero rows.
Koha does not create guarantors out of the blue. It should be impossible to have FK constraint error on an empty table. Any comments on Koha version, SQL version, SHOW CREATE TABLE borrower_relationships, and its records, etc. may help us further. Please repeat with the latest 22.11 and report back. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #17 from Pablo AB <pablo.bianchi@gmail.com> --- I'm upgrading on a Debian 11 from Koha 22.05.12-1 to 22.11, and it is in this upgrade/transition where I came across this issue. I can't reach 22.11 due to this. SELECT version()\G version(): 10.1.41-MariaDB-0+deb9u1 SELECT * FROM borrower_relationships; Empty set (0.000 sec) SHOW CREATE TABLE borrower_relationships\G *************************** 1. row *************************** Table: borrower_relationships Create Table: CREATE TABLE `borrower_relationships` ( `id` int(11) NOT NULL AUTO_INCREMENT, `guarantor_id` int(11) DEFAULT NULL, `guarantee_id` int(11) NOT NULL, `relationship` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `guarantor_guarantee_idx` (`guarantor_id`,`guarantee_id`), KEY `r_guarantee` (`guarantee_id`), CONSTRAINT `r_guarantee` FOREIGN KEY (`guarantee_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `r_guarantor` FOREIGN KEY (`guarantor_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci The DELETE I mention is only based on comment 1 of this issue. I originally wrote bug 33118, marked as duplicate of this one. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #18 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Pablo AB from comment #17)
SELECT version()\G version(): 10.1.41-MariaDB-0+deb9u1
I would recommend to upgrade your MariaDB server to the level that would be normal for Debian 11. This is older.
SELECT * FROM borrower_relationships; Empty set (0.000 sec)
So there should be really no harm in adding a FK.
SHOW CREATE TABLE borrower_relationships\G *************************** 1. row *************************** Table: borrower_relationships Create Table: CREATE TABLE `borrower_relationships` ( `id` int(11) NOT NULL AUTO_INCREMENT, `guarantor_id` int(11) DEFAULT NULL, `guarantee_id` int(11) NOT NULL, `relationship` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `guarantor_guarantee_idx` (`guarantor_id`,`guarantee_id`), KEY `r_guarantee` (`guarantee_id`), CONSTRAINT `r_guarantee` FOREIGN KEY (`guarantee_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `r_guarantor` FOREIGN KEY (`guarantor_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
This looks perfectly fine. Could you test this on the mysql command line too: ALTER TABLE borrower_relationships CHANGE COLUMN guarantor_id guarantor_id int(11) NOT NULL And revert again with: ALTER TABLE borrower_relationships CHANGE COLUMN guarantor_id guarantor_id int(11) DEFAULT NULL What is the error message, code ? It would not surprise me if it would not fail for a higher MariaDB ? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #19 from Pablo AB <pablo.bianchi@gmail.com> ---
version(): 10.1.41-MariaDB-0+deb9u1 I would recommend to upgrade your MariaDB server to the level that would be normal for Debian 11. This is older.
DB server is on a different host and managed by a different sector. ALTER TABLE borrower_relationships CHANGE COLUMN guarantor_id guarantor_id int(11) NOT NULL; ERROR 1832 (HY000): Cannot change column 'guarantor_id': used in a foreign key constraint 'r_guarantor' I would try to upgrade the MariaDB and test again. At least we find out that this error is not only caused by bad data. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #20 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Pablo AB from comment #19)
ALTER TABLE borrower_relationships CHANGE COLUMN guarantor_id guarantor_id int(11) NOT NULL; ERROR 1832 (HY000): Cannot change column 'guarantor_id': used in a foreign key constraint 'r_guarantor'
Ah. Thats helpful. I think we'd better change the dbrev. Remove the constraint, change column and add constraint back again. You could try that manually too. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Resolution|FIXED |--- Status|RESOLVED |REOPENED -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|REOPENED |ASSIGNED -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|Trivial patch |Small patch Status|ASSIGNED |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #21 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 153367 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=153367&action=edit Bug 33671: (follow-up) Drop FK constraint during column change See also BZ report. Test plan: 1) cp installer/data/mysql/db_revs/220600048.pl installer/data/mysql/atomicupdate/ 2) Run updatedatabase.pl 3) Remove copied file Bonus: Manually remove FK constraint before running updatedatabase. nsert a record with bad guarantor_id into relationships manually. Run the update again. You should not see the message that the constraint is removed. Is the bad record count reported and record removed? 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=33671 --- Comment #22 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Pablo please try these statements manually (copied from dbrev): ALTER TABLE borrower_relationships DROP FOREIGN KEY `r_guarantor`; DELETE borrower_relationships FROM borrower_relationships LEFT JOIN borrowers bo ON bo.borrowernumber=guarantor_id WHERE guarantor_id IS NULL OR bo.borrowernumber IS NULL; ALTER TABLE borrower_relationships CHANGE COLUMN guarantor_id guarantor_id int(11) NOT NULL; ALTER TABLE borrower_relationships ADD FOREIGN KEY `r_guarantor` (guarantor_id) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE; -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #23 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Testers: ONLY apply the LAST patch please! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Summary|Database update |Database update |22.06.00.048 breaks update |22.06.00.048 breaks update |process |process Severity|normal |major -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #150824|Bug 33671: Fix for 30472 - |[PUSHED] Bug 33671: Fix for description|Remove null-guarantors |30472 - Remove | |null-guarantors -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #150825|Bug 33671: Update POD, |[PUSHED] Bug 33671: Update description|remove unneeded check in |POD, remove unneeded check |relationship->guarantor |in relationship->guarantor -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #24 from Tomás Cohen Arazi <tomascohen@gmail.com> --- I got the same error updating on a MariaDB 10.4-run system, but with DBRev 211200048. And I think the right solution is to lift the FK checks (in that case, it is just renaming a FK column, not adding a fk, but the error was the same). -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> 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=33671 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #153367|0 |1 is obsolete| | --- Comment #25 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 153533 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=153533&action=edit Bug 33671: (follow-up) Disable FK check for column change See also BZ report. As suggested by Tomas. NOTE: The dbrev is extended too for removing records with a failing guarantor_id foreign key (theoretically not present obviously). Test plan: 1) cp installer/data/mysql/db_revs/220600048.pl installer/data/mysql/atomicupdate/ 2) Run updatedatabase.pl 3) Remove copied file Bonus: Manually remove FK constraint before running updatedatabase. Insert a record with bad guarantor_id into relationships manually. Run the update again. You should not see the message that the constraint is removed. Is the bad record count reported and record removed? Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> [EDIT] Simplified. No longer removes the FK. Just disables during ALTER. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #26 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Tomás Cohen Arazi from comment #24)
I got the same error updating on a MariaDB 10.4-run system, but with DBRev 211200048. And I think the right solution is to lift the FK checks (in that case, it is just renaming a FK column, not adding a fk, but the error was the same).
Patch adjusted accordingly. Thanks. Moved to SO queue. Would be nice to have a QA stamp still. Tested myself and got feedback from Pablo and Tomas. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |martin.renvoize@ptfs-europe | |.com QA Contact|testopia@bugs.koha-communit |martin.renvoize@ptfs-europe |y.org |.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #153533|0 |1 is obsolete| | --- Comment #27 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 153536 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=153536&action=edit Bug 33671: (follow-up) Disable FK check for column change See also BZ report. As suggested by Tomas. NOTE: The dbrev is extended too for removing records with a failing guarantor_id foreign key (theoretically not present obviously). Test plan: 1) cp installer/data/mysql/db_revs/220600048.pl installer/data/mysql/atomicupdate/ 2) Run updatedatabase.pl 3) Remove copied file Bonus: Manually remove FK constraint before running updatedatabase. Insert a record with bad guarantor_id into relationships manually. Run the update again. You should not see the message that the constraint is removed. Is the bad record count reported and record removed? Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> [EDIT] Simplified. No longer removes the FK. Just disables during ALTER. 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=33671 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA --- Comment #28 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Working for me too, thanks for the follow-up Marcel. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|23.05.00,22.11.06 |23.11.00,23.05.00,22.11.06 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=33671 --- Comment #29 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Pushed to master for 23.11. Nice work everyone, thanks! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |rel_22_11_candidate, | |rel_23_05_candidate -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|23.11.00,23.05.00,22.11.06 |23.11.00,23.05.02,23.05.00, released in| |22.11.06 Status|Pushed to master |Pushed to stable -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #30 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Thanks for all the hard work! Pushed to 23.05.x for the next release -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|rel_23_05_candidate | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Matt Blenkinsop <matt.blenkinsop@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|23.11.00,23.05.02,23.05.00, |23.11.00,23.05.02,23.05.00, released in|22.11.06 |22.11.08,22.11.06 Status|Pushed to stable |Pushed to oldstable -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #31 from Matt Blenkinsop <matt.blenkinsop@ptfs-europe.com> --- Nice work everyone! Pushed to oldstable for 22.11.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 Jeremiah <jhenderson@nsd.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jhenderson@nsd.org --- Comment #32 from Jeremiah <jhenderson@nsd.org> --- Hello, I updated the koha files on 7/19/2023. I am encountering the same error: Upgrade to 22.06.00.048 [22:23:00]: Bug 30472 - borrower_relationships.guarantor_id NOT NULL ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'guarantor_id': used in a foreign key constraint 'r_guarantor' at /usr/share/koha/lib/C4/Installer.pm line 741 Based on the bug comments, it looks like this is something in the works. Any solutions I can try in the meantime? Any help is appreciated. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #33 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Jeremiah from comment #32)
Hello,
I updated the koha files on 7/19/2023. I am encountering the same error:
Upgrade to 22.06.00.048 [22:23:00]: Bug 30472 - borrower_relationships.guarantor_id NOT NULL ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'guarantor_id': used in a foreign key constraint 'r_guarantor' at /usr/share/koha/lib/C4/Installer.pm line 741
Based on the bug comments, it looks like this is something in the works. Any solutions I can try in the meantime?
Any help is appreciated.
I guess you missed the latest change on this dbrev. You could apply the last follow-up to your install or pick the SQL statements from the last patch and run them under mysql client. Especially: SET FOREIGN_KEY_CHECKS=0; ALTER TABLE borrower_relationships CHANGE COLUMN guarantor_id guarantor_id int(11) NOT NULL SET FOREIGN_KEY_CHECKS=1 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #34 from Jeremiah <jhenderson@nsd.org> --- That worked, Thank you! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33671 --- Comment #35 from Pablo AB <pablo.bianchi@gmail.com> --- I upgraded MariaDB from 10.1 to 10.6 and tried to upgrade (from 22.05) again. I successfully passed through the `Bug 30472` section! Still having issues, now on Bug 24860 (Can't create table `koha_myinstance`.`reserves` (errno: 150 "Foreign key constraint is incorrectly formed")). I'll file a new bug. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org