Support MySQL safe updates mode in MySQLMaxValueIncrementer

[slankka]

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

spring-framework/spring-jdbc/src/main/java/org/springframework/jdbc/support/incrementer/MySQLMaxValueIncrementer.java

Lines 143 to 144 in 5b1ab31

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

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() + ")");

or

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;");
	}
}

[sbrannen]

Hi @slankka,

Thanks for creating your first issue for the Spring Framework!

We have decided to go with your limit 1 proposal for inclusion in Spring Framework 5.3.7.