[
https://issues.apache.org/jira/browse/OPENJPA-1489?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Hannes Schmidt updated OPENJPA-1489:
------------------------------------
Description:
In my unit tests, I'm using the openjpa.jdbc.SynchronizeMappings feature to
have OpenJPA create the schema in an HSQL in-memory database. Two of the tables
in my schema each have a unique constraint that lists the same column names
(foo,bar) in the same order. The generated name for both constraints is
UNQ_foobar. HSQL apparently puts the names of unique constraints into a
schema-wide namespace which causes the CREATE TABLE statement for the second
table to fail with "constraint already exist".
@Entity
@Table( uniqueConstraints = { @UniqueConstraint( columnNames = { "foo", "bar" }
) } )
public class X {
...
private String foo;
private long bar;
...
}
@Entity
@Table( uniqueConstraints = { @UniqueConstraint( columnNames = { "foo", "bar" }
) } )
public class Y {
...
private String foo;
private long bar;
...
}
912 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 1720095856>
executing stmnt 1211647530 CREATE TABLE X ( ... , foo VARCHAR(22) NOT NULL, ...
, bar BIGINT NOT NULL, PRIMARY KEY (id), CONSTRAINT UNQ_foobar UNIQUE (foo,
bar))
915 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 1720095856> [3 ms]
spent
915 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 579409826>
executing stmnt 853942561 CREATE TABLE Y ( ... , foo VARCHAR(22) NOT NULL, ...
, bar BIGINT NOT NULL, PRIMARY KEY (id), CONSTRAINT UNQ_foobar UNIQUE (foo,
bar))
920 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 579409826> [5 ms]
spent
Jan 30, 2010 11:46:16 AM org.springframework.test.context.TestContextManager
beforeTestMethod
WARNING: Caught exception while allowing TestExecutionListener
[org.springframework.test.context.transaction.transactionaltestexecutionliste...@2773a64a]
to process 'before' execution of test method [public void
com.eyealike.ps.core.test.CollectionDaoTest.setup()] for test instance
[com.eyealike.ps.core.test.collectiondaot...@19aa5882]
org.springframework.transaction.CannotCreateTransactionException: Could not
open JPA EntityManager for transaction; nested exception is
<openjpa-1.2.2-r422266:898935 nonfatal general error>
org.apache.openjpa.persistence.PersistenceException: Constraint already exists:
UNQ_OWNERIDKEY in statement [CREATE TABLE Y ... } [code=-60, state=S0011]
Note that I tweaked my sample code and log trace a little to simplify the test
case.
I didn't try to reproduce this with OpenJPA 2.0.0 yet. From looking at the code
in the 2.0.0 trunk, I can tell that this part has been rewritten completely and
this issue might not apply to 2.0.0. I also don't know whether HSQL is
violating the SQL standard by requiring unique constaint names to be
schema-unique, not just table-unique.
What works for me as a workaround is to reorder the columns in the constraint
on the second table.
Assuming this is not a bug in HSQL and assuming it also reproduces in 2.0.0
beta, I'd naively prefix constraint names with the table name but I'm no
specialist in either SQL or JPA ...
was:
In my unit tests, I'm using the openjpa.jdbc.SynchronizeMappings feature to
have OpenJPA create the schema in an HSQL in-memory database. Two of the tables
in my schema each have a unique constraints that list the same column names
(foo,bar) in the same order. The generated name for both constraints is
UNQ_foobar. HSQL apparently puts the names of unique constraints into a
schema-wide namespace which causes the CREATE TABLE statement for the second
table to fail with "constraint already exist".
@Entity
@Table( uniqueConstraints = { @UniqueConstraint( columnNames = { "foo", "bar" }
) } )
public class X {
...
private String foo;
private long bar;
...
}
@Entity
@Table( uniqueConstraints = { @UniqueConstraint( columnNames = { "foo", "bar" }
) } )
public class Y {
...
private String foo;
private long bar;
...
}
912 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 1720095856>
executing stmnt 1211647530 CREATE TABLE X ( ... , foo VARCHAR(22) NOT NULL, ...
, bar BIGINT NOT NULL, PRIMARY KEY (id), CONSTRAINT UNQ_foobar UNIQUE (foo,
bar))
915 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 1720095856> [3 ms]
spent
915 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 579409826>
executing stmnt 853942561 CREATE TABLE Y ( ... , foo VARCHAR(22) NOT NULL, ...
, bar BIGINT NOT NULL, PRIMARY KEY (id), CONSTRAINT UNQ_foobar UNIQUE (foo,
bar))
920 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 579409826> [5 ms]
spent
Jan 30, 2010 11:46:16 AM org.springframework.test.context.TestContextManager
beforeTestMethod
WARNING: Caught exception while allowing TestExecutionListener
[org.springframework.test.context.transaction.transactionaltestexecutionliste...@2773a64a]
to process 'before' execution of test method [public void
com.eyealike.ps.core.test.CollectionDaoTest.setup()] for test instance
[com.eyealike.ps.core.test.collectiondaot...@19aa5882]
org.springframework.transaction.CannotCreateTransactionException: Could not
open JPA EntityManager for transaction; nested exception is
<openjpa-1.2.2-r422266:898935 nonfatal general error>
org.apache.openjpa.persistence.PersistenceException: Constraint already exists:
UNQ_OWNERIDKEY in statement [CREATE TABLE Y ... } [code=-60, state=S0011]
Note that I tweaked my sample code and log trace a little to simplify the test
case.
I didn't try to reproduce this with OpenJPA 2.0.0 yet. From looking at the code
in the 2.0.0 trunk, I can tell that this part has been rewritten completely and
this issue might not apply to 2.0.0. I also don't know whether HSQL is
violating the SQL standard by requiring unique constaint names to be
schema-unique, not just table-unique.
What works for me as a workaround is to reorder the columns in the constraint
on the second table.
Assuming this is not a bug in HSQL and assuming it also reproduces in 2.0.0
beta, I'd naively prefix constraint names with the table name but I'm no
specialist in either SQL or JPA ...
Fixed spelling
> Generated UNIQUE constraint names and HSQL
> -------------------------------------------
>
> Key: OPENJPA-1489
> URL: https://issues.apache.org/jira/browse/OPENJPA-1489
> Project: OpenJPA
> Issue Type: Improvement
> Components: sql
> Affects Versions: 1.2.2
> Environment: OpenJPA 1.2.2
> HSQL 1.8.0.10 and HSQL 1.8.1.1 and 2.0.0-rc8
> Mac OS X 10.6.2
> Java 1.6.0_17 Java HotSpot(TM) 64-Bit Server VM (build 14.3-b01-101, mixed
> mode)
> Reporter: Hannes Schmidt
> Priority: Minor
>
> In my unit tests, I'm using the openjpa.jdbc.SynchronizeMappings feature to
> have OpenJPA create the schema in an HSQL in-memory database. Two of the
> tables in my schema each have a unique constraint that lists the same column
> names (foo,bar) in the same order. The generated name for both constraints is
> UNQ_foobar. HSQL apparently puts the names of unique constraints into a
> schema-wide namespace which causes the CREATE TABLE statement for the second
> table to fail with "constraint already exist".
> @Entity
> @Table( uniqueConstraints = { @UniqueConstraint( columnNames = { "foo", "bar"
> } ) } )
> public class X {
> ...
> private String foo;
> private long bar;
> ...
> }
> @Entity
> @Table( uniqueConstraints = { @UniqueConstraint( columnNames = { "foo", "bar"
> } ) } )
> public class Y {
> ...
> private String foo;
> private long bar;
> ...
> }
> 912 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 1720095856>
> executing stmnt 1211647530 CREATE TABLE X ( ... , foo VARCHAR(22) NOT NULL,
> ... , bar BIGINT NOT NULL, PRIMARY KEY (id), CONSTRAINT UNQ_foobar UNIQUE
> (foo, bar))
> 915 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 1720095856> [3
> ms] spent
> 915 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 579409826>
> executing stmnt 853942561 CREATE TABLE Y ( ... , foo VARCHAR(22) NOT NULL,
> ... , bar BIGINT NOT NULL, PRIMARY KEY (id), CONSTRAINT UNQ_foobar UNIQUE
> (foo, bar))
> 920 test TRACE [main] openjpa.jdbc.SQL - <t 7224872, conn 579409826> [5
> ms] spent
> Jan 30, 2010 11:46:16 AM org.springframework.test.context.TestContextManager
> beforeTestMethod
> WARNING: Caught exception while allowing TestExecutionListener
> [org.springframework.test.context.transaction.transactionaltestexecutionliste...@2773a64a]
> to process 'before' execution of test method [public void
> com.eyealike.ps.core.test.CollectionDaoTest.setup()] for test instance
> [com.eyealike.ps.core.test.collectiondaot...@19aa5882]
> org.springframework.transaction.CannotCreateTransactionException: Could not
> open JPA EntityManager for transaction; nested exception is
> <openjpa-1.2.2-r422266:898935 nonfatal general error>
> org.apache.openjpa.persistence.PersistenceException: Constraint already
> exists: UNQ_OWNERIDKEY in statement [CREATE TABLE Y ... } [code=-60,
> state=S0011]
> Note that I tweaked my sample code and log trace a little to simplify the
> test case.
> I didn't try to reproduce this with OpenJPA 2.0.0 yet. From looking at the
> code in the 2.0.0 trunk, I can tell that this part has been rewritten
> completely and this issue might not apply to 2.0.0. I also don't know whether
> HSQL is violating the SQL standard by requiring unique constaint names to be
> schema-unique, not just table-unique.
> What works for me as a workaround is to reorder the columns in the constraint
> on the second table.
> Assuming this is not a bug in HSQL and assuming it also reproduces in 2.0.0
> beta, I'd naively prefix constraint names with the table name but I'm no
> specialist in either SQL or JPA ...
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
