Bug 41008

Summary: bulkmarcimport.pl -d broken for authorities
Product: Koha Reporter: Katrin Fischer <katrin.fischer>
Component: Command-line UtilitiesAssignee: Katrin Fischer <katrin.fischer>
Status: Pushed to stable --- QA Contact: Marcel de Rooy <m.de.rooy>
Severity: normal    
Priority: P5 - low CC: bibliothek, dcook, jonathan.druart, m.de.rooy, robin
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=12488
GIT URL: Initiative type: ---
Sponsorship status: --- Comma delimited list of Sponsors:
Crowdfunding goal: 0 Patch complexity: Trivial patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
25.11.00,25.05.06
Circulation function:
Bug Depends on: 35659    
Bug Blocks:    
Attachments: Bug 41008: Fix bulkmarimport.pl -a -d
Bug 41008: Fix bulkmarimport.pl -a -d
Bug 41008: Fix bulkmarimport.pl -a -d
Bug 41008: Fix bulkmarimport.pl -a -d

Description Katrin Fischer 2025-10-14 08:07:36 UTC
In 24.11 the system complains about a FK constraint to the new oai tables when the script is run with -a -d.

DBI Exception: DBD::mysql::db do failed: Cannot truncate a table referenced in a foreign key constraint (`koha_xxx`.`import_oai_authorities`, CONSTRAINT `FK_import_oai_authorities_1` FOREIGN KEY (`authid`) REFERENCES `koha_dlre1`.`auth_header` (`authid`))  at /usr/share/perl5/DBIx/Class/Schema.pm line
Comment 1 Katrin Fischer 2025-10-14 08:21:22 UTC
Created attachment 187852 [details] [review]
Bug 41008: Fix bulkmarimport.pl -a -d

After bug 35659 that added the OAI harvester to Koha the -d switch
for deleting authorities before importing with bulkmarcimport.pl
was broken and resulted in a FK error.

Switching from truncate to DELETE FROM like we already did for items,
biblio etc. fixes the issue.

To test:
- Make sure you have some authorities in your database
- Export some into a file for importing
- Run bulkmarcimport -a -d --file <filename>
- Verify you get the a DB FK constraint error.
- Apply patch
- Repeat steps, verify the script now works as expected again
  * Existing authorities are deleted
  * File is imported
Comment 2 David Cook 2025-10-14 23:52:07 UTC
I'll take a look at this soon
Comment 3 David Cook 2025-10-27 02:50:01 UTC
This does work... although technically do we want to add the following to match its biblio counterparts?

"ALTER TABLE auth_header AUTO_INCREMENT = 1"

--

Personally, I'd advise against doing that, because it could lead to duplicate IDs in the deletedauth_header table... the biblio* counterpart is problematic...

I also note that the index isn't cleared with this delete either so a full re-index is necessary later...

--

I'll attach a signed off patch but leaving "In Discussion" just to get some feedback on those points...
Comment 4 David Cook 2025-10-27 02:50:18 UTC
Created attachment 188458 [details] [review]
Bug 41008: Fix bulkmarimport.pl -a -d

After bug 35659 that added the OAI harvester to Koha the -d switch
for deleting authorities before importing with bulkmarcimport.pl
was broken and resulted in a FK error.

Switching from truncate to DELETE FROM like we already did for items,
biblio etc. fixes the issue.

To test:
- Make sure you have some authorities in your database
- Export some into a file for importing
- Run bulkmarcimport -a -d --file <filename>
- Verify you get the a DB FK constraint error.
- Apply patch
- Repeat steps, verify the script now works as expected again
  * Existing authorities are deleted
  * File is imported

Signed-off-by: David Cook <dcook@prosentient.com.au>
Comment 5 Katrin Fischer 2025-10-27 06:27:09 UTC
My feeling is that this fixes the immediate bug, while your suggestions would be worthwhile enhancements for another bug.
Comment 6 Katrin Fischer 2025-10-27 06:27:36 UTC
In Discussion feels a little strong? Setting back to NSO to get more opinions.
Comment 7 Katrin Fischer 2025-10-27 06:30:34 UTC
(In reply to David Cook from comment #3)
> This does work... although technically do we want to add the following to
> match its biblio counterparts?
> 
> "ALTER TABLE auth_header AUTO_INCREMENT = 1"
> 
> --
> 
> Personally, I'd advise against doing that, because it could lead to
> duplicate IDs in the deletedauth_header table... the biblio* counterpart is
> problematic...

I feel like this would be an addition beyond the current problem of the option throwing an error and as you said, might need more thought to be done properly. We might want to add an option to clean out deleted tables as well, but my feeling is that this is a migration script. Most people use it on an empty database.

> I also note that the index isn't cleared with this delete either so a full
> re-index is necessary later...

I believe that is always the case when using bulkmarcimport and has always been the case. We could add a switch to properly reset the index, but that woudl be a separate bug in my opinion and also needed for bilbiographic.

> 
> I'll attach a signed off patch but leaving "In Discussion" just to get some
> feedback on those points...

In Discussion usually doesn't get any feedback because it falls out of the queues that are being looked at. Until we start argueing, I'll leave as NSO :)
Comment 8 David Cook 2025-10-27 23:15:23 UTC
(In reply to Katrin Fischer from comment #7)
> In Discussion usually doesn't get any feedback because it falls out of the
> queues that are being looked at. Until we start argueing, I'll leave as NSO
> :)

Hehe fair enough.

I don't know. Based on your responses, I think that I'm happy to put this to Signed Off. It works. Maybe a QAer will have a strong opinion one way or another.
Comment 9 Jan Kissig 2025-11-24 14:23:20 UTC
Created attachment 189916 [details] [review]
Bug 41008: Fix bulkmarimport.pl -a -d

After bug 35659 that added the OAI harvester to Koha the -d switch
for deleting authorities before importing with bulkmarcimport.pl
was broken and resulted in a FK error.

Switching from truncate to DELETE FROM like we already did for items,
biblio etc. fixes the issue.

To test:
- Make sure you have some authorities in your database
- Export some into a file for importing
- Run bulkmarcimport -a -d --file <filename>
- Verify you get the a DB FK constraint error.
- Apply patch
- Repeat steps, verify the script now works as expected again
  * Existing authorities are deleted
  * File is imported

Signed-off-by: David Cook <dcook@prosentient.com.au>
Signed-off-by: Jan Kissig <bibliothek@th-wildau.de>
Comment 10 Jan Kissig 2025-11-24 14:23:57 UTC
I linked #12488 where DELETE was introduced to bulkmarcimport for biblios.

In my opinion this fix is sufficient to solve the issue and all other discussions should be put into a separate bug: 
- set auto_increment in auth_header to 1
- also delete/empty the deleted-(auth_header,biblio,biblioitems,items)
Comment 11 Marcel de Rooy 2025-11-28 09:59:34 UTC
Created attachment 190014 [details] [review]
Bug 41008: Fix bulkmarimport.pl -a -d

After bug 35659 that added the OAI harvester to Koha the -d switch
for deleting authorities before importing with bulkmarcimport.pl
was broken and resulted in a FK error.

Switching from truncate to DELETE FROM like we already did for items,
biblio etc. fixes the issue.

To test:
- Make sure you have some authorities in your database
- Export some into a file for importing
- Run bulkmarcimport -a -d --file <filename>
- Verify you get the a DB FK constraint error.
- Apply patch
- Repeat steps, verify the script now works as expected again
  * Existing authorities are deleted
  * File is imported

Signed-off-by: David Cook <dcook@prosentient.com.au>
Signed-off-by: Jan Kissig <bibliothek@th-wildau.de>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 12 Lucas Gass (lukeg) 2025-12-01 15:36:12 UTC
Nice work everyone!

Pushed to main for 25.11
Comment 13 Paul Derscheid 2025-12-03 16:03:11 UTC
Nice work everyone!

Pushed to 25.05.x