Support MySQL safe updates mode in MySQLMaxValueIncrementer

Affects: all

Hi~ I found a small problem while using Spring Batch. I , it would be better that spring-jdbc's MySQLMaxValueIncrementer supports MYSQL safe_update_mode (or safe mode)

Mysql safe_update Mode

First if we have a MySQL database instance with global option:

SET global sql_safe_updates=1

Then the 'Incrementer' will fail:

Exception in thread "main" org.springframework.dao.DataAccessResourceFailureException: 
Could not increment value for tab_sequence sequence table; 
nested exception is java.sql.SQLException: 
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
    at org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer.getNextKey(MySQLMaxValueIncrementer.java:148)
    at org.springframework.jdbc.support.incrementer.AbstractDataFieldMaxValueIncrementer.nextIntValue(AbstractDataFieldMaxValueIncrementer.java:123)

Code from spring-jdbc

https://github.com/spring-projects/spring-framework/blob/5b1ab31559798df83f1e8d54d2b754f12c69c14e/spring-jdbc/src/main/java/org/springframework/jdbc/support/incrementer/MySQLMaxValueIncrementer.java#L143-L144

As Mysql Reference says:

Enabling sql_safe_updates causes UPDATE and DELETE statements to produce an error if they do not specify a key constraint in the WHERE clause, or provide a LIMIT clause, or both.

Solution

If we add limit 1, then this statement will success.

stmt.executeUpdate("update " + getIncrementerName() + " set " + columnName +
" = last_insert_id(" + columnName + " + " + getCacheSize() + ") limit 1");

or turn off sql_safe_updates (may be ignored by some dba middleware )

stmt.execute("SET sql_safe_updates=0;");
stmt.executeUpdate("update " + getIncrementerName() + " set " + columnName +
" = last_insert_id(" + columnName + " + " + getCacheSize() + ")");

stmt.executeUpdate("update " + getIncrementerName() + " set " + columnName +
" = last_insert_id(" + columnName + " + " + getCacheSize() + ") where id >=0 ");

Spring Batch

Here are some schema in Spring Batch project.

https://github.com/spring-projects/spring-batch/blob/master/spring-batch-core/src/main/resources/org/springframework/batch/core/schema-mysql.sql

for example

CREATE TABLE BATCH_STEP_EXECUTION_SEQ (
    ID BIGINT NOT NULL,
    UNIQUE_KEY CHAR(1) NOT NULL,
    constraint UNIQUE_KEY_UN unique (UNIQUE_KEY)
) ENGINE=InnoDB;

The ID column neither is a primary key, nor a indexed column. The safe_update_mode will raise an exception.

Appendix

A quick test code:

@SpringBootApplication
public class JdbcSafemodeApplication {

    @Bean
    public JdbcTemplate JdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean
    public MySQLMaxValueIncrementer incrementer(DataSource dataSource) {
        return new MySQLMaxValueIncrementer(dataSource, "tab_sequence", "value");
    }

    public static void main(String[] args) {
        ConfigurableApplicationContext context = SpringApplication.run(JdbcSafemodeApplication.class, args);
        JdbcTemplate jdbcTemplate = context.getBean(JdbcTemplate.class);

//      jdbcTemplate.execute("insert into tab_sequence values(0);");

//      MySQLMaxValueIncrementer incrementer = context.getBean(MySQLMaxValueIncrementer.class);
//      int i = incrementer.nextIntValue();

        jdbcTemplate.execute("SET sql_safe_updates=0;");
        jdbcTemplate.execute("update tab_sequence set value = 3;");
    }
}

spring-projects / spring-framework