makssent opened a new issue, #40269: URL: https://github.com/apache/shardingsphere/issues/40269
### 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 statements in a transaction with [`isc_tpb_autocommit`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/tra.cpp#L3312-L3314) as the Firebird server does. The Firebird server [commits with retaining](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/jrd.cpp#L1400-L1411) after each statement, so the cursor of a SELECT stays open and the client can fetch its rows. ### Actual behavior In a transaction with [`isc_tpb_autocommit`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/tra.cpp#L3312-L3314) the proxy closes the cursor of a SELECT before the client fetches its rows. The same statements were executed directly on the Firebird server and through the proxy, and the rows were read directly from the server: | Steps | Firebird server | Proxy | | --- | --- | --- | | `INSERT INTO T_PLAIN (ID, NAME) VALUES (8991, 'autocommit')` | Success, 1 row | Success, 1 row | | `SELECT ID, NAME FROM T_PLAIN WHERE ID = 8991` | The row with ID = 8991 | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "The result set is closed" | | `INSERT INTO T_PLAIN (ID, NAME) VALUES (8992, 'autocommit 2')` | Success, 1 row | Success, 1 row | | Read the rows with ID = 8991 and ID = 8992 directly on the server | Both rows are saved | Both rows are saved | So through the proxy an application that uses Firebird auto-commit can change data, but cannot read any rows with SELECT. ### Reason analysis - [`FirebirdStartTransactionPacket#isAutoCommit`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/transaction/FirebirdStartTransactionPacket.java#L48-L50) reads [`isc_tpb_autocommit`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/tra.cpp#L3312-L3314), and [`FirebirdStartTransactionCommandExecutor#execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/transaction/FirebirdStartTransactionCommandExecutor.java#L46) puts it into [`ConnectionSession`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/session/ConnectionSession.java#L57). So the proxy runs th e statements of this transaction in auto-commit mode, outside a transaction. - [`FirebirdExecuteStatementCommandExecutor#execute`](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#L88-L89) keeps the handler of the SELECT for the next [`op_fetch`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5253-L5255) and marks it as in use. But after each command [`CommandExecutorTask#finish`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/core/src/main/java/org/apache/shardingsphere/proxy/frontend/command/CommandExecutorTask.java#L162) calls [`ProxyDatabaseConnectionManager#closeExecutionResources`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/connector/ProxyD atabaseConnectionManager.java#L326-L341), which outside a transaction [closes](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/connector/ProxyDatabaseConnectionManager.java#L329-L331) the handlers in use too. - The client reads the rows with separate [`op_fetch`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5253-L5255) packets. When the first one comes, [`FirebirdFetchStatementCommandExecutor#getQueryRowPacket`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/fetch/FirebirdFetchStatementCommandExecutor.java#L71-L84) reads the closed result set of the backend, and the backend Jaybird [throws](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBResultSet.java#L336-L340) "The result set is closed". ### Steps to reproduce #### Environment - Firebird server 5.0. - Client: Jaybird 6.0.7. #### 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. Connect with the Jaybird property [`useFirebirdAutocommit`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jaybird/props/PropertyNames.java#L74)`=true` and keep auto-commit on, so that Jaybird [adds](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/gds/TransactionParameterBuffer.java#L99-L107) [`isc_tpb_autocommit`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/tra.cpp#L3312-L3314) to the transaction. 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 6.0.7, 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; import java.util.Properties; public class FirebirdAutocommit { // 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 8991 AND 8992"); firebirdAutocommit(); executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 8991 AND 8992"); } // Firebird server: "INSERT: 1 row", "SELECT: ID = 8991", "INSERT: 1 row", // "ID = 8991: the row is saved", "ID = 8992: the row is saved"; // proxy: "INSERT: 1 row", "SELECT: The result set is closed", "INSERT: 1 row", // "ID = 8991: the row is saved", "ID = 8992: the row is saved" static void firebirdAutocommit() throws SQLException { Properties properties = new Properties(); properties.setProperty("user", USER); properties.setProperty("password", PASSWORD); // In auto-commit mode Jaybird starts the transaction with isc_tpb_autocommit properties.setProperty("useFirebirdAutocommit", "true"); try (Connection connection = DriverManager.getConnection(URL, properties)) { update(connection, "INSERT INTO T_PLAIN (ID, NAME) VALUES (8991, 'autocommit')"); try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT ID, NAME FROM T_PLAIN WHERE ID = 8991")) { System.out.println("SELECT: " + (resultSet.next() ? "ID = " + resultSet.getInt(1) : "no row")); } catch (final SQLException ex) { System.out.println("SELECT: " + ex.getMessage()); } update(connection, "INSERT INTO T_PLAIN (ID, NAME) VALUES (8992, 'autocommit 2')"); } printRow(8991); printRow(8992); } static void update(final Connection connection, final String sql) throws SQLException { try (Statement statement = connection.createStatement()) { System.out.println("INSERT: " + statement.executeUpdate(sql) + " row"); } } // 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 lost")); } } 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]
