http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV>, what causes this error when attemp...
# questions-and-troubleshooting
r
@Rocky, what causes this error when attempting to analyze a table and update a histogram?
Copy code
*************************** 1. row ***************************
   Table: <mydb>.<mytable>
      Op: histogram
Msg_type: error
Msg_text: Getting syntax error from line 1, column 316 to line 1, column 336. Detail message: invalid percent which should in (0, 100).
r
This error occurs when the internal sampling logic of the 
ANALYZE
command generates a SQL query with a 
SAMPLE (PERCENT <n>)
clause where the calculated percentage 
<n>
is outside the allowed range of (0, 100) (exclusive). Root Causes The most common reasons for this error during a histogram update are: 1. 
histogram_sample_ratio
set to 1.0 (or 100%): StarRocks' internal sampling parser requires the sampling percentage to be strictly greater than 0 and strictly less than 100. If your 
histogram_sample_ratio
property is set to 
1.0
(which represents 100%), the generated internal query uses 
SAMPLE (PERCENT 100)
, which triggers the syntax error. 2. Using a whole number for 
histogram_sample_ratio
: The 
histogram_sample_ratio
property expects a decimal ratio (0.0 to 1.0). If you provide a value like 
50
(intending 50%), the system calculates a percentage of 5000%, which is invalid. 3. Default value conflicts: In some versions or specific table sizes, the larger of 
statistic_sample_collect_rows
and 
table_rows * histogram_sample_ratio
might accidentally resolve to the total row count of the table, causing the internal generator to attempt a 100% sample. How to Fix To resolve this, you should explicitly set the 
histogram_sample_ratio
to a decimal value strictly between 0 and 1 (e.g., 
0.1
to 
0.99
). If you are manually running the analyze command, use the following syntax:
Copy code
sql
ANALYZE TABLE <mytable> UPDATE HISTOGRAM ON <mycol> 
PROPERTIES (
    "histogram_sample_ratio" = "0.5"  -- Use a value less than 1.0
);
Recommended troubleshooting steps: * Check existing properties: Run 
SHOW ANALYZE STATUS
for that table and check the 
Properties
column to see what values were actually used. * Avoid 100% Sampling: If you want to sample as much data as possible, use a ratio like 
0.99
instead of 
1.0
. * Check 
fe.conf
: Verify if 
histogram_sample_ratio
has been modified globally in your FE configuration to an invalid value. For more details, see the Gather statistics for CBO documentation. References • sql-reference/sql-functions/Window_function.md • loading/Loading_data_template.md • using_starrocks/caching/query_cache.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • sql-reference/sql-functions/table-functions/generate_series.md
r
@Rocky these are my current frontend configs for histograms:
Copy code
mysql> ADMIN SHOW FRONTEND CONFIG LIKE '%histogram%';
+-------------------------------------------+------------+----------+--------+-----------+------------------------------------------------------+
| Key                                       | AliasNames | Value    | Type   | IsMutable | Comment                                              |
+-------------------------------------------+------------+----------+--------+-----------+------------------------------------------------------+
| histogram_buckets_size                    | []         | 64       | long   | true      |                                                      |
| histogram_collect_bucket_ndv_mode         | []         | none     | String | true      |                                                      |
| histogram_max_sample_row_count            | []         | 10000000 | long   | true      |                                                      |
| histogram_mcv_size                        | []         | 100      | long   | true      |                                                      |
| histogram_sample_ratio                    | []         | 0.1      | double | true      |                                                      |
| statistic_auto_collect_histogram_interval | []         | 3600     | long   | true      | The interval of auto collecting histogram statistics |
+-------------------------------------------+------------+----------+--------+-----------+------------------------------------------------------+
r
The error occurs because the internal query StarRocks generates to collect histogram data uses a 
SAMPLE (PERCENT <n>)
clause where the calculated percentage 
<n>
is greater than or equal to 100. The StarRocks parser requires the sampling percentage to be strictly between 0 and 100 (exclusive). Why this happens with your configuration Even though your 
histogram_sample_ratio
is set to 
0.1
(10%), StarRocks uses an additional parameter, 
statistic_sample_collect_rows
(default: 200,000), to determine the minimum number of rows to sample. The target number of rows to collect is calculated as: *`TargetRows = max(statistic_sample_collect_rows, table_rows * histogram_sample_ratio)`* The percentage in the internal SQL is then: *`Percent = (TargetRows / table_rows) * 100`* If your table has fewer than 200,000 rows (the default threshold), the 
TargetRows
becomes 200,000. When this is divided by your actual (smaller) row count, the resulting percentage exceeds 100%, causing the syntax error in the generated query. How to Fix To resolve this, you need to set the 
statistic_sample_collect_rows
property to a value smaller than your table's total row count for the 
ANALYZE
command. Example Fix: If your table is small (e.g., 10,000 rows), run the analyze command like this:
Copy code
sql
ANALYZE TABLE <mydb>.<mytable> UPDATE HISTOGRAM ON <col_name>
PROPERTIES (
    "statistic_sample_collect_rows" = "1000", -- Set this to something less than your total rows
    "histogram_sample_ratio" = "0.1"
);
Recommendation 1. Check Table Row Count: Verify the actual number of rows in 
<mytable>
. If it is less than 200,000, this is definitely the cause. 2. Adjust Threshold: If you frequently analyze small tables for histograms, you may want to lower the global 
statistic_sample_collect_rows
config, though it is usually better to handle it per-table in the 
PROPERTIES
of the 
ANALYZE
statement. 3. Full Collection: For very small tables, histograms are often not necessary as standard statistics are sufficient. If you must have one, ensure the sampling logic doesn't result in a "sample" larger than the table itself. References • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md References • sql-reference/sql-functions/aggregate-functions/var_samp.md • sql-reference/sql-functions/aggregate-functions/stddev_samp.md • sql-reference/sql-functions/aggregate-functions/percentile_approx.md • sql-reference/sql-functions/Window_function.md • administration/management/FE_configuration.md
r
@Rocky,the table is dramatically larger than 200k:
Copy code
mysql> select count(1) from <table>;
+------------+
| count(1)   |
+------------+
| 2092297885 |
+------------+
r
Even though your table is large, this error is often triggered by a mismatch between the actual row count and the metadata row count stored in the StarRocks Frontend (FE) cache. The Likely Cause: Metadata Under-counting When you run 
ANALYZE ... UPDATE HISTOGRAM
, StarRocks calculates a sampling percentage using this internal formula: 1. Target Rows to Sample: 
max(statistic_sample_collect_rows, table_rows * histogram_sample_ratio)
(capped by 
histogram_max_sample_row_count
). 2. Sample Percentage: 
(Target Rows / table_rows) * 100
. The 
table_rows
used in this calculation comes from the FE's cached metadata, not a fresh count. If the FE thinks the table has very few rows (e.g., 0 or 1), while the 
statistic_sample_collect_rows
is the default 200,000, the calculated percentage will be: _ 
(200,000 / 1) _ 100 = 20,000,000%
This results in an internal query like 
SELECT ... FROM table SAMPLE (PERCENT 20000000)
, which triggers the "invalid percent" syntax error because the percentage must be strictly less than 100. Potential Overflow Bug Your table row count (2,092,297,885) is very close to the maximum value of a 32-bit signed integer (2,147,483,647). If your table grows slightly larger or if an internal intermediate calculation (like 
TargetRows * 100
) is performed using 32-bit integers, an integer overflow can occur, resulting in a negative percentage or a value exceeding 100. Recommended Fixes 1. Update Basic Statistics First: Run a standard 
ANALYZE
(without histograms) to ensure the FE has the correct row count in its metadata:
Copy code
sql
    ANALYZE TABLE <mydb>.<mytable>;
After this completes, check the row count StarRocks perceives:
Copy code
sql
    SHOW TABLE STATUS LIKE '<mytable>'; -- Look at the 'Rows' column
2. Force a Small Sample Row Count: Manually override the sampling threshold in your command to ensure the ratio stays low:
Copy code
sql
    ANALYZE TABLE <mydb>.<mytable> UPDATE HISTOGRAM ON <col>
    PROPERTIES (
        "statistic_sample_collect_rows" = "10000",
        "histogram_sample_ratio" = "0.01"
    );
3. Check for External Table Issues: If this is an External Table (Hive, Iceberg, etc.), StarRocks often cannot retrieve the row count accurately until basic statistics are collected. Use 
ANALYZE TABLE
(basic) before attempting the histogram. If the error persists after updating basic stats, it is likely a bug related to the 2.1B row boundary in your specific StarRocks version. In that case, I recommend checking your StarRocks version and upgrading if you are on an older 2.x or early 3.x release. References • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_TABLE_STATUS.md References • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • using_starrocks/Cost_based_optimizer.md • knowledge/usage/best-practice.md • administration/management/FE_configuration.md • sql-reference/sql-functions/utility-functions/equiwidth_bucket.md
8 Views
Open in Slack
PreviousNext
Powered by