MySQL Bugs: #113121: The value '24:00:00' is an invalid TIME value.

Bug #113121	The value '24:00:00' is an invalid TIME value.
Submitted:	17 Nov 2023 13:34	Modified:	18 Nov 2023 16:49
Reporter:	Jan Burkhardt	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.2.0	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
Storing a java.sql.Time object into the Db can cause exception when reading it because of bad alignment of validation and rounding.
The MySQL Data Type TIME has second precision. The java.sql.Time has milliseconds precession. When writing a timestamp of less then half a second before midnight into the DB it gets rounded up to 24:00:00. When reading that value again there is an exception thrown in SqlTimeValueFactory.localCreateFromTime:

Caused by: com.mysql.cj.exceptions.DataReadException: The value '24:00:00' is an invalid TIME value. JDBC Time objects represent a wall-clock time and not a duration as MySQL treats them. If you are treating this type as a duration, consider retrieving this value as a string and dealing with it according to your requirements.
	at com.mysql.cj.result.SqlTimeValueFactory.localCreateFromTime(SqlTimeValueFactory.java:88)
	at com.mysql.cj.result.SqlTimeValueFactory.localCreateFromTime(SqlTimeValueFactory.java:51)
	at com.mysql.cj.result.AbstractDateTimeValueFactory.createFromTime(AbstractDateTimeValueFactory.java:74)
	at com.mysql.cj.protocol.a.MysqlTextValueDecoder.decodeTime(MysqlTextValueDecoder.java:84)
	at com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:96)
	at com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:244)
	at com.mysql.cj.protocol.a.result.ByteArrayRow.getValue(ByteArrayRow.java:91)
	... 5 more

How to repeat:
public class TimeBug extends BaseBugReport {

    @Override
    public void setUp() throws SQLException {
        TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
        try (final var con = getConnection()) {
            try (final var stm = con.prepareStatement("CREATE TABLE IF NOT EXISTS timebug (time TIME PRIMARY KEY NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci")) {
                stm.execute();
            }
            try (final var stm = con.prepareStatement("REPLACE INTO timebug (time) VALUES (?)")) {
                stm.setTime(1, new Time(TimeUnit.DAYS.toMillis(1) - 250));
                stm.execute();
            }
        }
    }

    @Override
    public void tearDown() throws SQLException {
        try (final var con = getConnection()) {
            try (final var stm = con.prepareStatement("DROP TABLE timebug")) {
                stm.execute();
            }
        }
    }

    @Override
    public void runTest() throws SQLException {
        try (final var con = getConnection(); final var stm = con.prepareStatement("SELECT time FROM timebug"); final var rs = stm.executeQuery()) {
            while (rs.next()) {
                System.out.println(rs.getTime("time"));
            }
        }
    }

    public static void main(String[] args) throws Exception {
        new TimeBug().run();
    }
}

Suggested fix:
The validation in SqlTimeValueFactory.localCreateFromTime needs to be changed in a way that at least 24:00:00 is allowed.

The Test to reproduce the bug

Attachment: TimeBug.java (text/x-java), 1.52 KiB.

you can check it out here https://github.com/bjrke/mysqlbug/blob/main/src/main/java/de/bjrke/mysqlbug/TimeBug.java

Hi Jan,

Thank you for your interest in MySQL Connector/J and for filing this report.

First of all please note that "24:00:00" is not a valid java.sql.Time value, so your request for making Connector/J support it the way you want will simply not be possible.

You have at least two options to handle this situation, though.

Taking it from the server side, you can create your TIME columns with fractional seconds support, for example, as "TIME(3)" [https://dev.mysql.com/doc/refman/8.2/en/fractional-seconds.html]. Just by doing so your test should pass.

From the client side, if you don't want to sanitize the data yourself in order to keep time values within the valid range before submitting it, then you can set the connection option "sendFractionalSecondsForTime=false" and Connector/J does the fractional seconds truncation for you. Or else you will just have to retrieve the values as strings, via statement.getString(time), and then process them however you like.

I hope this helps.

I have a different opinion about it, since using it straight forward, like I demonstrated, shows the issue. A default behavior should not have such a restriction.
Especially since I just request to allow 24:00:00, but not 24:00:01.
I found the workaround of using Time.valueOf(rs.getString()) myself. But I spend a whole day in investigating, fixing and deploying this issue after it happened in production, because of its low chance (1:172800).
Migrating to a different column type is not an option since we don't need this precision. Additionally in english speaking countries 12AM is midnight, not 0AM.