cqlsh查询异常：指定分区键的SELECT语句无法返回已有数据

Let’s work through this issue step by step—since your LIMIT 5 query clearly shows rows with origin = 'MSY' exist, the problem is likely a subtle mismatch or configuration quirk. Here are the most common fixes:

1. Check for Hidden Characters or Whitespace in the Partition Key #

Cassandra’s text type is case-sensitive and preserves every character, including invisible whitespace. A single trailing space or hidden character in the stored origin value could make your query return no results, even if it looks like a match.

To inspect the raw value stored:

SELECT origin, blobAsText(blob(origin)) AS raw_origin FROM myflights.flights_by_airport2 LIMIT 5;

Compare the raw_origin output to the string you’re querying ('MSY'). If you see extra spaces or odd characters, adjust your query to match the exact stored value, or clean up the data if needed.

2. Explicitly Set the Query Consistency Level #

While single-node clusters should behave predictably, sometimes default consistency level settings can cause unexpected results. Try forcing the query to use LOCAL_ONE (the safest consistency level for single-node setups):

CONSISTENCY LOCAL_ONE;
SELECT * FROM myflights.flights_by_airport2 WHERE origin = 'MSY';

This ensures you’re reading directly from the only available node, eliminating any consistency-related mismatches.

3. Flush Data to Disk and Clear Caches #

Cassandra uses key and row caches that might serve stale data. Force pending writes to disk and invalidate caches with these commands:

# Flush the table to ensure all writes are persisted
nodetool flush myflights flights_by_airport2

# Clear caches to remove stale entries
nodetool invalidatekeycache
nodetool invalidaterowcache

Retry your query after running these commands.

4. Verify Partition Token Ownership #

Even on a single node, it’s worth confirming that the MSY partition falls within the node’s token range:
First, get the token for the MSY partition:

SELECT token(origin) FROM myflights.flights_by_airport2 LIMIT 1;

Then check your node’s token range with:

nodetool status

The token from the CQL query should fall within the range listed for your node (look for the Token column in the nodetool output).

5. Double-Check the Table Schema #

A typo in the partition key definition could explain why filtering on origin doesn’t work. Re-describe the table to confirm:

DESCRIBE TABLE myflights.flights_by_airport2;

Ensure the primary key is exactly ((origin), dep_time)—no typos in the partition key name here.

内容的提问来源于stack exchange，提问作者I Singh