Hibernate Object Mapping causes SQL Error (errno: 150) with Liquibase

Question

I'm facing the following issue:

For my Java project I'm using the SpringFramework,Maven and for Database management Hibernate 3.6 and Liquibase for change management.

Now I'm facing an issue that bugs my mind. It is related to the foreign key constraints that shall be created from Hibernate.

2 Classes have Object entities which have an relation. Watch the following code:

@Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public class SearchSingle extends OfferSearch implements Comparable<SearchSingle>,   OfferSingle {
    private static final long serialVersionUID = 7618785527154737982L;

@ManyToOne
private Route routeOutbound;

@ManyToOne
private Route routeWayBack;
...
...
}

Now the other class has the corresponding objects:

@JsonAutoDetect
@Entity
public class Route extends PersistentObject {

private static final long serialVersionUID = -4611710805557036851L;
@OneToMany(mappedBy = "routeOutbound")
@Cascade(CascadeType.SAVE_UPDATE)
private List<SearchSingle> searchesOutbound;

@OneToMany(mappedBy = "routeWayBack")
@Cascade(CascadeType.SAVE_UPDATE)
private List<SearchSingle> searchesWayBack;

...
}

See here the resulting error I got in the tomcat log:

SEVERE 26.07.12 10:12:liquibase: Change Set classpath:dbchangelog.xml::1343227727949-11::mirco (generated) failed. Error: Error executing SQL ALTER TABLE ridesingle ADD CONSTRAINT FKD5C95340EE9D2F8 FOREIGN KEY (routeoutbound) REFERENCES route (id) ON UPDATE NO ACTION ON DELETE NO ACTION: Can't create table 'backendtest.#sql-454_5f' (errno: 150) liquibase.exception.DatabaseException: Error executing SQL ALTER TABLE ridesingle ADD CONSTRAINT FKD5C95340EE9D2F8 FOREIGN KEY (routeoutbound) REFERENCES route (id) ON UPDATE NO ACTION ON DELETE NO ACTION: Can't create table 'backendtest.#sql-454_5f' (errno: 150)

What confuses me most ist the "can't create table: backendtest" . That makes no sense at all because backendtest is the database name and no table. This error comes up when I redeploy the project and Liquibase tries to map the changes from the migrationdb to the database used by the project.

Error such as Can't create table 'backendtest.#sql-454_5f' may indicate some conflict between constraints. For example, you may need to delete old constraint before creating a new one. Though I'm not sure how Liquibase handles it. — axtavt
Hi @axtvat . Thx 4 ur answer. You are right this indeed is conatrint related. But liquibase should handle this. I made dozens of constraints like this and never had an issue...i guess i got to spend some more hours analyzing what went wrong... — m0rb

JimmyB JimmyB · Accepted Answer · 2012-12-18T07:18:46

Maybe there is a problem with Liquibase or its configuration: The fact that some DBMSs use the # prefix for temporary tables makes me believe that either Liquibase or MySQL or some layer in between tries to perform the ALTER TABLE by a 'copy-drop-create-copy' procedure creating a temporary table in the process.

Does the user issuing the ALTER TABLE have the CREATE [TEMPORARY] TABLE privilege in the backendtest database?

Hibernate Object Mapping causes SQL Error (errno: 150) with Liquibase

1 Answers