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 13:11:33 UTC

[I] Firebird Proxy describes system tables differently from the Firebird server (missing tables and columns, wrong types) [shardingsphere]

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

   ### 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 these statements on the system tables and describe their columns as the Firebird server does.
   
   ### Actual behavior
   
   The proxy describes two system table columns with a wrong type and does not know the other system tables and columns.
   
   The same statements were executed directly on the Firebird server and through the proxy, and the column types were read with [`getColumnTypeName`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBResultSetMetaData.java#L225):
   
   | Statement | Firebird server | Proxy |
   | --- | --- | --- |
   | `SELECT MON$PAGE_SIZE FROM MON$DATABASE` | One row: 8192, type [`INTEGER`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/fields.h#L138) | One row: 8192, type [`SMALLINT`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/connector/dialect/firebird/src/main/resources/schema/firebird/system_tables/mon$database.yaml#L27-L33) |
   | `SELECT RDB$SQL_SECURITY FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'T_PLAIN'` | One row: NULL, type [`BOOLEAN`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/fields.h#L197) | One row: NULL, type [`SMALLINT`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/connector/dialect/firebird/src/main/resources/schema/firebird/system_tables/rdb$relations.yaml#L139-L145) |
   | `SELECT COUNT(*) FROM RDB$KEYWORDS` | One row: 583 | Error [`isc_random`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/err/FirebirdErrorSegmentResolver.java#L56) "Table or view 'RDB$KEYWORDS' does not exist." |
   | `SELECT RDB$COLLATION_ID FROM RDB$FIELDS WHERE RDB$FIELD_NAME = 'RDB$RELATION_NAME'` | One row: 0 | Error [`isc_random`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/err/FirebirdErrorSegmentResolver.java#L56) "Unknown column 'RDB$COLLATION_ID' in 'field list'." |
   | `SELECT COUNT(*) FROM RDB$PACKAGES` | One row: 3 | Error [`isc_random`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/err/FirebirdErrorSegmentResolver.java#L56) "Table or view 'RDB$PACKAGES' does not exist." |
   
   So through the proxy an application gets wrong types for system table columns and cannot read the system tables and columns of Firebird 5.0 that the proxy does not know.
   
   ### Reason analysis
   
   - The proxy takes the schemas of the system tables from the YAML files in [`system_tables`](https://github.com/apache/shardingsphere/tree/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/connector/dialect/firebird/src/main/resources/schema/firebird/system_tables), not from the server. [`FirebirdPrepareStatementCommandExecutor#processReturnValues`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L248-L253) describes the columns of a system table from these files. The open pull request #40191 changes this code to describe the lengths of text columns of system tables, but the types still come from these files, and it adds no tables or columns.
   - These files differ from Firebird 5.0. [`MON$PAGE_SIZE`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/connector/dialect/firebird/src/main/resources/schema/firebird/system_tables/mon$database.yaml#L27-L33) and [`RDB$SQL_SECURITY`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/connector/dialect/firebird/src/main/resources/schema/firebird/system_tables/rdb$relations.yaml#L139-L145) have `dataType: 5`, [`rdb$fields.yaml`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/connector/dialect/firebird/src/main/resources/schema/firebird/system_tables/rdb$fields.yaml) has no [`RDB$COLLATION_ID`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/relations.h#L72), and there are no files for [`RDB$KEYWORDS`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/relations.h#L746) and [`RDB$PACKAGES`](https://github.com/FirebirdSQL/fi
 rebird/blob/v5.0.4/src/jrd/relations.h#L635).
   - [`SimpleTableSegmentBinder#checkTableExists`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinder.java#L378) rejects a table without a file with [`TableNotFoundException`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/common/src/main/java/org/apache/shardingsphere/infra/exception/kernel/metadata/TableNotFoundException.java#L26-L31), and [`ColumnSegmentBinder#bind`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/expression/type/ColumnSegmentBinder.java#L111-L112) rejects a column that is not in the file with [`ColumnNotFoundException`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/common/src/main/java/o
 rg/apache/shardingsphere/infra/exception/kernel/metadata/ColumnNotFoundException.java#L26-L31).
   
   ### 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 statements 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 SystemTableSchemas {
   
       // 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)) {
               // Firebird server: "SELECT MON$PAGE_SIZE FROM MON$DATABASE -> MON$PAGE_SIZE INTEGER: 8192";
               // proxy: "SELECT MON$PAGE_SIZE FROM MON$DATABASE -> MON$PAGE_SIZE SMALLINT: 8192"
               execute(connection, "SELECT MON$PAGE_SIZE FROM MON$DATABASE");
               // Firebird server: "SELECT RDB$SQL_SECURITY FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'T_PLAIN' -> RDB$SQL_SECURITY BOOLEAN: null";
               // proxy: "SELECT RDB$SQL_SECURITY FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'T_PLAIN' -> RDB$SQL_SECURITY SMALLINT: null"
               execute(connection, "SELECT RDB$SQL_SECURITY FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'T_PLAIN'");
               // Firebird server: "SELECT COUNT(*) FROM RDB$KEYWORDS -> COUNT BIGINT: 583";
               // proxy: "SELECT COUNT(*) FROM RDB$KEYWORDS -> error: Table or view 'RDB$KEYWORDS' does not exist. ..."
               execute(connection, "SELECT COUNT(*) FROM RDB$KEYWORDS");
               // Firebird server: "SELECT RDB$COLLATION_ID FROM RDB$FIELDS WHERE RDB$FIELD_NAME = 'RDB$RELATION_NAME' -> RDB$COLLATION_ID SMALLINT: 0";
               // proxy: "SELECT RDB$COLLATION_ID FROM RDB$FIELDS WHERE RDB$FIELD_NAME = 'RDB$RELATION_NAME'
               // -> error: Unknown column 'RDB$COLLATION_ID' in 'field list'. ..."
               execute(connection, "SELECT RDB$COLLATION_ID FROM RDB$FIELDS WHERE RDB$FIELD_NAME = 'RDB$RELATION_NAME'");
               // Firebird server: "SELECT COUNT(*) FROM RDB$PACKAGES -> COUNT BIGINT: 3";
               // proxy: "SELECT COUNT(*) FROM RDB$PACKAGES -> error: Table or view 'RDB$PACKAGES' does not exist. ..."
               execute(connection, "SELECT COUNT(*) FROM RDB$PACKAGES");
           }
       }
   
       // 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 describes system tables differently from the Firebird server (missing tables and columns, wrong types) [shardingsphere]

Posted by "terrymanu (via GitHub) via notifications" <no...@shardingsphere.apache.org>.
terrymanu closed issue #40254: Firebird Proxy describes system tables differently from the Firebird server (missing tables and columns, wrong types)
URL: https://github.com/apache/shardingsphere/issues/40254


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