JPA Hibernate integration with ProxySQL

[Biren Shah]

Hi,

I am trying out ProxySQL to implement schema based sharding for our multi tenant environment. So far I was able to configure ProxySQL, multiple mariadb instance, query rules/etc. 

Ran into issue when persisting data via hibernate. I believe it's because of the way hibernate uses/generates prepared statements. We have a table called tickets and related tables like ticket_comments. When I try to persist ticket with comments, It fails with below error. If i try to insert only ticket or only ticket comment it works fine. 

13:18:51.733 [main] ERROR org.hibernate.engine.jdbc.batch.internal.BatchingBatch - HHH000315: Exception executing batch [java.sql.BatchUpdateException: Error reading results -1], SQL: insert into ticket_comments (created_at, created_by, ......., id) values (?, ?, ?, ?, ?, ?)

13:18:51.753 [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Error reading results -1

13:18:51.755 [main] ERROR org.hibernate.internal.ExceptionMapperStandardImpl - HHH000346: Error during managed flush [org.hibernate.exception.GenericJDBCException: could not execute batch]

java.lang.RuntimeException: Database session error [namespace: XXXX]

at com.ccc.common.sql.DBSessionController.errorValue(DBSessionController.java:174)

at com.ccc.common.sql.DBSessionController.process(DBSessionController.java:86)

at com.ccc.common.sql.DBSessionController.useSessionUnsafe(DBSessionController.java:54)

at com.ccc.common.TicketTest.updateMI(TicketTest.java:190)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)

at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)

at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)

at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)

at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)

at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)

at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)

at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)

at org.junit.runners.ParentRunner.run(ParentRunner.java:363)

at org.junit.runner.JUnitCore.run(JUnitCore.java:137)

at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)

at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)

at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)

at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)

Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute batch

at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149)

at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)

at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:164)

at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1443)

at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:493)

at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3207)

at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2413)

at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:467)

at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:156)

at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$100(JdbcResourceLocalTransactionCoordinatorImpl.java:38)

at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:231)

at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:68)

at com.ccc.common.sql.DBSessionController.process(DBSessionController.java:83)

... 24 more

Caused by: org.hibernate.exception.GenericJDBCException: could not execute batch

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)

at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:121)

at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.doExecuteBatch(BatchingBatch.java:97)

at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.execute(AbstractBatchImpl.java:147)

at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.executeBatch(JdbcCoordinatorImpl.java:206)

at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:618)

at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:463)

at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337)

at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)

at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1437)

... 33 more

Caused by: java.sql.BatchUpdateException: Error reading results -1

at org.mariadb.jdbc.MariaDbStatement.executeBatchExceptionEpilogue(MariaDbStatement.java:282)

at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeBatch(MariaDbPreparedStatementClient.java:294)

at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128)

at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)

at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:111)

... 41 more

Caused by: java.sql.SQLException: Error reading results -1

at org.mariadb.jdbc.internal.protocol.AbstractMultiSend.executeBatchStandard(AbstractMultiSend.java:264)

at org.mariadb.jdbc.internal.protocol.AbstractMultiSend.executeBatch(AbstractMultiSend.java:189)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeBatchMulti(AbstractQueryProtocol.java:551)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeBatchClient(AbstractQueryProtocol.java:315)

at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternalBatch(MariaDbPreparedStatementClient.java:340)

at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeBatch(MariaDbPreparedStatementClient.java:288)

Thanks,

Biren