Hibernate, Oracle and Dates. A Story.

July 13, 2005 5:34 PM

One of the truly fun things about writing an application that runs against multiple databases is the heady joy of having to test said application against every database it claims to work with.

For the last day or two, a single test in our nightly build was failing against Oracle. Only against Oracle. The test in question creates three blog posts and checks that the "previous" and "next" links work between them. When running the test against Oracle, you could traverse forwards, but the link to go back wasn't there.

I ran the setup portion of the test manually, and somehow each post's "back" link pointed to the post you were viewing rather than the previous post. "Curious", I thought, and restarted the test server so I could attach a remote debugger.

Sod's law in full effect, the act of restarting the server in debug mode made the problem go away. All the links had magically reverted to pointing where they were supposed to point.

The query to find the previous post was mind-numbingly simple. You take the creation date of a post, and find the first post with a lesser date. We'd had problems with this sort of thing before - Hibernate maps dates to Oracle's DATE type (apparently changed to TIMESTAMP for Hibernate3) which only has a one-second granularity, but it's not a practical problem and the tests make sure the posts are spaced out sufficiently.

And anyway, if it was the data, why would restarting the application server make the problem go away? It must be something we're cache...

Aha.

Just before we store a blog post, we set its creation date. Like any Java date, this is captured with millisecond precision. However, Oracle isn't storing our dates with the same precision, and zeroes the last few digits. So java.util.Date thinks the time is 1121235471964, but Oracle records it as 1121235471000.

Hibernate then shoves the original BlogPost object in the second-level cache. When you look up the post in future requests, you're getting back the original, millisecond-precise date from the cache. You pass that date back to the database to look for the previous blog post. Oracle is told to look for the first post before 1121235471964, a position for which 1121235471000 obviously qualifies. You then get back the post you started with.

When you restart the server the cached date goes away and you're left with only Oracle's version of things, so all your queries are back to normal.

The lesson, of course, is not that Hibernate sucks, or that Oracle sucks, or that Charles can't code his way out of a paper bag (although the fundamental truth is that all software sucks, and by corollary, all programmers suck). In the end, it's just a long-winded way to agree with Joel about leaky abstractions.

Code generation tools which pretend to abstract out something, like all abstractions, leak, and the only way to deal with the leaks competently is to learn about how the abstractions work and what they are abstracting. So the abstractions save us time working, but they don't save us time learning.

10 Comments

I guess I've always assumed that timestamps should be considered fundamentally inexact, and used with the same care as floating point types. ie. never, ever, ever use = on a floating type or timestamp...

(Really, Java and most languages make a big mistake by letting you write floatx = floaty.)

Oracle's DATE type is really meant to be accurate to one DAY, not even to a second. It's only accurate to the second because previous versions of oracle were accurate to a second.

What you really want is a TIMESTAMP(3) which will give you 3 digits of precison per second (default for TIMESTAMP is 6, max is 9). In that case, Oracle would maintain the millisecond accuracy that you get with the JVM.

It's sad but true, Java and database tool developers suck at dates. (all puns intented).

- Don

Did I miss the part about getting women using Open Source Java tools? Or did I misunderstand the headline completely?

-m

Just wondering: why are dates used as keys for cache entries? Aren't you using surrogate keys for your blog posts? Or is there something in your system that escapes me?

And some people claim that one day all applications will be generated and programmers will become redundant!

Oracle sucks horse ass at just about everything...

Ah mother freaken damn. I just spent 2 days on a bug similar to this. What an absolute pain in the ass. Searching google for 'hibernate staleobjectexception oracle' really doesn't help..

After reading this, I'm wondering if it is best to avoid database timestamp types altogether if the objective is a Hibernate app that runs against multiple databases. That is, store time in an integer column in millisecs since the epoch, and map to java.util.Date (or Joda DateTime) with a Hibernate custom type. Does anyone endorse this approach?

Unbelievable. I'm dealing with the same issue in VC++. Not only are my milliseconds being truncated (from a double value), but in the afternoon, it's complaining about my hour value being > 12. What's the use of TIMESTAMP if it serves the less purpose than DATE? Anybody know if there's a way to pass a binary reference to 'select SYSTIMESTAMP from dual' in a RecordSet object (not using a query)? Rhetorical at best...

Previously: Reaffirming My Faith in Humanity

Next: When Not to Follow the Spec