makssent opened a new issue, #40238: URL: https://github.com/apache/shardingsphere/issues/40238
### 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 answer [`op_commit_retaining`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L3425-L3427), [`op_rollback_retaining`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L3421-L3423), [`op_info_transaction`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L4696-L4700), [`op_detach`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L3250-L3253) and [`op_fetch_scroll`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5253-L5255) as the Firebird server does. If the proxy does not support an operation, it should answer with an error, so that the client does not assume that the operation was done. ### Actual behavior The proxy answers each of these operations with an empty successful response and does nothing. The same steps were executed directly on the Firebird server and through the proxy. After each case the rows were read directly from the Firebird server. | Steps | Firebird server | Proxy | | --- | --- | --- | | Insert a row with ID = 8751, [`op_commit_retaining`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L3425-L3427), rollback | The row is saved | The row is lost | | Insert a row with ID = 8752, [`op_rollback_retaining`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L3421-L3423), commit | The row is not saved | The row is saved | | [`op_info_transaction`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L4696-L4700) for the [isolation level](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/inf.cpp#L1241-L1260), [access mode](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/inf.cpp#L1263-L1272) and [lock timeout](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/inf.cpp#L1275-L1277) | 17 bytes: read committed, read-write, lock timeout 0 | Empty answer, 0 bytes | | Insert a row with ID = 8753, [`op_detach`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L3250-L3253) | Error [`isc_open_trans`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/jrd.cpp#L8268-L8271) "cannot disconnect database with open transactions (1 active)" | Success | | Commit after [`op_detach`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L3250-L3253) | Error [`isc_att_shutdown`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/jrd.cpp#L6753-L6769) "connection shutdown", the row is not saved | Success, the row is saved | | Server-side scrollable cursor ([`scrollableCursor`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jaybird/props/PropertyNames.java#L84)`=SERVER`, [`op_fetch_scroll`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5253-L5255)) over the rows with ID from 8755 to 8758, calls [`last()`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBResultSet.java#L850), [`absolute(2)`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBResultSet.java#L865), [`previous()`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBResultSet.java#L883), [`relative(2)`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBResultSet.java#L874), [`first()`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBResultSet.java#L841) | ID = 8758, 8756, 8755, 8757, 8755 | No row for any call, Ja ybird [reports](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBResultSet.java#L521-L526) "The result set is not in a row, use next" | So through the proxy an application loses changes it committed with [`op_commit_retaining`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L3425-L3427), keeps changes it rolled back with [`op_rollback_retaining`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L3421-L3423), gets no transaction information and cannot read rows with a server-side scrollable cursor. No error tells it that something went wrong. ### Reason analysis - [`FirebirdCommandExecutorFactory#newInstance`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/FirebirdCommandExecutorFactory.java#L144-L145) has no executor for these operations, so its [`default`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/FirebirdCommandExecutorFactory.java#L144) branch creates [`FirebirdUnsupportedCommandExecutor`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/admin/FirebirdUnsupportedCommandExecutor.java#L32). - [`FirebirdUnsupportedCommandExecutor#execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/admin/FirebirdUnsupportedCommandExecutor.java#L35-L38) returns [`FirebirdGenericResponsePacket`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/generic/FirebirdGenericResponsePacket.java#L32) without an error, and the client reads it as success. The method has the comment [`// TODO send proper error packet`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/admin/FirebirdUnsupportedCommandExecutor.java#L36). ### 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. 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 org.firebirdsql.gds.TransactionParameterBuffer; import org.firebirdsql.gds.ng.FbStatement; import org.firebirdsql.gds.ng.FbTransaction; import org.firebirdsql.gds.ng.LockCloseable; import org.firebirdsql.gds.ng.fields.RowValue; import org.firebirdsql.gds.ng.wire.FbWireDatabase; import org.firebirdsql.jaybird.fb.constants.TpbItems; import org.firebirdsql.jdbc.FirebirdConnection; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import java.util.Properties; import java.util.concurrent.Callable; public class UnimplementedOperations { // 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 8751 AND 8758"); try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { // Jaybird wire API behind the JDBC connection, to send raw packets FbWireDatabase db = (FbWireDatabase) connection.unwrap(FirebirdConnection.class).getFbDatabase(); commitRetaining(db); rollbackRetaining(db); transactionInfo(db); // Last: after op_detach the Firebird server shuts the connection down detachWithActiveTransaction(db); } scrollableCursor(); } // Firebird server: "ID = 8751: the row is saved"; proxy: "ID = 8751: the row is lost" static void commitRetaining(final FbWireDatabase db) throws Exception { FbTransaction transaction = startWithRow(db, 8751); send(db, 50, transaction.getHandle()); // op_commit_retaining transaction.rollback(); printRow(8751); } // Firebird server: "ID = 8752: the row is lost"; proxy: "ID = 8752: the row is saved" static void rollbackRetaining(final FbWireDatabase db) throws Exception { FbTransaction transaction = startWithRow(db, 8752); send(db, 86, transaction.getHandle()); // op_rollback_retaining transaction.commit(); printRow(8752); } // Firebird server: "op_info_transaction: 17 bytes"; proxy: "op_info_transaction: 0 bytes" static void transactionInfo(final FbWireDatabase db) throws Exception { FbTransaction transaction = db.startTransaction(tpb(db)); // isc_info_tra_isolation, isc_info_tra_access, isc_info_tra_lock_timeout byte[] info = transaction.getTransactionInfo(new byte[]{8, 9, 10}, 64); System.out.println("op_info_transaction: " + info.length + " bytes"); transaction.rollback(); } // Firebird server: isc_open_trans, then isc_att_shutdown, "ID = 8753: the row is lost"; // proxy: success, success, "ID = 8753: the row is saved" static void detachWithActiveTransaction(final FbWireDatabase db) throws Exception { FbTransaction transaction = startWithRow(db, 8753); try { send(db, 21, 0); // op_detach, the object is 0 System.out.println("op_detach: success"); } catch (final SQLException ex) { System.out.println("op_detach: " + ex.getMessage()); } try { transaction.commit(); System.out.println("commit: success"); } catch (final SQLException ex) { System.out.println("commit: " + ex.getMessage()); } printRow(8753); } // Firebird server: "scrollable cursor: 8758, 8756, 8755, 8757, 8755"; // proxy: "The result set is not in a row, use next" for each call static void scrollableCursor() throws Exception { for (int id = 8755; id <= 8758; id++) { executeOnServer("INSERT INTO T_PLAIN (ID, NAME) VALUES (" + id + ", 'scroll')"); } Properties properties = new Properties(); properties.setProperty("user", USER); properties.setProperty("password", PASSWORD); // Jaybird reads the result set with op_fetch_scroll properties.setProperty("scrollableCursor", "SERVER"); try (Connection connection = DriverManager.getConnection(URL, properties)) { connection.setAutoCommit(false); try (Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet resultSet = statement.executeQuery("SELECT ID FROM T_PLAIN WHERE ID BETWEEN 8755 AND 8758 ORDER BY ID")) { List<String> result = new ArrayList<>(); for (Callable<Boolean> move : List.<Callable<Boolean>>of(resultSet::last, () -> resultSet.absolute(2), resultSet::previous, () -> resultSet.relative(2), resultSet::first)) { try { move.call(); result.add(String.valueOf(resultSet.getInt(1))); } catch (final SQLException ex) { result.add(ex.getMessage()); } } System.out.println("scrollable cursor: " + String.join(", ", result)); } } } // A transaction with read committed, record version, read-write and no wait static TransactionParameterBuffer tpb(final FbWireDatabase db) { TransactionParameterBuffer result = db.createTransactionParameterBuffer(); result.addArgument(TpbItems.isc_tpb_read_committed); result.addArgument(TpbItems.isc_tpb_rec_version); result.addArgument(TpbItems.isc_tpb_write); result.addArgument(TpbItems.isc_tpb_nowait); return result; } // Starts a transaction and inserts the row with the given ID in it static FbTransaction startWithRow(final FbWireDatabase db, final int id) throws SQLException { FbTransaction result = db.startTransaction(tpb(db)); FbStatement statement = db.createStatement(result); statement.prepare("INSERT INTO T_PLAIN (ID, NAME) VALUES (" + id + ", 'raw')"); statement.execute(RowValue.EMPTY_ROW_VALUE); statement.close(); return result; } // Jaybird has no method for these operations, so the packet is written directly. // An error answer is thrown as SQLException. static void send(final FbWireDatabase db, final int operation, final int object) throws Exception { try (LockCloseable ignored = db.withLock()) { // close() of a statement does not wait for its response; read it first, // so that the next response is the answer to this operation db.completeDeferredActions(); db.getXdrStreamAccess().withTransmitLock(out -> { out.writeInt(operation); out.writeInt(object); out.flush(); }); db.readGenericResponse(null); } } // 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]
