Bug 22642 - DB upgrade 18.06.00.005 can fail
Summary: DB upgrade 18.06.00.005 can fail
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: Main
Hardware: All All
: P1 - high critical
Assignee: Martin Renvoize (ashimema)
QA Contact: Testopia
URL:
Keywords:
Depends on: 12395
Blocks: 22658
  Show dependency tree / graph
 
Reported: 2019-04-04 14:40 UTC by Fridolin Somers
Modified: 2020-06-04 20:32 UTC (History)
5 users (show)

See Also:
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
Circulation function:

Attachments
Bug 22642: Corrections to updatedatebase for bug 12395 (1.60 KB, patch)
2019-04-08 17:05 UTC, Martin Renvoize (ashimema) 		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
Show Obsolete (2) View All Add an attachment (proposed patch, testcase, etc.)

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 (ashimema) 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 (ashimema) 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 (kidclamp) 2019-04-12 11:47:51 UTC 
Awesome work all!

Pushed to master for 19.05
Comment 8 Martin Renvoize (ashimema) 2019-04-16 13:12:18 UTC 
Pushed to 18.11.x for 18.11.05
Comment 9 Lucas Gass (lukeg) 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?