makssent opened a new issue, #40268: URL: https://github.com/apache/shardingsphere/issues/40268
### 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 apply the statement timeout that the client sends in [`op_execute`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5243-L5245) as the Firebird server [does](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L3947), and cancel the statement when the timeout expires. ### Actual behavior The proxy does not apply the timeout, and the statement runs to the end without an error. The same steps 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 a row with ID = 8881 without auto-commit | Success | Success | | [`setQueryTimeout(1)`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBStatement.java#L516-L521), then `EXECUTE PROCEDURE P_AUDIT_SPIN(30000000)`, which runs about 6 seconds without a timeout | Error [`isc_cancelled`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/jrd.cpp#L9270-L9275) with [`isc_req_stmt_timeout`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/DsqlRequests.cpp#L128-L134) "operation was cancelled; Statement level timeout expired." | Success, the procedure runs to the end | | Insert a row with ID = 8882, commit | Success | Success | | Read the rows with ID = 8881 and 8882 directly on the server | The rows are saved | The rows are saved | So through the proxy a statement that the Firebird server cancels runs to the end, and the application waits longer than the timeout it set and gets no error. ### Reason analysis - On protocol 16 and later Jaybird [sends](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/gds/ng/wire/version16/V16Statement.java#L71-L77) the timeout in the field [`p_sqldata_timeout`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L3947) of [`op_execute`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5243-L5245), and the Firebird server [sets](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L3947) it on the statement. - [`FirebirdExecuteStatementPacket`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/execute/FirebirdExecuteStatementPacket.java#L92-L94) reads this field into [`statementTimeout`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/execute/FirebirdExecuteStatementPacket.java#L55), but nothing in the Firebird frontend of the proxy uses it. - [`FirebirdExecuteStatementCommandExecutor#executePreparedStatement`](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#L117-L120) executes the statement on the backend without the timeout, so the backend statement runs to the end. ### Steps to reproduce #### Environment - Firebird server 5.0. - Client: Jaybird 6.0.7. #### Steps 1. Create the table and the procedure on the Firebird server: ```sql CREATE TABLE T_PLAIN (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(20)); SET TERM ^ ; CREATE OR ALTER PROCEDURE P_AUDIT_SPIN (N INTEGER) RETURNS (R INTEGER) AS DECLARE I INTEGER = 0; BEGIN WHILE (I < N) DO I = I + 1; R = I; END^ SET TERM ; ^ ``` 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 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; public class StatementTimeout { // 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 8881 AND 8882"); statementTimeout(); executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 8881 AND 8882"); } // Firebird server: "EXECUTE PROCEDURE: operation was cancelled; Statement level timeout expired."; // proxy: "EXECUTE PROCEDURE: success"; // both: "ID = 8881: the row is saved", "ID = 8882: the row is saved" static void statementTimeout() throws SQLException { try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { connection.setAutoCommit(false); update(connection, "INSERT INTO T_PLAIN (ID, NAME) VALUES (8881, 'before')"); try (Statement statement = connection.createStatement()) { // Jaybird sends the timeout of 1000 ms in op_execute statement.setQueryTimeout(1); // Without a timeout the procedure runs about 6 seconds statement.execute("EXECUTE PROCEDURE P_AUDIT_SPIN(30000000)"); System.out.println("EXECUTE PROCEDURE: success"); } catch (final SQLException ex) { System.out.println("EXECUTE PROCEDURE: " + ex.getMessage()); } update(connection, "INSERT INTO T_PLAIN (ID, NAME) VALUES (8882, 'after')"); connection.commit(); } printRow(8881); printRow(8882); } static void update(final Connection connection, final String sql) throws SQLException { try (Statement statement = connection.createStatement()) { statement.executeUpdate(sql); } } // 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]
