makssent opened a new issue, #40246: URL: https://github.com/apache/shardingsphere/issues/40246
### 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 reject a [BLOB](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/blb.cpp#L1181) created in a previous transaction, as the Firebird server does. The Firebird server [cancels](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/tra.cpp#L1232-L1250) the temporary BLOBs of a transaction when the transaction ends, so their ids are not valid in the next transaction. ### Actual behavior The proxy accepts the BLOB of the previous transaction and saves the row with the old BLOB content. The same steps were executed directly on the Firebird server and through the proxy on one connection, and the rows were read directly from the server: | Steps | Firebird server | Proxy | | --- | --- | --- | | Turn auto-commit off with [`setAutoCommit(false)`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBConnection.java#L351), create a BLOB with [`createBlob()`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBConnection.java#L308), write the bytes `010203` into it with [`setBytes`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBBlob.java#L399), [`commit()`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBConnection.java#L387) | Success | Success | | `INSERT INTO T_PLAIN (ID, NAME) VALUES (8931, 'before')` | Success | Success | | `INSERT INTO T_BLOB (ID, B) VALUES (8932, ?)`, the parameter is this BLOB, set with [`setBlob`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBPreparedStatement.java#L700) | Error [`isc_bad_segstr_id`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/blb.cpp#L1249-L1252) "invalid BLOB ID" | Success | | `INSERT INTO T_PLAIN (ID, NAME) VALUES (8933, 'after')` | Success | Success | | [`commit()`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBConnection.java#L387) | Success | Success | | Read the rows with ID = 8931 and ID = 8933 from `T_PLAIN` | The rows are saved | The rows are saved | | Read the row with ID = 8932 from `T_BLOB` | The row is not saved | The row is saved with `B` = `010203` | So through the proxy an application saves a BLOB that the Firebird server has already cancelled, and no error tells it that the BLOB id is not valid. ### Reason analysis - [`FirebirdCreateBlobCommandExecutor#execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/blob/FirebirdCreateBlobCommandExecutor.java#L44-L49) puts a new BLOB into [`FirebirdBlobWriteCache`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/blob/cache/FirebirdBlobWriteCache.java#L41) by connection only. The transaction that [`FirebirdCreateBlobCommandPacket`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/blob/FirebirdCreateBlobCommandPacket.java#L51) reads from the packet is not stored. - [`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#L44-L54) 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#L44-L53) end the transaction but do not remove its BLOBs from [`FirebirdBlobWriteCache`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/blob/cache/FirebirdBlobWriteCache.java#L41). A BLOB leaves the cache only when a statement uses it, in [`FirebirdExecuteStatementCommandExecutor#clearBlobUploads`](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#L159-L163). - On [`op_execute`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5243-L5245), [`FirebirdExecuteStatementCommandExecutor#bindBlobParameters`](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#L126-L157) checks the BLOB id only with [`FirebirdBlobWriteCache#isClosed`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/blob/cache/FirebirdBlobWriteCache.java#L171-L174), which checks that the BLOB is closed and not the transaction where it was created, and [puts the cached content](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/s rc/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/execute/FirebirdExecuteStatementCommandExecutor.java#L150-L153) into the parameter. So the BLOB of the previous transaction is saved. - The checks that #39773 added for #39052 reject unknown and unclosed BLOBs, but not a BLOB of another transaction. The open pull request #40081 stores the transaction of a BLOB in [`FirebirdBlobWriteCache`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/blob/cache/FirebirdBlobWriteCache.java#L41) and checks it only for BLOBs of a batch, not in [`FirebirdExecuteStatementCommandExecutor#bindBlobParameters`](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#L126-L157). ### Steps to reproduce #### Environment - Firebird server 5.0. - Client: Jaybird 5.0.13. #### Steps 1. Create the tables on the Firebird server: ```sql CREATE TABLE T_PLAIN (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(20)); CREATE TABLE T_BLOB (ID INTEGER NOT NULL PRIMARY KEY, B BLOB SUB_TYPE BINARY); ``` 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.Blob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class BlobIdPreviousTransaction { // 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 { deleteRows(); blobFromPreviousTransaction(); deleteRows(); } // Firebird server: "INSERT ID = 8932: invalid BLOB ID [SQLState:42000, ISC error code:335544329]", // "T_BLOB, ID = 8932: the row is not saved"; // proxy: "INSERT ID = 8932: success", "T_BLOB, ID = 8932: the row is saved, B = 010203". // The other steps print "success" and "the row is saved" on both. static void blobFromPreviousTransaction() throws SQLException { try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { connection.setAutoCommit(false); // The BLOB is written in the first transaction Blob blob = connection.createBlob(); blob.setBytes(1, new byte[]{1, 2, 3}); connection.commit(); System.out.println("commit after the BLOB is written: success"); insertRow(connection, 8931, "INSERT INTO T_PLAIN (ID, NAME) VALUES (8931, 'before')"); // The BLOB of the previous transaction as the parameter try (PreparedStatement statement = connection.prepareStatement("INSERT INTO T_BLOB (ID, B) VALUES (8932, ?)")) { statement.setBlob(1, blob); statement.executeUpdate(); System.out.println("INSERT ID = 8932: success"); } catch (final SQLException ex) { System.out.println("INSERT ID = 8932: " + ex.getMessage()); } insertRow(connection, 8933, "INSERT INTO T_PLAIN (ID, NAME) VALUES (8933, 'after')"); connection.commit(); System.out.println("commit: success"); } printRows(); } static void insertRow(final Connection connection, final int id, final String sql) { try (Statement statement = connection.createStatement()) { statement.executeUpdate(sql); System.out.println("INSERT ID = " + id + ": success"); } catch (final SQLException ex) { System.out.println("INSERT ID = " + id + ": " + ex.getMessage()); } } // Reads the rows directly on the Firebird server static void printRows() throws SQLException { try (Connection connection = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD); Statement statement = connection.createStatement()) { for (int id : new int[]{8931, 8933}) { try (ResultSet resultSet = statement.executeQuery("SELECT NAME FROM T_PLAIN WHERE ID = " + id)) { System.out.println("T_PLAIN, ID = " + id + ": " + (resultSet.next() ? "the row is saved" : "the row is not saved")); } } // B is converted to hex text on the Firebird server try (ResultSet resultSet = statement.executeQuery("SELECT HEX_ENCODE(CAST(B AS VARBINARY(8))) FROM T_BLOB WHERE ID = 8932")) { System.out.println("T_BLOB, ID = 8932: " + (resultSet.next() ? "the row is saved, B = " + resultSet.getString(1) : "the row is not saved")); } } } static void deleteRows() throws SQLException { executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 8931 AND 8933"); executeOnServer("DELETE FROM T_BLOB WHERE ID BETWEEN 8931 AND 8933"); } 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]
