Bug 22642

Summary: DB upgrade 18.06.00.005 can fail
Product: Koha Reporter: Fridolin Somers <fridolin.somers>
Component: DatabaseAssignee: Martin Renvoize <martin.renvoize>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: critical    
Priority: P1 - high CC: jonathan.druart, katrin.fischer, lucas, martin.renvoize, nick, wizzyrea
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: Small patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
19.05.00, 18.11.05
Bug Depends on: 12395    
Bug Blocks: 22658    
Attachments: Bug 22642: Corrections to updatedatebase for bug 12395
Bug 22642: Corrections to updatedatebase for bug 12395
Bug 22642: Corrections to updatedatebase for bug 12395

Description Fridolin Somers 2019-04-04 14:40:21 UTC
DB upgrade 18.06.00.005 from Bug 12395 can fail :

BD::mysql::db do failed: Cannot add or update a child row: a foreign key constraint fails (`koha`.`aqorders`, CONSTRAINT `aqorders_created_by` FOREIGN KEY (`created_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE) [for Statement "UPDATE aqorders, aqbasket SET aqorders.created_by = aqbasket.authorisedby  WHERE aqorders.basketno = aqbasket.basketno AND aqorders.created_by IS NULL;"] at ./installer/data/mysql/updatedatabase.pl line 16131.

This upgrade adds a constraint on aqorders.created_by linked to borrowers.borrowernumber.
Then copies aqbasket.authorisedby into aqorders.created_by.
But aqbasket.authorisedby as no constraint so its value may not be an existing borrowernumber (deleted borrower).
Comment 1 Fridolin Somers 2019-04-04 14:44:17 UTC
Correct manually with :

update aqbasket left join borrowers on (aqbasket.authorisedby = borrowers.borrowernumber) set aqbasket.authorisedby = NULL where borrowers.borrowernumber is null;
Comment 2 Martin Renvoize 2019-04-08 17:05:32 UTC
Created attachment 87535 [details] [review]
Bug 22642: Corrections to updatedatebase for bug 12395

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 3 Liz Rea 2019-04-08 19:36:29 UTC
Created attachment 87552 [details] [review]
Bug 22642: Corrections to updatedatebase for bug 12395

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Signed-off-by: Liz Rea <wizzyrea@gmail.com>
Comment 4 Martin Renvoize 2019-04-09 06:31:15 UTC
Confused
Comment 5 Liz Rea 2019-04-10 14:30:32 UTC
Sorry Martin, I just wanted to check with you before I REALLY DOUBLE COMMITTED to this SO, I was confused about the version but we have worked ourselves out. Back to SO it goes!
Comment 6 Katrin Fischer 2019-04-11 14:41:59 UTC
Created attachment 87774 [details] [review]
Bug 22642: Corrections to updatedatebase for bug 12395

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Signed-off-by: Liz Rea <wizzyrea@gmail.com>

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 7 Nick Clemens 2019-04-12 11:47:51 UTC
Awesome work all!

Pushed to master for 19.05
Comment 8 Martin Renvoize 2019-04-16 13:12:18 UTC
Pushed to 18.11.x for 18.11.05
Comment 9 Lucas Gass 2019-04-23 13:43:56 UTC
missing dependency, won't backport to 18.05.x series
Comment 10 Jonathan Druart 2019-04-29 01:58:17 UTC
Too late for installs that upgraded already?