makssent opened a new issue, #40242: URL: https://github.com/apache/shardingsphere/issues/40242
### 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 execute DDL statements in the client transaction as the Firebird server does, so that [`rollback()`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBConnection.java#L412) undoes the DDL and the rows written in the same transaction. The Firebird server [executes DDL](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/DsqlRequests.cpp#L1052) in the client transaction, applies it only on commit ([`DFW_perform_work`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/tra.cpp#L484-L487) in [`TRA_commit`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/tra.cpp#L432)) and [commits after DDL by itself](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/jrd.cpp#L9361-L9382) only a transaction started with [`isc_tpb_autocommit`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/tra.cpp#L3312-L3314). ### Actual behavior The proxy commits its backend transaction right after the DDL, so [`rollback()`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBConnection.java#L412) undoes only the row written after the DDL. 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 | | --- | --- | --- | | [`setAutoCommit(false)`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBConnection.java#L354) | Success | Success | | `INSERT INTO T_PLAIN (ID, NAME) VALUES (8891, 'before ddl')` | 1 row inserted | 1 row inserted | | `CREATE TABLE T_AUDIT_DDL_TX (ID INTEGER)` | Success | Success | | `INSERT INTO T_PLAIN (ID, NAME) VALUES (8892, 'after ddl')` | 1 row inserted | 1 row inserted | | [`rollback()`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBConnection.java#L412) | Success | Success | | Read the row with ID = 8891 | The row is not saved | The row is saved | | Read the row with ID = 8892 | The row is not saved | The row is not saved | | Look for the table T_AUDIT_DDL_TX in `RDB$RELATIONS` | The table does not exist | The table exists | So through the proxy an application that rolls back a transaction with DDL keeps the new table and the rows written before the DDL, and no error tells it that they were committed. ### Reason analysis - [`FirebirdDatabaseMetaData#getTransactionOption`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/connector/dialect/firebird/src/main/java/org/apache/shardingsphere/database/connector/firebird/metadata/database/FirebirdDatabaseMetaData.java#L60-L63) sets [`DDLCommitPolicy.COMMIT_CURRENT_TRANSACTION`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/database/metadata/option/transaction/DDLCommitPolicy.java#L25) for Firebird. - With auto-commit off, [`StandardDatabaseProxyConnector#doExecute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/connector/StandardDatabaseProxyConnector.java#L241-L244) commits the backend transaction with [`ProxyBackendTransactionManager#commit`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/connector/jdbc/transaction/ProxyBackendTransactionManager.java#L108-L120) after every DDL statement because of this policy ([`StandardDatabaseProxyConnector#isCurrentTransactionCommitRequired`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/connector/StandardDatabaseProxyConnector.java#L256-L260)). The client keeps its transaction, the statements after th e DDL run in the next backend transaction, and [`rollback()`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBConnection.java#L412) undoes only them. - The open pull request #39882 changes [`StandardDatabaseProxyConnector#doExecute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/connector/StandardDatabaseProxyConnector.java#L241-L244) to defer the metadata refresh after DDL to the commit, but keeps the commit after DDL for [`DDLCommitPolicy.COMMIT_CURRENT_TRANSACTION`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/database/metadata/option/transaction/DDLCommitPolicy.java#L25), so it does not change this behavior. ### 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. The table T_AUDIT_DDL_TX must not exist before each run. "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 DdlInTransaction { // 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 8891 AND 8892"); dropTable(); ddlInTransaction(); dropTable(); executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 8891 AND 8892"); } // Firebird server and proxy: "insert ID = 8891: 1 row", "create table: success", "insert ID = 8892: 1 row", "rollback: success"; // Firebird server: "ID = 8891: the row is not saved", "ID = 8892: the row is not saved", "T_AUDIT_DDL_TX: the table does not exist"; // proxy: "ID = 8891: the row is saved", "ID = 8892: the row is not saved", "T_AUDIT_DDL_TX: the table exists" static void ddlInTransaction() throws SQLException { try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD); Statement statement = connection.createStatement()) { connection.setAutoCommit(false); System.out.println("insert ID = 8891: " + statement.executeUpdate("INSERT INTO T_PLAIN (ID, NAME) VALUES (8891, 'before ddl')") + " row"); statement.executeUpdate("CREATE TABLE T_AUDIT_DDL_TX (ID INTEGER)"); System.out.println("create table: success"); System.out.println("insert ID = 8892: " + statement.executeUpdate("INSERT INTO T_PLAIN (ID, NAME) VALUES (8892, 'after ddl')") + " row"); connection.rollback(); System.out.println("rollback: success"); } printRow(8891); printRow(8892); try (Connection connection = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD); ResultSet resultSet = connection.createStatement().executeQuery("SELECT COUNT(*) FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'T_AUDIT_DDL_TX'")) { resultSet.next(); System.out.println("T_AUDIT_DDL_TX: " + (0 < resultSet.getInt(1) ? "the table exists" : "the table does not exist")); } } // Drops the table through the URL under test, so that the proxy does not keep it in its metadata. // The table may not exist, then the error is ignored. static void dropTable() { try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { connection.createStatement().execute("DROP TABLE T_AUDIT_DDL_TX"); } catch (final SQLException ignored) { // the table does not exist } } // 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 not saved")); } } 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]
