http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I keep getting this error: ```SQL E...
# questions-and-troubleshooting
d
@Rocky I keep getting this error:
Copy code
SQL Error [1064] [42000]: load_segments failed tablet:206458 rowset:8197 segid:0: starlet err [RequestID=TVV6Q57EKSKQRGZ1][StatusCode=404]Get object s3://... error: The specified key does not exist.:
Common causes for this error are:
1. A schema change has been made to the table
2. The current query is a slow query
For the first case, you can retry the current query.
For the second case, see "admin show frontend config like 'lake_autovacuum_grace_period_minutes'" for reference.: BE:58006
This is not a slow query nor there was schema change. Re-running query I get error for several different file paths. Version: 4.1.3-8a8e186 Autovacuum grace period is 30 minutes
r
This error typically indicates that the StarRocks shared-data (lake) cluster is attempting to read data segment files from S3 that have been deleted or were never successfully committed. Even if your query is fast and no schema changes occurred, the 
404 Not Found
error points to a mismatch between the metadata (which says the file should be there) and the physical storage. Since you are running v4.1.3 (a very recent release as of July 2026) and the 
lake_autovacuum_grace_period_minutes
is 30 minutes, here are the most likely causes and recommended steps: 1. Increase the Vacuum Grace Period Although 30 minutes is the default, it can be too aggressive if you have high-frequency data ingestion and compaction. If compaction finishes and the vacuum process triggers, it will delete "historical" versions of data. If any node still holds a stale metadata cache or if there is a slight delay in metadata propagation, it may try to access those deleted files. * Action: Temporarily increase the grace period to 24 hours to see if the errors stop:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("lake_autovacuum_grace_period_minutes" = "1440");
2. Check for External S3 Lifecycle Policies Ensure that the S3 bucket itself does not have a Lifecycle Policy (e.g., "Delete objects after X days" or "Abort incomplete multipart uploads"). External deletion policies can remove StarRocks data segments before the StarRocks Vacuum process does, leading to this exact error. 3. Verify Clock Synchronization In shared-data mode, StarRocks relies on timestamps for the vacuum grace period. If there is a significant clock skew between your FE and BE/CN nodes, the 30-minute grace period might be calculated incorrectly, leading to premature deletion of data segments. * Action: Ensure all nodes are synchronized via NTP. 4. Investigate the New v4.1 Vacuum Metrics Version 4.1 introduced specific metrics for the lake vacuum process. You can check these to see if vacuum tasks are failing or if the "Catalog Recycle Bin" is behaving unexpectedly: * Access the FE metrics: 
curl http://<FE_IP>:<FE_HTTP_PORT>/metrics | grep lake_vacuum
* Look for 
lake_vacuum_retry_count
or 
lake_vacuum_batch_size
. 5. Check FE/BE Logs Search your FE and BE logs for the specific 
tablet_id
and 
rowset_id
mentioned in the error: * FE (fe.log): Look for 
Vacuum
or 
LakeService
errors. * BE (be.INFO): Look for 
starlet
errors or S3 upload failures during the time the data was written. It’s possible the rowset was committed to metadata but the actual S3 upload failed silently or was interrupted. Recommendation: If increasing the grace period to 1440 (24 hours) does not resolve the issue, it may be a regression in the v4.1.3 metadata handling or tablet splitting mechanism. Since this is a very recent version, I recommend providing the logs for the specific 
tablet:206458
to the StarRocks community or maintainers for further analysis. References • faq/shared_data_faq.md • quick_start/shared-data.md
d
@Rocky if file in S3 is missing - does it mean that tablet is lost and that’s it?
r
No, a missing file does not mean the tablet is permanently lost, but it does mean the specific data version (rowset) contained in that file is gone. In StarRocks shared-data (lake) mode, data is stored as a single replica in S3. If a file is physically missing, the tablet becomes "corrupted" because its current metadata points to a file that doesn't exist. To recover, you can use the Metadata Rollback feature to restore the tablet to its last known healthy state. 1. The Recovery Tool: 
ADMIN REPAIR TABLE
 StarRocks provides a specific mechanism for shared-data clusters to handle "File not found" errors. It scans historical metadata to find the most recent version where all files are still present on S3 and rolls the tablet back to that version. How to run it:
Copy code
sql
-- Restore the table by rolling back to a consistent healthy version
ADMIN REPAIR TABLE <your_table_name>;
Advanced Recovery Options: If the default repair doesn't work (e.g., you want to prioritize availability over strict consistency), you can use properties:
Copy code
sql
ADMIN REPAIR TABLE <your_table_name>
PROPERTIES (
    "enforce_consistent_version" = "false", -- Allows tablets to roll back to different versions to save as much data as possible
    "allow_empty_tablet_recovery" = "true"  -- If a tablet is totally unrecoverable, recreate it as an empty tablet so the table remains queryable
);
Note: This will restore the table's availability, but you will lose the data that was in the missing/corrupted files. 2. Physical Recovery (S3 Side) Before rolling back metadata (which deletes your most recent successful writes), check if you can recover the files in S3: * S3 Versioning: If you have versioning enabled on the bucket, you may be able to restore the deleted object. * S3 Lifecycle Policies: Check if there is a policy (e.g., "Delete objects after 1 day") that is accidentally cleaning up StarRocks data. StarRocks manages its own cleanup via the Vacuum process; external lifecycle policies should not be applied to the data directories. 3. Why is this happening repeatedly? Since you mentioned this happens for "several different file paths" even after re-running, it suggests a systemic issue rather than a one-off accidental deletion: * Premature Vacuum: If your BE/CN clocks are out of sync, the Vacuum process might think a file is "expired" and delete it while the FE still thinks it's part of the active data set. Check NTP sync on all nodes. * Metadata Sync Bug: In v4.1.3, if you are performing heavy compaction or tablet splitting, there might be a race condition. If 
ADMIN REPAIR TABLE
doesn't stop the issue from recurring, I recommend checking the FE logs for 
Vacuum
errors and potentially increasing 
lake_autovacuum_grace_period_minutes
significantly (e.g., to 
1440
for 24 hours). Summary: The tablet is not "lost," but it is "broken." Use 
ADMIN REPAIR TABLE
to fix the metadata, but investigate your S3 lifecycle policies and clock synchronization to prevent it from happening again. References • administration/management/resource_management/Replica.md • sql-reference/sql-statements/cluster-management/tablet_replica/ADMIN_REPAIR.md References • sql-reference/sql-statements/cluster-management/tablet_replica/ADMIN_REPAIR.md • administration/management/resource_management/Replica.md • faq/shared_data_faq.md
d
@Rocky running repair table returns this: 
SQL Error [5014] [HY000]: Table state is not NORMAL: 'events_u'
r
The error 
Table state is not NORMAL: 'events_u'
indicates that StarRocks is currently performing a metadata-locking operation on the table, such as a schema change, a materialized view build (rollup), or a partition balance. 
ADMIN REPAIR TABLE
cannot be initiated until these tasks are finished or canceled. To diagnose and resolve this, follow these steps: 1. Identify the Ongoing Operation Check if there are any active schema changes or rollup tasks:
Copy code
sql
-- Check for ongoing column/schema changes
SHOW ALTER TABLE COLUMN WHERE TableName = 'events_u' ORDER BY CreateTime DESC;

-- Check for ongoing materialized view/rollup builds
SHOW ALTER TABLE ROLLUP WHERE TableName = 'events_u' ORDER BY CreateTime DESC;
Look at the 
State
column. If it says 
WAITING_TXN
, 
RUNNING
, or 
FINISHED
(but not yet cleaned up), the table is considered "not normal." 2. Check Partition States Sometimes the table state is blocked by a single partition. You can check the state of all partitions in the table:
Copy code
sql
-- First, find your database name, then use the following:
SHOW PROC '/dbs/<your_db_id>/<events_u_table_id>/partitions';
_(You can find the IDs by running 
SHOW DATA;
or checking the 
information_schema.tables_meta
if available in your version)._ 3. Handle the Stuck Task * Wait: If the state is 
RUNNING
, wait for it to complete. Schema changes on large tables can take time. * Cancel: If you believe the task is stuck and it is safe to do so, you can cancel it to return the table to `NORMAL`:
Copy code
sql
    CANCEL ALTER TABLE COLUMN FROM events_u;
    -- OR
    CANCEL ALTER TABLE ROLLUP FROM events_u;
4. Why this matters for your S3 error If you were in the middle of a schema change when the 
404 Not Found
errors started, the metadata might be in an inconsistent state. 1. Cancel any pending Alter/Rollup. 2. Once the table returns to 
NORMAL
state, run the repair:
Copy code
sql
   ADMIN REPAIR TABLE events_u;
Important Note: If you see 
State = 'RESTORE'
, it means the table is currently being recovered from a backup, and you must wait for that process to finish before attempting a manual repair. References • knowledge/admin_and_operation/shared_data_compaction.md
d
@Rocky all partitions are in normal state, no running alter operations
r
If StarRocks reports 
Table state is not NORMAL
even though 
SHOW ALTER TABLE COLUMN
and 
SHOW ALTER TABLE ROLLUP
show no running tasks, it means the internal state of the table object in the Frontend (FE) is set to something other than 
NORMAL
(e.g., 
SCHEMA_CHANGE
, 
ROLLUP
, 
BACKUP
, or 
RESTORE
). In a shared-data (lake) cluster, this is often caused by a background metadata task that has "locked" the table state but isn't showing up in the standard Alter logs. Please check the following in order: 1. Check for Tablet Splitting (Optimize) In StarRocks 3.3 and 4.x, shared-data tables support automatic or manual tablet splitting. This operation also changes the table state. * Action: Run 
SHOW ALTER TABLE OPTIMIZE;
* If you see a job for 
events_u
that is not 
FINISHED
or 
CANCELLED
, that is what's blocking the repair. 2. Check for Backup/Restore Operations If a backup or restore job was started (even if it failed), the table remains in a 
BACKUP
or 
RESTORE
state until the job is explicitly cleared or finished. * Action: Run 
SHOW BACKUP;
and 
SHOW RESTORE;
* If a job is stuck, you may need to cancel it: 
CANCEL BACKUP FROM events_u;
(or 
RESTORE
). 3. Check for "Stuck" Schema Changes As seen in some recent v3.x/v4.x bugs, a schema change can sometimes finish its physical work but fail to update the table state back to 
NORMAL
in the FE metadata. * Action: Look at the last entry in 
SHOW ALTER TABLE COLUMN WHERE TableName = 'events_u'
. * Even if the state is 
FINISHED
, if the 
FinishTime
is very recent or NULL, there might be a metadata sync issue. * Action: Try running 
CANCEL ALTER TABLE COLUMN FROM events_u;
anyway. Sometimes this "pokes" the FE to reset the state if it thinks a job is still active. 4. Verify Table Type Ensure the table is a standard OLAP table and not a Materialized View that is currently being refreshed or recalculated. * Action: 
SHOW FULL TABLES LIKE 'events_u';
* If it is a 
MATERIALIZED VIEW
, you cannot use 
ADMIN REPAIR TABLE
. You should use 
ALTER MATERIALIZED VIEW events_u ACTIVE;
or 
REFRESH MATERIALIZED VIEW events_u;
. 5. Inspect FE Metadata (Advanced) If none of the above show a job, you can use the FE's internal diagnostic tool to see the exact state: 1. Find the Database ID: 
SHOW PROC '/dbs';
2. Find the Table ID: 
SHOW PROC '/dbs/<db_id>';
3. Look for the 
State
column in the output for your table. This will confirm if it is stuck in 
SCHEMA_CHANGE
, 
ROLLUP
, etc. Why are you seeing 404 errors in the first place? The 404 error 
The specified key does not exist
on S3 usually means: * The Vacuum process deleted a file that was still needed (possibly due to FE/BE clock skew). * A Compaction task failed to upload a segment but the metadata was committed anyway (rare). * An S3 Lifecycle Policy is deleting files behind StarRocks' back. Recommendation: Once you get the table back to 
NORMAL
state and run 
ADMIN REPAIR TABLE
, immediately check your server clocks (
ntpstat
) and increase the 
lake_autovacuum_grace_period_minutes
to 
1440
(24 hours) to prevent the vacuum from being too aggressive while you stabilize the cluster. References • sql-reference/sql-statements/cluster-management/tablet_replica/ADMIN_REPAIR.md • faq/Sql_faq.md • administration/management/resource_management/Replica.md References • faq/Sql_faq.md • sql-reference/sql-statements/cluster-management/tablet_replica/ADMIN_REPAIR.md
d
@Rocky there are no active operations present. I see few errors on 
lake_vacuum_del_file_fails
metric
r
The 
Table state is not NORMAL
error, when combined with 
lake_vacuum_del_file_fails
errors and missing S3 files, suggests that your table's metadata is stuck in an inconsistent transition state (likely a "phantom" schema change or a failed background maintenance task). Since 
SHOW ALTER TABLE COLUMN
and 
SHOW ALTER TABLE ROLLUP
are empty, here is how to unstick the state and address the vacuum errors: 1. Check for Hidden Operations Even if the main "ALTER" commands are empty, shared-data tables use a few other mechanisms that lock table state: * Tablet Splitting: Run 
SHOW ALTER TABLE OPTIMIZE;
. If there is a job here for 
events_u
, it will block repair. Cancel it if it is stuck: 
CANCEL ALTER TABLE OPTIMIZE FROM events_u;
. * Tablet-Level State: Run 
SHOW TABLET FROM events_u WHERE State != "NORMAL";
. This will tell you if specific tablets are stuck in 
ALTER
or 
CREATING
state even if the partition reports as normal. 2. Force-Reset the Table State If no jobs are visible but the state remains non-normal, the FE's internal metadata flag is likely stuck. You can often "poke" the FE to reset this state by running a cancel command even if no job is listed:
Copy code
sql
CANCEL ALTER TABLE COLUMN FROM events_u;
If that doesn't work, try a "no-op" modification to force a state transition:
Copy code
sql
-- This doesn't change data but forces the FE to re-evaluate table metadata
ALTER TABLE events_u SET ("comment" = "manual repair trigger");
3. Understanding 
lake_vacuum_del_file_fails
 The 
lake_vacuum_del_file_fails
metric increases when the StarRocks vacuum process tries to delete a file from S3 but fails. In your case, this is likely a consequence of the missing files: 1. The Trigger: A file went missing (due to S3 policy, accidental deletion, or a previous vacuum bug). 2. The Error: The vacuum process now tries to clean up metadata or related segments for that version. Because the file is already gone, S3 returns a 
404 Not Found
to the vacuum worker, which triggers the 
del_file_fails
metric. 3. The Result: The vacuum task may fail to complete, which can keep a metadata lock on the table, preventing it from returning to 
NORMAL
. 4. How to proceed Once you get the table back to 
NORMAL
state: 1. Immediately run the repair with "loose" consistency:
Copy code
sql
    ADMIN REPAIR TABLE events_u
    PROPERTIES (
        "enforce_consistent_version" = "false",
        "allow_empty_tablet_recovery" = "true"
    );
2. Increase Vacuum Grace Period: To prevent more files from disappearing while you troubleshoot, increase the retention in 
fe.conf
(or via 
ADMIN SET FRONTEND CONFIG
if allowed):
Copy code
sql
    -- Set to 24 hours (1440 minutes) to give metadata time to stabilize
    ADMIN SET FRONTEND CONFIG ("lake_autovacuum_grace_period_minutes" = "1440");
3. Check FE Logs: If the table state stays "not NORMAL," search the 
fe.log
for the string 
events_u
. Look for errors related to 
LakeTable
, 
Vacuum
, or 
Alter
. This will reveal the exact internal task (e.g., 
RENAME
, 
SWAP
, or 
DROP
) that is hanging. Is the tablet lost? No, but by rolling back (using 
ADMIN REPAIR
), you are choosing to lose the data that was in those missing S3 files to save the rest of the table. References • sql-reference/sql-statements/cluster-management/tablet_replica/ADMIN_REPAIR.md • faq/Sql_faq.md • administration/management/monitoring/metrics.md References • faq/Sql_faq.md • faq/Others.md
d
@Rocky will restarting FE/CN help?
r
Restarting the FE or CN might help, but in StarRocks (especially in shared-data mode), it is rarely a complete fix for a "Table state not NORMAL" error. Here is the breakdown of what each restart will do and what you should try first. 1. Will restarting the FE help? Unlikely. The table state (
NORMAL
, 
SCHEMA_CHANGE
, 
ROLLUP
) is part of the FE's metadata, which is persisted in the edit logs (BDBJE). * If the FE thinks the table is in 
SCHEMA_CHANGE
state, it will reload that same state from the logs when it starts back up. * When it does help: If there is a "phantom" lock in the FE leader's memory that hasn't been written to the logs (rare), a restart or a leader switch (
ALTER SYSTEM RESTART FE
) might clear it. 2. Will restarting the CN help? Only for the vacuum/compaction side. Restarting the CNs will clear any stuck physical tasks (like a compaction or vacuum process that is hanging and holding a transaction open). * It will not change the table state from "not NORMAL" to "NORMAL." * However, if a CN is stuck trying to "publish" a version and failing (causing the vacuum errors you see), restarting it might allow the FE to eventually time out the transaction and revert the table to 
NORMAL
. ────────── What to do instead (Shared-Data specific) Before restarting everything, try these steps in order to force the metadata to synchronize: A. The "Shared-Data Clear" Sequence Since you see 
lake_vacuum_del_file_fails
, a background task might be blocking the table. Try this to clear the path for a repair: 1. Stop new compactions:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("lake_compaction_max_tasks" = "0");
2. Restart the CN nodes. This clears any stale "Write" or "Commit" tasks that the FE thinks are still running on the CNs. 3. Wait 2 minutes, then re-enable compactions:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("lake_compaction_max_tasks" = "-1");
B. Check for "Optimize" Jobs Shared-data clusters have a specific job type for tablet balancing and splitting that is different from standard `ALTER`:
Copy code
sql
SHOW ALTER TABLE OPTIMIZE;
If you see a job here for 
events_u
, run:
Copy code
sql
CANCEL ALTER TABLE OPTIMIZE FROM events_u;
C. Identify "Version Gaps" (The Root Cause) If the table is stuck even after the above, it's often because of a version discontinuity (e.g., 
VisibleVersion
is 100, but the 
NextVersion
is 102, and version 101 is missing). 1. Run: 
SHOW PARTITIONS FROM events_u;
2. Compare the 
VisibleVersion
and 
NextVersion
columns. 3. If there is a gap (more than +1 difference), the table will stay in a non-normal state. 4. The Fix: You must run the 
ADMIN REPAIR
with a loose consistency check to force the FE to ignore the missing version:
Copy code
sql
    ADMIN REPAIR TABLE events_u
    PROPERTIES ("enforce_consistent_version" = "false");
Summary Recommendation 1. Try to cancel a non-existent job anyway: 
CANCEL ALTER TABLE COLUMN FROM events_u;
(This sometimes triggers a state refresh). 2. Check 
SHOW ALTER TABLE OPTIMIZE;
 and cancel anything there. 3. If the error persists, restart the FE Leader. If that still doesn't fix it, the state is persisted in the logs and you likely have a "Version Gap" that requires the 
ADMIN REPAIR ... PROPERTIES ("enforce_consistent_version" = "false")
command once the state is cleared.` References • faq/shared_data_faq.md • sql-reference/sql-statements/cluster-management/tablet_replica/ADMIN_REPAIR.md • administration/Meta_recovery.md • sql-reference/sql-statements/cluster-management/tablet_replica/CANCEL_ALTER_TABLE.md
d
@Rocky trying to run ADMIN REPAIR TABLE events_u on partition with dry_run=true - and it takes long time, is that expected?
r
Yes, it is expected for 
ADMIN REPAIR TABLE ... PROPERTIES ("dry_run" = "true")
to take a significant amount of time, especially if your table has many tablets or missing files. In StarRocks (v3.5+), the 
dry_run
property is specifically designed to let you preview a repair plan without actually rolling back any data. Here is why it is slow and what it is doing: 1. What is happening behind the scenes? When you run a repair (or dry run) on a shared-data table, the Frontend (FE) coordinates with the Compute Nodes (CNs) to perform an exhaustive search of your historical metadata on S3/object storage. * Version Probing: The CNs must check every tablet's metadata files version-by-version, starting from the current version and moving backwards. * 404 Checks: Since you are seeing 
lake_vacuum_del_file_fails
, it means files are missing. The repair task has to physically "probe" the object storage for every single tablet to find the exact point where the data is still intact. * Scale: If your partition has 64 buckets and you are searching back 100 versions, the system may be making thousands of metadata checks against S3. Even with "Deterministic Path Derivation" (which StarRocks uses to speed this up), object storage latency adds up. 2. How to monitor the progress Because this is a synchronous SQL command, your client might look like it is hanging. * Check FE Logs: Search 
fe.log
for the phrase 
repair table
or the table name. You should see logs indicating that the FE is probing versions. * Check CN Metrics: Look for increased network activity or S3/Object Storage request rates on your CN nodes. * Timeout: The default timeout for this operation is 14,400 seconds (4 hours). It is intended to be a long-running heavy-duty task. 3. What the output will tell you When the command finally finishes, it won't change your data. Instead, it will return a table with these columns: * RepairStatus: * `NORMAL`: No repair needed. * `RECOVERABLE`: Metadata is missing, but a valid historical version was found. * `UNRECOVERABLE`: No valid history could be found (data may be lost). * TabletRecoverInfo: A JSON list showing exactly which versions each tablet will roll back to if you run it for real. Recommendation * Wait for it: If the table is large, it's normal for this to take 10–30 minutes or more. * Run per-partition: You are already doing this (
PARTITION (...)
), which is the best way to speed it up. * Next Step: Once the dry run gives you a 
RECOVERABLE
status, run the command again without the 
dry_run
property to actually perform the fix.
Copy code
sql
-- Example of the actual fix after dry_run succeeds
ADMIN REPAIR TABLE events_u PARTITION (your_partition) 
PROPERTIES (
    "enforce_consistent_version" = "false",
    "allow_empty_tablet_recovery" = "true"
);
Note: If the dry run fails with a timeout or error, it usually means the S3 latency is too high or the number of tablets is overwhelming the probe. You may need to increase the 
tablet_create_worker_count
or check your object storage health. storage-related RPC timeouts on the CNs. References • sql-reference/sql-statements/cluster-management/tablet_replica/ADMIN_REPAIR.md • administration/management/resource_management/Replica.md • administration/management/compaction.md
2 Views
Open in Slack
PreviousNext
Powered by