[Koha-bugs] [Bug 8915] New: Referential integrity constraints and InnoDB vs MyISAM storage engine

[bugzilla-daemon at bugs.koha-community.org]

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=8915

          Priority: P5 - low
 Change sponsored?: ---
            Bug ID: 8915
          Assignee: gmcharlt at gmail.com
           Summary: Referential integrity constraints and InnoDB vs MyISAM
                    storage engine
          Severity: enhancement
    Classification: Unclassified
                OS: All
          Reporter: frederic at tamil.fr
          Hardware: All
            Status: NEW
           Version: rel_3_10
         Component: Architecture, internals, and plumbing
           Product: Koha

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.

-- 
You are receiving this mail because:
You are watching all bug changes.