@Rocky getting error for below command. deployed version is 4.0.1. select * from ENTITY_CustomerMaster limit 1; ERROR 1064 (HY000): invalid pos:1489, num_elements0 BE:221990542

The error message

ERROR 1064 (HY000): invalid pos:1489, num_elements:0: BE:221990542

typically indicates an internal data inconsistency or a bug in the storage reader layer of the StarRocks Backend (BE). This specific error means the reader is attempting to access a row/element at position

1489

in a data page that reports having

0

elements. This is commonly seen when querying tables with Complex Types (ARRAY, MAP, STRUCT) or JSON columns, especially if certain optimizations are enabled. Recommended Troubleshooting Steps 1. Disable Complex Type Pruning There is a known issue in some versions where the optimizer's pruning of complex types causes an offset mismatch. Try running your query with this session variable disabled:

sql
SET enable_prune_complex_types = false;
-- Then run your query
SELECT * FROM ENTITY_CustomerMaster LIMIT 1;

2. Disable JSON Flattening (If applicable) If your table contains JSON columns, StarRocks 3.x and 4.x use "JSON flattening" by default to accelerate queries. In some cases, this can lead to

invalid pos

errors during read or compaction. Try:

sql
SET enable_json_flat = false;
-- Then run your query
SELECT * FROM ENTITY_CustomerMaster LIMIT 1;

3. Identify and Check the Tablet The error includes a BE ID (

221990542

). This indicates which node is encountering the error. You should check the BE logs (

<http://be.INFO|be.INFO>

or

be.WARNING

) on that specific machine for a stack trace or more details. To find which tablet is problematic: 1. Run

EXPLAIN VERBOSE SELECT * FROM ENTITY_CustomerMaster LIMIT 1;

to see which tablets are being scanned. 2. You can also run a consistency check on the table:

sql
   ADMIN SHOW REPLICA STATUS FROM ENTITY_CustomerMaster;

Look for any replicas marked as "Bad" or with version inconsistencies. 4. Restart the Backend In some reported cases, this error is transient or related to cached metadata. Restarting the BE node reported in the error message (

221990542

) may temporarily resolve the issue, though it may return if the underlying data on disk is corrupted. Summary If

SET enable_prune_complex_types = false;

fixes the query, it is likely a bug in the query optimizer/reader. Since version 4.0.1 is a very recent release, if the error persists after trying the workarounds above, it is highly recommended to: • Check the BE logs for a full stack trace. • Report the issue on the StarRocks GitHub Issues or the community Slack, providing the table schema and the BE log output. References • sql-reference/data-types/semi_structured/Array.md • faq/Others.md • faq/Deploy_faq.md • sql-reference/sql-functions/string-functions/strpos.md • sql-reference/sql-functions/array-functions/array_position.md

@Rocky mysql> SET enable_prune_complex_types = false; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM ENTITY_CustomerMaster LIMIT 1; ERROR 1064 (HY000): invalid pos:1489, num_elements0 BE:221990542 mysql> SET enable_json_flat = false; ERROR 1193 (HY000): Getting analyzing error. Detail message: Unknown system variable 'enable_json_flat', the most similar variables are {'enable_gin_filter', 'enable_async_profile', 'enable_split_topn_agg'}. mysql> SET enable_json_flat = false; ERROR 1193 (HY000): Getting analyzing error. Detail message: Unknown system variable 'enable_json_flat', the most similar variables are {'enable_gin_filter', 'enable_async_profile', 'enable_split_topn_agg'}. mysql> ADMIN SHOW REPLICA STATUS FROM ENTITY_CustomerMaster; ERROR 1064 (HY000): Getting analyzing error. Detail message: Unsupported operation on lake table com.starrocks.catalog.Database@af69.ENTITY_CustomerMaster. mysql> mysql> SET enable_prune_complex_types = false; Query OK, 0 rows affected (0.00 sec)

The error

invalid pos:1489, num_elements:0

on a Lake Table in StarRocks 4.0.1 indicates a low-level mismatch in the data page reader, where it attempts to read a row position that the page index says doesn't exist. Since this is a Lake Table (Shared Data), the behavior and troubleshooting steps differ from standard tables. 1. Correct way to disable Flat JSON (v4.0+) In StarRocks 4.0,

enable_json_flat

is no longer a session variable; it is a table property. If your table has JSON columns, this feature might be causing the reader mismatch. You can disable it at the table level to test:

sql
ALTER TABLE ENTITY_CustomerMaster SET ("flat_json.enable" = "false");

Note: This will only affect new data or reads moving forward; it won't change existing "flattened" data until a compaction occurs. 2. Bypass the BE Cache Lake tables rely heavily on a local disk cache on the BE. If the cache is corrupted, you will see this error. Try disabling the cache for your current session to see if the query succeeds by reading directly from object storage (S3/HDFS):

sql
SET enable_scan_block_cache = false;
SELECT * FROM ENTITY_CustomerMaster LIMIT 1;

If the query works with the cache disabled, you may need to clear the BE cache directory on the node mentioned in the error (

BE:221990542

). 3. Checking Tablet Status for Lake Tables

ADMIN SHOW REPLICA STATUS

is for Shared-Nothing tables. For Lake tables, use the following instead:

sql
-- Check the health and metadata of tablets
SHOW TABLET FROM ENTITY_CustomerMaster;

-- Check for any background compaction or loading tasks that might be failing
SHOW PROC '/cluster_balance/running_tablets';

4. BE Log Investigation The error message points to a specific backend (

BE:221990542

). Log in to that backend and check the

<http://be.INFO|be.INFO>

or

be.WARNING

logs. Look for the string

invalid pos

or

num_elements:0

. * If you see a stack trace involving

JsonFlat

, it confirms a bug in the JSON flattening logic. * If you see checksum errors, it indicates corrupted data in your object storage or local cache. Summary of next steps: 1. Run

SET enable_scan_block_cache = false;

and retry the query. 2. If it still fails, run

ALTER TABLE ENTITY_CustomerMaster SET ("flat_json.enable" = "false");

. 3. If the issue persists, please check the BE logs on node

221990542

for a more detailed stack trace to determine if this is a known bug in version 4.0.1. References • administration/management/resource_management/Replica.md

SELECT * FROM ENTITY_CustomerMaster LIMIT 1; ERROR 1064 (HY000): invalid pos:1489, num_elements0 BE:221990542 mysql> SELECT * FROM ENTITY_CustomerMaster LIMIT 1; ERROR 1064 (HY000): invalid pos:1489, num_elements0 BE:221990542 mysql> SELECT * FROM ENTITY_CustomerMaster LIMIT 1; ERROR 1064 (HY000): invalid pos:1489, num_elements0 BE:221990542 mysql>

The persistence of the error

invalid pos:1489, num_elements:0

on a Lake Table in version 4.0.1, even after disabling the cache and flat JSON, suggests a specific data-reading bug or an inconsistency in the tablet's metadata/data files on the object store. The fact that Tablet 69129 is twice the size of others (1.2GB vs 618MB) for the same row count is a strong indicator of a compaction issue or redundant data versions that the reader is struggling to parse. Recommended Steps 1. Isolate the Problematic Column Determine if the error is localized to a specific column (likely a JSON, Array, or Map column). Run these one by one:

sql
-- Test a simple primary key or int column
SELECT <simple_column> FROM ENTITY_CustomerMaster LIMIT 1;

-- Test the JSON/Complex columns individually
SELECT <json_column> FROM ENTITY_CustomerMaster LIMIT 1;

If the simple column works but a specific JSON/Complex column fails, the issue is confirmed to be in the semi-structured data reader. 2. Clear the BE Cache Manually While

enable_scan_block_cache = false

bypasses the data cache, Lake tables also use a Metadata Cache. If this is corrupted on BE

221990542

, it can cause offset errors. 1. Log into the server for BE

221990542

. 2. Locate the

storage/

directory (defined by

storage_root_path

in

be.conf

). 3. Under the

starrocks_lake

path, look for the

cache

directory and consider clearing it (or just the sub-directory for this tablet if identifiable), then restart the BE. 3. Check for Compaction Issues The size discrepancy suggests a failed or stuck compaction. You can try to trigger a manual compaction for this tablet, though Lake tables usually handle this automatically via the FE/Warehouse. Check the compaction status:

sql
-- This might show if there are many cumulative versions causing the "invalid pos"
SHOW TABLET 69129;

4. Examine BE Logs (Critical) The most important step now is to get the stack trace from the Backend. 1. Go to BE

221990542

. 2. Open

log/be.INFO

. 3. Search for the error string

invalid pos:1489

. 4. The lines immediately following it will show the function call stack (e.g.,

JsonFlatColumnIterator

,

ArrayColumnIterator

). This will pinpoint exactly which feature is failing. Important Note on Version 4.0.1 StarRocks 4.0.1 was an early release in the 4.0 series. There are several critical bug fixes in 4.0.3 related to: * Flat JSON crashes and reader mismatches (#65921). * Metadata parsing failures when the data cache is in a certain state (#66021). * Shared-data version skipping during publish (#66558). Suggestion: If isolating the column or clearing the BE cache does not work, upgrading to 4.0.3 is highly recommended as it addresses multiple "invalid state" reader bugs introduced in the initial 4.0 release. References • release_notes/release-4.0.md

@Rocky mysql> select deleted_count_of_orders_varchar from ENTITY_CustomerMaster limit 1; ERROR 1064 (HY000): invalid pos:1489, num_elements0 BE:221990542

The persistence of the error

invalid pos:1489, num_elements:0

on your Lake Table in version 4.0.1 confirms a low-level storage reader mismatch. Specifically, the error originates in the

plain_page

decoder (often in

be/src/storage/rowset/plain_page.h

). It occurs when the reader's index points to a row position (

1489

), but the data page metadata reports it contains zero elements. This is a known issue in the initial 4.0 release branch (e.g., Issue #63632). Root Cause Analysis The fact that Tablet 69129 is 1.2GB while others are ~600MB for the same row count is the smoking gun. This indicates: 1. Compaction is stuck: This tablet has many redundant versions or "garbage" rowsets that haven't been merged. 2. Corrupted Rowset: A specific rowset within that 1.2GB of data contains a malformed page header. Even selecting a simple

VARCHAR

column fails because the reader still needs to navigate the row indexes within the segment. Recommended Actions 1. Manually Trigger Compaction Since automatic compaction is likely failing and retrying infinitely on this tablet, try to force a manual compaction for the affected partition. This may either fix the data layout or provide a clearer error message in the BE logs.

sql
-- Find the partition name first using SHOW PARTITIONS FROM ENTITY_CustomerMaster;
ALTER TABLE ENTITY_CustomerMaster COMPACTION;
-- Or for a specific partition:
-- ALTER TABLE ENTITY_CustomerMaster PARTITION (p1) COMPACTION;

Check the progress and look for errors:

sql
SELECT * FROM information_schema.be_cloud_native_compactions WHERE TABLE_NAME = 'ENTITY_CustomerMaster';

2. Upgrade to 4.0.3 or later (Highly Recommended) StarRocks 4.0.1 has several documented bugs in the Lake storage reader and "Flat JSON" logic that cause

invalid pos

and

invalid rssid

errors. * Version 4.0.3 and later include fixes for these reader mismatches (see PR #65921 and #66021). * If you cannot upgrade immediately, you may be forced to re-create the table and reload the data to clear the corrupted tablet. 3. Inspect BE Logs for Stack Trace To confirm if this is exactly the known bug, log into Backend

221990542

and search the

<http://be.INFO|be.INFO>

log for the error. If you see a stack trace similar to the one below, it is a confirmed bug:

text
Internal error: invalid pos be/src/storage/rowset/plain_page.h:165 seek_to_position_in_page(0)
...
be/src/storage/rowset/scalar_column_iterator.cpp:348 _read_data_page

Summary of Situation * Immediate workaround: If the manual compaction fails, the only way to restore the table's health in 4.0.1 is usually to

TRUNCATE

or

DROP

and reload the data. * Permanent fix: Upgrade the cluster to at least 4.0.3 to prevent this reader inconsistency from happening again during background compaction tasks during background compaction. References • best_practices/primarykey_table.md • faq/Deploy_faq.md • loading/minio.md • faq/shared_data_faq.md • administration/management/compaction.md • administration/management/FE_configuration.md

@Rocky what if i drop column

Dropping the column is unlikely to fix the error and will probably fail during the process itself. Here is why: 1. Schema Change requires Reading Data In StarRocks, dropping a column is typically a Direct Schema Change. This means the background process must read the existing data from the object storage (S3/HDFS), remove the column, and write new, healthy segment files. * The Catch: Since your current reader is already failing with

invalid pos:1489, num_elements:0

when trying to read the table, the background schema change task will likely hit the same error and fail. 2. It won't "heal" the Tablet The error indicates a corruption or a bug in how the segment file's index/metadata is being interpreted. Even if you "drop" the column at a metadata level: * The physical files on your object storage still contain the corrupted data. * The "invalid pos" error often happens at the segment/rowset level, meaning the reader gets confused about where rows start and end. This can affect the entire rowset, not just one column. What will happen if you try: 1. You run

ALTER TABLE ENTITY_CustomerMaster DROP COLUMN ...;

. 2. StarRocks will create a new "Schema Change" job. 3. You can check its progress with

SHOW ALTER TABLE COLUMN;

. 4. In your current state, the

State

will likely move to

RUNNING

and then quickly to

CANCELLED

with the same

invalid pos

error message in the

Msg

column. ────────── Better Alternatives Since you are on v4.0.1 and have a tablet with double the normal size (69129), your focus should be on clearing that specific corrupted data. Option A: The "Lake Table" Reset (Safe but slow) If the data is reproducible or you have a backup: 1. Truncate and Reload: This is the most reliable way to clear the corrupted rowsets on object storage.

sql
    TRUNCATE TABLE ENTITY_CustomerMaster;
    -- Then re-run your load job.

Option B: Delete the specific data (If you can isolate it) If the error only happens on a specific partition: 1. Identify the partition:

SHOW PARTITIONS FROM ENTITY_CustomerMaster;

. 2. Drop just that partition:

ALTER TABLE ENTITY_CustomerMaster DROP PARTITION <name>;

. 3. Reload the data for that partition. Option C: Upgrade (Recommended) As mentioned, 4.0.1 is an early release with known stability issues in Lake storage. Version 4.0.3+ contains fixes specifically for these "invalid state" reader bugs. An upgrade often allows the system to correctly "see" and then compact the problematic rowsets into a healthy state it can read. Conclusion: Don't drop the column. It will likely just add a failed "Alter" task to your list of errors. Focus on re-loading the data or upgrading to get past the 4.0.1 reader bug. References • faq/shared_data_faq.md • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md

can you check fe/cn log, to find the original location where this error was escalated?

had applied this https://github.com/StarRocks/starrocks/pull/73730 and worked