Bug 22642 - DB upgrade 18.06.00.005 can fail
Summary: DB upgrade 18.06.00.005 can fail
Status: Pushed to Stable
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: master
Hardware: All All
: P1 - high critical (vote)
Target Milestone: ---
Assignee: Martin Renvoize
QA Contact: Testopia
URL:
Whiteboard:
Keywords:
Depends on: 12395
Blocks: 22658
  Show dependency treegraph
 
Reported: 2019-04-04 14:40 UTC by Fridolin SOMERS
Modified: 2019-04-29 01:58 UTC (History)
6 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Bot Control: ---
When did the bot last check this:
Who signed the patch off:
Text to go in the release notes:
Version(s) released in:
19.05.00, 18.11.05


Attachments
Bug 22642: Corrections to updatedatebase for bug 12395 (1.60 KB, patch)
2019-04-08 17:05 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 22642: Corrections to updatedatebase for bug 12395 (1.64 KB, patch)
2019-04-08 19:36 UTC, Liz Rea
Details | Diff | Splinter Review
Bug 22642: Corrections to updatedatebase for bug 12395 (1.70 KB, patch)
2019-04-11 14:41 UTC, Katrin Fischer
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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?