Exceptions during DST clock change

[metschesd]

Describe the bug I have a spring boot application (version 3.2.2) and a task annotated like this using shedlock version 5.10.2

    @Scheduled(timeUnit = TimeUnit.MINUTES, fixedRate = 2)
    @SchedulerLock(name = "my_schduler", lockAtMostFor = "4m", lockAtLeastFor = "1m")

During clock change for DST I got exceptions like these for about an hour:

c.m.c.j.e.MysqlDataTruncation: Data truncation: Incorrect datetime value: '2024-03-31 02:44:46.585' for column 'lock_until' at row 1
    at c.m.c.j.e.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104) 

Wrapped by: o.s.d.DataIntegrityViolationException: PreparedStatementCallback; SQL [UPDATE shedlock SET lock_until = TIMESTAMPADD(MICROSECOND, ?, UTC_TIMESTAMP(3)), locked_at = UTC_TIMESTAMP(3), locked_by = ? WHERE name = ? AND lock_until <= UTC_TIMESTAMP(3)]; Data truncation: Incorrect datetime value: '2024-03-31 02:50:56.998' for column 'lock_until' at row 1

I'm not quite sure if the bug is due to Spring Boot's Scheduler annotation or if it's a shedlock bug. But from the stack trace, it seems that there's an sql exception when trying to insert a value into the shedlock table.

Expected behavior Scheduler execution as usual every two minutes.

Actual behavior Scheduler threw exceptions for about an hour then started working again as expected.

[lukas-krecan]

It seems that lock_until has unexpected type. What type is it?

[metschesd]

It's a TIMESTAMP(3) as documented in the manual for a mysql db. And it's running the whole time without any problems. Exceptions occured only in that hour of the DST change. Here's how we configured the LockProvider for Spring Boot. It uses dbTime:

    @Bean
    public LockProvider lockProvider(DataSource dataSource) {
        return new JdbcTemplateLockProvider(
                JdbcTemplateLockProvider.Configuration.builder()
                        .withJdbcTemplate(new JdbcTemplate(dataSource))
                        .usingDbTime()
                        .build()
        );
    }

[lukas-krecan]

May be related https://bugs.mysql.com/bug.php?id=40329

[metschesd]

So you think that the problem might be fixed by using a timezone without DST? For example UTC? Where does shedlock get the timestamp from? Would it help to set the timezone on the Sheduled annotation like this?

@Scheduled(timeUnit = TimeUnit.MINUTES, fixedRate = 2, zone="UTC")

And what about the "usingDbTime()" for the LockProvider? Should I then remove this to make sure UTC is used?

[lukas-krecan]

It's always a good idea to run your servers in UTC.

It's a bug in ShedLock, I was assuming that if I use UTC_TIMESTAMP UTC time will be used. Apparently that's not the case. Hopefully I will fix it till the next DTC.

[metschesd]

Hi Lukas,

thanks for your quick reply! We would definitely appreciate it if you fix the bug.

Greetings