The SQL returned by getQueryGlobalTransactionSQLByStatus method in LogStoreSqls doesn't support Oracle

[wangrui821]

• I have searched the issues of this repository and believe that this is not a duplicate.

Ⅰ. Issue Description

The SQL returned by getQueryGlobalTransactionSQLByStatus method in LogStoreSqls doesn't support Oracle.

Ⅱ. Describe what happened

When seata-server is running, exception below occured:

2019-09-07 11:53:29.432 INFO [TxTimeoutCheck_1]io.seata.server.coordinator.DefaultCoordinator.lambda$init$4:452 -Exception timeout checking ... 
io.seata.common.exception.StoreException: ORA-00933: SQL 命令未正确结束

	at io.seata.core.store.db.LogStoreDataBaseDAO.queryGlobalTransactionDO(LogStoreDataBaseDAO.java:213)
	at io.seata.server.store.db.DatabaseTransactionStoreManager.readSession(DatabaseTransactionStoreManager.java:174)
	at io.seata.server.store.db.DatabaseTransactionStoreManager.readSession(DatabaseTransactionStoreManager.java:203)
	at io.seata.server.session.db.DataBaseSessionManager.findGlobalSessions(DataBaseSessionManager.java:183)
	at io.seata.server.session.db.DataBaseSessionManager.allSessions(DataBaseSessionManager.java:172)
	at io.seata.server.coordinator.DefaultCoordinator.timeoutCheck(DefaultCoordinator.java:266)
	at io.seata.server.coordinator.DefaultCoordinator.lambda$init$4(DefaultCoordinator.java:450)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.runAndReset$$$capture(FutureTask.java:308)
	at java.util.concurrent.FutureTask.runAndReset(FutureTask.java)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束

	at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
	at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
	at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
	at io.seata.core.store.db.LogStoreDataBaseDAO.queryGlobalTransactionDO(LogStoreDataBaseDAO.java:207)
	... 15 common frames omitted

Ⅲ. Describe what you expected to happen

When dbType is Oracle, the SQL returned by the method should change "limit ?" to "ROWNUM <= ?" and put it in where clause to support Oracle.

Ⅳ. How to reproduce it (as minimally and precisely as possible)

1. Execute seata-sample project with Oracle database.

Ⅴ. Anything else we need to know?

Ⅵ. Environment:

• JDK version : 1.8
• OS : Windows 10

[fescar-robot]

Hi @wangrui821, we detect non-English characters in the issue. This comment is an auto translation from @fescar-robot to help other users to understand this issue.
We encourage you to describe your issue in English which is more friendly to other users.

The SQL returned by getQueryGlobalTransactionSQLByStatus method in LogStoreSqls doesn't support Oracle

• I have searched the issues of this repository and believe that this is not a duplicate.

Ⅰ. Issue Description

The SQL returned by getQueryGlobalTransactionSQLByStatus method in LogStoreSqls doesn't support Oracle.

Ⅱ. Describe what happened

When seata-server is running, exception below occured:

2019-09-07 11:53:29.432 INFO [TxTimeoutCheck_1]io.seata.server.coordinator.DefaultCoordinator.lambda$init$4:452 -Exception timeout checking ... 
io.seata.common.exception.StoreException: ORA-00933: SQL command did not end correctly

	at io.seata.core.store.db.LogStoreDataBaseDAO.queryGlobalTransactionDO(LogStoreDataBaseDAO.java:213)
	at io.seata.server.store.db.DatabaseTransactionStoreManager.readSession(DatabaseTransactionStoreManager.java:174)
	at io.seata.server.store.db.DatabaseTransactionStoreManager.readSession(DatabaseTransactionStoreManager.java:203)
	at io.seata.server.session.db.DataBaseSessionManager.findGlobalSessions(DataBaseSessionManager.java:183)
	at io.seata.server.session.db.DataBaseSessionManager.allSessions(DataBaseSessionManager.java:172)
	at io.seata.server.coordinator.DefaultCoordinator.timeoutCheck(DefaultCoordinator.java:266)
	at io.seata.server.coordinator.DefaultCoordinator.lambda$init$4(DefaultCoordinator.java:450)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.runAndReset$$capture(FutureTask.java:308)
	at java.util.concurrent.FutureTask.runAndReset(FutureTask.java)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command did not end correctly

	at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
	at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
	at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
	at io.seata.core.store.db.LogStoreDataBaseDAO.queryGlobalTransactionDO(LogStoreDataBaseDAO.java:207)
	... 15 common frames omitted

Ⅲ. Describe what you expected to happen

When dbType is Oracle, the SQL returned by the method should change "limit ?" to "ROWNUM <= ?" and put it in where clause to support Oracle.

Ⅳ. How to reproduce it (as minimally and precisely as possible)

1. Execute seata-sample project with Oracle database.

Ⅴ. Anything else we need to know?

Ⅵ. Environment:

• JDK version : 1.8
• OS : Windows 10

[zjinlei]

thanks
Here uses the mysql limit, not compatible with oracle.
we will fix it.

[wangrui821]

@zjinlei Thanks!

[tq02ksu]

any body also have the problem? please aprove the pr:#1628