[CASSANDRA-6121] CASS 2.0, possibly 1.2.8 as well: Secondary Indexes not working

• Type: Bug
• Status: Resolved
• Priority: Normal
• Resolution: Duplicate
• Fix Version/s: 1.2.8, 2.0.1
• Component/s: Feature/2i Index

Environment

AWS ebs striped quad-volume for data directories, ubuntu 12.10. Currently single-node, but also possibly seen in two-node configuration.

• Severity: Normal
• Assignee: Unassigned
• Reporter: Constance Eustace
• Created: 01/Oct/13 14:51
• Updated: 16/Apr/19 09:32
• Resolved: 21/Oct/13 15:43

Description

I will attach the schema we are using. We are using CQL3, fed via the cass-jdbc driver project.

We are using off-heap JNA-enabled key and row caching.

We implement an entity model using cassandra wide/sparse row. So an "entityid" is the rowkey, and various properties are stored with property names as the column key and various value information (type, value(s)), etc. Some of these sparse columns are indexes so there can be searches on the values.

We have a fairly large number of indexes.

Data is populated using heavy batch intakes (1.2 million row keys done in about 16 minutes).

We will attempt to reproduce reliably, get stats, logs, traces. Filing the bug for now as a placeholder.

These 1.2 million rowkey updates are split into individual batches of about 200 statements, with a commonly shared timestamp specified for the batch so that update contention can be dealt with.

I have seen the previous filed bugs on compaction on TTL columns (not used by us) and rowkey caching having impacts on the indexes. We may attempt experiments where we do not use rowkey caching, toggling JNA/offheap, etc.

Any advice would be appreciated for detecting index failure...

Our schema: (we have another 8-10 near copies of this keyspace that split the data for vendors/storefronts/etc)

CREATE KEYSPACE internal_submission WITH REPLICATION=

{ 'class':'SimpleStrategy', 'replication_factor':<%=node.ingest.db.replication_factor%> }

;
CREATE TABLE internal_submission.Relation (ParentID text,ChildID text,GraphType text,Info map<text,text>,PRIMARY KEY (ParentID,ChildID)) with caching = 'all';
CREATE TABLE internal_submission.RelationBACKREF (ChildID text,ParentID text,PRIMARY KEY (ChildID,ParentID)) with caching = 'all';
CREATE TABLE internal_submission.Blob (BlobID text,Type text,SubType text,Encoding map<text,text>,BlobData blob,PRIMARY KEY (BlobID)) with caching = 'keys_only';
CREATE TABLE internal_submission.Entity_Job (e_EntID text,e_EntName text,e_EntType text,e_EntLinks text,p_Prop text,p_Storage text,p_PropID text,p_Flags text,p_Val text,p_ValType text,p_ValUnit text,p_ValLang text,p_ValLinks text,p_Vars text,p_PropLinks text,p_SubEnts text,PartnerID text,UserID text,SubmitDate bigint,SourceIP text,SubmitEvent text,Size int,Describes text,Version text,IngestStatus text,IngestStatusDetail text,ReferenceID text,DNDCondition text,PRIMARY KEY (e_EntID,p_Prop)) with caching = 'all';
CREATE TABLE internal_submission.Processing (EntityID text,InProcess counter,Complete counter,Success counter,Fail counter,Warn counter,Redo counter,Hold counter,PRIMARY KEY (EntityID)) with caching = 'all';
CREATE TABLE internal_submission.Entity_Asset (e_EntID text,e_EntName text,e_EntType text,e_EntLinks text,p_Prop text,p_Storage text,p_PropID text,p_Flags text,p_Val text,p_ValType text,p_ValUnit text,p_ValLang text,p_ValLinks text,p_Vars text,p_PropLinks text,p_SubEnts text,IngestStatus text,IngestStatusDetail text,PRIMARY KEY (e_EntID,p_Prop)) with caching = 'all';
CREATE TABLE internal_submission.Entity_MetaDataDef (e_EntID text,e_EntName text,e_EntType text,e_EntLinks text,p_Prop text,p_Storage text,p_PropID text,p_Flags text,p_Val text,p_ValType text,p_ValUnit text,p_ValLang text,p_ValLinks text,p_Vars text,p_PropLinks text,p_SubEnts text,PRIMARY KEY (e_EntID,p_Prop)) with caching = 'all';
CREATE TABLE internal_submission.Entity_HierarchyDef (e_EntID text,e_EntName text,e_EntType text,e_EntLinks text,p_Prop text,p_Storage text,p_PropID text,p_Flags text,p_Val text,p_ValType text,p_ValUnit text,p_ValLang text,p_ValLinks text,p_Vars text,p_PropLinks text,p_SubEnts text,Describes text,Version text,PRIMARY KEY (e_EntID,p_Prop)) with caching = 'all';
CREATE TABLE internal_submission.Entity_CategoryDef (e_EntID text,e_EntName text,e_EntType text,e_EntLinks text,p_Prop text,p_Storage text,p_PropID text,p_Flags text,p_Val text,p_ValType text,p_ValUnit text,p_ValLang text,p_ValLinks text,p_Vars text,p_PropLinks text,p_SubEnts text,Describes text,Version text,PRIMARY KEY (e_EntID,p_Prop)) with caching = 'all';
CREATE TABLE internal_submission.Entity_ProductDef (e_EntID text,e_EntName text,e_EntType text,e_EntLinks text,p_Prop text,p_Storage text,p_PropID text,p_Flags text,p_Val text,p_ValType text,p_ValUnit text,p_ValLang text,p_ValLinks text,p_Vars text,p_PropLinks text,p_SubEnts text,GTIN text,PIMCAT text,Describes text,Version text,PRIMARY KEY (e_EntID,p_Prop)) with caching = 'all';
CREATE TABLE internal_submission.Entity_ItemDef (e_EntID text,e_EntName text,e_EntType text,e_EntLinks text,p_Prop text,p_Storage text,p_PropID text,p_Flags text,p_Val text,p_ValType text,p_ValUnit text,p_ValLang text,p_ValLinks text,p_Vars text,p_PropLinks text,p_SubEnts text,SKU text,MIID text,PIMCAT text,Describes text,Version text,PRIMARY KEY (e_EntID,p_Prop)) with caching = 'all';
CREATE TABLE internal_submission.Entity_AttributeDef (e_EntID text,e_EntName text,e_EntType text,e_EntLinks text,p_Prop text,p_Storage text,p_PropID text,p_Flags text,p_Val text,p_ValType text,p_ValUnit text,p_ValLang text,p_ValLinks text,p_Vars text,p_PropLinks text,p_SubEnts text,Describes text,Version text,PRIMARY KEY (e_EntID,p_Prop)) with caching = 'all';
CREATE TABLE internal_submission.Entity_Category (e_EntID text,e_EntName text,e_EntType text,e_EntLinks text,p_Prop text,p_Storage text,p_PropID text,p_Flags text,p_Val text,p_ValType text,p_ValUnit text,p_ValLang text,p_ValLinks text,p_Vars text,p_PropLinks text,p_SubEnts text,IngestStatus text,IngestStatusDetail text,PRIMARY KEY (e_EntID,p_Prop)) with caching = 'all';
CREATE TABLE internal_submission.Entity_Product (e_EntID text,e_EntName text,e_EntType text,e_EntLinks text,p_Prop text,p_Storage text,p_PropID text,p_Flags text,p_Val text,p_ValType text,p_ValUnit text,p_ValLang text,p_ValLinks text,p_Vars text,p_PropLinks text,p_SubEnts text,IngestStatus text,IngestStatusDetail text,GTIN text,SKU text,MIID text,PRIMARY KEY (e_EntID,p_Prop)) with caching = 'all';
CREATE TABLE internal_submission.Entity_Item (e_EntID text,e_EntName text,e_EntType text,e_EntLinks text,p_Prop text,p_Storage text,p_PropID text,p_Flags text,p_Val text,p_ValType text,p_ValUnit text,p_ValLang text,p_ValLinks text,p_Vars text,p_PropLinks text,p_SubEnts text,IngestStatus text,IngestStatusDetail text,GTIN text,SKU text,MIID text,PRIMARY KEY (e_EntID,p_Prop)) with caching = 'all';
CREATE TABLE internal_submission.Entity_Synthetic (e_EntID text,e_EntName text,e_EntType text,e_EntLinks text,p_Prop text,p_Storage text,p_PropID text,p_Flags text,p_Val text,p_ValType text,p_ValUnit text,p_ValLang text,p_ValLinks text,p_Vars text,p_PropLinks text,p_SubEnts text,PRIMARY KEY (e_EntID,p_Prop)) with caching = 'all';
CREATE TABLE internal_submission.Entity_Validation (e_EntID text,e_EntName text,e_EntType text,e_EntLinks text,p_Prop text,p_Storage text,p_PropID text,p_Flags text,p_Val text,p_ValType text,p_ValUnit text,p_ValLang text,p_ValLinks text,p_Vars text,p_PropLinks text,p_SubEnts text,PRIMARY KEY (e_EntID,p_Prop)) with caching = 'all';
CREATE INDEX internal_submission__JobUserIDX ON internal_submission.Entity_Job (UserID);
CREATE INDEX internal_submission__JobIngestStatusIDX ON internal_submission.Entity_Job (IngestStatus);
CREATE INDEX internal_submission__JobIngestStatusDetailIDX ON internal_submission.Entity_Job (IngestStatusDetail);
CREATE INDEX internal_submission__JobDNDConditionIDX ON internal_submission.Entity_Job (DNDCondition);
CREATE INDEX internal_submission__JobDescribesIDX ON internal_submission.Entity_Job (Describes);
CREATE INDEX internal_submission__JobVersionIDX ON internal_submission.Entity_Job (Version);
CREATE INDEX internal_submission__JobReferenceIDIDX ON internal_submission.Entity_Job (ReferenceID);
CREATE INDEX internal_submission__AssetIngestStatusIDX ON internal_submission.Entity_Asset (IngestStatus);
CREATE INDEX internal_submission__AssetIngestStatusDetailIDX ON internal_submission.Entity_Asset (IngestStatusDetail);
CREATE INDEX internal_submission__ProductDefGTINIDX ON internal_submission.Entity_ProductDef (GTIN);
CREATE INDEX internal_submission__ProductDefPIMCATIDX ON internal_submission.Entity_ProductDef (PIMCAT);
CREATE INDEX internal_submission__ProductDefDescribesIDX ON internal_submission.Entity_ProductDef (Describes);
CREATE INDEX internal_submission__ProductDefVersionIDX ON internal_submission.Entity_ProductDef (Version);
CREATE INDEX internal_submission__HierarchyDefDescribesIDX ON internal_submission.Entity_HierarchyDef (Describes);
CREATE INDEX internal_submission__HierarchyDefVersionIDX ON internal_submission.Entity_HierarchyDef (Version);
CREATE INDEX internal_submission__CategoryDefDescribesIDX ON internal_submission.Entity_CategoryDef (Describes);
CREATE INDEX internal_submission__CategoryDefVersionIDX ON internal_submission.Entity_CategoryDef (Version);
CREATE INDEX internal_submission__ItemDefSKUIDX ON internal_submission.Entity_ItemDef (SKU);
CREATE INDEX internal_submission__ItemDefMIIDIDX ON internal_submission.Entity_ItemDef (MIID);
CREATE INDEX internal_submission__ItemDefPIMCATIDX ON internal_submission.Entity_ItemDef (PIMCAT);
CREATE INDEX internal_submission__ItemDefDescribesIDX ON internal_submission.Entity_ItemDef (Describes);
CREATE INDEX internal_submission__ItemDefVersionIDX ON internal_submission.Entity_ItemDef (Version);
CREATE INDEX internal_submission__AttributeDefDescribesIDX ON internal_submission.Entity_AttributeDef (Describes);
CREATE INDEX internal_submission__AttributeDefVersionIDX ON internal_submission.Entity_AttributeDef (Version);
CREATE INDEX internal_submission__CategoryIngestStatusIDX ON internal_submission.Entity_Category (IngestStatus);
CREATE INDEX internal_submission__CategoryIngestStatusDetailIDX ON internal_submission.Entity_Category (IngestStatusDetail);
CREATE INDEX internal_submission__ProductIngestStatusIDX ON internal_submission.Entity_Product (IngestStatus);
CREATE INDEX internal_submission__ProductIngestStatusDetailIDX ON internal_submission.Entity_Product (IngestStatusDetail);
CREATE INDEX internal_submission__ProductGTINIDX ON internal_submission.Entity_Product (GTIN);
CREATE INDEX internal_submission__ProductSKUIDX ON internal_submission.Entity_Product (SKU);
CREATE INDEX internal_submission__ProductMIIDIDX ON internal_submission.Entity_Product (MIID);
CREATE INDEX internal_submission__ItemIngestStatusIDX ON internal_submission.Entity_Item (IngestStatus);
CREATE INDEX internal_submission__ItemIngestStatusDetailIDX ON internal_submission.Entity_Item (IngestStatusDetail);
CREATE INDEX internal_submission__ItemGTINIDX ON internal_submission.Entity_Item (GTIN);
CREATE INDEX internal_submission__ItemSKUIDX ON internal_submission.Entity_Item (SKU);
CREATE INDEX internal_submission__ItemMIIDIDX ON internal_submission.Entity_Item (MIID);