https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |21983 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21983 [Bug 21983] Better handling of deleted biblios on ILL requests -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276 --- Comment #2 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Which version did each of you update from? -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276 --- Comment #4 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- As it says the key is already there, you could try and remove it with SQL then running the update again... that might be a quick fix to get you out of that situation. We need to take a closer look what happened there. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276 --- Comment #6 from Pedro Amorim <pedro.amorim@ptfs-europe.com> --- (In reply to Katrin Fischer from comment #2)

Which version did each of you update from?

The updates I can confirm were successful were from 22.11.06. And I'm not aware of us having any issues from previous versions either. We're not using 23.05 yet. Taking a quick look at this, I can't find any obvious issue.(In reply to SOS from comment #5)

I'm layman could you please help me with the SQL queries and how to run it.

You could run 'SHOW CREATE TABLE illrequests' and verify it matches what's on kohastructure.sql You could also run 'SELECT * FROM illrequests WHERE biblio.biblionumber IS NULL AND illrequests.biblio_id IS NOT NULL' Just to rule out that there aren't any dangling non-existent biblio_ids in your illrequests table. If both the above checks out, maybe just removing the dbrev and doing the update again is an option? The update seems to be idempotent so I'm not sure what happened here. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276 --- Comment #8 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- We went back and forth on the FK a bit, could it be the problem only arises of you are coming from 22.11.05? -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276 --- Comment #10 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Pedro Amorim from comment #9)

Here's what I did: 1) checkout v22.11.05 2) reset_all 3) checkout master 4) updatedatabase

All fine, no issues.

Did the same test again but this time with a non-existent biblionumber in illrequests.biblio_id, in step 1). Still all good and the bilbio_id ended up in deleted_biblio_id as expected.

Nothing obvious stands out as for what would cause this.

Thanks a lot Pedro! So maybe we can downgrade this one. SOS - do you remember any issues or anything unusual about your previous updates? Which version was initially installed? -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276 --- Comment #12 from SOS <aliakbar.petiwala88@gmail.com> --- My query got resolved through ChatGPT instructions, kindly copy-paste the error and ask for solution, it will guide tofurther steps. Maybe it will ask you to drop Foreign key. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|jonathan.druart+koha@gmail. |koha-bugs@lists.koha-commun |com |ity.org QA Contact| |testopia@bugs.koha-communit | |y.org Product|Project Infrastructure |Koha Component|Bugzilla |Installation and upgrade | |(command-line installer) -- 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=34276 --- Comment #15 from Pedro Amorim <pedro.amorim@ptfs-europe.com> --- (In reply to Jonathan Druart from comment #14)

Tomas, Pedro, can you take care of this one please?

I'd love to, but I'm not sure where to go from here! I'm unable to reproduce this no matter what. The atomicupdate looks idempotent enough to me, even if we do follow-up your suggestion JD, "we should add a separate check for the key (one for the FK, one for the key)." how do we make sure this has indeed fixed the issue? -- 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=34276 ivan <ipineiro@isep-cba.edu.ar> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |ipineiro@isep-cba.edu.ar --- Comment #17 from ivan <ipineiro@isep-cba.edu.ar> --- (In reply to Jeremiah from comment #11)

Hi,

I am having the same problem as SOS.

Upgrade to 23.05.00.002 [11:09:02]: Bug 21983 - Deleted biblio handling on ILL ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Duplicate key name 'illrequests_bibfk' at /usr/share/koha/lib/C4/Installer.pm line 741

I am coming from 22.05 I believe.

I tried their suggestion: "ALTER TABLE illrequests DROP KEY illrequests_bibfk;"

I got: Cannot drop index 'illrequests_bibfk': needed in a foreign key constraint

Hi, I tried this suggestion and i got the same: Cannot drop index 'illrequests_bibfk': needed in a foreign key constraint message... So i searched and droped the foreign key constraint first: "ALTER TABLE illrequests DROP FOREIGN KEY fk_illrequests_books;" ( I got some help from ChatGPT :) ) And then droped the index as proposed: "ALTER TABLE illrequests DROP KEY illrequests_bibfk;" And then it worked! but now i have the question of the consequences of dropping the foreign key constraint. Will it impact on some transaction? I guess this is a provisional fix. I appreciate any help you can provide on this matter. Thanks! -- 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=34276 beardst@rlasd.net changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |beardst@rlasd.net --- Comment #19 from beardst@rlasd.net --- I upgraded from 22.05 and came across this issue as well. Actually had 3 issues in the DB that I had to work through independently. For this one here is what worked, had to track down the FK that was blocking the drop of the illrequests_bibfk key ALTER TABLE illrequests DROP FOREIGN KEY illrequests_ibfk_1; ALTER TABLE illrequests DROP KEY illrequests_bibfk; After that I was able to retry the webinstall and it went through. Ran this command to see if the FK constraint was recreated. This is also the command I used to find the FK in the first place SELECT RefCons.constraint_schema, RefCons.table_name, RefCons.referenced_table_name, RefCons.constraint_name, KeyCol.column_name FROM information_schema.referential_constraints RefCons JOIN information_schema.key_column_usage KeyCol ON RefCons.constraint_schema = KeyCol.table_schema AND RefCons.table_name = KeyCol.table_name AND RefCons.constraint_name = KeyCol.constraint_name WHERE RefCons.constraint_schema = 'DATABASE_NAME'; -- 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=34276 dcowens76@gmail.com changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcowens76@gmail.com --- Comment #21 from dcowens76@gmail.com --- Thanks, so reading that thread, I ran these two commands in MySQL: mysql> ALTER TABLE illrequests DROP FOREIGN KEY illrequests_ibfk_1; mysql> ALTER TABLE illrequests DROP KEY illrequests_bibfk; I reran the upgrade script without error. However, the web installer script still shows up rather than the staff interface. -- 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=34276 --- Comment #22 from Fridolin Somers <fridolin.somers@biblibre.com> --- Mmm I do not reproduce with a fresh 23.05.00 install upgraded to 23.05.02 Ah I see Bug 22440 added db_revs/221200026.pl which is exactly like db_revs/230500002.pl from Bug 21983 They both contain the test : foreign_key_exists( 'illrequests', 'illrequests_bibfk' ) -- 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=34276 --- Comment #23 from Fridolin Somers <fridolin.somers@biblibre.com> --- Ah, there has been a change in kohastructure.sql and db_revs/221200026.pl during 23.05.00 release : https://git.koha-community.org/Koha-community/Koha/commit/919197f5171f64dbb7... https://git.koha-community.org/Koha-community/Koha/commit/78cce24e579bbcae67... -- 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=34276 --- Comment #25 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Created attachment 154970 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=154970&action=edit Bug 34276: (bug 21983 follow-up) Fix db rev 23.06.00.002 -- 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=34276 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |jonathan.druart+koha@gmail. |ity.org |com -- 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=34276 --- Comment #28 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- And... remember me to write a test for the upgrade process... -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276 Pedro Amorim <pedro.amorim@ptfs-europe.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=34276 --- Comment #31 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- (In reply to Pedro Amorim from comment #29)

(In reply to Jonathan Druart from comment #26)

...

You should also try to break the upgrade at 23.06.00.002 with the wrong FK name illrequests_ibfk_1 and updatedatabase again.

How exactly, sorry, I don't follow.

Hum, maybe it was wrong. But now wondering: if you run the original query in 230600002 you will end up with 'illrequests_ibfk_1'. Shouldn't we have another atomic update to rename it then? -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276 --- Comment #33 from Pedro Amorim <pedro.amorim@ptfs-europe.com> --- Yes that makes sense, currently, if we: git checkout v22.11.05 reset_all git checkout master updatedatabase koha-mysql kohadev -e 'show create table illrequests' we get: KEY `illrequests_bnfk` (`borrowernumber`), KEY `illrequests_bcfk_2` (`branchcode`), KEY `illrequests_safk` (`status_alias`), KEY `illrequests_bibfk` (`biblio_id`), CONSTRAINT `illrequests_bcfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `illrequests_bnfk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `illrequests_ibfk_1` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `illrequests_safk` FOREIGN KEY (`status_alias`) REFERENCES `authorised_values` (`authorised_value`) ON DELETE SET NULL ON UPDATE CASCADE So those who have already upgraded may or may not still have a illrequests_ibfk_1 lingering. Is this what you mean? -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |m.de.rooy@rijksmuseum.nl --- Comment #35 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Note this change on bug 29390 (waiting in NSO since June): sub foreign_key_exists { - my ( $table_name, $constraint_name ) = @_; + my ( $table_name, $constraint_name, $field_name ) = @_; my $dbh = C4::Context->dbh; my (undef, $infos) = $dbh->selectrow_array(qq|SHOW CREATE TABLE $table_name|); - return $infos =~ m|CONSTRAINT `$constraint_name` FOREIGN KEY|; + return $infos =~ m|CONSTRAINT `$constraint_name` FOREIGN KEY| if $constraint_name; + return $infos =~ m|FOREIGN KEY \(`$field_name`\)| if $field_name; } This allows me to check for a FK without the specific constraint name.. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |BLOCKED QA Contact|testopia@bugs.koha-communit |m.de.rooy@rijksmuseum.nl |y.org | --- Comment #37 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Looking here -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276 --- Comment #39 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Just mentioning: TYPO Add new /ill_requests endopoint -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #155009|0 |1 is obsolete| | --- Comment #40 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 155053 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=155053&action=edit Bug 34276: (bug 21983 follow-up) Fix db rev 23.06.00.002 Signed-off-by: Pedro Amorim <pedro.amorim@ptfs-europe.com> 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=34276 --- Comment #41 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=34276 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to master |Pushed to stable Version(s)|23.11.00 |23.11.00,23.05.04 released in| | --- Comment #43 from Fridolin Somers <fridolin.somers@biblibre.com> --- Pushed to 23.05.x for 23.05.04 -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276 --- Comment #44 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.