You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Hannes Schmidt (JIRA)" <ji...@apache.org> on 2010/01/30 21:20:34 UTC
[jira] Created: (OPENJPA-1489) Generated UNIQUE constraint names
and HSQL
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 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.TransactionalTestExecutionListener@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.CollectionDaoTest@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.
[jira] Resolved: (OPENJPA-1489) Generated UNIQUE constraint names
and HSQL
Posted by "Milosz Tylenda (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1489?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Milosz Tylenda resolved OPENJPA-1489.
-------------------------------------
Resolution: Duplicate
Fix Version/s: 2.0.0-beta
1.3.0
Assignee: Milosz Tylenda
> 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
> Assignee: Milosz Tylenda
> Priority: Minor
> Fix For: 1.3.0, 2.0.0-beta
>
>
> 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.TransactionalTestExecutionListener@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.CollectionDaoTest@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.
[jira] Updated: (OPENJPA-1489) Generated UNIQUE constraint names
and HSQL
Posted by "Hannes Schmidt (JIRA)" <ji...@apache.org>.
[ 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.TransactionalTestExecutionListener@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.CollectionDaoTest@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.TransactionalTestExecutionListener@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.CollectionDaoTest@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.TransactionalTestExecutionListener@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.CollectionDaoTest@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.