Bug 8915 - Referential integrity constraints and InnoDB vs MyISAM storage engine
Summary: Referential integrity constraints and InnoDB vs MyISAM storage engine
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: 3.10
Hardware: All All
: P5 - low normal (vote)
Assignee: Frédéric Demians
QA Contact: Jonathan Druart
URL:
Keywords:
Depends on:
Blocks: 1963
  Show dependency treegraph
 
Reported: 2012-10-13 07:45 UTC by Frédéric Demians
Modified: 2013-12-05 20:05 UTC (History)
5 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Script fixing missing constraints (4.88 KB, patch)
2012-10-13 14:49 UTC, Frédéric Demians
Details | Diff | Splinter Review
Script fixing missing constraints (5.24 KB, patch)
2012-10-14 15:07 UTC, Frédéric Demians
Details | Diff | Splinter Review
Bug 8915 A script fixing missing MySQL contraints (5.29 KB, patch)
2012-11-01 07:06 UTC, Chris Cormack
Details | Diff | Splinter Review
Bug 8915: Followup FIX QA issues (2.01 KB, patch)
2012-11-09 13:25 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 8915: Followup FIX QA issues (2.10 KB, patch)
2012-11-09 15:42 UTC, Kyle M Hall
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Frédéric Demians 2012-10-13 07:45:50 UTC
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.
Comment 1 Frédéric Demians 2012-10-13 14:49:52 UTC Comment hidden (obsolete)
Comment 2 Frédéric Demians 2012-10-14 15:07:40 UTC Comment hidden (obsolete)
Comment 3 Chris Cormack 2012-11-01 07:06:11 UTC
Standalone script, does what it says it does, and needs a confirmation switch to run.
Comment 4 Chris Cormack 2012-11-01 07:06:25 UTC
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>
Comment 5 Jonathan Druart 2012-11-09 13:25:32 UTC Comment hidden (obsolete)
Comment 6 Jonathan Druart 2012-11-09 13:31:10 UTC
Hi Chris and Frédéric,
Could one of you confirm that you agree with my patch ?
Comment 7 Kyle M Hall 2012-11-09 15:42:21 UTC
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>
Comment 8 Jonathan Druart 2012-11-12 11:39:41 UTC
Thanks Kyle.

Marked as Passed QA.
Comment 9 Jared Camins-Esakov 2012-11-12 15:05:01 UTC
This patch has been pushed to master.
Comment 10 Paul Poulain 2012-11-16 17:27:56 UTC
Patch pushed to branch 3.10.x
Comment 11 Chris Cormack 2013-04-22 08:49:15 UTC
Released in 3.10.0