http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I am using starrocks oss 3.4.10. I ...
# questions-and-troubleshooting
a
@Rocky I am using starrocks oss 3.4.10. I have created a table with 
partition_live_number
configuration it it does not seem to respect that
my table looks like this
Copy code
CREATE TABLE IF NOT EXISTS my_table (
    customer_id VARCHAR(255) NOT NULL',
    start_time_in_millis BIGINT NOT NULL COMMENT 'start time in milliseconds - time partition key',
    ... other columns
)

DUPLICATE KEY (customer_id, start_time_in_millis)
PARTITION BY date_trunc('day', from_unixtime(start_time_millis/1000))
DISTRIBUTED BY HASH(customer_id, start_time_in_millis)
PROPERTIES (
    "replication_num" = "1",
    "compression" = "LZ4_FRAME",
    "partition_live_number" = "9",
    "enable_persistent_index" = "true",
    "binlog_enable" = "false"
);
@Rocky ^
r
Hello! It looks like you might have sent a message by accident. How can I help you with StarRocks today?
a
can you check this thread
r
In StarRocks 3.4.10, the behavior of 
partition_live_number
depends strictly on how your table is partitioned. This property is specifically designed for Expression Partitioning (the recommended strategy in v3.4+) and works differently than the "Legacy" Dynamic Partitioning properties. Here are the most likely reasons why it might not seem to be working: 1. Expression Partitioning vs. Range Partitioning 
partition_live_number
is only effective for tables created with the Expression Partitioning syntax. * Correct usage (Expression):
Copy code
sql
    PARTITION BY date_trunc('day', event_time)
    PROPERTIES ("partition_live_number" = "30")
* Legacy Range Partitioning: If you used 
PARTITION BY RANGE(event_time)
, the property 
partition_live_number
is typically ignored. For Range partitioning, you must use the legacy dynamic partition properties instead:
Copy code
sql
    PROPERTIES (
        "dynamic_partition.enable" = "true",
        "dynamic_partition.time_unit" = "DAY",
        "dynamic_partition.start" = "-30", -- This deletes partitions older than 30 days
        "dynamic_partition.end" = "3"
    )
2. The "Benchmark" Date and Future Data The 
partition_live_number
calculation uses the current date as a benchmark. * It retains the 
N
most recent partitions based on chronological order. * Future Data Pitfall: If you have loaded data for future dates (e.g., today is July 20th but you have a partition for July 30th), those future partitions are counted and retained. However, the deletion logic for old partitions is still "current date minus live number." * If your 
partition_live_number
is set to 
5
and today is July 20, it will delete partitions older than July 16 (July 20 - 5 days + 1). 3. Scheduling Interval Partition management is not instantaneous. It is handled by a background task in the FE. * The default check interval is 10 minutes (600 seconds). * You can check the current interval or trigger it faster by adjusting the FE dynamic parameter:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("dynamic_partition_check_interval_seconds" = "60");
4. Added via 
ALTER TABLE
 In some earlier versions, adding 
partition_live_number
via 
ALTER TABLE
did not trigger the cleanup immediately or was not correctly registered. While largely fixed by v3.4.10, if you added this property after the table was already created, try checking if the property is correctly shown in:
Copy code
sql
SHOW CREATE TABLE <table_name>;
5. Check FE Logs If the partitions are still not being deleted after 10–20 minutes, check your 
fe.log
for any errors related to 
DynamicPartitionScheduler
. This thread handles both legacy dynamic partitioning and the newer Expression Partitioning TTL. Recommendation: Confirm your table's partitioning style with 
SHOW CREATE TABLE
. If it uses 
PARTITION BY RANGE
, switch to the 
dynamic_partition.start
property. If it uses 
PARTITION BY date_trunc(...)
, ensure no "future" partitions are confusing the count and wait at least 10 minutes for the scheduler to run. References • faq/operation_maintenance_faq.md • table_design/data_distribution/expression_partitioning.md • table_design/data_distribution/Data_distribution.md • table_design/data_distribution/dynamic_partitioning.md
a
@Rocky my table was created like this below
Copy code
CREATE TABLE IF NOT EXISTS my_table (
    customer_id VARCHAR(255) NOT NULL',
    start_time_in_millis BIGINT NOT NULL COMMENT 'start time in milliseconds - time partition key',
    ... other columns
)

DUPLICATE KEY (customer_id, start_time_in_millis)
PARTITION BY date_trunc('day', from_unixtime(start_time_millis/1000))
DISTRIBUTED BY HASH(customer_id, start_time_in_millis)
PROPERTIES (
    "replication_num" = "1",
    "compression" = "LZ4_FRAME",
    "partition_live_number" = "9",
    "enable_persistent_index" = "true",
    "binlog_enable" = "false"
);
r
In StarRocks v3.4.x, 
partition_live_number
is supported for Expression Partitioning, but its behavior with complex expressions (like those involving 
BIGINT
columns and divisions) can be tricky. Based on your table definition, here are the most common reasons why it might not be working as expected: 1. Typo in the Column Name In your provided SQL, the column is defined as 
start_time_in_millis
, but the partition expression uses 
start_time_millis
. * Check: If you manually created this table and there was a typo, the table creation should have failed. However, if this was a transcription error in your message, please verify that the column used in 
PARTITION BY
exactly matches the column in the schema. 2. Timezone Misalignment 
partition_live_number
uses the FE's system time as the benchmark for "current date." * If your 
BIGINT
timestamps are in UTC but your StarRocks FE is running in a different timezone (e.g., 
Asia/Shanghai
), or vice versa, the "live" window might not align with your data. * Verify: Check the FE timezone setting (
SHOW VARIABLES LIKE 'time_zone';
) and ensure it matches the logic of your Unix timestamps. 3. Support for Complex Expressions in v3.4.10 While StarRocks 3.4 improved expression partitioning to support 
from_unixtime
on 
BIGINT
columns, the TTL (Retention) logic historically expected the partition column to be a physical 
DATE
or 
DATETIME
type for the 
partition_live_number
property to calculate the age correctly. If the background scheduler cannot reliably derive a date value from your custom expression to compare against the "current date," it may fail to identify which partitions are "expired." Recommended Workaround (Generated Columns): The most reliable way to ensure TTL works for 
BIGINT
timestamps in v3.4 is to use a Generated Column. This stores the calculated date explicitly, which the TTL scheduler can easily process:
Copy code
sql
CREATE TABLE my_table (
    customer_id VARCHAR(255) NOT NULL,
    start_time_in_millis BIGINT NOT NULL,
    -- Create a generated column for the partition key
    event_date DATE AS to_date(from_unixtime(start_time_in_millis/1000)) 
)
DUPLICATE KEY (customer_id, start_time_in_millis)
PARTITION BY date_trunc('day', event_date) -- Use the generated column here
DISTRIBUTED BY HASH(customer_id)
PROPERTIES (
    "partition_live_number" = "9"
);
4. Scheduling Delay Partition cleanup is handled by a background thread (
DynamicPartitionScheduler
). * It runs every 10 minutes by default. * Check if the partitions are still there after 15–20 minutes. * You can force a check by temporarily lowering the interval:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("dynamic_partition_check_interval_seconds" = "60");
5. Future Data If you have data with timestamps in the future (e.g., test data from next year), StarRocks counts those future partitions toward the 
partition_live_number
. For example, if you have 10 partitions in the future and your live number is 9, it might delete all your current and historical partitions because the "9 most recent" are all in the future. Troubleshooting Steps: 1. Check FE Logs: Search 
fe.log
for keywords like 
DynamicPartitionScheduler
or 
drop partition
. If there is a calculation error with your expression, it will show up there. 2. Verify Partition Metadata: Run 
SHOW PARTITIONS FROM my_table;
. Look at the 
PartitionKey
column. If the values look like 
NULL
or unexpected strings, the TTL logic won't be able to sort them. 3. Check `information_schema`: Verify the property is actually registered:
Copy code
sql
    SELECT * FROM information_schema.tables_config WHERE table_name = 'my_table';
If you are certain the configuration is correct and the logs show no errors, you may be hitting a limitation of the early 3.4 implementation for nested expressions in TTL. Upgrading to v3.4.11+ or v3.5 (which introduced the more robust 
partition_retention_condition
) is often the recommended path. References • sql-reference/sql-functions/date-time-functions/from_unixtime.md • sql-reference/sql-functions/date-time-functions/unix_timestamp.md • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-functions/date-time-functions/to_datetime.md
a
@Rocky few corrections, the ddl is correct, here is what I have
Copy code
CREATE TABLE IF NOT EXISTS my_table (
    customer_id VARCHAR(255) NOT NULL',
    start_time_in_millis BIGINT NOT NULL COMMENT 'start time in milliseconds - time partition key',
    ... other columns
)

DUPLICATE KEY (customer_id, start_time_in_millis)
PARTITION BY date_trunc('day', from_unixtime(start_time_in_millis/1000))
DISTRIBUTED BY HASH(customer_id, start_time_in_millis)
PROPERTIES (
    "replication_num" = "1",
    "compression" = "LZ4_FRAME",
    "partition_live_number" = "9",
    "enable_persistent_index" = "true",
    "binlog_enable" = "false"
);
Here is how my partitions look like mysql> SHOW PARTITIONS FROM table; +-------------+---------------+----------------+---------------------+--------------------+--------+--------------+------------------+-----------------+---------+----------------+---------------+---------------------+--------------------------+----------+-------------+------------+----------+-------------+--------------------+----------------+ | PartitionId | PartitionName | VisibleVersion | VisibleVersionTime | VisibleVersionHash | State | PartitionKey | List | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime | LastConsistencyCheckTime | DataSize | StorageSize | IsInMemory | RowCount | DataVersion | VersionEpoch | VersionTxnType | +-------------+---------------+----------------+---------------------+--------------------+--------+--------------+------------------+-----------------+---------+----------------+---------------+---------------------+--------------------------+----------+-------------+------------+----------+-------------+--------------------+----------------+ | 5000973 | p20260706 | 962 | 2026-07-07 001417 | 0 | NORMAL | event_date | [["2026-07-06"]] | customer_id | 8 | 1 | HDD | 9999-12-31 155959 | NULL | 238.5KB | 238.5KB | false | 5496 | 962 | 431019580534554624 | TXN_NORMAL |
r
Based on your DDL and the 
SHOW PARTITIONS
output, there are a few reasons why 
partition_live_number
might not be behaving as expected in StarRocks v3.4.10. 1. Discrepancy in Schema vs. Partition Key In your 
SHOW PARTITIONS
output, the PartitionKey column shows 
event_date
, but in your DDL, the partitioning is done via an expression on 
start_time_in_millis
. * If your table actually has a Generated Column named 
event_date
(e.g., 
event_date DATE AS ...
), then 
partition_live_number
is generally very reliable. * However, if you are using the complex expression 
date_trunc('day', from_unixtime(start_time_in_millis/1000))
directly, StarRocks v3.4.10 sometimes struggles to evaluate the TTL benchmark against a calculated expression compared to a physical 
DATE/DATETIME
column. 2. TTL Benchmark Logic For 
partition_live_number
, StarRocks performs the following logic: 1. It determines the "Current Date" (based on FE system time). 2. It identifies the "live" window (Current Date minus $N$ days). 3. It evaluates the partition values. If the internal scheduler cannot consistently map the results of your 
from_unixtime(...)
expression to a date object it can compare against "Today," the cleanup task may skip the table. 3. Check the "Dynamic Partition" Scheduler Even though you are using Expression Partitioning, the cleanup is handled by the 
DynamicPartitionScheduler
thread. * Interval: By default, this runs every 10 minutes. If you just created the table or added data, it may not have run yet. * Verification: Run 
ADMIN SHOW FRONTEND CONFIG LIKE 'dynamic_partition_check_interval_seconds';
. * Logs: Check your 
fe.log
for any warnings or errors containing 
DynamicPartitionScheduler
. If the expression evaluation is failing during the TTL check, it will be logged there. 4. Recommendation: Use a Generated Column For the most robust TTL behavior when dealing with 
BIGINT
timestamps in v3.4, it is highly recommended to use a Generated Column. This stores the date explicitly in the metadata, making it trivial for the TTL logic to identify and drop old partitions. Example Rewrite:
Copy code
sql
CREATE TABLE my_table (
    customer_id VARCHAR(255) NOT NULL,
    start_time_in_millis BIGINT NOT NULL,
    -- Create a generated column for the date
    event_date DATE AS to_date(from_unixtime(start_time_in_millis/1000)),
    ...
)
DUPLICATE KEY (customer_id, start_time_in_millis)
-- Partition by the generated column
PARTITION BY date_trunc('day', event_date)
DISTRIBUTED BY HASH(customer_id, start_time_in_millis)
PROPERTIES (
    "partition_live_number" = "9"
);
5. Troubleshooting Steps * Verify Properties: Run 
SHOW CREATE TABLE my_table;
and ensure 
"partition_live_number" = "9"
is definitely present in the output. * Check FE Timezone: Ensure your FE timezone (
SHOW VARIABLES LIKE 'time_zone';
) matches your data expectations. If the FE thinks it is still July 10th because of a timezone offset, it won't delete July 6th. * Future Data: Ensure you don't have any partitions with dates in the future (e.g., August 2026). If you have 9 partitions in the future, the "9 most recent" will be those, and your current data will be treated as "old" (and potentially deleted or kept depending on the benchmark). If you are already using a generated column and it's still not working, I recommend checking the 
fe.log
specifically for any 
NullPointerException
or 
AnalysisException
during the dynamic partition check interval. References • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-functions/date-time-functions/from_unixtime.md • administration/management/FE_configuration.md
a
@Rocky getting this log 
[DynamicPartitionUtil.isDynamicPartitionTable():436] unable to schedule olap table table-name-5001157 because dynamic partition is not enabled, partition type: LIST, partition column size: 1
r
That log message is the "smoking gun." It reveals that the StarRocks background scheduler (DynamicPartitionScheduler) is skipping your table because it believes dynamic partitioning is disabled for it, even though you have 
partition_live_number
set. Why this is happening In StarRocks v3.4.x, Expression Partitioning (your 
PARTITION BY date_trunc(...)
syntax) is internally managed by the same scheduler as legacy dynamic partitioning. The log 
partition type: LIST
confirms that StarRocks has implemented your expression as an Automatic List Partition, which is the standard behavior for expression partitioning in 3.4. However, the scheduler has a check that says: _"If this table does not have 
dynamic_partition.enable
set to 
true
, ignore it."_ While the documentation states that you don't need to configure legacy dynamic partition properties for expression partitioning, in v3.4.10, the scheduler still requires the "master switch" to be on to process the TTL (
partition_live_number
) logic. The Fix You need to explicitly enable the dynamic partition property for this table. You can do this without recreating the table:
Copy code
sql
ALTER TABLE internal_db.metric_view 
SET ("dynamic_partition.enable" = "true");
Important Observations 1. Discrepancy in Column Names: In your 
SHOW PARTITIONS
output, the 
PartitionKey
column is listed as 
event_date
. However, your DDL uses 
start_time_in_millis
. * If you have a Generated Column named 
event_date
(e.g., 
event_date DATE AS ...
), make sure you are targeting the right table and that the property is set on that specific table. * If the table 
internal_db.metric_view
is actually a View or a different table than the one in the DDL, the 
partition_live_number
property must be set on the underlying Base Table. 2. Wait for the Scheduler: After running the 
ALTER
command, the scheduler runs every 10 minutes by default. You can check the logs again after 10 minutes to see if it successfully identifies and drops the old partitions. 3. Future Partitions: If your 
SHOW PARTITIONS
output shows a partition for 
2026-07-06
and today is (hypothetically) 
2026-07-07
, and your live number is 
9
, that partition is safely within the window. If you have any partitions with dates much further in the future (e.g., 2027), they will count against your "9 most recent" and may cause older current data to be deleted. Summary Checklist * [ ] Run 
ALTER TABLE ... SET ("dynamic_partition.enable" = "true");
* [ ] Verify the property is set: 
SHOW CREATE TABLE internal_db.metric_view;
* [ ] Check 
fe.log
after 10 minutes. The error message 
unable to schedule olap table
should disappear. References • table_design/data_distribution/expression_partitioning.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/data_distribution/dynamic_partitioning.md _partitioning) References • table_design/data_distribution/list_partitioning.md • table_design/data_distribution/expression_partitioning.md
The log message you provided is the "smoking gun." It confirms that the background scheduler (DynamicPartitionScheduler) is skipping your table because the master switch for dynamic partitioning is not enabled. In StarRocks v3.4.x, even though Expression Partitioning (Automatic Partitioning) is designed to be simpler than legacy dynamic partitioning, the background task that processes 
partition_live_number
(TTL) still checks the 
"dynamic_partition.enable"
property. If it is not set to 
"true"
, the scheduler ignores the table. 1. The Fix: Enable Dynamic Partitioning Run the following command to turn on the scheduler for this table:
Copy code
sql
ALTER TABLE internal_db.metric_view 
SET ("dynamic_partition.enable" = "true");
2. Why it isn't working yet * The Scheduler: The background thread runs every 10 minutes (defined by 
dynamic_partition_check_interval_seconds
). Once you enable the property, it should pick up the July 6th partition (which is older than your 9-day window relative to July 20th) and drop it in the next cycle. * Partition Key Discrepancy: In your 
SHOW PARTITIONS
output, the 
PartitionKey
is listed as 
event_date
, but your DDL partitions by a raw expression on 
start_time_in_millis
. * This usually means your actual table uses a Generated Column (e.g., 
event_date DATE AS ...
) or the expression was internally mapped to that name. * Regardless, the TTL logic works based on the values in that partition key. Since the value is 
2026-07-06
and today is 
2026-07-20
, it will be deleted once the scheduler is enabled. 3. Check for Future Data The 
partition_live_number
logic calculates the "live" window as: 
[Current Date - (partition_live_number - 1), Current Date + Future Partitions]
. If you have any data with timestamps in the future (e.g., August 2026), StarRocks will treat those as the most recent partitions. If you have 9 or more partitions in the future, your "current" July data might be dropped immediately because it is no longer among the "9 most recent." Based on your output, you have at least one partition from July 6th, so just keep an eye on your future-dated data. Summary Checklist 1. Enable the property: 
ALTER TABLE ... SET ("dynamic_partition.enable" = "true");
2. Wait 10 minutes: Check the 
fe.log
again. The "unable to schedule" warning should disappear. 3. Verify: Run 
SHOW PARTITIONS FROM table;
to confirm 
p20260706
is gone. If the partitions still persist after enabling the property, check your FE's system time (
select now();
) to ensure the cluster's clock is correct. References • table_design/data_distribution/expression_partitioning.md
a
@Rocky I have not used 
event_date
anywhere
r
The log message you provided confirms that the StarRocks background scheduler (DynamicPartitionScheduler) is skipping your table. Even though you are using Expression Partitioning (which is "Automatic"), in StarRocks v3.4.10, the background task that manages the TTL (
partition_live_number
) still checks the 
dynamic_partition.enable
flag. If it is not set to 
true
, the scheduler ignores the table entirely. 1. The Fix You need to explicitly enable the dynamic partition switch. This will allow the scheduler to process the 
partition_live_number
property and clean up the July 6th partition.
Copy code
sql
ALTER TABLE my_table SET ("dynamic_partition.enable" = "true");
Note: Ensure you run this on the base table (the one created with the DDL), even if you are viewing it via a view. 2. Why 
event_date
appears in 
SHOW PARTITIONS
 If you did not explicitly define a column named 
event_date
, here is why it is showing up as your PartitionKey: When you use a complex expression like 
date_trunc('day', from_unixtime(...))
as a partition key, StarRocks cannot use the original 
BIGINT
column directly for partitioning logic. Instead, it creates a hidden shadow column (often called a "slot") to store the result of that expression. In some versions or when using certain integration tools (like dbt, SQLMesh, or internal StarRocks optimizations), this shadow column is automatically given a name like 
event_date
or 
event_day
to represent the "date" version of your timestamp. The 
SHOW PARTITIONS
output reflects the metadata of this hidden column. 3. Verification & Timing * Scheduling Interval: The scheduler runs every 10 minutes by default (controlled by 
dynamic_partition_check_interval_seconds
). After running the 
ALTER
command, you may need to wait up to 10 minutes for the cleanup to trigger. * Check Logs: After the next cycle, the 
unable to schedule
log should disappear, and you should see a log entry similar to: 
Succeed to delete expired partition ... p20260706
. * TTL Calculation: With 
partition_live_number = 9
and today being July 20, your "live" window is approximately July 12 to July 20. Since July 6 is outside this window, it will be dropped as soon as the scheduler is enabled. Summary The DDL logic is correct, but the "master switch" for the background cleanup task is off. Enabling 
dynamic_partition.enable
will resolve the "unable to schedule" error and allow the TTL to function. References • table_design/data_distribution/expression_partitioning.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/data_distribution/dynamic_partitioning.md References • table_design/data_distribution/expression_partitioning.md
a
@Rocky getting this error
Copy code
mysql> alter table my_table SET ("dynamic_partition.enable" = "true");
ERROR 1064 (HY000): Table <my_table> is not a dynamic partition table.
r
The error 
Table <my_table> is not a dynamic partition table
occurs because you are using Expression Partitioning (the newer, "automatic" style), but the command 
ALTER TABLE ... SET ("dynamic_partition.enable" = "true")
is specifically reserved for Legacy Range Partitioning. Here is the explanation for your current situation and how to resolve it: 1. Why you see the error and the log * The Error: In StarRocks, "Dynamic Partitioning" and "Expression Partitioning" are handled by two different internal structures. When you try to set the legacy 
dynamic_partition.enable
property, the system checks if the table was created as a legacy dynamic table. Since it wasn't, it blocks the command. * The Log: The log message 
unable to schedule olap table ... because dynamic partition is not enabled
indicates that the background Scheduler is looking at your table (correctly identifying it as a 
LIST
type used for expression partitioning) but sees that the "master switch" for scheduling is off. * The 
event_date
mystery: When you use 
PARTITION BY date_trunc(...)
, StarRocks creates a hidden shadow column to store the truncated date values. It automatically names this column 
event_date
(or 
__generated_partition_column_0
) for internal tracking. This is normal and confirms your expression partitioning is active. 2. How to fix the TTL (Cleanup) Since you already have 
"partition_live_number" = "9"
in your DDL, the scheduler should be picking it up. If it isn't, and the log says it's disabled, check the Global FE Configuration first: Run this command:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE 'dynamic_partition_enable';
If the value is 
false
, the scheduler is turned off for the entire cluster. Enable it globally:
Copy code
sql
ADMIN SET FRONTEND CONFIG ("dynamic_partition_enable" = "true");
3. If the Global Config is already 
true
 If the global setting is on, but your table is still being skipped, there may be a mismatch in how the properties were initialized. Try "re-applying" the TTL property to force the scheduler to recognize it:
Copy code
sql
ALTER TABLE my_table SET ("partition_live_number" = "9");
4. Important: Verify the Table ID The log message specifically mentioned 
table-name-5001157
. It is possible this is not your 
my_table
. To confirm which table the log is complaining about, run:
Copy code
sql
-- Replace 5001157 with the ID from your log
SELECT TABLE_NAME, TABLE_SCHEMA 
FROM information_schema.tables 
WHERE TABLE_ID = 5001157;
_(Note: If your version doesn't support TABLE_ID in information_schema, use 
SHOW PROC '/db';
to navigate into your database and list tables/IDs.)_ Summary 1. Do not use 
dynamic_partition.enable
on expression partitioning tables; use 
partition_live_number
. 2. Check the Global Switch: Ensure 
dynamic_partition_enable
is 
true
in FE config. 3. Ignore `event_date`: It is a system-generated internal name for your 
date_trunc
result. 4. Confirm the Table: Make sure the log isn't referring to a different (perhaps old or temporary) table with ID 
5001157
. References • table_design/data_distribution/expression_partitioning.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/data_distribution/dynamic_partitioning.md References • table_design/data_distribution/expression_partitioning.md • administration/management/FE_configuration.md
2 Views
Open in Slack
PreviousNext
Powered by