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.
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>
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)
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.
Ditto for the other check constraint.. that's also upon a foreign key column then :)
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>
Nice work! Pushed to master for 19.11.00
backported to 19.05.x for 19.05.07
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>