Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Exceptions during DST clock change #1876

Open
metschesd opened this issue Apr 5, 2024 · 7 comments
Open

Exceptions during DST clock change #1876

metschesd opened this issue Apr 5, 2024 · 7 comments

Comments

@metschesd
Copy link

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
Copy link
Owner

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

@metschesd
Copy link
Author

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
Copy link
Owner

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

@metschesd
Copy link
Author

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
Copy link
Owner

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
Copy link
Author

Hi Lukas,

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

Greetings

@lukas-krecan
Copy link
Owner

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants