MySQL does not support millisecond accuracy in timestamps (problem for JobParameters) [BATCH-1379]

[spring-projects-issues]

Dave Syer opened BATCH-1379 and commented

MySQL does not support millisecond accuracy in timestamps (problem for JobParameters). The issue is that round tripping from JobParameters to the database and back does not preserve the timestamp, so a job can be launched twice with different parameters, when they should have been the same.

---

Affects: 2.1.0

Reference URL: http://forum.springsource.org/showthread.php?t=74219

Issue Links:

• BATCH-2522 Job key hash returns different result for restart when using date parameter
  ("is duplicated by")
• BATCH-2542 Multiple Job Intance for same job parameters
  ("is duplicated by")

[spring-projects-issues]

Dave Syer commented

There is a workaround at the driver level (http://mysqlpreacher.com/wordpress/2009/08/once-upon-a-timestampmilliseconds/), but currently this is only useful to Spring Batch clients who are prepared to modify the Daos.

[fmbenhassine]

I was able to reproduce the issue with Spring Batch 4.2.1 and MySQL 8.0.18 (See this repo).

MySQL added support for milliseconds accuracy since v5.6.4 (released in 2011): https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-4.html#mysqld-5-6-4-fractional-seconds

The issue is that the DDL that Spring Batch provides for MySQL does not declare the fractional seconds precision for the BATCH_JOB_EXECUTION_PARAMS#DATE_VAL column.

Using the fsp syntax as described in MySQL docs fixes the issue:

CREATE TABLE BATCH_JOB_EXECUTION_PARAMS  (
     JOB_EXECUTION_ID BIGINT NOT NULL ,
     TYPE_CD VARCHAR(6) NOT NULL ,
     KEY_NAME VARCHAR(100) NOT NULL ,
     STRING_VAL VARCHAR(250) ,
--   DATE_VAL DATETIME DEFAULT NULL ,
++   DATE_VAL DATETIME(6) DEFAULT NULL ,
     LONG_VAL BIGINT ,
     DOUBLE_VAL DOUBLE PRECISION ,
     IDENTIFYING CHAR(1) NOT NULL ,
     constraint JOB_EXEC_PARAMS_FK foreign key (JOB_EXECUTION_ID)
     references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ENGINE=InnoDB;

[parikshitdutta]

Hi @benas, is it open for contribution? I can take it.

[fmbenhassine]

@parikshitdutta Everything without a PR is open for contribution, so you are welcome! For this one we need to:

1. Update the DDL for MySQL to add the fractional seconds precision as mentioned in my previous comment (for new users)
2. Add a migration script for v4.3 with the statement that modifies the column ALTER TABLE MODIFY COLUMN ... (for existing users)
3. Add a test to cover the case

Point 3) is a bit tricky to do automatically, so I will do it manually for now. If you want to work on this issue, you can open a PR with 1) and 2).

Now here is the reason why testing this automatically is a bit tricky. Until now, we have been using embedded DBs and messages Brokers in most of our automated test suite. However, some parts are tested manually because it is impossible to embed the target database or broker in a JVM (MySQL is one of them). I am preparing a test infrastucture based on Docker + testcontainers. Once this test infrastructure is ready, we can add an automated test that covers this bug. But for now, I will test the fix manually.

[parikshitdutta]

Hi @benas, as discussed except the test case to cover the scenario, other changes done and PR: #3704 opened for your review.

Thank you.