Bug 22642

Summary:	DB upgrade 18.06.00.005 can fail
Product:	Koha	Reporter:	Fridolin Somers <fridolin.somers>
Component:	Database	Assignee:	Martin Renvoize (ashimema) <martin.renvoize>
Status:	CLOSED FIXED	QA Contact:	Testopia <testopia>
Severity:	critical
Priority:	P1 - high	CC:	jonathan.druart, 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
Circulation function:
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 (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?