MySQLMaxValueIncrementer is not multi-master replication-safe [SPR-14418]

[spring-projects-issues]

Tim Gokcen opened SPR-14418 and commented

The Spring org.springframework.jdbc.support.incrementer subsystem is used by Spring Batch, among others, to generate increasing "unique" IDs.

However, org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer is not safe to use in multi-master MySQL or MariaDB environments, e.g. master-master replication or MySQL/Galera cluster. This is because of the way in which it always kicks forward last_insert_id() by getCacheSize(), which will result in duplicate IDs across hosts and result in collisions if those IDs are used to, for example, insert rows into a table that has a unique key on the ID.

The global MySQL variable auto_increment_increment is available and used by clustering systems to ensure that AUTO_INCREMENT columns do not collide across the cluster (see the Galera documentation). The same value could also be used by MySQLMaxValueIncrementer to avoid collisions.

In multi-master environments, the cacheSize would also have to always be equal to 1; there is no way to pre-allocate IDs without guaranteeing that they were not already selected by another host.

Alternatively, the whole idea of using the backing RDBMS to generate increasing IDs could be abandoned in favour of generating non-linearly increasing "probably-unique" IDs, as for example in in Twitter Snowflake, which uses a combination of "timestamp + host-ID + thread-ID + counter" to produce a very-likely-to-be-unique 64-bit integer.

---

Affects: 4.2.6

[spring-projects-issues]

[email protected] commented

Spring Batch does not seem to provide support to inject/override custom implementations as they not annotated beans that you can override using @Primary at runtime. However, src/main/resources/data-source-context.xml already has externalized this class based on src/main/resources/batch-mysql.properties based on the ENVIRONMENT

<!--  Use this to set additional properties on beans at run time -->
<bean id="overrideProperties" class="org.springframework.beans.factory.config.PropertyOverrideConfigurer"
   depends-on="environment">
   <property name="location" value="classpath:batch-${ENVIRONMENT}.properties" />

batch-mysql.properties
..
batch.jdbc.password=test
batch.database.incrementer.class=org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer
..

We probably have to implement a custom class and override this properties file in classpath if I am not wrong. Worst case, we were able to fork and inject our own Incrementer to resolve this problem in our Galera cluster across DCs besides thinking about stopping replication completely.

[jhoeller]

Superseded by PR #31784.