[h2] Hibernate 5.6.9 @Version annotation requires precision 9 for timestamp

[Michael Brizic]

Does anyone know why using H2 on Java 15+ and Spring/Hibernate with entitys 
that are versioned using timestamps require a precision of 9 instead of 6?

We upgraded our web app to use Java 17. Since Java 15+ the JDK/JRE support 
nanosecond precision for datetime objects. Our integration tests use H2 
version 1.4.200. 

It seems I have needed to change our entities from:
@Version
@Column(columnDefinition = “DATETIME(6)”)
private Instant version;
To:
@Version
@Column(columnDefinition = “DATETIME(9)”)
private Instant version;

Otherwise I will get optimistic locking failed; nested exception is 
org.hibernate.StaleObjectStateException

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/3cc47ed7-e94c-4195-813d-2b8e51d1d9fcn%40googlegroups.com.

[h2] Re: Hibernate 5.6.9 @Version annotation requires precision 9 for timestamp

[Evgenij Ryazanov]

Hello!

Resolution of system timestamps in JVM depends on Java version and 
operating system. Old versions of Java (Java 8 and older) provide only 3 
digits. Resolution was improved in Java 9, this version provides 7 digits 
on Windows, but only 6 digits on Linux, because it uses an old system 
function with this limitation. Java 15 and newer versions use a modern 
function and provide 9 digits instead of 6 on Linux.

There are two data types in the SQL Standard and H2 for datetime values 
with both date and time parts: TIMESTAMP 
 and TIMESTAMP 
WITH TIME ZONE 
. 
Both data types have default fractional seconds precision of 6 as required 
by the Standard and some database systems, including the H2, support larger 
precision.

(DATETIME is silently replaced with standard TIMESTAMP data type, actually 
you normally should use TIMESTAMP(9) WITH TIME ZONE for Instant values in 
H2 to avoid issues on DST or other time zone transitions.)

Instant values in Java also support up to 9 fractional digits. When you 
insert a value with non-zero nanoseconds into a column with lower 
fractional seconds precision, this value is rounded to that precision by H2 
(the SQL Standard doesn't specify an exact behavior, it only requires an 
implementation-defined rounding or truncation). When Hibernate reads it 
back it gets a rounded value instead of expected original one. To avoid it, 
you must define an explicit fractional seconds precision of 9 for this 
column.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a73f0faa-d9ae-4e2b-a408-9603c4efd0ban%40googlegroups.com.

[h2] Re: Hibernate 5.6.9 @Version annotation requires precision 9 for timestamp

[Michael Brizic]

Hi Evgenij,

Thanks for the explanation, very helpful!

Wondering if you could would know any additional details ... With respect 
to "Hibernate reads it back it gets a rounded value instead of expected 
original one" do you have any further details as to exactly HOW this fails 
if for example, the timestamps are indeed *different *or is this a problem 
only when the timestamp differences are microsecond or nanoseconds apart? 
Is there some kind of hashing and equals checks that fail somehow 
internally? In other words, initial insert using nanoseconds but rounded to 
microsecods is followed by an update some microseconds later ... wouldn't 
those two timestamps be different despite the rounding that occurred?



On Thursday, January 5, 2023 at 7:00:39 AM UTC-6 Evgenij Ryazanov wrote:

> Hello!
>
> Resolution of system timestamps in JVM depends on Java version and 
> operating system. Old versions of Java (Java 8 and older) provide only 3 
> digits. Resolution was improved in Java 9, this version provides 7 digits 
> on Windows, but only 6 digits on Linux, because it uses an old system 
> function with this limitation. Java 15 and newer versions use a modern 
> function and provide 9 digits instead of 6 on Linux.
>
> There are two data types in the SQL Standard and H2 for datetime values 
> with both date and time parts: TIMESTAMP 
>  and TIMESTAMP 
> WITH TIME ZONE 
> . 
> Both data types have default fractional seconds precision of 6 as required 
> by the Standard and some database systems, including the H2, support larger 
> precision.
>
> (DATETIME is silently replaced with standard TIMESTAMP data type, actually 
> you normally should use TIMESTAMP(9) WITH TIME ZONE for Instant values in 
> H2 to avoid issues on DST or other time zone transitions.)
>
> Instant values in Java also support up to 9 fractional digits. When you 
> insert a value with non-zero nanoseconds into a column with lower 
> fractional seconds precision, this value is rounded to that precision by H2 
> (the SQL Standard doesn't specify an exact behavior, it only requires an 
> implementation-defined rounding or truncation). When Hibernate reads it 
> back it gets a rounded value instead of expected original one. To avoid it, 
> you must define an explicit fractional seconds precision of 9 for this 
> column.
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/f4a6c51f-c76b-4f40-b456-1285b9ed0fc9n%40googlegroups.com.

[h2] Re: RecoverTools creating scrip with tablenames 0_Number pattern? why

[Ram K]

thank you for the reply, I have gone through the generated script, some how 
the RunScript did not fail after creating the tamporary (O_N) tables.

I manually copied the script and ran in it in the console worked. 

I am trying to migrate to sql server,  could you please give me some tips 
to migrate to sql server (mssql).

Thank you,
Ram

On Wednesday, January 4, 2023 at 12:50:36 AM UTC-8 Evgenij Ryazanov wrote:

> Hi!
>
> Table definition and table data are stored separately and they are 
> recovered separately too.
>
> The recovery tool works in the following way (I skipped unrelated steps 
> for simplicity):
> 1. It constructs a temporary table with LOB chunks from all tables first.
> 2. Then, it recovers data from tables, it creates a new temporary table 
> for each found table for that purpose. LOB values for them are constructed 
> from chunks from (1). Actual table names, column names, data types and 
> other attributes aren't yet known at that moment.
> 3. Next, it tries to recover metadata of database, including definitions 
> of actual tables.
> 4. After that, If metadata of some table was recovered on step (3) and 
> data for that table was recovered on step (2) data is copied from the 
> temporary table into actual one.
> 5. Finally, all temporary tables are dropped.
>
> This strategy isn't perfect and can potentially be improved, at least for 
> some cases when both data and metadata are recoverable.
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/8205720d-46a2-4b0d-a590-bfab00c900bcn%40googlegroups.com.