You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@cassandra.apache.org by Javier Canillas <ja...@gmail.com> on 2017/10/03 19:17:31 UTC

CREATE INDEX without IF NOT EXISTS when snapshoting

Hi everyone,

I came across something that bothers me a lot. I'm using snapshots to
backup data from my Cassandra cluster in case something really bad happens
(like dropping a table or a keyspace).

Exercising the recovery actions from those backups, I discover that the
schema put on the file "schema.cql" as a result of the snapshot has the
"CREATE IF NOT EXISTS" for the table, but not for the indexes.

When restoring from snapshots, and relying on the execution of these
schemas to build up the table structure, everything seems fine for tables
without secondary indexes, but for the ones that make use of them, the
execution of these statements fail miserably.

Here I paste a generated schema.cql content for a table with indexes:

CREATE TABLE IF NOT EXISTS keyspace1.table1 (
id text PRIMARY KEY,
content text,
last_update_date date,
last_update_date_time timestamp)
WITH ID = f1045fc0-2f59-11e7-95ec-295c3c064920
AND bloom_filter_fp_chance = 0.01
AND dclocal_read_repair_chance = 0.1
AND crc_check_chance = 1.0
AND default_time_to_live = 8640000
AND gc_grace_seconds = 864000
AND min_index_interval = 128
AND max_index_interval = 2048
AND memtable_flush_period_in_ms = 0
AND read_repair_chance = 0.0
AND speculative_retry = '99PERCENTILE'
AND caching = { 'keys': 'NONE', 'rows_per_partition': 'NONE' }
AND compaction = { 'max_threshold': '32', 'min_threshold': '4', 'class':
'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy' }
AND compression = { 'chunk_length_in_kb': '64', 'class':
'org.apache.cassandra.io.compress.LZ4Compressor' }
AND cdc = false
AND extensions = {  };
*CREATE INDEX table1_last_update_date_idx ON keyspace1.table1
(last_update_date);*

I think the last part should be:

*CREATE INDEX IF NOT EXISTS table1_last_update_date_idx ON keyspace1.table1
(last_update_date);*

Any ideas? Have you wrote this part of the snapshot behavior for a
particular reason I'm not seeing?

I'm willing to help on coding (as I have done before xD) if you consider
this a trivial bug, but something that should be address.

Kind regards,

Javier.

Re: CREATE INDEX without IF NOT EXISTS when snapshoting

Posted by Lutaya Shafiq Holmes <lu...@gmail.com>.

TRY THIS CODE -

REPLACE WITH YOUR OWN KEYSPACE AND TABLE

cqlsh:tutorialspoint> describe table emp;
CREATE TABLE tutorialspoint.emp (
emp_id int PRIMARY KEY,
emp_city text,
emp_name text,
emp_phone varint,
emp_sal varint
) WITH bloom_filter_fp_chance = 0.01
AND caching = '{"keys":"ALL", "rows_per_partition":"NONE"}'
AND comment = ''
AND compaction = {'min_threshold': '4', 'class':
'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy',
'max_threshold': '32'}
AND compression = {'sstable_compression':
'org.apache.cassandra.io.compress.LZ4Compressor'}
AND dclocal_read_repair_chance = 0.1
AND default_time_to_live = 0
AND gc_grace_seconds = 864000
AND max_index_interval = 2048
AND memtable_flush_period_in_ms = 0
AND min_index_interval = 128
AND read_repair_chance = 0.0
AND speculative_retry = '99.0PERCENTILE';
CREATE INDEX emp_emp_sal_idx ON tutorialspoint.emp (emp_sal);

On 10/10/17, ganesh.gadde@bt.com <ga...@bt.com> wrote:
> Hi,
>
> Is there a way to estimate the size of table and index ?
> I know we can estimate the size once table and index are already created
> using nodetool cfstats, but I want to know before loading data into table.
> Could you please help if there is any such formula to find out.
>
> Thanks and Regards
> Ganesh
> From: Javier Canillas [mailto:javier.canillas@gmail.com]
> Sent: 05 October 2017 14:42
> To: user@cassandra.apache.org
> Cc: Cassandra DEV <de...@cassandra.apache.org>
> Subject: Re: CREATE INDEX without IF NOT EXISTS when snapshoting
>
> Well,
>
> Patches submitted for version 3.0, 3.11 and trunk (although 3.0 patch can be
> applied to 3.11). Feel free to review and comment.
>
> Thanks!
>
> 2017-10-04 16:41 GMT-03:00 Javier Canillas
> <ja...@gmail.com>>:
> Kurt,
>
> Thanks for your response. Created this
> ticket<https://issues.apache.org/jira/browse/CASSANDRA-13935>. Feel free to
> add anything to it that seems legit.
>
> Downloading Cassandra code right now.
>
> Fix seems quite simple. Expect a pull-request soon xD
>
> 2017-10-03 20:19 GMT-03:00 kurt greaves
> <ku...@instaclustr.com>>:
> Certainly would make sense and should be trivial.
> here<https://github.com/apache/cassandra/blob/982ab93a2f8a0f5c56af9378f65d3e9e430000b9/src/java/org/apache/cassandra/db/ColumnFamilyStoreCQLHelper.java#L251>
> is where you want to look. Just create a ticket for it and prod here for a
> reviewer once you've got a change.
>
>
>


-- 
Lutaaya Shafiq
Web: www.ronzag.com | info@ronzag.com
Mobile: +256702772721 | +256783564130
Twitter: @lutayashafiq
Skype: lutaya5
Blog: lutayashafiq.com
http://www.fourcornersalliancegroup.com/?a=shafiqholmes

"The most beautiful people we have known are those who have known defeat,
known suffering, known struggle, known loss and have found their way out of
the depths. These persons have an appreciation, a sensitivity and an
understanding of life that fills them with compassion, gentleness and a
deep loving concern. Beautiful people do not just happen." - *Elisabeth
Kubler-Ross*

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@cassandra.apache.org
For additional commands, e-mail: user-help@cassandra.apache.org

RE: CREATE INDEX without IF NOT EXISTS when snapshoting

Posted by ga...@bt.com.

Hi,

Is there a way to estimate the size of table and index ?
I know we can estimate the size once table and index are already created using nodetool cfstats, but I want to know before loading data into table. Could you please help if there is any such formula to find out.

Thanks and Regards
Ganesh
From: Javier Canillas [mailto:javier.canillas@gmail.com]
Sent: 05 October 2017 14:42
To: user@cassandra.apache.org
Cc: Cassandra DEV <de...@cassandra.apache.org>
Subject: Re: CREATE INDEX without IF NOT EXISTS when snapshoting

Well,

Patches submitted for version 3.0, 3.11 and trunk (although 3.0 patch can be applied to 3.11). Feel free to review and comment.

Thanks!

2017-10-04 16:41 GMT-03:00 Javier Canillas <ja...@gmail.com>>:
Kurt,

Thanks for your response. Created this ticket<https://issues.apache.org/jira/browse/CASSANDRA-13935>. Feel free to add anything to it that seems legit.

Downloading Cassandra code right now.

Fix seems quite simple. Expect a pull-request soon xD

2017-10-03 20:19 GMT-03:00 kurt greaves <ku...@instaclustr.com>>:
Certainly would make sense and should be trivial.  here<https://github.com/apache/cassandra/blob/982ab93a2f8a0f5c56af9378f65d3e9e430000b9/src/java/org/apache/cassandra/db/ColumnFamilyStoreCQLHelper.java#L251> is where you want to look. Just create a ticket for it and prod here for a reviewer once you've got a change.

RE: CREATE INDEX without IF NOT EXISTS when snapshoting

Posted by ga...@bt.com.

Hi,

Is there a way to estimate the size of table and index ?
I know we can estimate the size once table and index are already created using nodetool cfstats, but I want to know before loading data into table. Could you please help if there is any such formula to find out.

Thanks and Regards
Ganesh
From: Javier Canillas [mailto:javier.canillas@gmail.com]
Sent: 05 October 2017 14:42
To: user@cassandra.apache.org
Cc: Cassandra DEV <de...@cassandra.apache.org>
Subject: Re: CREATE INDEX without IF NOT EXISTS when snapshoting

Well,

Patches submitted for version 3.0, 3.11 and trunk (although 3.0 patch can be applied to 3.11). Feel free to review and comment.

Thanks!

2017-10-04 16:41 GMT-03:00 Javier Canillas <ja...@gmail.com>>:
Kurt,

Thanks for your response. Created this ticket<https://issues.apache.org/jira/browse/CASSANDRA-13935>. Feel free to add anything to it that seems legit.

Downloading Cassandra code right now.

Fix seems quite simple. Expect a pull-request soon xD

2017-10-03 20:19 GMT-03:00 kurt greaves <ku...@instaclustr.com>>:
Certainly would make sense and should be trivial.  here<https://github.com/apache/cassandra/blob/982ab93a2f8a0f5c56af9378f65d3e9e430000b9/src/java/org/apache/cassandra/db/ColumnFamilyStoreCQLHelper.java#L251> is where you want to look. Just create a ticket for it and prod here for a reviewer once you've got a change.