[GLASSFISH-701] Sequence generator conflicts with existing primary key values Created: 02/Jun/06  Updated: 01/Nov/12

Status: Reopened
Project: glassfish
Component/s: entity-persistence
Affects Version/s: 9.0pe
Fix Version/s: future release

Type: New Feature Priority: Critical
Reporter: craig_mcc Assignee: tware
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Operating System: All
Platform: All


Issuezilla Id: 701

 Description   

When I build a persistence unit for a set of entity classes that correspond to
an existing database (again, in my case, I was using the TRAVEL example database
that ships with Creator 2 Update 1), I wanted to take advantage of the ability
to autogenerate primary key values. When I deployed an application using these
classes the first time, it created a SEQUENCE table for me (good). But the
starting value it stored in the SEQ_COUNT column was within a range of primary
key values that were used by existing rows in the database (bad), so I got a
"duplicate key" error the first time I tried to insert a new row.

Workaround is to manually adjust the stored sequence number to something larger
than the highest used key in any of the existing tables represented in this
persistence unit. But that is something the JPA architecture should do for me,
so I don't have to.



 Comments   
Comment by marina vatkina [ 07/Jun/06 ]

Hmmm... I'm confused... If you map entities to existing tables, we do not add
any more tables to the mapping, and your SEQUENCE table should already exist. If
you use java2db functionality to create or drop-and-create tables, the new tables
will be created and no pre-existing data should be found in them.

Can you please check?

Comment by marina vatkina [ 13/Jun/06 ]

Looks for me as a user error - the use of create-tables with existing tables.
If this is not the case, please provide a reproduceable test case and reopen the
issue

Comment by craig_mcc [ 14/Jul/06 ]

The use case was listed in the original report, but the important issues are
summarized again here:

  • The data tables already existed, because they were created
    for a previously existing application (in this case the database
    involved is the Travel Center example database that is shipped
    with Sun Java Studio Creator.
  • The SEQUENCE table did not previously exist, because this is the
    first time that these data tables were being accessed via a
    JPA persistence unit.
  • The first time an application is deployed using JPA to access these
    tables, a SEQUENCE table was created. However, no attempt was made
    to ensure that the sequence numbers about to be generated would not
    conflict with primary key values that were already assigned by the
    previous applications that populated these data tables.
  • As a result, any attempt to add a new row in any table supported by
    a JPA entity class runs the risk of creating a collision on a primary
    key value. Since a large number of potential uses for Glassfish
    involves creating JPA entity classes to work with existing database
    tables, this is definitely a sub-optimal result.

This is a P2 only because there is a workaround – go in with the command
console for the database in use and manually tweak the SEQ_COUNT value in the
one and only row of the SEQUENCE table. But that is not a user-friendly solution.

An upcoming version of Creator will feature support for JPA entity classes. I
am not interested in apologizing for this functionality in the underlying database.

Craig McClanahan
Architect, Java Studio Creator

Comment by Sanjeeb Sahoo [ 15/Jul/06 ]

Added myself to cc-list

Comment by ijuma [ 17/Jul/06 ]

Adding myself to cc list.

Comment by marina vatkina [ 17/Jul/06 ]

I feel that there is a disconnect here. You think that there is a bug in an
interesting feature - adding a SEQUENCE table to an existing set of tables, and
I'm saying that such feature does not exist: we either create all of the tables
(or at least try to), or use existing set of tables.

Please check your persistence.xml - does it have property
"toplink.ddl-generation" set to either "drop-and-create-tables" or
"create-tables"?

  • If not, no tables will be created at deployment. No exceptions.
  • If yes, all of them will be created (or at least attempted), again without any
    exception. Which means that those tables which names conflict with the existing
    ones will fail to be created, and those that don't (SEQUENCE in your case) will
    succeed.
    If you still have the server.log around, you should see error messages about
    failed statement execution. Those errors are also (unless there is another bug)
    displayed in the output of the asadmin deploy command.

While partial table creation is an interesting feature, it's not a bug in the
current implementation. Do you want to keep it as a feature request, or change
to a doc bug to add a warning to our docs about possible side effects of using
auto DDL generation?

Meanwhile I'm downgrading the bug.

Regards,
-marina

Comment by craig_mcc [ 17/Jul/06 ]

Yes, the persistence unit was set for create tables – deleting the existing
production tables would not have been a good thing. Yes, SEQUENCE is the only
table that was created; all the rest failed because the tables already existed.

However, the initial sequence number was set to a value such that, on the very
first attempt to insert a new row, I got a duplicate primary key error. Whose
fault was that? It sure isn't the developer's fault that their persistence
framework was not smart enough to recognize it was being configured (for the
first time) on data tables that already head existing rows.

Given that the vast majority of people who try JPA for the first time are going
to be dealing with existing database tables, I am not the only person who is
going to run into this problem. Sounds like I need to go over your head and
lean on Tony a bit to get this issue paid attention to, so that's what I'll do next.

Craig

Comment by marina vatkina [ 25/Jul/06 ]

Feature request

Comment by marina vatkina [ 06/Sep/06 ]

The requested feature will not be spec compliant if implemented by default (i.e.
without any extra hint or vendor specific annotation). Users of such
applications will not know that they are relying on a vendor-specific behavior.

The spec requires (see 9.1.37) that SEQUENCE has an initial value of 1 if the
initialValue is not specified. Does specifying the initialValue in the
annotation solves your problem?

Regards,
-marina

Comment by craig_mcc [ 06/Sep/06 ]

> The requested feature will not be spec compliant if implemented by default (i.e.
> without any extra hint or vendor specific annotation). Users of such
> applications will not know that they are relying on a vendor-specific behavior.

Wow, that's a pretty horrible spec bug, then. I'll forward a pointer to this to
the JSR-220 expert grup.

> The spec requires (see 9.1.37) that SEQUENCE has an initial value of 1 if the
> initialValue is not specified. Does specifying the initialValue in the
> annotation solves your problem?

No.

Consider a scenario where I am building an app in a big enterprise installation
that has multiple stagings for deploying an updated version of an existing app
(test, staging, and production). What are the chances that any single
initialValue will be appropriate for all of them? What happens when I redeploy
later and the "used" values have changed?

As it is, the only thing I can see the developer doing is going into the
SEQUENCE table with a SQL console, and manually changing the value. That means
several things:

  • They have to understand the cause of this problem in the first place.
  • They have to understand how to construct an appropriate SQL UPDATE
    statement.
  • They have to undertand how to use a SQL command tool to update the
    SEQUENCE table, which is implementation specific.

That is not an appropriate solution for a Java EE 5 release that bills itself
as being focused on ease of use.

Craig McClanahan

Comment by marina vatkina [ 12/Feb/07 ]

resetting the default owner

Comment by Mitesh Meswani [ 01/Nov/12 ]

Not targeting for 4.0.

I think a RFE should be filled against JPA spec to discuss this further.

Generated at Sun May 03 08:49:45 UTC 2015 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.