makssent opened a new issue, #40249: URL: https://github.com/apache/shardingsphere/issues/40249
### 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 [`SAVEPOINT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5310-L5313), [`ROLLBACK TO SAVEPOINT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5324-L5327), [`RELEASE SAVEPOINT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5316-L5321), [`COMMIT RETAIN`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5335-L5338) and [`ROLLBACK RETAIN`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5341-L5344) as the Firebird server does. ### Actual behavior The proxy rejects each of these statements with an error. The same statements were executed directly on the Firebird server and through the proxy, one after another in one transaction with auto-commit off: | Statement | Firebird server | Proxy | | --- | --- | --- | | With auto-commit off, `SELECT ID FROM T_PLAIN WHERE ID = 6601` | Success, no rows | Success, no rows | | `SAVEPOINT S1` | Success | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown exception." | | `ROLLBACK TO SAVEPOINT S1` | Success | Error [`isc_dsql_error`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L250) "Dynamic SQL Error; You have an error in your SQL syntax: ROLLBACK TO SAVEPOINT S1, null" | | `RELEASE SAVEPOINT S1` | Success | Error [`isc_dsql_error`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L250) "Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'RELEASE'" | | `COMMIT RETAIN` | Success | Error [`isc_dsql_error`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L250) "Dynamic SQL Error; You have an error in your SQL syntax: COMMIT RETAIN, null" | | `ROLLBACK RETAIN` | Success | Error [`isc_dsql_error`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L250) "Dynamic SQL Error; You have an error in your SQL syntax: ROLLBACK RETAIN, null" | So through the proxy an application cannot set, roll back to or release a savepoint, and cannot commit or roll back its work while keeping the transaction open. ### Reason analysis - The rules [`commit`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/TCLStatement.g4#L26-L28), [`rollback`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/TCLStatement.g4#L30-L32) and [`savepoint`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/TCLStatement.g4#L38-L40) of the Firebird grammar of ShardingSphere have no [`RETAIN`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5352-L5355), no [`ROLLBACK TO SAVEPOINT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5324-L5327) and no [`RELEASE SAVEPOINT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5316-L5321), so the proxy rejects these statem ents with a syntax error. The operations [`op_commit_retaining`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L3425-L3427) and [`op_rollback_retaining`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L3421-L3423) tracked in #39938 do not cover [`COMMIT RETAIN`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5335-L5338) and [`ROLLBACK RETAIN`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5341-L5344), because the client sends these statements as SQL text and the proxy rejects them in the parser. - [`FirebirdTCLStatementVisitor#visitSavepoint`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/type/FirebirdTCLStatementVisitor.java#L58-L60) creates [`SavepointStatement`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/tcl/SavepointStatement.java#L27) with the savepoint name `null`. [`SetSavepointProxyBackendHandler#execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/handler/tcl/local/type/SetSavepointProxyBackendHandler.java#L55-L59) passes this name through [`ProxyBackendTransactionManager#setSavepoint`](https://github.com/apache/shardingsphere/blob/01446d4fad 48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/connector/jdbc/transaction/ProxyBackendTransactionManager.java#L190-L195) and [`ConnectionSavepointManager#setSavepoint`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/kernel/transaction/core/src/main/java/org/apache/shardingsphere/transaction/savepoint/ConnectionSavepointManager.java#L61-L64) to [`FBConnection#setSavepoint(String)`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBConnection.java#L940-L948) of the backend Jaybird. Jaybird [quotes the name](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBConnection.java#L908-L909) with [`QuoteStrategy#appendQuoted`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/QuoteStrategy.java#L48-L50) and fails with `NullPointerException`, and [`SQLExceptionTransformEngine#toSQLException`](https:// github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/exception/core/src/main/java/org/apache/shardingsphere/database/exception/core/SQLExceptionTransformEngine.java#L68) turns it into [`UnknownSQLException`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/common/src/main/java/org/apache/shardingsphere/infra/exception/generic/UnknownSQLException.java#L30-L32) "Unknown exception.". ### 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. Turn auto-commit off, execute the statements from the table under "Actual behavior" in one transaction through the proxy and directly on the Firebird server, and compare the results. "Example code" runs all of them. ### Example code The statements from the table on Jaybird 6.0.7. The comment above each call 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.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import java.util.ArrayList; import java.util.List; public class SavepointStatements { // 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"; public static void main(final String[] args) throws SQLException { try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { connection.setAutoCommit(false); // A statement before SAVEPOINT, so that the transaction already uses a backend connection of the proxy // Firebird server: "SELECT ID FROM T_PLAIN WHERE ID = 6601 -> ID INTEGER: no rows"; // proxy: "SELECT ID FROM T_PLAIN WHERE ID = 6601 -> ID INTEGER: no rows" execute(connection, "SELECT ID FROM T_PLAIN WHERE ID = 6601"); // Firebird server: "SAVEPOINT S1 -> update count -1"; proxy: "SAVEPOINT S1 -> error: Unknown exception. ..." execute(connection, "SAVEPOINT S1"); // Firebird server: "ROLLBACK TO SAVEPOINT S1 -> update count -1"; // proxy: "ROLLBACK TO SAVEPOINT S1 -> error: Dynamic SQL Error; You have an error in your SQL syntax: ROLLBACK TO SAVEPOINT S1, null ..." execute(connection, "ROLLBACK TO SAVEPOINT S1"); // Firebird server: "RELEASE SAVEPOINT S1 -> update count -1"; // proxy: "RELEASE SAVEPOINT S1 -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'RELEASE' ..." execute(connection, "RELEASE SAVEPOINT S1"); // Firebird server: "COMMIT RETAIN -> update count -1"; // proxy: "COMMIT RETAIN -> error: Dynamic SQL Error; You have an error in your SQL syntax: COMMIT RETAIN, null ..." execute(connection, "COMMIT RETAIN"); // Firebird server: "ROLLBACK RETAIN -> update count -1"; // proxy: "ROLLBACK RETAIN -> error: Dynamic SQL Error; You have an error in your SQL syntax: ROLLBACK RETAIN, null ..." execute(connection, "ROLLBACK RETAIN"); connection.commit(); } } // Executes the statement and prints the described columns with the rows, the update count or the error static void execute(final Connection connection, final String sql) { try (Statement statement = connection.createStatement()) { if (!statement.execute(sql)) { System.out.println(sql + " -> update count " + statement.getUpdateCount()); return; } try (ResultSet resultSet = statement.getResultSet()) { ResultSetMetaData metaData = resultSet.getMetaData(); List<String> columns = new ArrayList<>(); for (int i = 1; i <= metaData.getColumnCount(); i++) { int type = metaData.getColumnType(i); boolean text = Types.CHAR == type || Types.VARCHAR == type; columns.add(metaData.getColumnLabel(i) + " " + metaData.getColumnTypeName(i) + (text ? "(" + metaData.getPrecision(i) + ")" : "")); } List<String> rows = new ArrayList<>(); while (resultSet.next()) { List<String> values = new ArrayList<>(); for (int i = 1; i <= metaData.getColumnCount(); i++) { values.add(String.valueOf(resultSet.getObject(i))); } rows.add(String.join(", ", values)); } System.out.println(sql + " -> " + String.join(", ", columns) + ": " + (rows.isEmpty() ? "no rows" : String.join("; ", rows))); } } catch (final SQLException ex) { System.out.println(sql + " -> error: " + ex.getMessage()); } } } ``` -- 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]
