Support for DUPLICATE KEY in H2

Question

Support for DUPLICATE KEY in H2

I am using h2 for testing purposes in a Spring Boot (& Spring JPA) application.

I store statistics for the day, and so I set up ON DUPLICATE KEY UPDATE queries to store these statistics. If nothing was saved during this day, create a new record. If this day is already in the database, simply increase its value.

Scheme:

CREATE TABLE statistic (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  my_date DATE UNIQUE,
  value_1 INTEGER DEFAULT 0,
  value_2 INTEGER DEFAULT 0,
  value_3 INTEGER DEFAULT 0,
  ...
  value_X INTEGER DEFAULT 0
)

Repo:

@Modifying
@Query(value = "INSERT INTO statistic (value_X, my_date) SELECT :valueX,, :myDate " +
        "ON DUPLICATE KEY UPDATE value_X, = value_X, + :valueX,", nativeQuery = true)
void saveOrIncrementValueX(@Param("valueX") Integer valueX, @Param("myDate") LocalDate myDate);

Finally, I changed H2, so mySQL mode is used

my_schema.datasource.jdbcUrl: jdbc:h2:mem:my_schema;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=MYSQL

When I run a test that just tries to store the values several times in the same day, it works the first time, then an exception is thrown saying that a duplicate key is inserted:

o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 23505, SQLState: 23505
o.h.engine.jdbc.spi.SqlExceptionHelper   : Unique index or primary key violation: "UK_SQNR5X4KWWB6W0IXLL4HURKTF_INDEX_2 ON PUBLIC.STATISTIC(MATCH_DATE) VALUES (DATE '2018-03-06', 1)"; SQL statement:
    INSERT INTO statistic (version_ok, match_date) SELECT ?, ? ON DUPLICATE KEY UPDATE version_ok = version_ok + ? [23505-185]
o.s.s.s.TaskUtils$LoggingErrorHandler    : Unexpected error occurred in scheduled task.
org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint ["UK_SQNR5X4KWWB6W0IXLL4HURKTF_INDEX_2 ON PUBLIC.STATISTIC(MATCH_DATE) VALUES (DATE '2018-03-06', 1)"; SQL statement:
    INSERT INTO statistic (version_ok, match_date) SELECT ?, ? ON DUPLICATE KEY UPDATE version_ok = version_ok + ? [23505-185]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement