makssent opened a new issue, #40270: URL: https://github.com/apache/shardingsphere/issues/40270
### Before reporting a bug - [x] I have searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - [x] I have read the [ShardingSphere documentation](https://shardingsphere.apache.org/document/current/en/overview). ### ShardingSphere version master, commit [`01446d4fad4`](https://github.com/apache/shardingsphere/commit/01446d4fad48450e7fc3eb3b3631b25d6bf411df) ### ShardingSphere project ShardingSphere-Proxy ### Expected behavior The proxy should execute [`COMMIT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5335-L5338) sent as an SQL statement as the Firebird server does. The server [commits the transaction](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/StmtNodes.cpp#L9669-L9678) of the statement and [releases its handle](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L4003-L4008), so the next statement with this handle is rejected. ### Actual behavior The proxy commits its backend transaction, but keeps the client transaction handle valid and executes the next statements with it. The same steps were executed directly on the Firebird server and through the proxy on a connection with auto-commit off, and the rows were read directly from the server: | Steps | Firebird server | Proxy | | --- | --- | --- | | `INSERT INTO T_PLAIN (ID, NAME) VALUES (9001, 'before')` | Success | Success | | `COMMIT` with [`Statement.execute`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBStatement.java#L578-L583) | Success | Success | | `INSERT INTO T_PLAIN (ID, NAME) VALUES (9002, 'after')` | Error [`isc_bad_trans_handle`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/remote.h#L1501-L1504) "invalid transaction handle (expecting explicit transaction start)" | Success | | [`Connection.rollback()`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBConnection.java#L404-L420) | Jaybird [reports](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBConnection.java#L407-L410) "You cannot rollback closed connection." | Success | | Read the rows with ID = 9001 and ID = 9002 | The row with ID = 9001 is saved, the row with ID = 9002 is not saved | The row with ID = 9001 is saved, the row with ID = 9002 is not saved | So through the proxy an application keeps writing in a transaction that it has already committed with `COMMIT`, while the Firebird server rejects these statements. No error tells the application that its transaction has ended. ### Reason analysis - The proxy executes the `COMMIT` statement of [`op_execute`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5243-L5245) with [`CommitProxyBackendHandler#execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/handler/tcl/local/type/CommitProxyBackendHandler.java#L52-L56), which commits only the backend transaction. - The client transaction handle is removed from [`FirebirdTransactionIdGenerator`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/transaction/FirebirdTransactionIdGenerator.java#L32) by [`FirebirdTransactionIdGenerator#closeTransaction`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/transaction/FirebirdTransactionIdGenerator.java#L98-L100). Only [`FirebirdCommitTransactionCommandExecutor#execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/transaction/FirebirdCommitTransactionCommandExecutor.java#L52) for [`op_commit`](https://github. com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L3413-L3415) and [`FirebirdRollbackTransactionCommandExecutor#execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/transaction/FirebirdRollbackTransactionCommandExecutor.java#L51) for [`op_rollback`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L3417-L3419) call it, so after the `COMMIT` statement the handle stays active. - The Firebird server rejects the released handle already in [`op_prepare_statement`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5262-L5264), but [`FirebirdPrepareStatementCommandExecutor#execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L113-L127) does not check the transaction handle. [`FirebirdExecuteStatementCommandExecutor#validateTransactionHandle`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/execute/FirebirdExecuteStatementCommandExecutor.java#L105-L108) and [`FirebirdRollbackTransactionCommandExecutor#validateTransactionHandle`](https://github.com/apache/shar dingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/transaction/FirebirdRollbackTransactionCommandExecutor.java#L55-L59) accept the old handle, so the next INSERT and the rollback succeed. ### Steps to reproduce #### Environment - Firebird server 5.0. - Client: Jaybird 5.0.13. #### Steps 1. Create the table on the Firebird server: ```sql CREATE TABLE T_PLAIN (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(20)); ``` 2. Start ShardingSphere-Proxy with this configuration: ```yaml # global.yaml authority: users: - user: root@% password: root privilege: type: ALL_PERMITTED props: proxy-frontend-database-protocol-type: Firebird ``` ```yaml # database-firebird.yaml databaseName: firebird_db dataSources: ds_0: url: jdbc:firebirdsql://<host>/<database file> username: <user> password: <password> rules: - !SINGLE tables: - "*.*" ``` 3. Execute the steps from the table under "Actual behavior" through the proxy and directly on the Firebird server, and compare the results. "Example code" runs all of them. ### Example code All cases on Jaybird 5.0.13, one method per case. The comment above each method shows what it prints against the Firebird server and through the proxy. ```java import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class CommitStatement { // The proxy; put the URL of the Firebird server here to compare static final String URL = "jdbc:firebirdsql://<proxy host>:<proxy port>/firebird_db?encoding=UTF8&wireCrypt=DISABLED"; static final String USER = "root"; static final String PASSWORD = "root"; // The Firebird server, to read the rows directly static final String SERVER_URL = "jdbc:firebirdsql://<host>/<database file>"; static final String SERVER_USER = "<user>"; static final String SERVER_PASSWORD = "<password>"; public static void main(final String[] args) throws Exception { executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 9001 AND 9002"); commitStatement(); executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 9001 AND 9002"); } // Firebird server: "INSERT 9001: success", "COMMIT: success", // "INSERT 9002: invalid transaction handle (expecting explicit transaction start) [SQLState:08003, ISC error code:335544332]", // "rollback: You cannot rollback closed connection.", "ID = 9001: the row is saved", "ID = 9002: the row is not saved"; // proxy: "INSERT 9001: success", "COMMIT: success", "INSERT 9002: success", "rollback: success", // "ID = 9001: the row is saved", "ID = 9002: the row is not saved" static void commitStatement() throws SQLException { try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { connection.setAutoCommit(false); execute(connection, "INSERT 9001", "INSERT INTO T_PLAIN (ID, NAME) VALUES (9001, 'before')"); // Jaybird 5 sends COMMIT as an SQL statement in the transaction of the connection execute(connection, "COMMIT", "COMMIT"); // The same transaction handle as before COMMIT execute(connection, "INSERT 9002", "INSERT INTO T_PLAIN (ID, NAME) VALUES (9002, 'after')"); try { connection.rollback(); System.out.println("rollback: success"); } catch (final SQLException ex) { System.out.println("rollback: " + ex.getMessage()); } } printRow(9001); printRow(9002); } static void execute(final Connection connection, final String step, final String sql) { try (Statement statement = connection.createStatement()) { statement.execute(sql); System.out.println(step + ": success"); } catch (final SQLException ex) { System.out.println(step + ": " + ex.getMessage()); } } // Reads the row directly on the Firebird server static void printRow(final int id) throws SQLException { try (Connection connection = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD); ResultSet resultSet = connection.createStatement().executeQuery("SELECT NAME FROM T_PLAIN WHERE ID = " + id)) { System.out.println("ID = " + id + ": " + (resultSet.next() ? "the row is saved" : "the row is not saved")); } } static void executeOnServer(final String sql) throws SQLException { try (Connection connection = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD)) { connection.createStatement().execute(sql); } } } ``` -- 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]
