[I] Firebird Proxy rejects reuse of a temporary BLOB in the same transaction [shardingsphere]

["makssent (via GitHub)" <gi...@apache.org>]

makssent opened a new issue, #40402:
URL: https://github.com/apache/shardingsphere/issues/40402

   ### 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 [`ab8624a369da`](https://github.com/apache/shardingsphere/commit/ab8624a369dac2a9a84762a24e1f078a57ee75e6)
   
   ### ShardingSphere project
   
   ShardingSphere-Proxy
   
   ### Expected behavior
   
   A prepared statement should be able to use the same temporary BLOB parameter more than once within a transaction, as on the Firebird server.
   
   ### Actual behavior
   
   Firebird Proxy rejects the second and subsequent executions with the same BLOB parameter, although the Firebird server accepts them.
   
   | Steps | Firebird server | Firebird Proxy |
   | --- | --- | --- |
   | Disable auto-commit, prepare `INSERT INTO T_BLOB(ID, B) VALUES (?, ?)`, and set the BLOB parameter once with `setBytes` | Success | Success |
   | Execute the statement with ID 2301 | Success | Success |
   | Execute the same statement with IDs 2302 and 2303 without setting the BLOB parameter again | Both executions succeed | Both executions return [`isc_bad_segstr_id`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/blb.cpp#L1249-L1252), "invalid BLOB ID" |
   | Commit and read the rows directly from the server | Three rows with BLOB `0102030405` | No rows |
   
   The missing first row after commit is a separate transaction error; the repeated BLOB rejection is the problem reported here. An application reusing a parameter cannot insert the subsequent rows.
   
   ### Reason analysis
   
   - The Firebird server [looks up the temporary BLOB ID in the current transaction](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/blb.cpp#L1181-L1185). After the first use, it [reuses the materialized BLOB](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/blb.cpp#L1207-L1208).
   - In the proxy, [`executePreparedStatement`](https://github.com/apache/shardingsphere/blob/ab8624a369dac2a9a84762a24e1f078a57ee75e6/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/execute/FirebirdExecuteStatementCommandExecutor.java#L105-L118) removes the BLOB upload after execution. On the next execution, [`bindBlobParameters`](https://github.com/apache/shardingsphere/blob/ab8624a369dac2a9a84762a24e1f078a57ee75e6/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/execute/FirebirdExecuteStatementCommandExecutor.java#L145-L147) no longer finds the ID and rejects it.
   - Related issue #40246 describes a BLOB from a previous transaction, not repeated use inside the same transaction.
   
   ### Steps to reproduce
   
   #### Environment
   
   - Firebird-compatible server.
   - Client: Jaybird 5.0.13.
   
   #### Steps
   
   1. Create the table 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);
   ```
   
   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>?encoding=UTF8
       username: <user>
       password: <password>
   rules:
     - !SINGLE
       tables:
         - "*.*"
   ```
   
   3. Run the example directly on the Firebird server and through the proxy, and compare the results. Use a test database: the example deletes IDs 2301 through 2303.
   
   ### Example code
   
   ```java
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.util.HexFormat;
   
   public class ReuseTempBlob {
       // Use the server URL and credentials for a direct run, or the proxy URL and credentials for a proxy run.
       static final String URL = "jdbc:firebirdsql://<host>:<port>/<database>?encoding=UTF8&wireCrypt=DISABLED";
       static final String USER = "<user>";
       static final String PASSWORD = "<password>";
       static final String SERVER_URL = "jdbc:firebirdsql://<server host>:<port>/<database file>?encoding=UTF8&wireCrypt=DISABLED&maxInlineBlobSize=0";
       static final String SERVER_USER = "<user>";
       static final String SERVER_PASSWORD = "<password>";
   
       public static void main(String[] args) throws Exception {
           reuseBlob();
       }
   
       // Server: all three inserts succeed and three rows are saved. Proxy: the second and third inserts report invalid BLOB ID and no rows are saved.
       static void reuseBlob() throws Exception {
           try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
                Statement statement = connection.createStatement()) {
               statement.executeUpdate("DELETE FROM T_BLOB WHERE ID BETWEEN 2301 AND 2303");
               connection.setAutoCommit(false);
               try (PreparedStatement prepared = connection.prepareStatement("INSERT INTO T_BLOB(ID, B) VALUES (?, ?)")) {
                   prepared.setBytes(2, new byte[]{1, 2, 3, 4, 5});
                   for (int id = 2301; id <= 2303; id++) {
                       prepared.setInt(1, id);
                       try {
                           System.out.println("execute " + id + " -> " + prepared.executeUpdate());
                       } catch (SQLException ex) {
                           System.out.println("execute " + id + " -> " + ex.getErrorCode() + " " + ex.getMessage());
                       }
                   }
               }
               connection.commit();
               System.out.println("commit -> success");
           }
           try (Connection server = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD);
                Statement statement = server.createStatement();
                ResultSet rows = statement.executeQuery("SELECT ID, B FROM T_BLOB WHERE ID BETWEEN 2301 AND 2303 ORDER BY ID")) {
               int count = 0;
               while (rows.next()) {
                   System.out.println("row " + rows.getInt(1) + " B=" + HexFormat.of().formatHex(rows.getBytes(2)));
                   count++;
               }
               System.out.println("rows=" + count);
           }
       }
   }
   ```


-- 
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org