You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cassandra.apache.org by "Vito Giuliani (JIRA)" <ji...@apache.org> on 2013/11/18 10:11:22 UTC
[jira] [Commented] (CASSANDRA-6137) CQL3 SELECT IN CLAUSE
inconsistent
[ https://issues.apache.org/jira/browse/CASSANDRA-6137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13825205#comment-13825205 ]
Vito Giuliani commented on CASSANDRA-6137:
------------------------------------------
I've hit this bug too in my local environment (using C* 2.0.2), both in the java driver (datastax's) and cqlsh:
{noformat}
SELECT code, date, price FROM inventory_price_by_day WHERE code='3853853853852' and date in ('2013-11-08', '2013-11-09', '2013-11-10', '2013-11-11', '2013-11-12', '2013-11-13', '2013-11-14', '2013-11-15');
code | date | price
---------------+------------+-------
3853853853852 | 2013-11-08 | 66.00
3853853853852 | 2013-11-09 | 66.00
3853853853852 | 2013-11-10 | 66.00
3853853853852 | 2013-11-11 | 66.00
3853853853852 | 2013-11-12 | 66.00
3853853853852 | 2013-11-13 | 66.00
3853853853852 | 2013-11-14 | 66.00
3853853853852 | 2013-11-15 | 66.00
(8 rows)
SELECT code, date, price FROM inventory_price_by_day WHERE code='3853853853852' and date in ('2013-11-07', '2013-11-08', '2013-11-09', '2013-11-10', '2013-11-11', '2013-11-12', '2013-11-13', '2013-11-14', '2013-11-15');
code | date | price
---------------+------------+-------
3853853853852 | 2013-11-15 | 66.00
(1 rows)
{noformat}
(the only difference between the two queries is that the latter includes an additional day)
I tried to run flush / compact / keycacheinvalidate but they don't seem to have any kind of effect here.
Enabling tracing, there seems to be a difference in the way the two queries are executed:
{noformat}
activity | timestamp | source | source_elapsed
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-----------+----------------
execute_cql3_query | 09:23:28,987 | 127.0.0.1 | 0
Parsing SELECT code, date, price FROM inventory_price_by_day WHERE code='3853853853852' and date in ('2013-11-08', '2013-11-09', '2013-11-10', '2013-11-11', '2013-11-12', '2013-11-13', '2013-11-14', '2013-11-15') LIMIT 10000; | 09:23:28,987 | 127.0.0.1 | 66
Preparing statement | 09:23:28,987 | 127.0.0.1 | 161
Executing single-partition query on inventory_price_by_day | 09:23:28,988 | 127.0.0.1 | 467
Acquiring sstable references | 09:23:28,988 | 127.0.0.1 | 488
Merging memtable tombstones | 09:23:28,988 | 127.0.0.1 | 514
Key cache hit for sstable 4 | 09:23:28,988 | 127.0.0.1 | 589
Seeking to partition indexed section in data file | 09:23:28,988 | 127.0.0.1 | 604
Skipped 0/1 non-slice-intersecting sstables, included 0 due to tombstones | 09:23:28,988 | 127.0.0.1 | 870
Merging data from memtables and 1 sstables | 09:23:28,988 | 127.0.0.1 | 893
Read 8 live and 0 tombstoned cells | 09:23:28,988 | 127.0.0.1 | 1045
Request complete | 09:23:28,988 | 127.0.0.1 | 1216
activity | timestamp | source | source_elapsed
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-----------+----------------
execute_cql3_query | 09:23:32,003 | 127.0.0.1 | 0
Parsing SELECT code, date, price FROM inventory_price_by_day WHERE code='3853853853852' and date in ('2013-11-07', '2013-11-08', '2013-11-09', '2013-11-10', '2013-11-11', '2013-11-12', '2013-11-13', '2013-11-14', '2013-11-15') LIMIT 10000; | 09:23:32,004 | 127.0.0.1 | 76
Preparing statement | 09:23:32,004 | 127.0.0.1 | 184
Executing single-partition query on inventory_price_by_day | 09:23:32,004 | 127.0.0.1 | 491
Acquiring sstable references | 09:23:32,004 | 127.0.0.1 | 514
Merging memtable tombstones | 09:23:32,004 | 127.0.0.1 | 540
Skipped 1/1 non-slice-intersecting sstables, included 0 due to tombstones | 09:23:32,004 | 127.0.0.1 | 601
Merging data from memtables and 0 sstables | 09:23:32,004 | 127.0.0.1 | 622
Read 1 live and 0 tombstoned cells | 09:23:32,004 | 127.0.0.1 | 757
Request complete | 09:23:32,003 | 127.0.0.1 | 960
{noformat}
Notice the "Skipped 1/1 non-slice-intersecting sstables" in the second query, which is interesting given that there's only 1 sstable for that column family.
> CQL3 SELECT IN CLAUSE inconsistent
> ----------------------------------
>
> Key: CASSANDRA-6137
> URL: https://issues.apache.org/jira/browse/CASSANDRA-6137
> Project: Cassandra
> Issue Type: Bug
> Components: Core
> Environment: Ubuntu AWS Cassandra 2.0.1 SINGLE NODE on EBS RAID storage
> OSX Cassandra 1.2.8 on SSD storage
> Reporter: Constance Eustace
> Priority: Minor
>
> Possible Resolution:
> What seems to be key is to run a nodetool compact (possibly a nodetool flush) after schema drops / schema creations / schema truncates and invalidate the caches. This seems to align the data for new inserts/updates. From my reproduction tests, I have been unable to generate the database corruption if nodetool flush, nodetool compact, nodetool keycacheinvalidate (we have turned off rowcache due to other bugs). Then, even after running a more stressful test with 10x the inserts and five separate concurrent update threads the corruption did not appear.
> So I believe this is a tentative "fix" to this issue... in general, after any manipulation to the schema, you should run nodetool compact and keycacheinvalidate. I have not tested if a general compact on all keyspaces and tables versus a more specific compact on the affected keyspace and/or keyspace tables is all that is necessary (compact can be a very expensive operation).
> ------------------------------------------------------------------
> Problem Encountered:
> We are encountering inconsistent results from CQL3 queries with column keys using IN clause in WHERE. This has been reproduced in cqlsh and the jdbc driver. Specifically, we are doing queries to pull a subset of column keys for a specific row key.
> We detect this corruption by selecting all the column keys for a row, and then trying different subsets of column keys in WHERE <columnkey> IN (<column key subset list>). We see some of these column key subset queries not return all the column keys, even though the select-all-column-keys query finds them.
> It seems to appear when there is a large amount of raw insertion work (non-updates / new ingested data) combined with simultaneous updates to existing data. EDIT: this also seems to only happen with mass insert+updates after schema changes / drops / table creation / table truncation. See the Possible Resolution section above.
> ------------------------------------------------------------------
> Details:
> Rowkey is e_entid
> Column key is p_prop
> This returns roughly 21 rows for 21 column keys that match p_prop.
> cqlsh> SELECT e_entid,e_entname,e_enttype,p_prop,p_flags,p_propid,e_entlinks,p_proplinks,p_subents,p_val,p_vallinks,p_vars FROM internal_submission.Entity_Job WHERE e_entid = '845b38f1-2b91-11e3-854d-126aad0075d4-CJOB';
> These three queries each return one row for the requested single column key in the IN clause:
> SELECT e_entid,e_entname,e_enttype,p_prop,p_flags,p_propid,e_entlinks,p_proplinks,p_subents,p_val,p_vallinks,p_vars FROM internal_submission.Entity_Job WHERE e_entid = '845b38f1-2b91-11e3-854d-126aad0075d4-CJOB' AND p_prop in ('urn:bby:pcm:job:ingest:content:complete:count');
> SELECT e_entid,e_entname,e_enttype,p_prop,p_flags,p_propid,e_entlinks,p_proplinks,p_subents,p_val,p_vallinks,p_vars FROM internal_submission.Entity_Job WHERE e_entid = '845b38f1-2b91-11e3-854d-126aad0075d4-CJOB' AND p_prop in ('urn:bby:pcm:job:ingest:content:all:count');
> SELECT e_entid,e_entname,e_enttype,p_prop,p_flags,p_propid,e_entlinks,p_proplinks,p_subents,p_val,p_vallinks,p_vars FROM internal_submission.Entity_Job WHERE e_entid = '845b38f1-2b91-11e3-854d-126aad0075d4-CJOB' AND p_prop in ('urn:bby:pcm:job:ingest:content:fail:count');
> This query returns ONLY ONE ROW (one column key), not three as I would expect from the three-column-key IN clause:
> cqlsh> SELECT e_entid,e_entname,e_enttype,p_prop,p_flags,p_propid,e_entlinks,p_proplinks,p_subents,p_val,p_vallinks,p_vars FROM internal_submission.Entity_Job WHERE e_entid = '845b38f1-2b91-11e3-854d-126aad0075d4-CJOB' AND p_prop in ('urn:bby:pcm:job:ingest:content:complete:count','urn:bby:pcm:job:ingest:content:all:count','urn:bby:pcm:job:ingest:content:fail:count');
> This query does return two rows however for the requested two column keys:
> cqlsh> SELECT e_entid,e_entname,e_enttype,p_prop,p_flags,p_propid,e_entlinks,p_proplinks,p_subents,p_val,p_vallinks,p_vars FROM internal_submission.Entity_Job WHERE e_entid = '845b38f1-2b91-11e3-854d-126aad0075d4-CJOB' AND p_prop in ( 'urn:bby:pcm:job:ingest:content:all:count','urn:bby:pcm:job:ingest:content:fail:count');
> cqlsh> describe table internal_submission.entity_job;
> CREATE TABLE entity_job (
> e_entid text,
> p_prop text,
> describes text,
> dndcondition text,
> e_entlinks text,
> e_entname text,
> e_enttype text,
> ingeststatus text,
> ingeststatusdetail text,
> p_flags text,
> p_propid text,
> p_proplinks text,
> p_storage text,
> p_subents text,
> p_val text,
> p_vallang text,
> p_vallinks text,
> p_valtype text,
> p_valunit text,
> p_vars text,
> partnerid text,
> referenceid text,
> size int,
> sourceip text,
> submitdate bigint,
> submitevent text,
> userid text,
> version text,
> PRIMARY KEY (e_entid, p_prop)
> ) WITH
> bloom_filter_fp_chance=0.010000 AND
> caching='KEYS_ONLY' AND
> comment='' AND
> dclocal_read_repair_chance=0.000000 AND
> gc_grace_seconds=864000 AND
> index_interval=128 AND
> read_repair_chance=0.100000 AND
> replicate_on_write='true' AND
> populate_io_cache_on_flush='false' AND
> default_time_to_live=0 AND
> speculative_retry='NONE' AND
> memtable_flush_period_in_ms=0 AND
> compaction={'class': 'SizeTieredCompactionStrategy'} AND
> compression={'sstable_compression': 'LZ4Compressor'};
> CREATE INDEX internal_submission__JobDescribesIDX ON entity_job (describes);
> CREATE INDEX internal_submission__JobDNDConditionIDX ON entity_job (dndcondition);
> CREATE INDEX internal_submission__JobIngestStatusIDX ON entity_job (ingeststatus);
> CREATE INDEX internal_submission__JobIngestStatusDetailIDX ON entity_job (ingeststatusdetail);
> CREATE INDEX internal_submission__JobReferenceIDIDX ON entity_job (referenceid);
> CREATE INDEX internal_submission__JobUserIDX ON entity_job (userid);
> CREATE INDEX internal_submission__JobVersionIDX ON entity_job (version);
> -------------------------------
> My suspicion is that the three-column-key IN Clause is translated (improperly or not) to a two-column key range with the assumption that the third column key is present in that range, but it isn't...
> -----------------------------------
> We have tried: nodetool cache invalidations, start/stop of cassandra. Those did NOT fix the problem. A table dump (COPY TO) and then reload (COPY FROM) does fix the rows, but then more corruption creeps in.
> We are using the cassandra-jdbc driver, but I don't see anything wrong with the issued statements inside the cassandra source code when I step through the code.
> With additional writes, it may be possible that some rows get fixed. Compaction or other jobs may repair this, but on the timescale of hours done debugging, the failures are consistent.
--
This message was sent by Atlassian JIRA
(v6.1#6144)