Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » SQLServer, IDENTITY and History issue(Problems caused the history customerizer with IDENTITY generator strategy)
SQLServer, IDENTITY and History issue [message #725227] Wed, 14 September 2011 10:06 Go to next message
Sam Ratcliff is currently offline Sam RatcliffFriend
Messages: 3
Registered: September 2011
Junior Member

I am using MS SQL Server 2008 which is automatically detected by EclipseLink on start up. What I am trying to do is to use a history customiser to maintain the log of a table which has an auto-increment identity column as the primary key. The definition of the table and its corresponding history table:

CREATE TABLE [dbo].[organisation_external_identifier](
[externalIdentifierKey] [bigint] IDENTITY(1,1) NOT NULL,
[organisationKey] [bigint] NOT NULL,
[schemeKey] [bigint] NOT NULL,
[identifier] [nvarchar](255) NOT NULL,
CONSTRAINT [PK_organisation_external_identifier] PRIMARY KEY CLUSTERED
[externalIdentifierKey] ASC

CREATE TABLE [hist].[organisation_external_identifier](
[externalIdentifierKey] [bigint] NOT NULL,
[organisationKey] [bigint] NOT NULL,
[schemeKey] [bigint] NOT NULL,
[identifier] [nvarchar](255) NOT NULL,
[startDate] [datetime] NOT NULL,
[endDate] [datetime] NULL,
CONSTRAINT [PK_organisation_external_identifier] PRIMARY KEY CLUSTERED
[externalIdentifierKey] ASC,
[startDate] ASC

When I am trying to insert an organisation into the table I receive the below stack trace.

Exception Description: Error preallocating sequence numbers. The sequence table information is not complete.
at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commitInternal( [eclipselink.jar:2.3.0.v20110604-r9504]
at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit( [eclipselink.jar:2.3.0.v20110604-r9504]
at com.daiwacm.rts.organisation.master.OrganisationMasterImpl.loadTestData( [bin/:na]

Using the debugger to find the trace of execution of where the exception is actually thrown:

NativeSequence(StandardSequence).getGeneratedValue(Accessor, AbstractSession, String) line: 63
NativeSequence(Sequence).getGeneratedValue(Accessor, AbstractSession) line: 225
SequencingManager$NoPreallocation_State.getNextValue(Sequence, AbstractSession) line: 677
SequencingManager.getNextValue(AbstractSession, Class) line: 1067
ClientSessionSequencing.getNextValue(Class) line: 70
ObjectBuilder.assignSequenceNumber(Object, AbstractSession, WriteObjectQuery) line: 360
ObjectBuilder.assignSequenceNumber(WriteObjectQuery) line: 333
StatementQueryMechanism(DatabaseQueryMechanism).updateObjectAndRowWithSequenceNumber() line: 841
StatementQueryMechanism(DatasourceCallQueryMechanism).insertObject() line: 337
StatementQueryMechanism.insertObject() line: 162
HistoryPolicy.logicalInsert(ObjectLevelModifyQuery, boolean) line: 773
HistoryPolicy.postInsert(ObjectLevelModifyQuery) line: 732
ExpressionQueryMechanism(DatabaseQueryMechanism).insertObjectForWrite() line: 489
InsertObjectQuery.executeCommit() line: 80

At the bold line the value of the "object" parameter has the field which holds the identity value correctly set to a valid identifier from the database, yet it still decides that it needs to ask the database again for the identifier which I assume is what is failing.

The eclipselink logging which immediately precedes the thrown exception is:
[EL Finest]: 2011-09-14 10:53:35.633--ClientSession(815749913)--Thread(Thread[qtp1485988078-14,5,main])--Execute query ValueReadQuery(name="SEQ_GEN_IDENTITY" sql="SELECT @@IDENTITY")
[EL Fine]: 2011-09-14 10:53:35.634--ClientSession(815749913)--Connection(341973975)--Thread(Thread[qtp1485988078-14,5,main])--SELECT @@IDENTITY
[EL Finest]: 2011-09-14 10:53:35.634--UnitOfWork(773185343)--Thread(Thread[qtp1485988078-14,5,main])--assign sequence to the object (43 -> DAIWATEST)
[EL Finest]: 2011-09-14 10:53:35.635--UnitOfWork(773185343)--Thread(Thread[qtp1485988078-14,5,main])--Execute query InsertObjectQuery(DAIWATEST~bcp)
[EL Fine]: 2011-09-14 10:53:35.635--ClientSession(815749913)--Connection(341973975)--Thread(Thread[qtp1485988078-14,5,main])--INSERT INTO dbo.organisation_external_identifier (identifier, organisationKey, schemeKey) VALUES (?, ?, ?)
bind => [bcp, 51, 43]
[EL Finest]: 2011-09-14 10:53:35.636--ClientSession(815749913)--Thread(Thread[qtp1485988078-14,5,main])--Execute query ValueReadQuery(name="SEQ_GEN_IDENTITY" sql="SELECT @@IDENTITY")
[EL Fine]: 2011-09-14 10:53:35.636--ClientSession(815749913)--Connection(341973975)--Thread(Thread[qtp1485988078-14,5,main])--SELECT @@IDENTITY
[EL Finest]: 2011-09-14 10:53:35.636--UnitOfWork(773185343)--Thread(Thread[qtp1485988078-14,5,main])--assign sequence to the object (32 -> DAIWATEST~bcp)
[EL Fine]: 2011-09-14 10:53:35.637--ClientSession(815749913)--Connection(341973975)--Thread(Thread[qtp1485988078-14,5,main])--INSERT INTO hist.organisation_external_identifier (externalIdentifierKey, identifier, organisationKey, schemeKey, startDate) VALUES (?, ?, ?, ?, ?)
bind => [32, bcp, 51, 43, 2011-09-14 10:53:35.637]
[EL Finest]: 2011-09-14 10:53:35.638--ClientSession(815749913)--Thread(Thread[qtp1485988078-14,5,main])--Execute query ValueReadQuery(name="SEQ_GEN_IDENTITY" sql="SELECT @@IDENTITY")
[EL Fine]: 2011-09-14 10:53:35.638--ClientSession(815749913)--Connection(341973975)--Thread(Thread[qtp1485988078-14,5,main])--SELECT @@IDENTITY

I think this is possibly a bug as when I remove the history customiser it works fine and does not encounter any issues. it is only with the history customiser activated that the problem occurs.

Sorry there is lots of trace etc but hopefully everything anyone should need is listed.

Re: SQLServer, IDENTITY and History issue [message #725727 is a reply to message #725227] Thu, 15 September 2011 15:43 Go to previous message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

I think this is bug,

The issue is that EclipseLink thinks the history table is also using identity.

Please vote for the bug.

You should be able to use TABLE sequencing as a workaround (I would recommend TABLE sequencing anyway, as IDENTITY sequencing is very limited in that it does not allow preallocation, so is very inefficient).

James : Wiki : Book : Blog : Twitter
Previous Topic:error during startup
Next Topic:[JPA] @Index used in cache?
Goto Forum:

Current Time: Mon Apr 23 11:59:34 GMT 2018

Powered by FUDForum. Page generated in 0.01575 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software