SQL-Server datetime issue

[Eilebrecht, Karl (Key-Work)]

Hi,

this is only for people using Microsoft SQL-Server, no need to read this if you're using another database:

We just stumbled across a problem while migrating to SQL Server 2008 R2 64 bit, newest JDBC-driver.
It seems that the datetime datatype (used for timestamps) never was suitable for being part of a primary key due to
rounding issues.
http://msdn.microsoft.com/en-us/library/ms187819.aspx
However it is used (i.e. some relations and some of our own tables) and we actually never faced any problems with that - up to now.
With any older versions all worked fine. But under certain circumstances with the newest server/driver combination we can
now reproduce an error when calling createOrStore twice with the same PK values right after another. The second statement
results in a primary key violation. Similar problem when executing findByPrimaryKey(entityPkJustStored) - not found.
Reason seems to be that the value itself is stored at a slightly lower precision that the comparison (second call)
is processed.

We're still investigating and are currently discussing two options:

(1)    use datetime2 (higher precision)

(2)    remove any "rounding-prone" datatypes like datetime etc. from primary keys (replace with integer types)

Regards.
Karl


Karl Eilebrecht
Key-Work Consulting GmbH | Kriegsstr. 100 | 76133 Karlsruhe | Germany | www.key-work.de<http://www.key-work.de>
Fon: [phone] | E-Mail: [hidden email]<mailto:[hidden email]> | Fax: [phone]

Key-Work Consulting GmbH, Karlsruhe, HRB 108695, HRG Mannheim
Gesch?ftsf?hrer: Andreas Stappert, Tobin Wotring

[Eilebrecht, Karl (Key-Work)]

Hi,

I figured out that this is indeed a 2008-JDBC-driver-"feature".
The quick workaround is to use the older 2005-JDBC-driver.
Medium-term we will change the related columns to datetime2(3) which
means exactly the millisecond-precision we like.

This simulates what Microsoft does before storing datetime:

    public static Timestamp roundTimestampLikeMicrosoft(Timestamp source) {
        //http://msdn.microsoft.com/de-de/library/ms187819.aspx
        if (source == null) {
                return null;
        }
        long time = source.getTime();
        long remainder = time % 10;
        if (remainder == 0 || remainder == 3 || remainder == 7) {
                return source; //won't change
        }
        if (remainder < 2 || remainder > 8) {
                remainder = 0;
        }
        else if (remainder > 1 && remainder < 5) {
                remainder = 3;
        }
        else if (remainder > 4 && remainder < 9) {
                remainder = 7;
        }
        return new Timestamp((time / 10) + remainder);
    }

For clarification: The problem with the new driver is NOT that the database stores at low precision (no difference to the old one, no bug). The problem arises when comparing (for a select). There seems to be an  "optimization" in the new driver that leads to the described symptoms.
Can't even say if this is a bug or a shot in the foot caused by wrong usage ...

Regards.
Karl

Karl Eilebrecht
Key-Work Consulting GmbH | Kriegsstr. 100 | 76133 Karlsruhe | Germany | www.key-work.de
Fon: [phone] | E-Mail: [hidden email] | Fax: [phone]

Key-Work Consulting GmbH Karlsruhe, HRB 108695, HRG Mannheim
Geschäftsführer: Andreas Stappert, Tobin Wotring
-----Ursprüngliche Nachricht-----
Von: Eilebrecht, Karl (Key-Work)
Gesendet: Montag, 21. Juni 2010 07:44
An: [hidden email]
Betreff: SQL-Server datetime issue

Hi,

this is only for people using Microsoft SQL-Server, no need to read this if you're using another database:

We just stumbled across a problem while migrating to SQL Server 2008 R2 64 bit, newest JDBC-driver.
It seems that the datetime datatype (used for timestamps) never was suitable for being part of a primary key due to
rounding issues.
http://msdn.microsoft.com/en-us/library/ms187819.aspx
However it is used (i.e. some relations and some of our own tables) and we actually never faced any problems with that - up to now.
With any older versions all worked fine. But under certain circumstances with the newest server/driver combination we can
now reproduce an error when calling createOrStore twice with the same PK values right after another. The second statement
results in a primary key violation. Similar problem when executing findByPrimaryKey(entityPkJustStored) - not found.
Reason seems to be that the value itself is stored at a slightly lower precision that the comparison (second call)
is processed.

We're still investigating and are currently discussing two options:

(1)    use datetime2 (higher precision)

(2)    remove any "rounding-prone" datatypes like datetime etc. from primary keys (replace with integer types)

Regards.
Karl


Karl Eilebrecht
Key-Work Consulting GmbH | Kriegsstr. 100 | 76133 Karlsruhe | Germany | www.key-work.de<http://www.key-work.de>
Fon: [phone] | E-Mail: [hidden email]<mailto:[hidden email]> | Fax: [phone]

Key-Work Consulting GmbH, Karlsruhe, HRB 108695, HRG Mannheim
Gesch?ftsf?hrer: Andreas Stappert, Tobin Wotring