javadatetimemysql-workbenchdbeaver

Migrating from MySQL 5.7 to 8 causing problems with fractional seconds

I was running a MySQL 5.7 database in AWS RDS and AWS forced me to upgrade it because I was getting charged daily for keeping an old version. So I upgraded it to 8.0.mysql_aurora.3.08.0.

I have also some old code running on Java on a Docker container, the image is openjdk:8-jdk-alpine. When running MySQL 5.7 the code was using this MySQL connector:

         <dependency>
             <groupId>mysql</groupId>
             <artifactId>mysql-connector-java</artifactId>
             <version>5.1.42</version>
         </dependency>

Now I have updated my code to use:

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>8.4.0</version>
        </dependency>

Here is where my problems begin: I have some tables with some columns defined as datetime(3) (with three fractional seconds). For some reason, storing DateTime values with fractional seconds other than .000 works perfectly. I can save and read into/from the data base. But when I save a datetime which its fractional seconds are .000, when reading the value back from the data base, the string representation of the datetime has missing the fractional seconds, hence my datetime parse breaks because it expects the fractional part.

Here is my code for the datatime formatter:

DateTimeFormatter formatter = DateTimeFormat.forPattern("YYYY-MM-dd HH:mm:ss.SSS").withZoneUTC();

// MySQL DateTime -> Joda DateTime
public DateTime readingFromDB(String mysqlDateTime) {
    return formatter.withZoneUTC().parseDateTime(mysqlDateTime);
}

// Joda DateTime -> MySQL DateTime
public String savingToDB(DateTime dt) {
    return dt.toString(formatter);
}

Here is a screenshot from my DBeaver client Version 23.2.4.202311051706:

enter image description here

And here is a key point in all these:

The DBeaver connector driver is this one: enter image description here

The MySQLWorkbench driver is this one, you can read Connector/C++ from the picture: enter image description here

My code connector is com.mysql:mysql-connector-j:8.4.0 but I've also tried with mysql:mysql-connector-java:8.0.33, and the issue can be reproduced from both.

The only connector that seems to work well is Connector/C++. The fact that from MySQLWorkbench (which usesConnector/C++) I can see the values as expected, explains that the Java connectors, either mysql-connector-j or mysql-connector-java, are having some kind of problem.

Does anyone know how to fix this with the Java connectors?

P.S.: I know I can hack my code adding a codition to check if the value from the DB includes the fractional part or not, but I'm trying to get to the bottom of this and give it the most possible elegant solution.

Solution

  • This isn't a problem with the Connector/J, it's a problem with the implementation of java.sql.Timestamp.toString() according to https://bugs.mysql.com/bug.php?id=83989

    That bug shows a standalone Java example of java.sql.Timestamp truncating trailing zeroes, even without fetching the value from a JDBC connection.

    You can subclass java.sql.Timestamp.

    Or you can cast a datetime to a string in your SQL query before it is returned in the result set.

    I tested DBeaver and made it display trailing zeroes of a datetime by casting it. Here's my test query:

    mysql> create table t ( id serial primary key, dt datetime(3));

mysql> insert into t (dt) values (now()), (now(3));

mysql> select id, dt, cast(dt as char) from test.t

    Here's a screenshot of the result in DBeaver, showing the non-cast dt column truncates trailing zeroes, and the column preserves trailing zeroes when I cast it to CHAR.

    enter image description here

    I'm not sure why it was working with your old version. Perhaps you were using a different JDK that implemented java.sql.Timestamp differently?