makssent opened a new issue, #40271: URL: https://github.com/apache/shardingsphere/issues/40271
### 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_seek_blob`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5187-L5189) for a BLOB of a query result as the Firebird server does, which allows seek only in a [stream BLOB](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/blb.cpp#L977-L978) and answers with an error for a [segmented BLOB](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/blb.h#L212-L215). ### Actual behavior The proxy treats every BLOB of a query result as a stream BLOB and executes [`op_seek_blob`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5187-L5189) in a [segmented BLOB](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/blb.h#L212-L215) too. The row with ID = 9121 was written directly on the Firebird server from a string literal, so its BLOB is segmented. The same steps were executed directly on the Firebird server and through the proxy: | Steps | Firebird server | Proxy | | --- | --- | --- | | `SELECT T FROM T_BLOB WHERE ID = 9121`, then [`getBlob(1)`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBResultSet.java#L484) and [`getBytes(4, 3)`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBBlob.java#L342-L371), which [sends](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBBlob.java#L357-L359) [`op_seek_blob`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5187-L5189); the connection has [`maxInlineBlobSize`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jaybird/props/PropertyNames.java#L85)`=0`, so that the BLOB is not sent inline | Error [`isc_bad_segstr_type`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/blb.cpp#L977-L978) "invalid BLOB type for operation" | Success, the bytes `333435` ("345") | So through the proxy an application reads data from a segmented BLOB with seek, and the same code fails with an error on the Firebird server. ### Reason analysis - [`FirebirdOpenBlobCommandExecutor#isStreamBlob`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/blob/FirebirdOpenBlobCommandExecutor.java#L87-L92) returns `true` for every BLOB with a negative ID, that is for every BLOB of a query result, and [`FirebirdOpenBlobCommandExecutor#execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/blob/FirebirdOpenBlobCommandExecutor.java#L63) registers the opened BLOB with this flag. The [class comment](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/blob/FirebirdOpenBlobCommandExecutor.java#L46-L49) sa ys that a result BLOB carries no stored Firebird type, so the real type of the BLOB on the Firebird server is not used. This was chosen in #39984 to make seek usable for query results, but the Firebird server keeps the type of a stored BLOB and rejects seek in a segmented one. - [`FirebirdSeekBlobCommandExecutor#execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/blob/FirebirdSeekBlobCommandExecutor.java#L55-L63) throws [`InvalidSegstrTypeException`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/exception/protocol/InvalidSegstrTypeException.java#L27) only for a BLOB that is [registered](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/blob/FirebirdSeekBlobCommandExecutor.java#L60) as not stream, so seek in a BLOB of a query result always succeeds. ### Steps to reproduce #### Environment - Firebird server 5.0. - Client: Jaybird 5.0.13. #### Steps 1. Create the table and write the row on the Firebird server: ```sql CREATE TABLE T_BLOB (ID INTEGER NOT NULL PRIMARY KEY, B BLOB SUB_TYPE BINARY, T BLOB SUB_TYPE TEXT CHARACTER SET UTF8); INSERT INTO T_BLOB (ID, T) VALUES (9121, '0123456789'); COMMIT; ``` 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 Jaybird 5.0.13. The code runs the same seek through the proxy and directly on the Firebird server and prints both results. The comments in `main` show what it prints. ```java import java.sql.Blob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.HexFormat; import java.util.Properties; public class ResultBlobSeek { // The proxy 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 write the BLOB row and to compare 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_BLOB WHERE ID BETWEEN 9120 AND 9129"); // A BLOB written from a string literal is a segmented BLOB executeOnServer("INSERT INTO T_BLOB (ID, T) VALUES (9121, '0123456789')"); // Through the proxy: "proxy: getBytes(4, 3): 333435" seekInResultBlob("proxy", URL, USER, PASSWORD, new Properties()); // Directly on the Firebird server: "Firebird server: getBytes(4, 3): invalid BLOB type for operation". // Inline BLOBs are turned off: otherwise Jaybird reads a small BLOB together with the row // and seeks in its own copy without asking the server Properties direct = new Properties(); direct.setProperty("maxInlineBlobSize", "0"); seekInResultBlob("Firebird server", SERVER_URL, SERVER_USER, SERVER_PASSWORD, direct); executeOnServer("DELETE FROM T_BLOB WHERE ID BETWEEN 9120 AND 9129"); } static void seekInResultBlob(final String target, final String url, final String user, final String password, final Properties properties) throws SQLException { properties.setProperty("user", user); properties.setProperty("password", password); try (Connection connection = DriverManager.getConnection(url, properties)) { connection.setAutoCommit(false); try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT T FROM T_BLOB WHERE ID = 9121")) { resultSet.next(); Blob blob = resultSet.getBlob(1); try { // A position greater than 1 makes Jaybird seek in the BLOB System.out.println(target + ": getBytes(4, 3): " + HexFormat.of().formatHex(blob.getBytes(4, 3))); } catch (final SQLException ex) { System.out.println(target + ": getBytes(4, 3): " + ex.getMessage()); } } connection.commit(); } } 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]
