Schema-validation: missing table [...] error message using flyway for a SQL code generated by Hibernate

Question

I've been trying to deal with some problems regarding Flyway. My situation is the following: I have two Java classes, which I'd like to migrate as two schemas. Let's name them Table and CustomTable. My java classes look like:

@Entity
public class xtable{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
//getters, setters, constructors

@Entity
public class CustomTable{

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String a;
private String b;
private String c;
//getters, setters, constructors

My application.properties:

spring.flyway.url=${env.var1}
spring.flyway.user=${env.var2}
spring.flyway.password=${env.var3}
spring.jpa.hibernate.ddl-auto=validate

//If I use create-drop, hibernate creates it, but after that the validation fails

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect
spring.logging.level.org.hibernate.SQL=debug
spring.jpa.show-sql=true
hibernate.temp.use_jdbc_metadata_defaults=true
spring.flyway.enabled=true

My build.gradle:

plugins {
    id "org.flywaydb.flyway" version "5.2.4"
}

dependencies {
    implementation 'org.flywaydb:flyway-core'
}

The situation is so weird, because it does not even work with the auto-generated SQL code, which I let the program create without flyway.

It looks like this:

    create table custom_table (
  id         bigint not null,
  a          varchar(255),
  b          varchar(255),
  c          varchar(255),
  xtable_id bigint,
  primary key (id)
)
  engine = InnoDB;

create table xtable (
  id                  bigint not null,
  name                varchar(255),
  xtable_id bigint,
  primary key (id)
)
  engine = InnoDB;

alter table custom_table
  add constraint FKep6vooglihwraille12muox9 foreign key (xtable_id) references xtable (id);

alter table xtable
  add constraint FK426q765pr4gv5wux6jaktafqk foreign key (custom_table_id) references custom_table (id);

I also don't understand why Hibernate creates one-one foreign key into each class, but the bigger problem is that I still get the error message

 Schema-validation: missing table [custom_table]

I tried renaming custom_table to customtable (and also renaming the class in Java), but the error message was the same.

Have you ever met the same problem? Have you got any suggestions? I've been working on this problem for - at least - 2 days.

I looked for relevant - or seemingly identical - topics here, but I couldn't find a good solution.

Thank you.

Answer 1

Finally I got the problem. The problem was with inserting multiple foreign keys. (So these two lines):

alter table custom_table
add constraint FKep6vooglihwraille12muox9 foreign key (xtable_id) references xtable (id);

alter table xtable
  add constraint FK426q765pr4gv5wux6jaktafqk foreign key (custom_table_id) references custom_table (id);

I couldn't figure out, though, the reason why Flyway couldn't handle this, but when I recreated the whole structure with the two tables and another one containing the proper ID's, doing exactly the same thing in the whole project, it worked.