Flyway & MariaDB: SQLException Foreign key constraint is incorrectly formed

Question

I'm currently trying to switch from Hibernate generating my ddl scheme to Flyway migrations. I generated my V1__Initial.sql script by this means. When I run it on an in-memory H2 database everything works fine. But when I try to run it on MariaDB I get the following exception:

Migration V1__Initial.sql failed
--------------------------------
SQL State  : HY000
Error Code : 1005
Message    : (conn=130) Can't create table `booking`.`booking_cancelled_event` (errno: 150 "Foreign key constraint is incorrectly formed")
Location   : db/migration/V1__Initial.sql (C:\code\ajt\backend\target\classes\db\migration\V1__Initial.sql)
Line       : 463
Statement  : alter table booking_cancelled_event
   add constraint FKnxiyj0m730pl9ol2y4qng7577
   foreign key (cancelled_booking_id)
   references cancelled_internal_booking

The corresponding part from the V1__Initial.sql:

alter table booking_cancelled_event
   add constraint FKnxiyj0m730pl9ol2y4qng7577
   foreign key (cancelled_booking_id)
   references cancelled_internal_booking;

Output from SHOW ENGINE InnoDB STATUS;:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2019-06-23 21:07:30 0x1974 Error in foreign key constraint of table `xxx`.`booking_cancelled_event`:
Alter  table `booking`.`booking_cancelled_event` with foreign key constraint failed. Parse error in '
   foreign key (cancelled_booking_id)
   references cancelled_internal_booking' near '
   references cancelled_internal_booking'.

Versions:

• MariaDB 10.4

• Spring Boot 2.1.5.RELEASE

• Flyway 5.2.4

Answer 1

According to the ALTER TABLE page of the MariaDB documentation the syntax of the ADD CONSTRAINT should be:

ADD [CONSTRAINT [symbol]]
    FOREIGN KEY [IF NOT EXISTS] [index_name] (index_col_name,...)
    reference_definition

and according to the CREATE TABLE page of the MariaDB documentation, the syntax of the reference_definition should be:

REFERENCES tbl_name (index_col_name,...)
  [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
  [ON DELETE reference_option]
  [ON UPDATE reference_option]

So the parse error you encounter is most probably because you miss a (index_col_name,...) after the cancelled_internal_booking table name.