Bug 23995 - Check constraints are supported differently by MySQL and MariaDB so we should remove them for now.
Summary: Check constraints are supported differently by MySQL and MariaDB so we should...
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Martin Renvoize
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks: 34428 23996
  Show dependency treegraph
 
Reported: 2019-11-08 10:26 UTC by Martin Renvoize
Modified: 2023-09-01 14:55 UTC (History)
2 users (show)

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


Attachments
Bug 23995: Remove CHECK constraints from kohastructure (2.40 KB, patch)
2019-11-08 10:29 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 23995: Remove CHECK constraints from kohastructure (2.47 KB, patch)
2019-11-08 10:38 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 23995: (19.05.x follow-up) Corrections for backport error (1.92 KB, patch)
2020-01-10 15:16 UTC, Martin Renvoize
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Martin Renvoize 2019-11-08 10:26:46 UTC
We had two check constraints in kohastructure.. MariaDB and MySQL have now both implimented support for such constraints, but decided to use different, incompatible, syntaxes.  

As such, we should remove the current check constraints until a time when we have a better understanding on how to work around these differences.
Comment 1 Martin Renvoize 2019-11-08 10:29:01 UTC
Created attachment 95191 [details] [review]
Bug 23995: Remove CHECK constraints from kohastructure

MySQL8 and MariaDB10.3 impliment check constraints but utilise
different, incompatible, syntaxes to do so.

This patch removes the check constraints entirely whilst we work on a
better solution to work around the incompatabilities of the two DBs.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 2 Martin Renvoize 2019-11-08 10:33:32 UTC
Mysql8 Manual:

`Foreign key referential actions (ON UPDATE, ON DELETE) are prohibited on columns used in CHECK constraints. Likewise, CHECK constraints are prohibited on columns used in foreign key referential actions.`

This is why the accountline check constraint must be dropped to support mysql8 (no such requirement in mariadb I believe)
Comment 3 Jonathan Druart 2019-11-08 10:34:41 UTC
Error is:
DBD::mysql::st execute failed: Column 'frombranch' cannot be used in a check constraint 'transport_cost_chk_1': needed in a foreign key constraint 'transport_cost_ibfk_1' referential action. at /usr/share/perl5/DBIx/RunSQL.pm line 273.
Comment 4 Martin Renvoize 2019-11-08 10:37:48 UTC
Ditto for the other check constraint.. that's also upon a foreign key column then :)
Comment 5 Jonathan Druart 2019-11-08 10:38:38 UTC
Created attachment 95192 [details] [review]
Bug 23995: Remove CHECK constraints from kohastructure

MySQL8 and MariaDB10.3 impliment check constraints but utilise
different, incompatible, syntaxes to do so.

This patch removes the check constraints entirely whilst we work on a
better solution to work around the incompatabilities of the two DBs.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 6 Martin Renvoize 2019-11-08 11:23:47 UTC
Nice work!

Pushed to master for 19.11.00
Comment 7 Lucas Gass 2020-01-09 22:38:47 UTC
backported to 19.05.x for 19.05.07
Comment 8 Martin Renvoize 2020-01-10 15:16:00 UTC
Created attachment 97230 [details] [review]
Bug 23995: (19.05.x follow-up) Corrections for backport error

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>