Unsuccessful: alter table XXX drop constraint YYY in Hibernate/JPA/HSQLDB standalone
Solution 1
You can ignore these errors. Combination of create-drop
and empty (which is the case always for in-memory) database produces these for every database object it tries to drop. Reason being that there is not any database objects to remove - DROP statements are executed against empty database.
Also with normal permanent database such a errors do come, because Hibernate does not figure out before executing DROP statements does added object exist in database or is it new.
Solution 2
This solution worked for me, as opposed to the other solution that's given. Apparently mileages vary.
This was my exact error:
HHH000389: Unsuccessful: alter table ... drop constraint FK_g1uebn6mqk9qiaw45vnacmyo2 if exists
Table "..." not found; SQL statement: ...
This is my solution, overriding the H2 dialect:
package com.totaalsoftware.incidentmanager;
import org.hibernate.dialect.H2Dialect;
/**
* Workaround.
*
* @see https://hibernate.atlassian.net/browse/hhh-7002
*
*/
public class ImprovedH2Dialect extends H2Dialect {
@Override
public String getDropSequenceString(String sequenceName) {
// Adding the "if exists" clause to avoid warnings
return "drop sequence if exists " + sequenceName;
}
@Override
public boolean dropConstraints() {
// We don't need to drop constraints before dropping tables, that just
// leads to error messages about missing tables when we don't have a
// schema in the database
return false;
}
}
Solution 3
The Solution @Sander provided above works for MYSQL too. Just extend MySQL5InnoDBDialect instead like below:
import org.hibernate.dialect.MySQL5InnoDBDialect;
public class ImprovedMySQLDialect extends MySQL5InnoDBDialect {
@Override
public String getDropSequenceString(String sequenceName) {
// Adding the "if exists" clause to avoid warnings
return "drop sequence if exists " + sequenceName;
}
@Override
public boolean dropConstraints() {
// We don't need to drop constraints before dropping tables, that just leads to error
// messages about missing tables when we don't have a schema in the database
return false;
}
}
Then in your datasource file change the following line:
dialect = org.hibernate.dialect.MySQL5InnoDBDialect
to
dialect = my.package.name.ImprovedMySQLDialect
Solution 4
Just set dbCreate="update"
, and the errors go away immediately.
See: https://stackoverflow.com/a/31257468/715608
Solution 5
The annoying error messages became more obnoxious stack traces at the start of every test using an in-memory database with HSQLDB and hbm2ddl.auto=create-drop.
The response to an HSQLDB bug report suggested the use of DROP TABLE ... CASCADE rather than DROP CONSTRAINT IF EXISTS. Unfortunately the HSQLDB syntax for drop table is DROP TABLE <table> [IF EXISTS] [RESTRICT | CASCADE];
Hibernate Dialect does not provide an easy mechanism for a Dialect to specify CASCADE following the final IF EXISTS clause. I wrote a bug for this limitation.
However, I was able to overcome the issue by creating a custom Dialect as follows:
public class HsqlDialectReplacement extends HSQLDialect {
@Override
public String getDropTableString( String tableName ) {
// Append CASCADE to formatted DROP TABLE string
final String superDrop = super.getDropTableString( tableName );
return superDrop + " cascade";
}
@Override
public boolean dropConstraints() {
// Do not explicitly drop constraints, use DROP TABLE ... CASCADE
return false;
}
@Override
public Exporter<Table> getTableExporter() {
// Must override TableExporter because it also formulates DROP TABLE strings
synchronized( this ) {
if( null == overrideExporter ) {
overrideExporter = new HsqlExporter( super.getTableExporter() );
}
}
return overrideExporter;
}
private Exporter<Table> overrideExporter = null;
private static class HsqlExporter implements Exporter<Table> {
HsqlExporter( Exporter<Table> impl ) {
this.impl = impl;
}
@Override
public String[] getSqlCreateStrings( Table exportable, Metadata metadata ) {
return impl.getSqlCreateStrings( exportable, metadata );
}
@Override
public String[] getSqlDropStrings( Table exportable, Metadata metadata ) {
final String[] implDrop = impl.getSqlDropStrings( exportable, metadata );
final String[] dropStrings = new String[implDrop.length];
for( int i=0; i<implDrop.length; ++i ) {
dropStrings[i] = implDrop[i] + " cascade";
}
return dropStrings;
}
private final Exporter<Table> impl;
};
}
Comments
-
Jérôme Verstrynge almost 2 years
I am trying to run some Hibernate/JPA examples using an in-memory HSQL DB. The error message I get is the following:
13:54:21,427 ERROR SchemaExport:425 - HHH000389: Unsuccessful: alter table ReferringItem_map drop constraint FK5D4A98E0361647B8 13:54:21,427 ERROR SchemaExport:426 - user lacks privilege or object not found: PUBLIC.REFERRINGITEM_MAP 13:54:21,427 ERROR SchemaExport:425 - HHH000389: Unsuccessful: alter table ReferringItem_myCollection drop constraint FK75BA3266361647B8 13:54:21,427 ERROR SchemaExport:426 - user lacks privilege or object not found: PUBLIC.REFERRINGITEM_MYCOLLECTION 13:54:21,428 ERROR SchemaExport:425 - HHH000389: Unsuccessful: alter table ReferringItem_myList drop constraint FK6D37AA66361647B8 13:54:21,428 ERROR SchemaExport:426 - user lacks privilege or object not found: PUBLIC.REFERRINGITEM_MYLIST 13:54:21,428 ERROR SchemaExport:425 - HHH000389: Unsuccessful: alter table ReferringItem_mySet drop constraint FK3512699A361647B8 13:54:21,429 ERROR SchemaExport:426 - user lacks privilege or object not found: PUBLIC.REFERRINGITEM_MYSET
The corresponding class is:
@Entity public class ReferringItem implements Serializable { @Id private long id; @ElementCollection private Collection<AnEmbeddable> myCollection = new ArrayList<AnEmbeddable>(); @ElementCollection(fetch=FetchType.EAGER) private Set<Long> mySet = new HashSet<Long>(); @ElementCollection(targetClass=String.class) private List myList = new ArrayList(); @ElementCollection private Map<String,AnEmbeddable> map = new HashMap<String,AnEmbeddable>(); public ReferringItem() { } // Setters & Getters }
The embeddable is:
@Embeddable public class AnEmbeddable implements Serializable { private String s; public AnEmbeddable() { } public String getS() { return s; } public void setS(String s) { this.s = s; } }
My
persistence.xml
:<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd" version="2.0"> <persistence-unit name="JPA" transaction-type="RESOURCE_LOCAL"> <provider>org.hibernate.ejb.HibernatePersistence</provider> <class>com.jverstry.jpa.AuthorizedTypes.AuthorizedTypes</class> <class>com.jverstry.jpa.AuthorizedTypes.OtherEntity</class> <class>com.jverstry.jpa.AuthorizedTypes.SomeEmbeddable</class> <properties> <property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbcDriver"/> <property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:mem:testdb"/> <property name="javax.persistence.jdbc.user" value="sa"/> <property name="javax.persistence.jdbc.password" value=""/> <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/> <property name="hibernate.hbm2ddl.auto" value="create-drop"/> </properties> </persistence-unit> </persistence>
I am on Hibernate
4.1.5.Final
andHSQLDB 2.2.8
.Does anyone know what is causing this issue and how to solve it?
-
Jérôme Verstrynge over 11 yearsGreat, I though it was an issue in my code I could not find. Many thanks.
-
Eugen over 11 yearsThe issue is hibernate.onjira.com/browse/HHH-7002 and I'm seeing the same behaviour with MySQL, so not only in memory DBs.
-
TahitianGabriel almost 11 yearsThis issue seems to be fixed in hibernate 4.3.0 beta 4, so I guess it should be in the next release. hibernate.atlassian.net/browse/HHH-7002
-
Sander Verhagen over 10 yearsI upgraded to
4.3.0.Final
and I'm seeing still "the" error, or in my case maybe a slightly different one:HHH000389: Unsuccessful: alter table ... drop constraint FK_g1uebn6mqk9qiaw45vnacmyo2 if exists
andTable "..." not found; SQL statement: ...
-
Sander Verhagen over 10 yearsThe JIRA issue points to a GitHub commit which contains changes to the HSQLDB dialect. This question was about H2, and @Eugen mentioned MySQL. Not the same thing.
-
gclaussn over 9 yearsWorks also in the exact same way for hsqldb (org.hibernate.dialect.HSQLDialect)
-
PathOfNeo over 9 yearsand where do you add your class in a grails app (obvious it's not in any of the controllers/domains/views folders?
-
Emmanuel John over 9 yearsI usually put it in src/groovy. Just make sure you use the right package name.
-
PathOfNeo over 9 yearsthx i did that. Unfortunately I gave up after X hours of trying everything possible. I have accepted the fact that i need to drop/create my schema manually by hand in MySQL every time i change a relationship in grails domain classes
-
borjab over 9 yearsI work with HSQLDB 2.3.2 and Hibernate 4.3.8 and I still get the warning.
-
rhmiller_ic over 8 yearsNote that if you are using something like jdbc:h2:mem:play;MODE=MYSQL your dialect is still the H2 dialect.
-
McCoy about 6 yearsI can't get this compiled, don't know where to import Exporter from. My Hibernate version is 4.3.8
-
McCoy about 6 yearsI work with HSQLDB 2.4.0 and Hibernate 4.3.8, still get the error
-
lijat over 5 yearsI got this to compile by using the imports used here github.com/Bato/demo-jpa/blob/master/src/main/java/eu/mh/jpa/… this seems like a version of this code. The imports where: import org.hibernate.boot.Metadata; import org.hibernate.dialect.HSQLDialect; import org.hibernate.mapping.Table; import org.hibernate.tool.schema.spi.Exporter;
-
lijat over 5 yearsSadly this did not work, it silenced the errors but caused other errors down the line. Depending on your db chema it may or may not work for you.
-
GabrielOshiro over 5 years"You can ignore these error." every time you ignore an error, a baby panda dies... #truestory