You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/01/08 12:18:45 UTC
[GitHub] [incubator-shardingsphere] geomonlin opened a new issue #3904:
Could not roll back JDBC transaction using postgresql in sharding-proxy
geomonlin opened a new issue #3904: Could not roll back JDBC transaction using postgresql in sharding-proxy
URL: https://github.com/apache/incubator-shardingsphere/issues/3904
shardingsphere version: sharding-proxy dev
database: postgresql
throw new RuntimeException in transaction rolled back error.
example code:
print log:
`Caused by: org.postgresql.util.PSQLException: Expected command status BEGIN, got .
at org.postgresql.core.v3.QueryExecutorImpl$1.handleCommandStatus(QueryExecutorImpl.java:536) ~[postgresql-42.2.5.jar:42.2.5]
at org.postgresql.core.v3.QueryExecutorImpl.interpretCommandStatus(QueryExecutorImpl.java:2492) ~[postgresql-42.2.5.jar:42.2.5]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2127) ~[postgresql-42.2.5.jar:42.2.5]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308) ~[postgresql-42.2.5.jar:42.2.5]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) ~[postgresql-42.2.5.jar:42.2.5]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365) ~[postgresql-42.2.5.jar:42.2.5]
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143) ~[postgresql-42.2.5.jar:42.2.5]
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:132) ~[postgresql-42.2.5.jar:42.2.5]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-java7-2.4.11.jar:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-java7-2.4.11.jar:na]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46) ~[mybatis-3.4.2.jar:3.4.2]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.4.2.jar:3.4.2]
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.4.2.jar:3.4.2]
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.4.2.jar:3.4.2]
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.4.2.jar:3.4.2]
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198) ~[mybatis-3.4.2.jar:3.4.2]
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185) ~[mybatis-3.4.2.jar:3.4.2]
at sun.reflect.GeneratedMethodAccessor10.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_221]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_221]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434) ~[mybatis-spring-1.3.0.jar:1.3.0]
... 47 common frames omitted
**Could not roll back JDBC transaction; nested exception is java.sql.SQLException: Connection is closed**`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] cherrylzhao commented on issue #3904:
Could not roll back JDBC transaction using postgresql in sharding-proxy
Posted by GitBox <gi...@apache.org>.
cherrylzhao commented on issue #3904: Could not roll back JDBC transaction using postgresql in sharding-proxy
URL: https://github.com/apache/incubator-shardingsphere/issues/3904#issuecomment-572379279
you should send sql `sctl:set transaction_type=XA` to proxy to switch transaction type
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] geomonlin closed issue #3904: Could not
roll back JDBC transaction using postgresql in sharding-proxy
Posted by GitBox <gi...@apache.org>.
geomonlin closed issue #3904: Could not roll back JDBC transaction using postgresql in sharding-proxy
URL: https://github.com/apache/incubator-shardingsphere/issues/3904
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] geomonlin commented on issue #3904:
Could not roll back JDBC transaction using postgresql in sharding-proxy
Posted by GitBox <gi...@apache.org>.
geomonlin commented on issue #3904: Could not roll back JDBC transaction using postgresql in sharding-proxy
URL: https://github.com/apache/incubator-shardingsphere/issues/3904#issuecomment-572381764
Yeah, the problem is configuration way error in my code.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] geomonlin commented on issue #3904:
Could not roll back JDBC transaction using postgresql in sharding-proxy
Posted by GitBox <gi...@apache.org>.
geomonlin commented on issue #3904: Could not roll back JDBC transaction using postgresql in sharding-proxy
URL: https://github.com/apache/incubator-shardingsphere/issues/3904#issuecomment-572377182
I used the mybatis but did not set setAutoCommit (false)
already **set max_prepared_transactions > 0**
code:
` TransactionTypeHolder.set(TransactionType.XA);
jdbcTemplate.execute(new ConnectionCallback<Object>() {
@Override
public Object doInConnection(final Connection connection) throws SQLException, DataAccessException {
connection.setAutoCommit(false);
doInsert(connection);
printData(jdbcTemplate, "----------------- query all before commit ------------------");
connection.rollback();
printData(jdbcTemplate, "----------------- query all after rollback ------------------");
return null;
}
});`
Now. I use row-jdbc and set connection.setAutoCommit(false);
It appear `org.postgresql.util.PSQLException: Expected command status BEGIN, got .
at org.postgresql.core.v3.QueryExecutorImpl$1.handleCommandStatus(QueryExecutorImpl.java:536)
at org.postgresql.core.v3.QueryExecutorImpl.interpretCommandStatus(QueryExecutorImpl.java:2492)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2127)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:120)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.example.transaction.xa.raw.jdbc.ExampleMainPg.doInsert(ExampleMainPg.java:108)
at org.apache.shardingsphere.example.transaction.xa.raw.jdbc.ExampleMainPg.access$000(ExampleMainPg.java:37)
at org.apache.shardingsphere.example.transaction.xa.raw.jdbc.ExampleMainPg$2.doInConnection(ExampleMainPg.java:91)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:353)
at org.apache.shardingsphere.example.transaction.xa.raw.jdbc.ExampleMainPg.process(ExampleMainPg.java:87)
at org.apache.shardingsphere.example.transaction.xa.raw.jdbc.ExampleMainPg.main(ExampleMainPg.java:53)`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] geomonlin edited a comment on issue
#3904: Could not roll back JDBC transaction using postgresql in
sharding-proxy
Posted by GitBox <gi...@apache.org>.
geomonlin edited a comment on issue #3904: Could not roll back JDBC transaction using postgresql in sharding-proxy
URL: https://github.com/apache/incubator-shardingsphere/issues/3904#issuecomment-572377182
I used the mybatis but did not set setAutoCommit (false)
code:
` TransactionTypeHolder.set(TransactionType.XA);
jdbcTemplate.execute(new ConnectionCallback<Object>() {
@Override
public Object doInConnection(final Connection connection) throws SQLException, DataAccessException {
connection.setAutoCommit(false);
doInsert(connection);
printData(jdbcTemplate, "----------------- query all before commit ------------------");
connection.rollback();
printData(jdbcTemplate, "----------------- query all after rollback ------------------");
return null;
}
});`
Now. I use row-jdbc and set connection.setAutoCommit(false);
already **set max_prepared_transactions > 0**
It appear `org.postgresql.util.PSQLException: Expected command status BEGIN, got .
at org.postgresql.core.v3.QueryExecutorImpl$1.handleCommandStatus(QueryExecutorImpl.java:536)
at org.postgresql.core.v3.QueryExecutorImpl.interpretCommandStatus(QueryExecutorImpl.java:2492)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2127)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:120)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.example.transaction.xa.raw.jdbc.ExampleMainPg.doInsert(ExampleMainPg.java:108)
at org.apache.shardingsphere.example.transaction.xa.raw.jdbc.ExampleMainPg.access$000(ExampleMainPg.java:37)
at org.apache.shardingsphere.example.transaction.xa.raw.jdbc.ExampleMainPg$2.doInConnection(ExampleMainPg.java:91)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:353)
at org.apache.shardingsphere.example.transaction.xa.raw.jdbc.ExampleMainPg.process(ExampleMainPg.java:87)
at org.apache.shardingsphere.example.transaction.xa.raw.jdbc.ExampleMainPg.main(ExampleMainPg.java:53)`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] cherrylzhao commented on issue #3904:
Could not roll back JDBC transaction using postgresql in sharding-proxy
Posted by GitBox <gi...@apache.org>.
cherrylzhao commented on issue #3904: Could not roll back JDBC transaction using postgresql in sharding-proxy
URL: https://github.com/apache/incubator-shardingsphere/issues/3904#issuecomment-572060946
see #3545, postgresql do not open XA set by default
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] geomonlin edited a comment on issue
#3904: Could not roll back JDBC transaction using postgresql in
sharding-proxy
Posted by GitBox <gi...@apache.org>.
geomonlin edited a comment on issue #3904: Could not roll back JDBC transaction using postgresql in sharding-proxy
URL: https://github.com/apache/incubator-shardingsphere/issues/3904#issuecomment-572377182
I used the mybatis but did not set setAutoCommit (false)
code:
```
TransactionTypeHolder.set(TransactionType.XA);
jdbcTemplate.execute(new ConnectionCallback<Object>() {
@Override
public Object doInConnection(final Connection connection) throws SQLException, DataAccessException {
connection.setAutoCommit(false);
doInsert(connection);
printData(jdbcTemplate, "----------------- query all before commit ------------------");
connection.rollback();
printData(jdbcTemplate, "----------------- query all after rollback ------------------");
return null;
}
});`
Now. I use row-jdbc and set connection.setAutoCommit(false);
already **set max_prepared_transactions > 0**
It appear `org.postgresql.util.PSQLException: Expected command status BEGIN, got .
at org.postgresql.core.v3.QueryExecutorImpl$1.handleCommandStatus(QueryExecutorImpl.java:536)
at org.postgresql.core.v3.QueryExecutorImpl.interpretCommandStatus(QueryExecutorImpl.java:2492)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2127)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:120)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.example.transaction.xa.raw.jdbc.ExampleMainPg.doInsert(ExampleMainPg.java:108)
at org.apache.shardingsphere.example.transaction.xa.raw.jdbc.ExampleMainPg.access$000(ExampleMainPg.java:37)
at org.apache.shardingsphere.example.transaction.xa.raw.jdbc.ExampleMainPg$2.doInConnection(ExampleMainPg.java:91)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:353)
at org.apache.shardingsphere.example.transaction.xa.raw.jdbc.ExampleMainPg.process(ExampleMainPg.java:87)
at org.apache.shardingsphere.example.transaction.xa.raw.jdbc.ExampleMainPg.main(ExampleMainPg.java:53)
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services