http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> Error occurred during SQL query exe...
# questions-and-troubleshooting
l
@Rocky Error occurred during SQL query execution SQL Error [1064] [42000]: Getting syntax error at line 3, column 36. Detail message: Unexpected input 'CURRENT_DATE', the most similar input is {a legal identifier}. CREATE TABLE 
bi_top_24h_pct_tbl
( 
symbol
*varchar*(20) NOT NULL COMMENT "Trading pair, e.g. BICOUSDT", 
sys_run_date
date NULL DEFAULT (*CURRENT_DATE*()) COMMENT "", 
fetched_at
datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT "Row ingestion time", 
open_time
datetime NULL COMMENT "Stats window open time", 
close_time
datetime NULL COMMENT "Stats window close time", 
open_price
*decimal*(24, 8) NULL COMMENT "", 
high_price
*decimal*(24, 8) NULL COMMENT "", 
low_price
*decimal*(24, 8) NULL COMMENT "", 
last_price
*decimal*(24, 8) NULL COMMENT "", 
last_qty
*decimal*(24, 8) NULL COMMENT "", 
price_change
*decimal*(24, 8) NULL COMMENT "", 
price_change_percent
*decimal*(10, 4) NULL COMMENT "", 
weighted_avg_price
*decimal*(24, 8) NULL COMMENT "", 
volume
*decimal*(30, 8) NULL COMMENT "Base asset volume", 
quote_volume
*decimal*(30, 8) NULL COMMENT "Quote asset volume", 
count
*bigint*(20) NULL COMMENT "Number of trades", 
first_id
*bigint*(20) NULL COMMENT "", 
last_id
*bigint*(20) NULL COMMENT "" ) ENGINE=OLAP PRIMARY *KEY*(
symbol
, 
sys_run_date
) PARTITION BY date_trunc('day', 
sys_run_date
) DISTRIBUTED BY HASH(
symbol
) BUCKETS 1 PROPERTIES ( "compression" = "LZ4", "fast_schema_evolution" = "true", "partition_live_number" = "5", "replicated_storage" = "true", "replication_num" = "2" );
r
The error you're seeing is caused by the syntax 
DEFAULT (CURRENT_DATE())
. In StarRocks, while 
CURRENT_DATE()
is a valid function for queries, it is not supported as an expression-based default value using the 
DEFAULT (<expr>)
syntax. The supported way to set a "current date" or "current time" as a default value is to use the 
CURRENT_TIMESTAMP
 keyword. Recommended Fixes 1. Fix the Default Value Change 
DEFAULT (CURRENT_DATE())
to 
DEFAULT CURRENT_TIMESTAMP
. StarRocks allows 
CURRENT_TIMESTAMP
as a default for 
DATE
columns; it will automatically truncate the time part and store only the date. 2. Primary Key Columns must be 
NOT NULL
 In a StarRocks Primary Key table, all columns included in the 
PRIMARY KEY
(in your case, 
symbol
and 
sys_run_date
) must be defined as 
NOT NULL
. Your current DDL defines 
sys_run_date
as 
NULL
. Corrected SQL Snippet:
Copy code
sql
CREATE TABLE `bi_top_24h_pct_tbl` (
  `symbol` varchar(20) NOT NULL COMMENT "Trading pair, e.g. BICOUSDT",
  -- Changed DEFAULT and set to NOT NULL
  `sys_run_date` date NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT "", 
  `fetched_at` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT "Row ingestion time",
  ...
) ENGINE=OLAP
PRIMARY KEY(`symbol`, `sys_run_date`)
PARTITION BY date_trunc('day', `sys_run_date`)
DISTRIBUTED BY HASH(`symbol`) BUCKETS 1
PROPERTIES (
  "fast_schema_evolution" = "true",
  "replicated_storage" = "true",
  "replication_num" = "2"
);
Key Considerations * Version Requirement: Your use of 
PARTITION BY date_trunc(...)
(Expression Partitioning) requires StarRocks v3.0 or later. If you are on an older version, you must use standard range partitioning. * Primary Key Constraints: In Primary Key tables, the partition column must be part of the Primary Key. Since 
sys_run_date
is in your 
PRIMARY KEY(...)
list, your expression partitioning is valid for versions 3.0+. * Default Values: Only specific functions like 
CURRENT_TIMESTAMP
, 
uuid()
, and 
uuid_numeric()
are supported as functional defaults. Most other functions cannot be used in the 
DEFAULT
clause. References • faq/Others.md • sql-reference/sql-functions/date-time-functions/current_timestamp.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/management/timezone.md
l
@Rocky PARTITION BY date_trunc('day', 
fetched_at
) DISTRIBUTED BY HASH(
symbol
) BUCKETS 1 PROPERTIES ( "compression" = "LZ4", "fast_schema_evolution" = "true", "partition_live_number" = "5", "replicated_storage" = "true", "replication_num" = "2" ); how to add above code to a masterialized vierw below: CREATE MATERIALIZED VIEW starrocks_audit_db__.bi_top_24h_pct REFRESH ASYNC *START*('2026-08-01 080000') EVERY (interval 5 minute) DISTRIBUTED BY HASH(symbol) BUCKETS 1 AS
r
To integrate the partitioning, distribution, and properties into your asynchronous Materialized View (MV), you need to place them after the 
CREATE
statement but before the 
AS
clause. Note that some properties from your table definition (like 
fast_schema_evolution
and 
replicated_storage
) are specific to OLAP Primary Key tables and do not apply to Materialized Views. For MVs, you should use 
partition_ttl_number
to manage the number of active partitions. Final Materialized View SQL
Copy code
sql
CREATE MATERIALIZED VIEW starrocks_audit_db__.bi_top_24h_pct
-- 1. Partitioning (requires StarRocks 3.0+)
PARTITION BY date_trunc('day', `fetched_at`)
-- 2. Distribution
DISTRIBUTED BY HASH(`symbol`) BUCKETS 1
-- 3. Refresh Schedule
REFRESH ASYNC START('2026-08-01 08:00:00') EVERY (interval 5 minute)
-- 4. Properties (Note: partition_ttl_number is the MV equivalent of partition_live_number)
PROPERTIES (
  "replication_num" = "2",
  "partition_ttl_number" = "5",
  "compression" = "LZ4"
)
AS
SELECT 
  symbol,
  fetched_at,
  -- Ensure you include all columns needed from the source table
  open_time,
  close_time,
  last_price,
  volume
FROM your_source_table;
Key Changes and Explanations: * Placement: The 
PARTITION BY
, 
DISTRIBUTED BY
, 
REFRESH
, and 
PROPERTIES
clauses must all appear before the 
AS
keyword. * Partitioning: Using 
date_trunc('day', fetched_at)
is supported in asynchronous MVs starting from StarRocks v3.0. Ensure 
fetched_at
is included in your 
SELECT
list. * Property Mapping: * 
partition_ttl_number
: In Materialized Views, this property is used to limit the number of partitions kept (equivalent to 
partition_live_number
in tables). * Removed 
fast_schema_evolution
: This is an optimization for Primary Key table schema changes and does not apply to MVs. * Removed 
replicated_storage
: This is a storage optimization for Primary Key tables and is not a valid property for MVs. * Primary Key: MVs do not have a 
PRIMARY KEY
clause. The data distribution and indexing are handled by the 
DISTRIBUTED BY
and the internal sort keys. References * sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md * using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
l
@Rocky how to submit task each 5m to insert to a table
r
To schedule an 
INSERT
statement to run every 5 minutes in StarRocks, you can use the 
SUBMIT TASK
 command. Syntax (StarRocks v3.3 and later) Starting from version 3.3, StarRocks supports native scheduling directly within the 
SUBMIT TASK
statement:
Copy code
sql
SUBMIT TASK my_insert_task
SCHEDULE EVERY (INTERVAL 5 MINUTE)
AS
INSERT INTO target_table
SELECT * FROM source_table
-- Optional: WHERE condition to fetch only new data
WHERE created_at >= now() - INTERVAL 5 MINUTE;
Key Details * Version Requirement: The 
SCHEDULE
clause for periodic tasks requires StarRocks v3.3 or later. * Task Name: 
my_insert_task
is a unique identifier for your job. * Interval: The minimum supported interval is 10 seconds. * Asynchronous: Once submitted, the task runs in the background. You do not need to keep your SQL session open. ────────── Monitoring and Management 1. Check Task Status To see the definition and schedule of your created tasks:
Copy code
sql
SELECT * FROM INFORMATION_SCHEMA.tasks WHERE task_name = 'my_insert_task';
2. Check Execution History To see if specific runs succeeded or failed:
Copy code
sql
SELECT * FROM INFORMATION_SCHEMA.task_runs 
WHERE task_name = 'my_insert_task' 
ORDER BY create_time DESC;
3. Delete a Task If you want to stop the schedule:
Copy code
sql
DROP TASK my_insert_task;
For StarRocks Versions Older than v3.3 If you are on an older version (v2.5 to v3.2), 
SUBMIT TASK
only supports one-time execution. To achieve a 5-minute schedule on older versions, you must use an external orchestrator such as Apache Airflow, DolphinScheduler, or a simple Crontab script that triggers the SQL command. References • sql-reference/sql-statements/loading_unloading/pipe/CREATE_PIPE.md • loading/InsertInto.md • loading/RoutineLoad.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md
l
@Rocky recommend storage for FE log and metadata
r
For FE (Front-End) nodes, StarRocks focuses on metadata persistence and operational logging. Below are the recommended storage specifications and configurations based on official documentation. 1. FE Metadata Storage The FE uses BDBJE (Berkeley DB Java Edition) to persist metadata edit logs. * Medium: * Recommended: SSD or NVMe. While the official docs state that 100 GB HDD is "enough for most scenarios" because the absolute data volume is small, using high-performance storage significantly reduces latency for metadata write operations (edit logs) and speeds up FE startup/checkpoint replaying. * Primary Key Tables: If you use Primary Key tables extensively, the metadata load is higher; SSD becomes more critical. * Capacity: 100 GB per FE node is sufficient for almost all production clusters. * File System: XFS is recommended over ext4 as StarRocks typically performs better with it. * I/O Scheduler: * For SSD/NVMe: Use 
kyber
or `none`/`noop`. * For SATA/HDD: Use 
mq-deadline
. 2. FE Log Storage Logs are stored in the directory defined by 
sys_log_dir
(default: 
${STARROCKS_HOME}/log
). * Medium: Standard HDD is usually sufficient. However, if your cluster handles extremely high query concurrency (resulting in massive audit and query logs), consider a dedicated disk or SSD to prevent logging I/O from impacting metadata operations. * Retention Configuration: * Default rotation is every 1024 MB or every day. * Default retention is 7 days (
sys_log_delete_age = 7d
). * Ensure the partition hosting the logs has enough head-room to prevent a full disk from crashing the FE process. 3. Summary Recommendation Table Component | Recommended Disk Type | Capacity | File System FE Metadata | SSD / NVMe | 100 GB+ | XFS FE Logs | HDD or SSD | 50 GB - 200 GB* | XFS / ext4 _\_Capacity for logs depends on your configured retention period and audit log volume.* Important Architecture Note Because StarRocks uses a Follower/Leader mechanism (Raft-based), metadata is replicated across FE nodes. To ensure high availability, you should deploy at least three Follower FE nodes. This hardware-level redundancy is more critical for metadata safety than local RAID configurations, though RAID 1 or 10 is still a common best practice for local disk protection in production. References • deployment/plan_cluster.md • deployment/environment_configurations.md • administration/management/logs.md • faq/operation_maintenance_faq.md References • faq/operation_maintenance_faq.md • deployment/plan_cluster.md • deployment/environment_configurations.md • deployment/deployment_prerequisites.md • introduction/Architecture.md
l
@Rocky what role can create database
r
In StarRocks, the ability to create a database is controlled by specific roles and privileges: 1. Built-in Roles The following system-defined roles have the permission to create databases by default: * 
root
: The superuser role with global privileges across the entire cluster. * 
db_admin
: The built-in database administrator role. It specifically holds the 
CREATE DATABASE
privilege at the Catalog level. 2. Required Privilege If you are creating a custom role, you must grant it the 
CREATE DATABASE
privilege on a specific catalog (e.g., the default 
default_catalog
). Example: Granting privilege to a custom role
Copy code
sql
-- Create a custom role
CREATE ROLE developer_lead;

-- Grant the privilege to create databases in the default catalog
GRANT CREATE DATABASE ON CATALOG default_catalog TO ROLE developer_lead;

-- Assign the role to a user
GRANT developer_lead TO USER 'jack'@'%';
3. Summary of Roles Role | Can Create Database? | Scope 
root
| Yes | Global (All Catalogs) 
db_admin
| Yes | All Catalogs 
user_admin
| No | Only manages users/roles 
cluster_admin
| No | Only manages nodes/infrastructure 
public
| No | Default role for all users (no privileges by default) References • administration/user_privs/authorization/built_in_roles.md • administration/user_privs/authorization/user_privs.md References • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authorization/privilege_faq.md • administration/user_privs/authorization/privilege_item.md • administration/user_privs/authorization/user_privs.md • administration/user_privs/authorization/built_in_roles.md • sql-reference/sql-statements/account-management/GRANT.md
l
@Rocky what 
db_admin can do?
r
The 
db_admin
 role is a built-in role designed for users who need full control over data and database objects but do not need to manage the cluster infrastructure or user accounts. 1. Data and Object Management A 
db_admin
can perform almost all operations related to data and the objects that contain it: * Catalogs & Databases: Create, drop, and alter catalogs (including external catalogs) and databases. * Tables & Views: Create, drop, alter, and query (
SELECT
) tables and views. They can also perform DML operations like 
INSERT
, 
UPDATE
, and 
DELETE
. * Materialized Views: Create, drop, alter, and manually 
REFRESH
materialized views. * Loading: Create and manage 
PIPE
objects for data loading. 2. System and Resource Management Unlike a standard user, a 
db_admin
has several system-level administrative privileges: * Resources: Manage external resources (e.g., Spark, HDFS) and resource groups. * Storage: Create and manage Storage Volumes and global functions. * Advanced Features: Manage plugins, files, query blacklists, and security-related configurations (like masking or row-access policies). 3. What 
db_admin
CANNOT do To maintain security through separation of duties, the 
db_admin
role is restricted from the following: * User Management: It cannot create/drop users or grant/revoke privileges (this requires 
user_admin
). * Cluster Management: It cannot add, drop, or decommission FE/BE nodes (this requires 
cluster_admin
). * Role Mutation: 
db_admin
is an immutable role; you cannot change its predefined set of permissions. Privilege Summary Table Level | Key Privileges System | 
CREATE RESOURCE
, 
CREATE EXTERNAL CATALOG
, 
CREATE STORAGE VOLUME
, 
PLUGIN
, 
OPERATE
Catalog | 
CREATE DATABASE
, 
USAGE
, 
ALTER
, 
DROP
Database | 
CREATE TABLE
, 
CREATE VIEW
, 
CREATE MATERIALIZED VIEW
, 
CREATE PIPE
Table | 
SELECT
, 
INSERT
, 
UPDATE
, 
DELETE
, 
ALTER
, 
DROP
, 
EXPORT
References • administration/user_privs/authorization/built_in_roles.md • administration/user_privs/authorization/user_privs.md References • administration/user_privs/authorization/built_in_roles.md • administration/user_privs/authorization/user_privs.md • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authorization/privilege_item.md
l
@Rocky 
db_admin can select all db?
r
Yes, the 
db_admin
 role can SELECT from all databases and tables across all catalogs in the cluster. As the built-in database administrator, 
db_admin
is automatically granted full data-related privileges. This includes: * All Catalogs: It has 
USAGE
on all catalogs (internal and external). * All Databases: It can create, drop, and alter any database. * All Tables/Views: It has 
SELECT
, 
INSERT
, 
UPDATE
, and 
DELETE
privileges on all tables, views, and materialized views globally. Key Points to Remember: 1. Immutable: You cannot revoke these privileges from the 
db_admin
role; its scope is fixed by the system. 2. No Granting: While a 
db_admin
can see and modify all data, they cannot grant these permissions to other users. Only the 
root
or 
user_admin
roles can manage privileges. 3. Visibility: Because 
db_admin
has privileges on all databases, statements like 
SHOW DATABASES;
or 
SHOW TABLES;
will return every object in the cluster for a user with this role. References • administration/user_privs/authorization/built_in_roles.md • administration/user_privs/authorization/user_privs.md References • administration/user_privs/authorization/built_in_roles.md • administration/user_privs/authorization/user_privs.md • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authorization/privilege_faq.md • sql-reference/sql-statements/account-management/GRANT.md • administration/user_privs/authorization/privilege_item.md • item) References • administration/user_privs/authorization/built_in_roles.md • administration/user_privs/authorization/user_privs.md • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authorization/privilege_faq.md • sql-reference/sql-statements/account-management/GRANT.md
l
@Rocky a có check được ví dụ user: dm_ds của e trên starrock đang chạy jobs nào tốn source ra sao k ta
r
Chào bạn, việc kiểm tra các "job" (truy vấn hoặc tác vụ load) của một user cụ thể như 
dm_ds
trên StarRocks và mức độ tiêu tốn tài nguyên hoàn toàn thực hiện được. Dưới đây là các cách chính để bạn kiểm tra: 1. Kiểm tra các truy vấn đang chạy (Real-time) Để xem các query đang thực thi của user 
dm_ds
, bạn có thể sử dụng các lệnh sau: * Xem trên FE hiện tại:
Copy code
sql
    SHOW PROC '/current_queries';
Cột User sẽ hiển thị tên người dùng, còn các cột MemoryUsage (Bộ nhớ) và CPUTime (Thời gian CPU) sẽ cho bạn biết mức độ "ngốn" tài nguyên của query đó. * Xem trên toàn bộ cụm (Global - Version 3.4+):
Copy code
sql
    SHOW PROC '/global_current_queries';
* Sử dụng bảng hệ thống (Dễ lọc hơn): Nếu phiên bản StarRocks của bạn hỗ trợ, bạn có thể dùng SQL để lọc trực tiếp:
Copy code
sql
    SELECT * FROM information_schema.active_queries WHERE user = 'dm_ds';
2. Kiểm tra các tác vụ Load dữ liệu (Load Jobs) Nếu "job" bạn nói là các tác vụ nạp dữ liệu (như Broker Load, Insert Into), bạn có thể kiểm tra trong database cụ thể:
Copy code
sql
USE <tên_database>;
SHOW LOAD WHERE Label LIKE '%'; -- Sau đó bạn tìm theo User/Label của dm_ds
Hoặc với Routine Load (nạp từ Kafka):
Copy code
sql
SHOW ROUTINE LOAD FOR <tên_database>;
3. Kiểm tra các Task đã lập lịch (Scheduled Tasks) Nếu user 
dm_ds
sử dụng tính năng Task của StarRocks:
Copy code
sql
-- Xem danh sách task
SHOW TASKS;
-- Xem lịch sử chạy và tài nguyên tiêu tốn của các lần chạy gần đây
SELECT * FROM information_schema.task_runs WHERE user = 'dm_ds' ORDER BY create_time DESC;
4. Kiểm tra qua Resource Group Nếu bạn có phân quyền user 
dm_ds
vào một Resource Group riêng, bạn có thể xem tổng tài nguyên mà group đó đang chiếm dụng:
Copy code
sql
SHOW USAGE RESOURCE GROUPS;
Lệnh này sẽ hiển thị 
BEInUseCpuCores
(CPU đang dùng) và 
BEInUseMemBytes
(Bộ nhớ đang dùng) của từng group. Tóm lại: Cách nhanh nhất để "bắt" các query đang chạy tốn resource của user đó là: 1. Chạy 
SHOW PROC '/current_queries'
. 2. Tìm dòng có User là 
dm_ds
. 3. Nhìn vào cột MemoryUsage và CPUTime. Nếu query đó gây treo hệ thống, bạn có thể lấy 
QueryId
và dừng nó bằng lệnh: 
KILL QUERY 'query_id';
References • using_starrocks/running_queries.md • administration/management/resource_management/query_queues.md • sql-reference/sql-statements/loading_unloading/SHOW_LOAD.md • administration/management/resource_management/resource_group.md References • sql-reference/sql-statements/loading_unloading/SHOW_LOAD.md
l
@Rocky my starrocks fe log foler inscrease very fast like below, how to change this 2026-08-10 172850.062+07:00 INFO (tablet scheduler|66) [TabletScheduler.updateWorkingSlots():236] not all backends have path info 2026-08-10 172851.062+07:00 INFO (tablet scheduler|66) [TabletScheduler.updateWorkingSlots():236] not all backends have path info 2026-08-10 172852.062+07:00 INFO (tablet scheduler|66) [TabletScheduler.updateWorkingSlots():236] not all backends have path info 2026-08-10 172852.092+07:00 INFO (starrocks-mysql-nio I/O-2|201) [AcceptListener.handleEvent():80] Connection established. remote=/10.0.82.23:53248, connectionId=16777225 2026-08-10 172852.092+07:00 INFO (starrocks-mysql-nio-pool-0|221) [AcceptListener.lambda$handleEvent$1():88] Connection scheduled to worker thread 221. remote=/10.0.82.23:53248, connectionId=16777225 2026-08-10 172853.063+07:00 INFO (tablet scheduler|66) [TabletScheduler.updateWorkingSlots():236] not all backends have path info 2026-08-10 172854.063+07:00 INFO (tablet scheduler|66) [TabletScheduler.updateWorkingSlots():236] not all backends have path info 2026-08-10 172854.537+07:00 INFO (AutoStatistic|55) [DatabaseTransactionMgr.beginTransaction():193] begin transaction: txn_id: 13873009 with label insert_019feb37-dc89-71e6-995e-3a9350891f10 from coordinator FE: 10.0.27.71, listner id: -1 2026-08-10 172854.545+07:00 INFO (AutoStatistic|55) [DefaultCoordinator.prepareProfile():638] dispatch load job: 019feb37-dc89-71e6-995e-3a9350891f10 to [3731846] 2026-08-10 172854.609+07:00 INFO (AutoStatistic|55) [DatabaseTransactionMgr.commitPreparedTransaction():550] transaction:[TransactionState. txn_id: 13873009, label: insert_019feb37-dc89-71e6-995e-3a9350891f10, db id: 10001, table id list: 10028, load id list: null, callback id: [-1, 46457009], coordinator: FE: 10.0.27.71, transaction status: COMMITTED, error replicas num: 0, unknown replicas num: 0, prepare time: 1786357734537, write end time: 1786357734603, allow commit time: -1, commit time: 1786357734606, finish time: -1, write cost: 69ms, reason: , attachment: com.starrocks.transaction.InsertTxnCommitAttachment@367be999, tabletCommitInfos size: 20, partition commit info:[partitionId=10030, version=176123, versionTime=1786357734606, isDoubleWrite=false,], warehouse: 0] successfully committed 2026-08-10 172854.649+07:00 WARN (starrocks-mysql-nio-pool-0|221) [StmtExecutor.logOptimizerTraceOnGenerateExecPlanFailure():761] Generate exec plan failed. Planner profile: query_id=019feb37-dcf3-7d36-8e79-fbe63b63c8e7, sql=/* ApplicationName=DBeaver 25.3.0 - SQLEditor <Script-164.sql> */ select * from dmapp.py_etl_db_connection LIMIT 0, 200, err=SemanticException: Getting analyzing error. Detail message: Unknown table 'dmapp.py_etl_db_connection'., profile=Planner: - -- Parser[1] 1ms - -- Total[1] 0 - -- Analyzer[1] 0 - -- Lock[1] 0 - -- AnalyzeDatabase[1] 0 - -- AnalyzeTemporaryTable[1] 0 - -- AnalyzeTable[1] 0 Reason: 2026-08-10 172854.650+07:00 INFO (starrocks-mysql-nio-pool-0|221) [StmtExecutor.execute():1057] execute Exception, sql: /* ApplicationName=DBeaver 25.3.0 - SQLEditor <Script-164.sql> */ select * from dmapp.py_etl_db_connection LIMIT 0, 200, error: Getting analyzing error. Detail message: Unknown table 'dmapp.py_etl_db_connection'. 2026-08-10 172854.739+07:00 INFO (PUBLISH_VERSION|52) [DatabaseTransactionMgr.finishTransaction():1373] finish transaction TransactionState. txn_id: 13873009, label: insert_019feb37-dc89-71e6-995e-3a9350891f10, db id: 10001, table id list: 10028, load id list: null, callback id: [-1, 46457009], coordinator: FE: 10.0.27.71, transaction status: VISIBLE, error replicas num: 0, unknown replicas num: 0, prepare time: 1786357734537, write end time: 1786357734603, allow commit time: -1, commit time: 1786357734606, finish time: 1786357734737, write cost: 69ms, wait for publish cost: 10ms, publish rpc cost: 118ms, publish ack cost: 3ms, publish total cost: 131ms, total cost: 200ms, reason: , attachment: com.starrocks.transaction.InsertTxnCommitAttachment@367be999, partition commit info:[partitionId=10030, version=176123, versionTime=1786357734606, isDoubleWrite=false,], warehouse: 0 successfully 2026-08-10 172854.743+07:00 INFO (AutoStatistic|55) [StatisticExecutor.collectStatistics():574] execute statistics job successfully, duration=27.89 s, job=HyperStatisticsCollectJob{type=FULL, scheduleType=SCHEDULE, db=com.starrocks.catalog.Database@36b2b3, table=Table [id=3986544, name=app_tracking_event, type=OLAP], partitionIdList=[3986608], columnNames=[ID, EVENT_NAME, FEATURE, SCREEN_CATE, DEVICE_ID, DEVICE_NAME, SESSION_ID, SESSION_START, USERNAME, CUSTOMER_CODE, CUSTOMER_GROUP, CUSTOMER_SOURCE, APP_CODE, PRODUCT_CODE, EVENT_TIMESTAMP, PARAMETERS, created_at, created_by, updated_at, updated_by, op, dbz_table, source_ts_ms], properties={}} 2026-08-10 172855.063+07:00 INFO (tablet scheduler|66) [TabletScheduler.updateWorkingSlots():236] not all backends have path info
r
To manage the rapid growth of your FE logs and address the repetitive messages, you can adjust the log configuration and troubleshoot the root cause of the flood. 1. Fix the Root Cause of the "Path Info" Log The log 
not all backends have path info
repeats every second because the FE's Tablet Scheduler cannot see the disk storage information for one or more BE (Backend) nodes. * Check BE Status: Run 
SHOW PROC '/backends';
in your SQL editor. * What to look for: Check if any BE has 
Alive = false
or if the 
Disk
related columns are empty/zero. * Solution: If a BE is down or has lost its storage path, restart that BE or fix its 
storage_root_path
configuration. Once all BEs report their paths correctly, this log will stop. 2. Change FE Log Configuration You can modify the following parameters in your 
fe/conf/fe.conf
file to reduce log volume and save disk space. Note: Most of these require an FE restart to take effect. A. Reduce Log Level (To hide INFO logs) Change the log level from 
INFO
to 
WARN
. This will suppress the connection messages and "path info" messages, as they are 
INFO
level.
Copy code
properties
sys_log_level = WARN
B. Enable Log Compression Enable Gzip compression for rotated log files to significantly reduce disk usage.
Copy code
properties
sys_log_enable_compress = true
C. Adjust Retention and Rotation If you want to keep fewer logs or rotate them faster, adjust these: * 
sys_log_delete_age = 3d
(Keep only 3 days instead of 7) * 
sys_log_roll_num = 5
(Keep only 5 rotated files) * 
log_roll_size_mb = 512
(Rotate the file when it reaches 512MB instead of 1GB) 3. Suppress Specific Modules (Advanced) If you want to keep the overall level at 
INFO
but silence only the noisy modules (like 
TabletScheduler
or 
AutoStatistic
), you can add them to the 
sys_log_warn_modules
list in `fe.conf`:
Copy code
properties
sys_log_warn_modules = com.starrocks.clone.TabletScheduler, com.starrocks.statistic.StatisticExecutor
4. Stop MySQL Connection Noise The logs show 
Connection established
from DBeaver every few seconds. * This is often caused by a "Keep Alive" or "Auto-refresh" setting in your SQL client (DBeaver). * Changing 
sys_log_level = WARN
(Step 2A) is the most effective way to hide these from the FE log. Summary Checklist 1. Run 
SHOW PROC '/backends';
to ensure all nodes are healthy. 2. Edit 
fe/conf/fe.conf
. 3. Set 
sys_log_level = WARN
. 4. Set 
sys_log_enable_compress = true
. 5. Restart the FE node. References • administration/management/FE_configuration.md • administration/management/logs.md • faq/Deploy_faq.md References • administration/management/FE_configuration.md
l
@Rocky GRANT create table IN DATABASE dmapp TO hungnm40;
r
The SQL syntax you provided is almost correct, but in StarRocks, you must use the keyword 
ON
 instead of 
IN
, and explicitly specify if it is a 
USER
 or a 
ROLE
. The correct query is:
Copy code
sql
GRANT CREATE TABLE ON DATABASE dmapp TO USER hungnm40;
Key details: * Keyword: Use 
ON DATABASE
(instead of 
IN DATABASE
). * User Identity: If the user 
hungnm40
was created with a specific host (e.g., 
'hungnm40'@'%'
), use the full identity: 
TO USER 'hungnm40'@'%'
. * Prerequisite: If you are using multiple catalogs, ensure you have the correct catalog selected (e.g., 
SET CATALOG default_catalog;
) before running the grant. * Catalog Usage: The user 
hungnm40
will also need the 
USAGE
privilege on the catalog containing that database to actually access it.
Copy code
sql
    GRANT USAGE ON CATALOG default_catalog TO USER hungnm40;
References • sql-reference/sql-statements/account-management/GRANT.md • administration/user_privs/authorization/privilege_item.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • sql-reference/sql-statements/account-management/GRANT.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/user_privs/authorization/privilege_item.md
l
@Rocky GRANT ALTER TABLE ON DATABASE dmapp TO USER hungnm40;
r
Câu lệnh bạn cung cấp gần đúng, nhưng trong StarRocks, tên quyền (privilege) cho hành động "sửa" là 
ALTER
 (không phải 
ALTER TABLE
khi đi kèm với 
ON DATABASE
). Tùy vào mục đích của bạn, có 2 cách viết đúng như sau: 1. Nếu bạn muốn cho phép user được sửa thông tin của chính Database (như đổi tên DB) Sử dụng cú pháp `GRANT ALTER ON DATABASE`:
Copy code
sql
GRANT ALTER ON DATABASE dmapp TO USER hungnm40;
2. Nếu bạn muốn cho phép user được sửa cấu trúc của TẤT CẢ các bảng trong Database đó (Phổ biến nhất) Bạn cần sử dụng cú pháp `ON ALL TABLES IN DATABASE`:
Copy code
sql
GRANT ALTER ON ALL TABLES IN DATABASE dmapp TO USER hungnm40;
Lưu ý quan trọng: * User Identity: Đảm bảo user 
hungnm40
đã được tạo. Nếu user được tạo kèm host, hãy viết đầy đủ: 
TO USER 'hungnm40'@'%'
. * Catalog: Bạn cần thực hiện lệnh 
SET CATALOG default_catalog;
(hoặc tên catalog tương ứng) trước khi chạy lệnh GRANT này. * Quyền bổ sung: Để user có thể thao tác hoàn chỉnh, thường họ sẽ cần thêm quyền 
USAGE
trên Catalog:
Copy code
sql
    GRANT USAGE ON CATALOG default_catalog TO USER hungnm40;
Tóm lại: Nếu mục đích của bạn là để user 
hungnm40
có thể chạy các lệnh như 
ALTER TABLE dmapp.my_table ...
, hãy sử dụng Cách 2. References • sql-reference/sql-statements/account-management/GRANT.md • administration/user_privs/authorization/privilege_item.md • sql-reference/sql-statements/account-management/SET_CATALOG.md References • sql-reference/sql-statements/account-management/GRANT.md • administration/user_privs/authorization/privilege_item.md .).** References • sql-reference/sql-statements/account-management/GRANT.md
l
@Rocky alter can drop table
r
No, the 
ALTER
 privilege does not include the ability to 
DROP
 a table in StarRocks. They are distinct privileges. • 
ALTER
: Allows you to modify the table schema (add/drop columns), rename the table, or change properties. • 
DROP
: Allows you to delete the table entirely. If you want a user to be able to do both, you must grant them both privileges or use `ALL`: Granting both specifically
Copy code
sql
GRANT ALTER, DROP ON ALL TABLES IN DATABASE dmapp TO USER hungnm40;
Granting all privileges (Select, Insert, Alter, Drop, etc.)
Copy code
sql
GRANT ALL ON ALL TABLES IN DATABASE dmapp TO USER hungnm40;
Summary of Table Privileges Privilege | Description ALTER | Modifies table structure or refreshes metadata. DROP | Deletes/Drops the table. ALL | Includes ALTER, DROP, SELECT, INSERT, UPDATE, etc. References • administration/user_privs/authorization/privilege_item.md • sql-reference/sql-statements/account-management/GRANT.md References • administration/user_privs/authorization/privilege_item.md
l
@Rocky insert overwrite losrep.application by name select * from dmapp.losrep_application correct
r
Câu lệnh của bạn là đúng về mặt cú pháp trong StarRocks, bắt đầu từ phiên bản 3.4. Cú pháp chi tiết:
Copy code
sql
INSERT OVERWRITE losrep.application 
BY NAME 
SELECT * FROM dmapp.losrep_application;
Các lưu ý quan trọng: 1. Phiên bản hỗ trợ: Tính năng 
BY NAME
cho lệnh 
INSERT
được chính thức hỗ trợ từ StarRocks v3.4. Nếu bạn dùng phiên bản cũ hơn (như 3.0, 3.1, 3.2), từ khóa này sẽ gây lỗi cú pháp. 2. Cơ chế hoạt động: * Khớp theo tên: Hệ thống sẽ tự động đối chiếu tên cột trả về từ lệnh 
SELECT *
với tên cột của bảng đích 
losrep.application
, bất kể thứ tự các cột như thế nào. * Cột thừa/thiếu: Các cột có trong 
SELECT
nhưng không có trong bảng đích sẽ bị bỏ qua. Các cột có trong bảng đích nhưng không có trong 
SELECT
sẽ được điền giá trị mặc định (
DEFAULT
hoặc 
NULL
). 3. INSERT OVERWRITE: Lệnh này sẽ xóa toàn bộ dữ liệu cũ trong bảng đích (hoặc partition cụ thể nếu được chỉ định) và thay thế bằng dữ liệu mới từ câu lệnh truy vấn. 4. Tên bảng/DB: Nếu 
application
hoặc 
losrep
trùng với các từ khóa dự phòng của hệ thống, bạn nên bao chúng trong dấu backtick (ví dụ: `` losrep.
application
``). Nếu bạn dùng phiên bản thấp hơn 3.4: Bạn phải bỏ 
BY NAME
và đảm bảo thứ tự các cột trong câu lệnh 
SELECT
khớp hoàn toàn với thứ tự cột trong bảng đích:
Copy code
sql
-- Dành cho StarRocks < 3.4 (Khớp theo vị trí)
INSERT OVERWRITE losrep.application 
SELECT * FROM dmapp.losrep_application;
References • sql-reference/sql-statements/loading_unloading/INSERT.md • loading/InsertInto.md References • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md
l
@Rocky grant insert, update on table dwh.af_extract_mapping in database dwh TO 'hoangqv'@'%'; correct
r
Câu lệnh của bạn có một vài điểm cần điều chỉnh để đúng với cú pháp của StarRocks. Cụ thể: 1. Từ khóa 
USER
: Bạn cần thêm từ khóa 
USER
trước tên người dùng. 2. Cấu trúc 
ON TABLE
: Nếu bạn đã chỉ định tên bảng dưới dạng 
database.table
(
dwh.af_extract_mapping
), bạn không cần thêm đoạn 
IN DATABASE dwh
nữa. 3. Quyền 
UPDATE
: Quyền này khả dụng cho các bảng thuộc loại Primary Key table. Câu lệnh đúng:
Copy code
sql
GRANT INSERT, UPDATE ON TABLE dwh.af_extract_mapping TO USER 'hoangqv'@'%';
Một số lưu ý: * Database context: Đảm bảo bạn đã chọn đúng catalog (thường là 
default_catalog
) bằng lệnh 
SET CATALOG default_catalog;
trước khi thực hiện lệnh GRANT. * User existence: Người dùng 
'hoangqv'@'%'
phải tồn tại trong hệ thống. * Quyền USAGE: Để user có thể truy cập vào database và bảng, họ cần có quyền 
USAGE
trên catalog và database đó:
Copy code
sql
    GRANT USAGE ON CATALOG default_catalog TO USER 'hoangqv'@'%';
    GRANT USAGE ON DATABASE dwh TO USER 'hoangqv'@'%';
References • sql-reference/sql-statements/account-management/GRANT.md • administration/user_privs/authorization/privilege_item.md References • sql-reference/sql-statements/account-management/GRANT.md
l
@Rocky current i have 1 starrocks FE, and 2 BE, how to setup cluster or HA for FE
r
To set up High Availability (HA) for StarRocks Frontends (FE), you need to transition from a single FE to a cluster of at least 3 Follower nodes. StarRocks uses a Raft-based consensus protocol (BDB JE). To survive a failure, a majority of Followers must be alive. * 1 FE: No HA. * 2 FEs: No HA (if 1 fails, the other cannot form a majority). * 3 FEs: HA (can survive 1 failure). Here is the step-by-step guide to adding FE nodes for HA: 1. Configure the New FE Nodes On your new server(s), prepare the StarRocks environment and edit 
fe/conf/fe.conf
. Ensure the 
priority_networks
is set correctly to avoid the FE picking the wrong IP address (e.g., a Docker bridge IP).
Copy code
properties
# Example fe.conf
priority_networks = 192.168.1.0/24
2. Register the New FEs (Run on the current Leader) Connect to your existing FE using a MySQL client and run the following command for each new node:
Copy code
sql
-- To add a Follower (contributes to HA/voting)
ALTER SYSTEM ADD FOLLOWER "IP_OF_NEW_FE:9010";

-- OR to add an Observer (read-only, does not contribute to HA quorum)
-- ALTER SYSTEM ADD OBSERVER "IP_OF_NEW_FE:9010";
_Note: 
9010
is the default 
edit_log_port
._ 3. Start the New FE On the new server, start the FE process by pointing it to the existing Leader to synchronize metadata:
Copy code
bash
bin/start_fe.sh --helper EXISTING_FE_IP:9010 --daemon
Wait for the metadata to sync. After the first successful start, you no longer need the 
--helper
flag. 4. Verify the Cluster Status Back in the MySQL client, check the status:
Copy code
sql
SHOW FRONTENDS\G
* Alive: Should be 
true
. * Role: Should show 
FOLLOWER
. * IsMaster: One will be 
true
(the Leader), others 
false
. 5. (Recommended) Load Balancing Since you now have multiple FEs, you should use a Load Balancer (like HAProxy or nginx) or a VIP (Virtual IP) in front of them to distribute client connections and handle automatic failover if the Leader changes. Important Notes: * Odd Numbers: Always aim for an odd number of Followers (3, 5) to maximize fault tolerance. * Metadata Backup: Ensure the 
meta/
directory has enough disk space. * Clock Sync: Ensure all FE and BE nodes have synchronized clocks via NTP. References • deployment/deploy_starrocks.md • administration/management/FE_Column.md
l
@Rocky a cluster of at least 3 Follower nodes. this meam 1 leader and 3 followers, so 4 FE wright? and what happen when an FE fail
r
No, that is a common misconception. In StarRocks, the Leader is one of the Followers. 1. How many FEs do you need? When we say "3 Followers," we mean 3 FE nodes in total that are configured with the Follower role. * Total FE count = 3. * Roles: 1 node will be elected as the Leader, and the other 2 will remain Followers. Why 3 and not 4? StarRocks uses a "Majority" rule ($n/2 + 1$). * With 3 FEs: Majority is 2. You can lose 1 FE and the cluster stays alive. * With 4 FEs: Majority is 3. You can still only lose 1 FE (if you lose 2, you only have 2 left, which is not a majority of 4). * Conclusion: Even numbers of FEs (like 4) add overhead without increasing fault tolerance. Stick to 3 or 5. ────────── 2. What happens when an FE fails? The behavior depends on which FE fails and how many are still running: Scenario A: A "Follower" (non-leader) fails * Impact: Almost none. * Behavior: The Leader continues to provide services. Metadata is still replicated to the remaining healthy Follower. The cluster remains fully functional (Read & Write). Scenario B: The "Leader" fails * Impact: A brief pause (seconds) in service. * Behavior: 1. The remaining 2 Followers detect the Leader is gone. 2. They hold an election immediately. 3. One of the healthy Followers is promoted to be the new Leader. 4. The cluster resumes normal operations automatically. Scenario C: Majority of FEs fail (e.g., 2 out of 3 nodes die) * Impact: Cluster goes down. * Behavior: Since the remaining 1 FE cannot form a "majority" (2), it cannot guarantee data consistency. It will stop accepting writes and metadata changes to prevent data corruption. You must bring at least one failed FE back online to recover. Summary Table for 3 FE Followers: Status | Can Write? | Can Read? 3 alive | Yes | Yes 2 alive (1 fails) | Yes (New leader elected) | Yes 1 alive (2 fail) | No | No (Cluster enters protection mode) Recommendation: To make this HA setup useful for your applications, use a Load Balancer (like HAProxy) in front of the 3 FEs. Your application connects to the Load Balancer IP, and if the Leader fails, the Load Balancer automatically redirects your queries to the new Leader. References • administration/management/FE_Column.md • deployment/deploy_starrocks.md
l
@Rocky can i change current FE ip address to new ip address and set a Load Balancer (like HAProxy) to existing FE ip address, so that exixting system does not
r
Yes, you can do this. The goal is to "liberate" the existing FE IP address so you can assign it to a Load Balancer (HAProxy) or a Virtual IP (VIP), ensuring your existing applications don't need to change their connection strings. To do this safely without losing data, you should follow this sequence: Step 1: Add New FEs First Do not move your existing FE yet. First, expand your cluster to 3 FEs using new IP addresses. 1. Set up two new FE nodes on different servers (e.g., 
New_IP_1
and 
New_IP_2
). 2. In the MySQL client (connected to your 
Existing_FE_IP
), run:
Copy code
sql
    ALTER SYSTEM ADD FOLLOWER "New_IP_1:9010";
    ALTER SYSTEM ADD FOLLOWER "New_IP_2:9010";
3. Start the new FEs using the 
--helper
flag:
Copy code
bash
    ./bin/start_fe.sh --helper Existing_FE_IP:9010 --daemon
4. Verify all 3 are 
Alive
and 
Follower
using 
SHOW FRONTENDS;
. Step 2: Migrate the Leader If your 
Existing_FE_IP
is currently the Leader (Master), you need to move that role to one of the new nodes. 1. Stop the FE on the `Existing_FE_IP`:
Copy code
bash
    ./bin/stop_fe.sh
2. The cluster will automatically elect one of the new nodes as the new Leader. Check this by connecting to one of the new IPs and running 
SHOW FRONTENDS;
. Step 3: Remove the Old FE from the Cluster Now that the cluster is running on the two new nodes, remove the old IP from the StarRocks metadata:
Copy code
sql
ALTER SYSTEM DROP FOLLOWER "Existing_FE_IP:9010";
Step 4: Set Up the Load Balancer on the Old IP Now that the 
Existing_FE_IP
is no longer used by StarRocks, you can repurpose it. 1. If the IP was a physical IP on a server: Install HAProxy on that server. 2. If using a VIP (Keepalived): Configure the VIP to be the 
Existing_FE_IP
. 3. HAProxy Configuration: Configure HAProxy to listen on the 
query_port
(default 9030) and route traffic to the new FE IPs. Example HAProxy snippet:
Copy code
haproxy
listen starrocks_cluster
    bind Existing_FE_IP:9030
    mode tcp
    option tcplog
    balance roundrobin
    server fe1 New_IP_1:9030 check
    server fe2 New_IP_2:9030 check
Why this is the best approach: * Zero downtime for applications: Since the Load Balancer takes over the 
Existing_FE_IP
, your apps never notice a change. * Safety: You ensure the cluster has a healthy majority on new IPs before touching the original node. * Metadata Integrity: StarRocks metadata is very sensitive to IP changes. Dropping the old node and adding new ones is much cleaner than trying to "rename" the IP of a running single-node cluster. Important Note on 
priority_networks
 Before starting the new FEs, ensure you set the 
priority_networks
in their 
fe.conf
to their respective new subnets. This prevents them from accidentally binding to the wrong network interface. References • administration/management/FE_Column.md • deployment/deploy_starrocks.md • administration/Meta_recovery.md References • administration/Meta_recovery.md • deployment/upgrade.md • deployment/deploy_manually.md • administration/management/enable_fqdn.md
l
@Rocky Remove the Old FE from the Cluster, after remove can i change ip and start this old FE again
r
Yes, you can absolutely do this. However, there is one critical step: you must treat the "old FE" as a completely new node because StarRocks FE metadata (BDBJE) hardcodes the IP address inside its internal log files. If you change the IP of a server but keep the old 
meta/
directory, the FE will fail to start because of an "IP mismatch" error. The correct workflow to re-add the old FE: 1. Stop and Drop (You've already planned this): * Stop the old FE process. * From the new Leader, run: 
ALTER SYSTEM DROP FOLLOWER "Old_IP:9010";
2. Change the Server IP: * Change the IP address of the server to the new one (e.g., 
New_IP_3
). 3. Clean the Metadata (Crucial): * On that server, go to your StarRocks FE directory. * Delete or rename the 
meta/
folder:
Copy code
bash
        mv fe/meta fe/meta_backup
_ _Note: Don't worry about data loss; this node will pull a fresh copy of the metadata from the current Leader when it joins.* 4. Update 
fe.conf
: * Ensure 
priority_networks
in 
fe/conf/fe.conf
is updated to reflect the new IP range/subnet. 5. Re-register the Node: * Connect to your current Leader (one of the new FEs you set up) via MySQL and run:
Copy code
sql
        ALTER SYSTEM ADD FOLLOWER "New_IP_3:9010";
6. Start the FE as a "Helper": * On the old server (now with the new IP), start the FE by pointing it to the current Leader:
Copy code
bash
        bin/start_fe.sh --helper NEW_LEADER_IP:9010 --daemon
Summary of why you must delete `meta/`: StarRocks FEs use a library called BDBJE for metadata replication. BDBJE records the node's identity and IP address in its image files. If the IP changes, the library sees a conflict and refuses to start. Deleting the 
meta/
folder forces the node to start "clean" and download the current, correct metadata state from the cluster Leader. Final Cluster State: After this, you will have: * 1 Load Balancer (running on the original 
Existing_FE_IP
). * 3 FE Followers (running on 3 new/updated IPs) behind that Load Balancer. * Full High Availability. References • administration/Meta_recovery.md • administration/management/FE_Column.md • deployment/deploy_starrocks.md References • administration/Meta_recovery.md • administration/management/enable_fqdn.md • administration/stargo.md
2 Views
Open in Slack
PreviousNext
Powered by