You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "makssent (via GitHub)" <gi...@apache.org> on 2026/10/19 17:51:57 UTC

[I] Firebird Proxy rejects DROP ROLE with a syntax error instead of an unsupported statement error [shardingsphere]

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

   ### 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 [`DROP ROLE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4623-L4624) as ShardingSphere-Proxy answers the other DCL statements, with an error that says that the statement is not supported ([`ProxyBackendHandlerFactory#checkSupportedSQLStatement`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/handler/ProxyBackendHandlerFactory.java#L161-L171)), and not with a syntax error for a statement that the Firebird server executes.
   
   ### Actual behavior
   
   The proxy rejects the statement with a syntax error.
   
   The same statements were executed directly on the Firebird server and through the proxy, after the role was created directly on the Firebird server:
   
   | Steps | Firebird server | Proxy |
   | --- | --- | --- |
   | `DROP ROLE R_AUDIT` | 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 'DROPROLE'" |
   
   So through the proxy an application gets a syntax error for a valid statement, as if its SQL were wrong.
   
   ### Reason analysis
   
   - The rule [`execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/FirebirdStatement.g4#L22-L55) of the Firebird grammar of ShardingSphere has no alternative for [`DROP ROLE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4623-L4624), so the parser rejects it. The rules of [`DCLStatement.g4`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/DCLStatement.g4) have no `dropRole` either.
   - With such a rule the parser would build [`DropRoleStatement`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dcl/role/DropRoleStatement.java), a [`DCLStatement`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dcl/DCLStatement.java#L26) that [`checkSupportedSQLStatement`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/handler/ProxyBackendHandlerFactory.java#L161-L171) rejects as not supported, as it rejects the other DCL statements.
   
   ### 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>?encoding=UTF8
       username: <user>
       password: <password>
   rules:
     - !SINGLE
       tables:
         - "*.*"
   ```
   
   3. Create the role R_AUDIT directly on the Firebird server, then execute the statement 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
   
   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 DropRole {
   
       // The proxy; put the URL, user and password 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 create the role
       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 SQLException {
           createRoleOnServer();
           try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
               // Firebird server: "DROP ROLE R_AUDIT -> update count -1";
               // proxy: "DROP ROLE R_AUDIT -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'DROPROLE' [SQLState:42000, ISC error code:335544569]"
               execute(connection, "DROP ROLE R_AUDIT");
           }
       }
   
       // Creates the role R_AUDIT directly on the Firebird server if it does not exist
       static void createRoleOnServer() throws SQLException {
           try (Connection connection = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD);
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery("SELECT COUNT(*) FROM RDB$ROLES WHERE RDB$ROLE_NAME = 'R_AUDIT'")) {
               if (resultSet.next() && 0 == resultSet.getInt(1)) {
                   statement.execute("CREATE ROLE R_AUDIT");
               }
           }
       }
   
       // 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: notifications-unsubscribe@shardingsphere.apache.org.apache.org

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


Re: [I] Firebird Proxy rejects DROP ROLE with a syntax error instead of an unsupported statement error [shardingsphere]

Posted by "terrymanu (via GitHub) via notifications" <no...@shardingsphere.apache.org>.
terrymanu closed issue #40291: Firebird Proxy rejects DROP ROLE with a syntax error instead of an unsupported statement error
URL: https://github.com/apache/shardingsphere/issues/40291


-- 
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

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