Hibernate 5.4 - unable to persist Java Instant

Question

I have a Kotlin app built on Spring Boot 2.3.4. This version of Spring Boot includes Hibernate 5.4.21. I've also imported mysql-connector-java 8.0.21.

Whenever I try to persist an Instant object into my database, I get the following exception:

com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '\xAC\xED\x00\x05sr\x00\x0Djava.time.Ser\x95]\x84\xBA\x1B"H\xB2\x0C\x00\x00xpw\x0D\x02\x00\x00\x00\x00_\xCDvA)\xE6Jxx' for column 'myDateTimeColumn' at row 1

Here's the object I'm trying to persist, in Kotlin; it only has two fields:

@Entity
class RoomEntity{
    @Id
    lateinit var roomId: String
    lateinit var lastAccessedAt: Instant
}

The table I'm trying to save it to has two columns: roomId is a CHAR(7), and lastAccessedAt is a TIMESTAMP.

The code that does the save is only 2 lines long:

val sql = "INSERT INTO mydb.rooms (roomId, lastAccessedAt) VALUES (?, ?);"
db.update(sql, "1234567", Instant.now())

I've looked at other SO posts similar to my issue, but the answers all say to update to version 5.2+ of Hibernate, which I am using. What could be causing this exception?

After you upgrade Hibernate, did you upgrade the schema or set the persistence unit to automatically update the schema? Java 8 types has been natively supported with Hibernate 5.2+, so the problem is likely due to mismatch of schema and entity types? Would be helpful if you can share the relevant entities annotation and db schema. — Alex, Dec 07 '20 at 09:16

Christian Beikov · Answer 1 · 2020-12-10T09:41:48.780

MySQL has a default precision for timestamps that is different from what the SQL standard expects. Also see here: https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

You can fix this by using registering custom types in a dialect e.g.

public class SaneMySQLDialect extends MySQL5InnoDBDialect {

    public SaneMySQLDialect() {
        registerColumnType( Types.TIME, "time(6)" );
        registerColumnType( Types.TIMESTAMP, "datetime(6)" );
    }

    @Override
    public String getTableTypeString() {
        return " ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_bin";
    }
}

But Hibernate 5.2+ has native support for Java 8 time, so you don't need this library anymore. — Mathew Alden, Dec 10 '20 at 02:00

score 0 · Answer 2 · answered Dec 14 '20 at 01:41

I never did figure out how to store an Instant in the DB. It was much easier to simply use a java.sql.Timestamp instead in my Data Access Layer.

So my Entity looks like this:

@Entity
class RoomEntity{
    @Id
    lateinit var roomId: String
    lateinit var lastAccessedAt: Timestamp

    fun toRoom(): Room {
        return Room(roomId, lastAccessedAt.toInstant());
    }
}

And I persist a new Room row with this code:

val sql = "INSERT INTO mydb.rooms (roomId, lastAccessedAt) VALUES (?, ?);"
db.update(sql, room.roomId, Timestamp.from(room.lastAccessedAt))

Where my room class looks like this:

data class Room(var roomId: String, var lastAccessedAt: Instant)

Hibernate 5.4 - unable to persist Java Instant

2 Answers2