makssent opened a new issue, #34973:
URL: https://github.com/apache/shardingsphere/issues/34973
### Which version of ShardingSphere did you use?
5.5.2
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
------
**Hello everyone!**
While working with XA transactions and the Firebird database, I encountered
a problem that I don’t yet know how to solve. Perhaps someone has some ideas?
This issue may not be directly related to ShardingSphere's code but rather to
its interaction with Firebird.
### Problem Description
When initializing a connection, ShardingSphere retrieves metadata for each
table in the database. Example code:
```Java
try {
connection = dataSource.getConnection();
connection.setAutoCommit(false);
Statement statement = null;
try {
statement = connection.createStatement();
```
In Firebird, the `FBStandAloneConnectionManager` ([GitHub
link](https://github.com/FirebirdSQL/jaybird/blob/master/src/main/org/firebirdsql/jaybird/xca/FBStandAloneConnectionManager.java))
is responsible for regular connections and transaction execution. In this
class, there is a variable `mc.setManagedEnvironment(false);` that changes
during operation. When we retrieve metadata, this variable is modified.
Then, we execute the required query:
```Java
String sql1 = "INSERT INTO T_ORDER (USER_ID, ORDER_TYPE, ADDRESS_ID, STATUS)
VALUES (123, 1, 456, 'PENDING')";
String sql2 = "INSERT INTO T_ORDER (USER_ID, ORDER_TYPE, ADDRESS_ID, STATUS)
VALUES (789, 2, 101, 'PROCESSING')";
Connection connection = null;
try {
connection = dataSource.getConnection();
connection.setAutoCommit(false);
Statement statement = null;
try {
statement = connection.createStatement();
statement.executeUpdate(sql1);
statement.executeUpdate(sql2);
```
Typically, for XA transactions in Firebird, the `XAConnectionManager`
([GitHub
link](https://github.com/FirebirdSQL/jaybird/blob/master/src/main/org/firebirdsql/ds/FBXADataSource.java))
is used, where the `ManagedEnvironment` variable is set to true:
```Java
@Override
public FirebirdConnection allocateConnection(FBManagedConnectionFactory mcf,
FBConnectionRequestInfo cxRequestInfo) throws SQLException {
FBManagedConnection mc = mcf.createManagedConnection(cxRequestInfo);
mc.setManagedEnvironment(true);
mc.addConnectionEventListener(this);
return mc.getConnection();
}
```
However, since we use transaction managers like `Atomikos or Narayana` in
our configuration, we don’t go through the `XAConnectionManager` (in
`Atomikos`, `UserTransactionManager` is used). As a result, the
`ManagedEnvironment` variable is not updated to true and remains false.
### Resulting Error
This leads to the following issue: when attempting to execute an XA
transaction, we receive the error:
`Connection enlisted in distributed transaction`. This occurs because the
InternalTransactionCoordinator ([GitHub
link](https://github.com/FirebirdSQL/jaybird/blob/master/src/main/org/firebirdsql/jdbc/InternalTransactionCoordinator.java))
performs a check:
```Java
void setTransactionCoordinator(boolean managedConnection, boolean
autoCommit) throws SQLException {
FBManagedConnection mc = connection.getManagedConnection();
InternalTransactionCoordinator.AbstractTransactionCoordinator
coordinator;
if (managedConnection && mc.inDistributedTransaction()) {
coordinator = new ManagedTransactionCoordinator(connection);
} else if (autoCommit) {
if (mc.inDistributedTransaction()) {
throw new SQLException("Connection enlisted in distributed
transaction",
SQLStateConstants.SQL_STATE_INVALID_TX_STATE);
}
```
Since we are using a DistributedTransaction, but managedConnection is false,
the error is thrown.
----
**Perhaps someone will find a solution faster than I can. Maybe I missed
something or have a misunderstanding — I would be grateful for any help.**
Config.yaml:
```yaml
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements. See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
mode:
type: Standalone
repository:
type: JDBC
# props:
# path: demo
dataSources:
# ds_1:
# dataSourceClassName: com.zaxxer.hikari.HikariDataSource
# driverClassName: com.mysql.cj.jdbc.Driver
# jdbcUrl:
jdbc:mysql://localhost:3306/SHARD5?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true&logger=com.mysql.cj.log.Slf4JLogger&profileSQL=true
# username: root
# password: password
# maxPoolSize: 10
ds_0:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: org.firebirdsql.jdbc.FBDriver
jdbcUrl:
jdbc:firebirdsql://localhost:3050/D:/sharding/Databases/SHARD1.FDB?useManagedConnection=true&log_level=4&trace_file=D:/sharding/logs/shard1_trace.log
username: sysdba
password: masterkey
maxPoolSize: 10
ds_1:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: org.firebirdsql.jdbc.FBDriver
jdbcUrl:
jdbc:firebirdsql://localhost:3050/D:/sharding/Databases/SHARD2.FDB?useManagedConnection=true&log_level=4&trace_file=D:/sharding/logs/shard2_trace.log
username: sysdba
password: masterkey
maxPoolSize: 10
# ds_2:
# dataSourceClassName: com.zaxxer.hikari.HikariDataSource
# driverClassName: org.firebirdsql.jdbc.FBDriver
# jdbcUrl:
jdbc:firebirdsql://localhost:3050/D:/sharding/Databases/SHARD3.FDB
# username: sysdba
# password: masterkey
# maxPoolSize: 10
# ds_3:
# dataSourceClassName: com.zaxxer.hikari.HikariDataSource
# driverClassName: org.firebirdsql.jdbc.FBDriver
# jdbcUrl:
jdbc:firebirdsql://localhost:3050/D:/sharding/Databases/SHARD4.FDB
# username: sysdba
# password: masterkey
# maxPoolSize: 10
rules:
- !SHARDING
tables:
T_ORDER:
actualDataNodes: ds_$->{0..1}.T_ORDER_$->{0..1}
tableStrategy:
standard:
shardingColumn: ORDER_ID
shardingAlgorithmName: T_ORDER_inline
keyGenerateStrategy:
column: ORDER_ID
keyGeneratorName: snowflake_generator
defaultDatabaseStrategy:
standard:
shardingColumn: USER_ID
shardingAlgorithmName: database_inline
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${USER_ID % 2}
T_ORDER_inline:
type: INLINE
props:
algorithm-expression: T_ORDER_$->{ORDER_ID % 2}
keyGenerators:
snowflake_generator:
type: SNOWFLAKE
transaction:
# defaultType: LOCAL
defaultType: XA
providerType: Atomikos
# props:
# transactionTimeout: 10
props:
sql-show: true
```
### You can get the sample code from:
https://github.com/apache/shardingsphere/tree/master/examples/shardingsphere-jdbc-example-generator,
when using the command: ```./mvnw -B clean install -f
examples/shardingsphere-jdbc-example-generator/pom.xml -Pexample-generator
-Dmodes=standalone -Dtransactions=xa-atomikos -Dfeatures=sharding
-Dframeworks=spring-boot-starter-jdbc```
The code for the test:
```Java
public void DoubleInsertTestWithoutAutoCommit() throws SQLException {
String sql1 = "INSERT INTO T_ORDER (USER_ID, ORDER_TYPE, ADDRESS_ID,
STATUS) VALUES (123, 1, 456, 'PENDING')";
String sql2 = "INSERT INTO T_ORDER (USER_ID, ORDER_TYPE, ADDRESS_ID,
STATUS) VALUES (789, 2, 101, 'PROCESSING')";
Connection connection = null;
try {
connection = dataSource.getConnection();
connection.setAutoCommit(false);
Statement statement = null;
try {
statement = connection.createStatement();
statement.executeUpdate(sql1);
statement.executeUpdate(sql2);
} catch (SQLException e) {
throw new RuntimeException(e);
}
connection.commit();
} catch (SQLException e) {
if (connection != null) {
connection.rollback();
}
throw e;
} finally {
if (connection != null) {
try {
connection.setAutoCommit(true);
connection.close();
} catch (SQLException e) {
LOGGER.error("Failed to close connection", e);
}
}
}
}
```
--
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.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
