An increasing number of Koha functionalities rely on database level referential integrity constraints. For example, see bug 1963, when a biblio record is deleted, it is also deleted in all the virtual shelves containing it. If you do: SHOW CREATE TABLE virtualshelfcontents you see this specific constraint: CONSTRAINT shelfcontents_ibfk_2 FOREIGN KEY biblionumber REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE, Depending on MySQL storage engine, constraints are supported or not. InnoDB support referential integrity constraints, MyISAM doesn't. Since version 5.1, MySQL default storage engine is InnoDB. Prior to this version, MyISAM was the default engine. So depending on various parameters, a Koha DB can contain MyISAM tables, therefore without integrity constraints, when those constraints are required by Koha for proper operations. Those parameters are: - Old Koha DB can have been created at first place with MyISAM engine, and never upgraded to InnoDB. - The upgrade to InnoDB can have been done later, but some constraints have been added by updatedatabase.pl when tables was still MyISAM. - The InnoDB can be silently disabled on the server: DBA choice, bad my.cnf configuration, etc.
Created attachment 12799 [details] [review] Script fixing missing constraints
Created attachment 12804 [details] [review] Script fixing missing constraints
Standalone script, does what it says it does, and needs a confirmation switch to run.
Created attachment 13166 [details] [review] Bug 8915 A script fixing missing MySQL contraints Help: fix_mysql_contraints.pl -h Signed-off-by: Chris Cormack <chris@bigballofwax.co.nz>
Created attachment 13342 [details] [review] Bug 8915: Followup FIX QA issues Before this patch: * misc/maintenance/fix_mysql_constraints.pl FAIL pod FAIL *** ERROR: unterminated B<...> in file misc/maintenance/fix_mysql_constraints.pl forbidden patterns OK valid OK critic FAIL "require" statement with library name as string at line 25, column 12. Use a bareword instead. + I add a die on the open statement
Hi Chris and Frédéric, Could one of you confirm that you agree with my patch ?
Created attachment 13355 [details] [review] Bug 8915: Followup FIX QA issues Before this patch: * misc/maintenance/fix_mysql_constraints.pl FAIL pod FAIL *** ERROR: unterminated B<...> in file misc/maintenance/fix_mysql_constraints.pl forbidden patterns OK valid OK critic FAIL "require" statement with library name as string at line 25, column 12. Use a bareword instead. + I add a die on the open statement Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Thanks Kyle. Marked as Passed QA.
This patch has been pushed to master.
Patch pushed to branch 3.10.x
Released in 3.10.0