@Rocky hello

Hello! I'm Rocky, your StarRocks assistant. How can I help you today?

@*MySQLdb.OperationalError: (5501, "Unknown database 'DMAPP'")* [2026-10-27, 230358 UTC] {taskinstance.py:907} DEBUG - Task Duration set to 12.302431 [2026-10-27, 230358 UTC] {job.py:234} DEBUG - [heartbeat] [2026-10-27, 230358 UTC] {taskinstance.py:929} DEBUG - Clearing next_method and next_kwargs. [2026-10-27, 230358 UTC] {taskinstance.py:907} DEBUG - Task Duration set to 12.395585 [2026-10-27, 230358 UTC] {taskinstance.py:1226} INFO - Marking task as UP_FOR_RETRY. dag_id=DMAPP_UW_ORACLE_TO_STARROCKS, task_id=EXTRACTION_STAGE.GROUP_1.DTM_APP_INFO, run_id=scheduled__2026-10-26T230000+00:00, execution_date=20261026T230000, start_date=20261027T230346, end_date=20261027T230358 got thif message from airflow when running: TRUNCATE TABLE DMAPP.DTM_APP_INFO after upgrade from 4.0.12 to 4.1.6

This error,

MySQLdb.OperationalError: (5501, "Unknown database 'DMAPP'")

, typically occurs when StarRocks cannot resolve the database name provided in the

TRUNCATE TABLE

statement. Since this started occurring after your upgrade from 4.0.12 to 4.1.6, it is likely related to a change in how StarRocks handles catalog and database resolution or a known bug in the 4.x series. Potential Causes and Solutions 1. Known Bug with External Catalogs (#67420) There is a known issue (GitHub #67420) reported in StarRocks 4.x where

TRUNCATE TABLE

on tables in external catalogs (like Iceberg or Hive) fails with an "Unknown database" error, even if the database exists. * Workaround: Try using the fully qualified name including the catalog, for example:

sql
    TRUNCATE TABLE default_catalog.DMAPP.DTM_APP_INFO;
    -- Or if using an external catalog:
    TRUNCATE TABLE <catalog_name>.DMAPP.DTM_APP_INFO;

* Verification: Check if

DMAPP

is an internal database or part of an external catalog by running

SHOW CATALOGS;

and

SHOW DATABASES;

. 2. Case Sensitivity Changes StarRocks 4.0 introduced more granular support for case sensitivity via

enable_case_sensitive_names

. If your database was created with a different case (e.g.,

dmapp

vs

DMAPP

), the new version might be enforcing case sensitivity strictly. * Check: Run

SHOW DATABASES;

to see the exact casing of the database. * Test: Try running the command with the exact case shown in

SHOW DATABASES

. 3. Default Catalog/Database Context In Airflow, if the connection does not explicitly set the database or if the upgrade changed the default behavior of the connection session, the "current" catalog might not be what you expect. * Fix: Ensure your Airflow task explicitly switches to the correct catalog and database before running the truncate, or use the fully qualified name:

sql
    SET CATALOG default_catalog;
    USE DMAPP;
    TRUNCATE TABLE DTM_APP_INFO;

4. Metadata Inconsistency Post-Upgrade If the database

DMAPP

exists but is "unseen" during the truncate, there may be a metadata sync issue. * Verification: Confirm the database is visible in the current session:

sql
    SELECT * FROM information_schema.tables WHERE table_schema = 'DMAPP' AND table_name = 'DTM_APP_INFO';

Recommendation If

DMAPP

is an internal database and the fully qualified name does not work, it is recommended to: 1. Verify the database existence and casing using

SHOW DATABASES

. 2. If you are using an external catalog (Iceberg/Hive), this is likely the bug mentioned above. You may need to use

DELETE FROM table WHERE 1=1

as a temporary alternative if

TRUNCATE

remains blocked, though

TRUNCATE

is preferred for performance. 3. Check your FE logs (

fe.log

) for more detailed stack traces when the error occurs. For enterprise-level support or specific upgrade bugs in the 4.x branch, you can reach out to CelerData or the StarRocks community Slack. References • release_notes/release-4.0.md • administration/Data_recovery.md • data_source/catalog/unified_catalog.md • sql-reference/sql-statements/table_bucket_part_index/TRUNCATE_TABLE.md

@Rocky update dwh.af_extract_mapping set DEST_PRE_SQL = lower(DEST_PRE_SQL) where PROJECT_NAME ='DMAPP_UW_ORACLE_TO_STARROCKS'; correct

Yes, the SQL syntax is correct for StarRocks, but there is one critical requirement for it to work: 1. Primary Key Table Requirement In StarRocks, the

UPDATE

statement is only supported for Primary Key tables. * If

dwh.af_extract_mapping

was created as a Duplicate Key, Aggregate Key, or Unique Key table, this command will return an error stating that the table type does not support updates. * If it is a Primary Key table, the command will execute as expected. 2. Functional Correctness *

lower()

: This is a supported function in StarRocks that will convert the string in

DEST_PRE_SQL

to lowercase. *

WHERE

clause: StarRocks requires a

WHERE

condition for updates (which you have provided) to prevent accidental full-table updates. Contextual Note on your "Unknown Database" Error If your goal is to fix the

Unknown database 'DMAPP'

error by lowercasing the SQL stored in your mapping table: * This is a valid fix if the database in StarRocks is actually named

dmapp

(lowercase). * StarRocks is case-sensitive for database and table names by default (or depending on the

enable_case_sensitive_names

setting). If the table was created in lowercase but the Airflow task is trying to call it in uppercase, lowercasing the command string should resolve the mismatch. To verify the table type before running:

sql
SHOW CREATE TABLE dwh.af_extract_mapping;

Look for

PRIMARY KEY

in the output. If it's not there, you would instead need to use an

INSERT INTO ... SELECT

or

UPSERT

pattern to modify the data. References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • sql-reference/sql-functions/string-functions/lower.md • faq/Others.md References • sql-reference/sql-functions/string-functions/lower.md • sql-reference/sql-functions/string-functions/lcase.md • sql-reference/sql-functions/string-functions/ucase.md • sql-reference/sql-functions/string-functions/upper.md • sql-reference/sql-functions/string-functions/initcap.md • sql-reference/sql-functions/string-functions/group_concat.md • sql-reference/sql-functions/math-functions/floor.md • sql-reference/sql-functions/string-functions/locate.md

@Rocky how to check config

enable_case_sensitive_names

To check the case sensitivity configuration in StarRocks (specifically from version 4.0 onwards), the parameter you are looking for is actually

enable_table_name_case_insensitive

. You can check its value using the following SQL command:

sql
ADMIN SHOW FRONTEND CONFIG LIKE "enable_table_name_case_insensitive";

Key Details about this Setting: * Default Value:

false

(which means database and table names are case-sensitive). * Behavior when `true`: All names (catalog, database, table, view) are stored in lowercase, and SQL commands are automatically converted to lowercase for matching. * Mutability: This parameter cannot be changed after the cluster is initialized. It must be set at the time of cluster creation. If you attempt to change it later, the FE will fail to start or ignore the change. Other Related Variables If you are coming from a MySQL background, you might also see: *

lower_case_table_names

: You can check this via

SHOW VARIABLES LIKE 'lower_case_table_names';

. However, in StarRocks, this is primarily for MySQL client compatibility and typically does not change the core engine's case-sensitivity behavior. Why you are seeing the error Since you just upgraded to 4.1.6, if your previous version (4.0.12) had this set to

false

(default), then

DMAPP

and

dmapp

are treated as different databases. If your Airflow tasks started failing, it's likely because the SQL is using

DMAPP

while the database was actually created as

dmapp

(or vice versa), and the newer version is enforcing this strictly. References • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/cluster-management/config_vars/ADMIN_SHOW_CONFIG.md References • administration/management/FE_configuration.md • sql-reference/System_variable.md • administration/management/BE_configuration.md • deployment/deploy_manually.md

@Rocky select * from losrep.approval a WHERE (a.CREATED_DATE > current_date() - interval 10 hour) OR (a.LAST_UPDATED_DATE >= current_date() - - interval 10 hour); correct

The SQL query you provided has a few syntax and logical issues that may cause it to fail or return unexpected results in StarRocks. 1. HTML Encoding Error The symbol

>

is HTML encoding for

>

. In a SQL editor or script, this must be replaced with the actual character: * Incorrect:

a.CREATED_DATE > ...

* Correct:

a.CREATED_DATE > ...

2. Double Negative (

- - interval

) While mathematically

- -

equals

+

, StarRocks documentation notes that you cannot use two consecutive hyphens (

--

) because they are interpreted as the start of a comment. * If there is no space between them (e.g.,

current_date()--interval

), the rest of your query will be ignored as a comment, causing a syntax error. * Even with a space (

- -

), it is highly recommended to use

+

for readability. * Correct:

current_date() + interval 10 hour

3. Logical Check:

current_date()

vs

now()

*

current_date()

returns today's date at midnight (e.g.,

2026-10-30 00:00:00

). *

current_date() - interval 10 hour

results in yesterday at 2:00 PM. *

current_date() + interval 10 hour

results in today at 10:00 AM. If your goal is to find records from the last 10 hours relative to the current time, you should use

now()

instead:

sql
WHERE a.CREATED_DATE > now() - interval 10 hour

Recommended Version If you want records created or updated within the last 10 hours of this exact moment:

sql
SELECT * 
FROM losrep.approval a 
WHERE a.CREATED_DATE > now() - INTERVAL 10 HOUR 
   OR a.LAST_UPDATED_DATE >= now() - INTERVAL 10 HOUR;

If you specifically intended to use midnight as the reference point:

sql
SELECT * 
FROM losrep.approval a 
WHERE a.CREATED_DATE > current_date() - INTERVAL 10 HOUR 
   OR a.LAST_UPDATED_DATE >= current_date() + INTERVAL 10 HOUR; -- Note the + instead of - -

Summary of Rules * Intervals:

DATE - INTERVAL 10 HOUR

is valid and returns a

DATETIME

. * Comments: Avoid

--

unless you intend to comment out the rest of the line. * Encoding: Ensure

>

is converted back to

>

. References • sql-reference/sql-functions/math-functions/negative.md • faq/Sql_faq.md • knowledge/usage/best-practice.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • release_notes/release-2.2.md

WHERE (a.CREATED_DATE > SYSDATE - 14/24) OR (a.LAST_UPDATED_DATE >= SYSDATE - 14/24) … 2026-10-30 135433,006 [INFO] Batch 1 (mb): rows=827 csv=0.38 MB / limit=100 MB 2026-10-30 135433,026 [INFO] StreamLoad → 10.0.27.71/api/losrep/approval/_stream_load label=approval_22_2026-10-30_1 rows=827 bytes=401503 2026-10-30 135433,061 [INFO] StreamLoad redirect → 10.0.27.71/api/losrep/approval/_stream_load 2026-10-30 135433,079 [INFO] StreamLoad result: Status=Fail NumberLoadedRows=0 NumberFilteredRows=0 2026-10-30 135433,079 [ERROR] Job 22 failed: Stream Load failed [Fail]: Duplicate column: loan_emi_internal Traceback (most recent call last): File "C:\Users\thanh.lt17\Desktop\Tool\WPy64-31450\notebooks\etl_runner_v4.py", line 967, in run_job loaded = stream_load_to_starrocks( dest_cfg = dest_cfg, ...<6 lines>... max_filter_ratio = max_filter_ratio, ) File "C:\Users\thanh.lt17\Desktop\Tool\WPy64-31450\notebooks\etl_runner_v4.py", line 737, in stream_load_to_starrocks raise RuntimeError(f"Stream Load failed [{status}]: {error_msg}") RuntimeError: Stream Load failed [Fail]: Duplicate column: loan_emi_internal 2026-10-30 135433,155 [INFO] Tick done | success=0 error=1

The error

Duplicate column: loan_emi_internal

during a Stream Load occurs because the column mapping sent in the HTTP headers (specifically the

columns

header) defines the same column name more than once. In StarRocks, the

columns

parameter must define a unique set of names. Here are the most likely reasons this is happening in your Python ETL script: 1. Duplicate Column in Source Query Since your script pulls from Oracle, check if the source SQL select statement accidentally includes the column twice or has a join that results in two columns with the same name. * Example of what causes this:

SELECT loan_emi_internal, ..., loan_emi_internal FROM ...

* How it fails: If your script automatically generates the

-H "columns: ..."

header from the source result set, it will include

loan_emi_internal

twice, which StarRocks rejects. 2. Manual Mapping Conflict If your script or configuration defines a transformation and a raw column with the same name, StarRocks will throw this error. * Example:

-H "columns: loan_emi_internal, temp_col, loan_emi_internal = upper(temp_col)"

* Fix: If you are transforming a column, the "raw" column from the source must be given a temporary name. _ _Correction:*

-H "columns: original_val, loan_emi_internal = upper(original_val)"

3. Case Sensitivity Issues StarRocks column names are typically case-insensitive. If your source (Oracle) has two columns like

LOAN_EMI_INTERNAL

and

loan_emi_internal

, and your script passes them both to StarRocks without renaming them, StarRocks treats them as the same name. * Verification: Check the schema of the source table/query. If there are two columns that differ only by case, you must alias one of them in your source query. 4. Hidden Duplicate in the Python Script In your

etl_runner_v4.py

, the code that builds the

columns

header might be appending the list of columns incorrectly (e.g., appending the same list twice or not clearing a buffer). How to Troubleshoot: 1. Check your Script Logs: Look for the actual

curl

command or the HTTP headers being sent by

etl_runner_v4.py

. Specifically, look at the

columns

header. 2. Verify the Target Table:

sql
    DESC losrep.approval;

Ensure the table itself doesn't have a naming conflict (though StarRocks usually prevents this at creation). 3. Check the Oracle Source: Run the source query manually in an Oracle client to see if

loan_emi_internal

appears in the result set twice. Recommendation: Modify your source query to ensure every column has a unique name, and ensure your Python script isn't accidentally doubling up the column list in the Stream Load headers. References • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • faq/loading/Stream_load_faq.md • loading/StreamLoad.md References • faq/loading/Stream_load_faq.md • faq/loading/Loading_faq.md • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • faq/loading/Broker_load_faq.md