Update MySQLMaxValueIncrementer to not rely on MYISAM [SPR-15107]

[spring-projects-issues]

Thomas Risberg opened SPR-15107 and commented

We should update MySQLMaxValueIncrementer to not rely on MYISAM for the sequence table since this engine might not always be available. This is the case with the latest MySQL service available in Cloud Foundry. It would be better to do the sequence value update in a separate transaction so we can support any type of storage engine.

---

Referenced from: pull request #1286, and commits cc53d59, 5c7aee7, 60882ce

[spring-projects-issues]

Juergen Hoeller commented

Rolled into master with useNewConnection=true by default; to be backported to 4.3.6 with useNewConnection=false by default.

[spring-projects-issues]

Erwin Vervaet commented

Juergen Hoeller,
It seems like I'm missing something, but I don't immediately understand how the "useNewConnection" implementation does the trick.
Looking at the MySQLMaxValueIncrementer source code, I see that it is simply using getDataSource().getConnection(); to get a "non-managed connection so our modifications are handled in a separate transaction" (as explained in the comments). However, if you are in a managed environment like a JEE application server, the DataSource is managed and the connection you get back is of course also managed! It's typically the connection used by the transaction active on the current thread.
So how does this guarantee that the increment is handled in a separate transaction?

[spring-projects-issues]

Juergen Hoeller commented

Erwin Vervaet, indeed, this is designed for a typical Spring-managed environment where DataSource.getConnection() is a native call, as opposed to DataSourceUtils.getConnection which is Spring's form of enforced participating in the current Spring-managed transaction. This will also work fine in a Java EE environment as long as you're not using a JTA setup (i.e. no EJB CMT and no Spring JtaTransactionManager either but rather DataSourceTransactionManager or JpaTransactionManager / HibernateTransactionManager), since Spring manages its own thread-bound transaction there which the EE server is unaware of.

For a Java EE environment with JTA transactions, we'd have to accept a second DataSource reference which needs to be unmanaged in EE terms and perform the separate call there. This is simply outside of the particular design scope for MySQLMaxValueIncrementer.

[spring-projects-issues]

Mohamed Abuthahir commented

Hi @Juergen Hoeller  Right now I am into the same situation you have described about JTA transaction. I am suppose to use JTA because I have two data sources MySQL and DB2. While doing some ID manipulation with MySQL the code calls commit() inside MySQLMaxValueIncrementer.getNextKey() method which is causing the failure. Because it is running in a global transaction. What would be the solution here?

[spring-projects-issues]

Mohamed Abuthahir commented

@ Michael Minella The above said issue is happening if I am on a distributed transaction where I am using mySQL as my spring metadata and DB2 as my business db.

 

2018-06-21 23:53:21.390  WARN 23324 --- [           main] com.atomikos.jdbc.AtomikosSQLException   : Cannot call method 'commit' while a global transaction is running2018-06-21 23:53:21.390  WARN 23324 --- [           main] com.atomikos.jdbc.AtomikosSQLException   : Cannot call method 'commit' while a global transaction is running2018-06-21 23:53:21.456  WARN 23324 --- [           main] c.atomikos.jdbc.AbstractConnectionProxy  : Forcing close of pending statement: org.mariadb.jdbc.MariaDbStatement@66434cc82018-06-21 23:53:21.456  INFO 23324 --- [           main] ConditionEvaluationReportLoggingListener : 
Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.2018-06-21 23:53:21.473 ERROR 23324 --- [           main] o.s.boot.SpringApplication               : Application run failed
java.lang.IllegalStateException: Failed to execute CommandLineRunner at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:800) at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:781) at org.springframework.boot.SpringApplication.run(SpringApplication.java:335) at org.springframework.boot.SpringApplication.run(SpringApplication.java:1255) at org.springframework.boot.SpringApplication.run(SpringApplication.java:1243) at net.teamservices.TtcInvoiceProcessingBatchApplication.main(TtcInvoiceProcessingBatchApplication.java:10)Caused by: org.springframework.dao.DataAccessResourceFailureException: Unable to commit new sequence value changes for BATCH_JOB_SEQ at org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer.getNextKey(MySQLMaxValueIncrementer.java:177) at org.springframework.jdbc.support.incrementer.AbstractDataFieldMaxValueIncrementer.nextLongValue(AbstractDataFieldMaxValueIncrementer.java:128) at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:108) at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:139) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)