Hbm2ddl.auto=update fails to add cross schema foreign key constraints because it ignores the hibernate.default_schema property and tries to create the constraints as if the tables where in the same schema. Adding @Table(schema="<the schema>") to entities in the default schema solves this issue but this should not be necessary.
This issue appears to only affect hbm2ddl.auto=update. hbm2ddl.auto=create works fine.
I have attached a test case that replicates this issue. It assumes it will have access to a mysql server on localhost accessible with user='root', password=''. If that is the case you should be able to replicate the error by unpacking the attachment and running "mvn spring-boot:run" in the resultant "test" directory.
This will result in the following tables being created: testA.my_object testA.other_object testB.my_object testB.something_different
You will also see some errors logged on the console that show hbm2ddl is attempting to add foreign key constraints to the wrong schema.
HHH000388: Unsuccessful: alter table testB.my_object add constraint FK_ti6qf0litl79l7wniwvvw6bll foreign key (other_object_id) references testB.other_object (id)
There should be a constraint between testB.my_object.other_object_id and testA.other_object.id. However, the following SQL shows no foreign key on testB.my_object because of the error above.
select concat(table_schema, '.', table_name, '.', column_name) as 'foreign key', concat(REFERENCED_TABLE_SCHEMA, '.', referenced_table_name, '.', referenced_column_name) as 'references' from information_schema.key_column_usage where referenced_table_name is not null and table_schema = 'testB' and table_name = 'my_object';
|