Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Open JPA issue with deleting a record with timestamps

 
Padma Prasad
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

We have an issue with Open JPA framework. We are using Oracle 10.2 database with Open JPA 1.0. We have a database table with a composite primary key. One of the keys is a timestamp. We have a requirement to delete rows from this table. When we do a remove() on this table, we encounter an error like " Caused by: <1.0.0-SNAPSHOT-SNAPSHOT nonfatal user error> org.apache.openjpa.util.InvalidStateException: Operation attempted on a deleted instance.
at org.apache.openjpa.kernel.PCState.error(PCState.java:443)
at org.apache.openjpa.kernel.PDeletedState.beforeOptimisticWrite(PDeletedState.java:76)
at org.apache.openjpa.kernel.StateManagerImpl.dirty(StateManagerImpl.java:1445)
at org.apache.openjpa.kernel.StateManagerImpl.dirty(StateManagerImpl.java:1384)
at org.apache.openjpa.util.Proxies.dirty(Proxies.java:68)
at org.apache.openjpa.util.java$sql$Timestamp$proxy.setNanos(Unknown Source)
at org.apache.openjpa.jdbc.sql.DBDictionary.setTimestamp(DBDictionary.java:1054)
at org.apache.openjpa.jdbc.sql.DBDictionary.setTyped(DBDictionary.java:1194)
at org.apache.openjpa.jdbc.sql.RowImpl.flush(RowImpl.java:883)
at org.apache.openjpa.jdbc.sql.RowImpl.flush(RowImpl.java:843)
at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:93)
at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:71)
at org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager.flushPrimaryRow(OperationOrderUpdateManager.java:162)
at org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager.flush(OperationOrderUpdateManager.java:89)
at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:89)
at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:72)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:514)
at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:130) "

Record exists in the database but the above error is thrown by JPA. Not sure why JPA is not able to delete a record. is it because of the timestamp? What we identified while debugging is that the timestamp is stored as 5/29/2008 12:00:00.000000 AM. When we retrieve the value, we get 5/29/2008 00:00:00.0. What we also identified through database trace is that Open JPA is not hitting database at the delete statement.It executes select, insert and update but we cannot see delete error traces in the database. Could you help identify where the issue is?

Thanks,
Padma.
[ June 24, 2008: Message edited by: Padma Prasad ]
 
Tim Holloway
Saloon Keeper
Posts: 18367
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Managing dates as lookup objects (keys) in Java is tricky. The java.sql.Date has a granularity of 1 day, but it's based on java.util.Date, which has a granularity of 1 millisecond. Oracle Dates have a granularity of 1 second. I can personally attest to the grief that this can cause, both with JDO and with OpenJPA. So the date you write may not be the date you read.

In fact, this such a nuisance that I opened an issue on the OpenJPA documentation, requesting that the documentation devote a section to explaining this problem and how to avoid it.

"Operation attempted on a deleted instance" refers to OpenJPA's understanding of the data, not what's actually in the database. If a delete has been scheduled but not yet committed to the database, for example, you'd get that message.

While you might not think you'd deleted the record, problems with data granularity might be to blame. An date in memory could have been written out the the database, purged from local memory, then the date read back in from the database truncated (in Oracle's case, to seconds). This might cause it to look like another date, possibly also truncated. In other words, something like a hash collision. The first object might have been explicitly deleted, but since both objects had the same date, deleting the second object would appear to be an attempt to delete the first object for a second time.
 
Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper
Posts: 4968
1
Hibernate Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's awesome insight into Date problems, Tim. I almost wish we had a FAQ to add that too.

I see these types of issues with dates all the time. You've cleared up much of my confusion,ar as they say on the Ranch, 'doubt!'

-Cameron McKenzie
 
Tim Holloway
Saloon Keeper
Posts: 18367
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Cameron Wallace McKenzie:
That's awesome insight into Date problems


Well, actually, it's because I'm still wiping the blood from my nose.



I'm working on a project with PostgreSQL. The date granularity there is even worse - both the precision and the format are dependent on the options used to build the server, with the default being floating-point, which means that an interval as large as 1/10th of a second will come out imprecise.
 
Padma Prasad
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Tim,

Thanks for an awesome and detailed reply. I have another question though. You said "
The java.sql.Date has a granularity of 1 day, but it's based on java.util.Date, which has a granularity of 1 millisecond. Oracle Dates have a granularity of 1 second. "

Does this change if we use Timestamps? The granularity should be more if we use Timestamps.

Also, in our case, we are not doing anything but deleting the record. I did not quite understand when you said the record in local memory is purged. What we are doing is get the record, delete the record. Do you mean when we got the record, it is cached and later got purged for some reason? and when we do a delete, there is a comparision with the actual record in the database (this is retrieved after got truncated) and because of the truncation there is a mismatch?

Thanks,
Padma.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic