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

[lukas-krecan]

Hi, I tried to reproduce it and I failed. I don't doubt that the bug is there, I jsut do not have enough mental capacity to play with MySQl, try to reproduce it and then try the fix. So if anyone is willing to help, can you please try to reproduce it and try to find out how to rewrite the query (if possible).

I was hoping in something like this

SET time_zone = 'Europe/Prague';

select d, timestampadd(MICROSECOND, 3600000000, d) from (
select convert_tz(str_to_date('2024-03-31 00:44:46', '%Y-%m-%d %H:%i:%s'), 'UTC', 'Europe/Prague') as d from dual) as x;

See this https://stackoverflow.com/questions/78941714/how-to-add-timestamps-in-utc