数据库使用TiDB，在启动时报错，导致不能执行补偿方法

[pengyu0929]

Error Code: 1146 Call: INSERT INTO TL_TxTimeout (SURROGATEID, STATUS, VERSION) SELECT t0.SURROGATEID, ?, (t0.VERSION + ?) FROM TxTimeout t0 WHERE ((t0.STATUS <> ?) AND EXISTS (SELECT ? FROM TxEvent t1 WHERE (((t1.GLOBALTXID = t0.GLOBALTXID) AND (t1.LOCALTXID = t0.LOCALTXID)) AND (t1.TYPE <> t0.TYPE))) ) bind => [4 parameters bound] Query: UpdateAllQuery(referenceClass=TxTimeout sql="CREATE TEMPORARY TABLE IF NOT EXISTS TL_TxTimeout (SURROGATEID BIGINT NOT NULL, EVENTID BIGINT, EXPIRYTIME DATETIME, GLOBALTXID VARCHAR(255), INSTANCEID VARCHAR(255), LOCALTXID VARCHAR(255), PARENTTXID VARCHAR(255), SERVICENAME VARCHAR(255), STATUS VARCHAR(255), TYPE VARCHAR(255), VERSION BIGINT, PRIMARY KEY (SURROGATEID))") at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:331) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:905) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:967) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:637) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:564) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2093) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:309) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:270) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.updateAllUsingTempTables(DatasourceCallQueryMechanism.java:899) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.updateAll(DatasourceCallQueryMechanism.java:861) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.queries.UpdateAllQuery.executeDatabaseQuery(UpdateAllQuery.java:154) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:911) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:810) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.queries.ModifyAllQuery.executeInUnitOfWork(ModifyAllQuery.java:148) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2979) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1892) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1874) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839) ~[org.eclipse.persistence.core-2.7.1.jar:?] at org.eclipse.persistence.internal.jpa.QueryImpl.executeUpdate(QueryImpl.java:300) ~[org.eclipse.persistence.jpa-2.7.1.jar:?] at org.springframework.data.jpa.repository.query.JpaQueryExecution$ModifyingExecution.doExecute(JpaQueryExecution.java:256) ~[spring-data-jpa-2.0.11.RELEASE.jar:2.0.11.RELEASE] at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91) ~[spring-data-jpa-2.0.11.RELEASE.jar:2.0.11.RELEASE] at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:136) ~[spring-data-jpa-2.0.11.RELEASE.jar:2.0.11.RELEASE] at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125) ~[spring-data-jpa-2.0.11.RELEASE.jar:2.0.11.RELEASE] at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:602) ~[spring-data-commons-2.0.11.RELEASE.jar:2.0.11.RELEASE] at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:590) ~[spring-data-commons-2.0.11.RELEASE.jar:2.0.11.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.10.RELEASE.jar:5.0.10.RELEASE] at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59) ~[spring-data-commons-2.0.11.RELEASE.jar:2.0.11.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.10.RELEASE.jar:5.0.10.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) ~[spring-tx-5.0.10.RELEASE.jar:5.0.10.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.0.10.RELEASE.jar:5.0.10.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.10.RELEASE.jar:5.0.10.RELEASE] at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.0.10.RELEASE.jar:5.0.10.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.10.RELEASE.jar:5.0.10.RELEASE] at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135) ~[spring-data-jpa-2.0.11.RELEASE.jar:2.0.11.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.10.RELEASE.jar:5.0.10.RELEASE] at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[spring-aop-5.0.10.RELEASE.jar:5.0.10.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.10.RELEASE.jar:5.0.10.RELEASE] at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61) ~[spring-data-commons-2.0.11.RELEASE.jar:2.0.11.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.10.RELEASE.jar:5.0.10.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.0.10.RELEASE.jar:5.0.10.RELEASE] at org.apache.servicecomb.pack.alpha.server.$Proxy149.updateStatusOfFinishedTx(Unknown Source) ~[?:?] at org.apache.servicecomb.pack.alpha.server.SpringTxTimeoutRepository.markTimeoutAsDone(SpringTxTimeoutRepository.java:59) ~[alpha-server-0.4.0.jar:?] at org.apache.servicecomb.pack.alpha.server.SpringTxTimeoutRepository$$FastClassBySpringCGLIB$$dda60806.invoke(<generated>) ~[alpha-server-0.4.0.jar:?] at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-5.0.10.RELEASE.jar:5.0.10.RELEASE] at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684) ~[spring-aop-5.0.10.RELEASE.jar:5.0.10.RELEASE] at org.apache.servicecomb.pack.alpha.server.SpringTxTimeoutRepository$$EnhancerBySpringCGLIB$$f8b46627.markTimeoutAsDone(<generated>) ~[alpha-server-0.4.0.jar:?] at org.apache.servicecomb.pack.alpha.core.EventScanner.updateTimeoutStatus(EventScanner.java:115) ~[alpha-core-0.4.0.jar:0.4.0] at org.apache.servicecomb.pack.alpha.core.EventScanner.lambda$pollEvents$0(EventScanner.java:90) ~[alpha-core-0.4.0.jar:0.4.0] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[?:1.8.0_131] at java.util.concurrent.FutureTask.runAndReset$$$capture(FutureTask.java:308) ~[?:1.8.0_131] at java.util.concurrent.FutureTask.runAndReset(FutureTask.java) ~[?:1.8.0_131] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) ~[?:1.8.0_131] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) ~[?:1.8.0_131] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) ~[?:1.8.0_131] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) ~[?:1.8.0_131] at java.lang.Thread.run(Thread.java:748) [?:1.8.0_131] Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'hsrm_saga.TL_TxTimeout' doesn't exist at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_131] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_131] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_131] at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_131] at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.47.jar:5.1.47] at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[mysql-connector-java-5.1.47.jar:5.1.47] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944) ~[mysql-connector-java-5.1.47.jar:5.1.47] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978) ~[mysql-connector-java-5.1.47.jar:5.1.47] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914) ~[mysql-connector-java-5.1.47.jar:5.1.47] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) ~[mysql-connector-java-5.1.47.jar:5.1.47] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) ~[mysql-connector-java-5.1.47.jar:5.1.47] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495) ~[mysql-connector-java-5.1.47.jar:5.1.47] at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903) ~[mysql-connector-java-5.1.47.jar:5.1.47] at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2124) ~[mysql-connector-java-5.1.47.jar:5.1.47] at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2058) ~[mysql-connector-java-5.1.47.jar:5.1.47] at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5158) ~[mysql-connector-java-5.1.47.jar:5.1.47] at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2043) ~[mysql-connector-java-5.1.47.jar:5.1.47] at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-2.7.9.jar:?] at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-2.7.9.jar:?] at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:895) ~[org.eclipse.persistence.core-2.7.1.jar:?] ... 54 more

[coolbeevip]

你可能要看一下 tdib 数据库使用 jpa 时的一些说明，尤其是关于临时表，这部分我不太确定

[coolbeevip]

Alpha 使用了JPA 作为数据库 ORM，你可以在以上错误日志中看到用到了临时表

Error Code: 1146 Call: INSERT INTO TL_TxTimeout (SURROGATEID, STATUS, VERSION) SELECT t0.SURROGATEID, ?, (t0.VERSION + ?) FROM TxTimeout t0 WHERE ((t0.STATUS <> ?) AND EXISTS (SELECT ? FROM TxEvent t1 WHERE (((t1.GLOBALTXID = t0.GLOBALTXID) AND (t1.LOCALTXID = t0.LOCALTXID)) AND (t1.TYPE <> t0.TYPE))) ) bind => [4 parameters bound] Query: UpdateAllQuery(referenceClass=TxTimeout sql="CREATE TEMPORARY TABLE IF NOT EXISTS TL_TxTimeout (SURROGATEID BIGINT NOT NULL, EVENTID BIGINT, EXPIRYTIME DATETIME, GLOBALTXID VARCHAR(255), INSTANCEID VARCHAR(255), LOCALTXID VARCHAR(255), PARENTTXID VARCHAR(255), SERVICENAME VARCHAR(255), STATUS VARCHAR(255), TYPE VARCHAR(255), VERSION BIGINT, PRIMARY KEY (SURROGATEID))") at 

我刚才看了一些TiDB，貌似他们最近才修复了关于临时表与MySQL协议的兼容，你可能需要确定一下你用的 TiDB 的版本，或者直接给 TiDB 提一个关于临时表的问题

兼容 MySQL协议的 issues
pingcap/tidb#11486

关于支持临时表的 issues
pingcap/parser#408

[pengyu0929]

JPA关于临时表这方面可以通过什么方式关闭吗？

[WillemJiang]

临时表是JPA创建的， 如果关闭了JPA就没有办法正常工作了。

[pengyu0929]

那现在这种情况就只有升级TiDB这一种解决方式吗？

[WillemJiang]

你也可以尝试ServiceComb 0.5.0 提供的状态机模式，后台数据是使用elastic search来提供。
详细内容可以参考此文档。