Truncate metadata strings from virtual schemas if they are too long for system table columns

Details

Detail name Value
Changelog Number 20712
Type Bug
Status Resolved
Affected Versions Exasol 7.1.0, Exasol 8.0.0
Fix Versions Exasol 8.31.0, Exasol 8.29.2, Exasol 7.1.30
Resolution Date 2024-08-29

Description

It is possible to create virtual schemas with very long metadata strings such as adapter notes. If you try to select (or filter on) the corresponding column from the system tables and the value is longer than the column’s maximum length then the query will raise an “Internal Server Error”.

Example

-- Setup
DROP SCHEMA IF EXISTS test CASCADE;
DROP VIRTUAL SCHEMA IF EXISTS test_vs CASCADE;
CREATE SCHEMA test;
--/
CREATE OR REPLACE PYTHON3 ADAPTER SCRIPT test.vs_adapter AS
import json
def adapter_call(req):
    return json.dumps({
        'type': json.loads(req)['type'],
        'capabilities': [],
        'schemaMetadata': {
            'tables': [
                {
                    'name': 'T',
                    'columns': [
                        {
                            'name': 'C',
                            'dataType': {
                                'type': 'BOOLEAN'
                            }
                        }
                    ]
                }
            ],
            'adapterNotes': 'x' * 2000001
        },
        'sql': "SELECT true AS c"
    })
/

-- Observed: Virtual schema is created successfully
CREATE VIRTUAL SCHEMA test_vs USING test.vs_adapter;

-- Observed: Its objects are accessible
SELECT c FROM test_vs.t;

-- Expected: Success
-- Observed: Error "Internal Server Error"
SELECT * FROM exa_all_virtual_schemas WHERE schema_name = 'TEST_VS';

Workaround

There are two possibilities:

  1. Drop the virtual schema; or
  2. Fix the adapter and recreate the virtual schema.

Fix

Truncate strings defined by adapter scripts to the maximum lengths when selecting or filtering on them in system table columns. Values that are truncated will be marked with '…' at the end.

Further notes

  • A separate ticket makes a fix to prevent the user from creating virtual schemas where the metadata are too long. However, it does not remove any such virtual schemas that already exist.
  • The following system table columns are affected:
System table Affected columns
EXA_ALL_VIRTUAL_SCHEMAS ADAPTER_NOTES
EXA_ALL_TABLES TABLE_NAME, TABLE_COMMENT
EXA_ALL_VIRTUAL_TABLES TABLE_NAME, ADAPTER_NOTES
EXA_ALL_COLUMNS COLUMN_TABLE, COLUMN_NAME, COLUMN_COMMENT, COLUMN_DEFAULT
EXA_ALL_VIRTUAL_COLUMNS COLUMN_TABLE, COLUMN_NAME, ADAPTER_NOTES
  • The string length limits are:
Metadata Maximum character length
Table or column name 128
Schema, table or column adapter notes 2,000,000
Table or column comment 2,000
Column default 2,000