Re: Alter table

[Rahul Singh]

If you use collections such as a map you could get by with just 
upserts. A collection in a column gives you the ability to have “flexible” 
schema for your “documents” as in mongo while the regular fields can act as 
“records” as in a more
Traditional table.

Rahul Singh
Chief Executive Officer
m 202.905.2818

Anant Corporation
1010 Wisconsin Ave NW, Suite 250
Washington, D.C. 20007

We build and manage digital business technology platforms.
On Dec 17, 2018, 4:45 PM -0500, Mark Furlong , wrote:
> Why would I want to use alter table vs upserts with the new document format?
>
> Mark Furlong
> Sr. Database Administrator
> mfurl...@ancestry.com
> M: 801-859-7427
> O: 801-705-7115
> 1300 W Traverse Pkwy
> Lehi, UT 84043
>
>
>
>
>

Re: Alter table

[Peter Corless]

Alter table would change columns (the structure) of a table. Adding or
deleting a column, for instance.

Upserts would add (or edit) rows of an existing table.

ALTER TABLE <https://docs.scylladb.com/getting-started/ddl/#id10> vs. UPDATE
<https://docs.scylladb.com/getting-started/dml/#update-statement>

(These docs are for Scylla, but Cassandra should be the same or similar for
Datastax.)

On Mon, Dec 17, 2018 at 1:45 PM Mark Furlong  wrote:

> Why would I want to use alter table vs upserts with the new document
> format?
>
>
>
> *Mark Furlong*
>
> Sr. Database Administrator
>
> *mfurl...@ancestry.com *
> M: 801-859-7427
>
> O: 801-705-7115
>
> 1300 W Traverse Pkwy
>
> Lehi, UT 84043
>
>
>
>
>
> ​[image: http://c.mfcreative.com/mars/email/shared-icon/sig-logo.gif]
>
>
>
>
>


-- 
Peter Corless
Technical Marketing Manager
pe...@scylladb.com
650-906-3134

Alter table

[Mark Furlong]

Why would I want to use alter table vs upserts with the new document format?

Mark Furlong

Sr. Database Administrator

mfurl...@ancestry.com<mailto:mfurl...@ancestry.com>
M: 801-859-7427
O: 801-705-7115
1300 W Traverse Pkwy
Lehi, UT 84043





​[http://c.mfcreative.com/mars/email/shared-icon/sig-logo.gif]

Re: Corrupt insert during ALTER TABLE add

[Max C.]

Yep, that’s the problem!  Thanks Jeff (and Alex Petrov for fixing it).

- Max

> On Sep 13, 2018, at 1:24 pm, Jeff Jirsa  wrote:
> 
> CASSANDA-13004 (fixed in recent 3.0 and 3.11 builds)

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

Re: Corrupt insert during ALTER TABLE add

[Jeff Jirsa]

CASSANDA-13004 (fixed in recent 3.0 and 3.11 builds)


On Thu, Sep 13, 2018 at 1:12 PM Max C.  wrote:

> I ran “alter table” today to add the “task_output_capture_state” column
> (see below), and we found a few rows inserted around the time of the ALTER
> TABLE did not contain the same values when selected as when they were
> inserted.
>
> When the row was selected, what we saw was:
> - test_id —> OK (same as insert)
> - test_instance_group_id —> inserted as UNSET_ID, selected it contained
> the same value as test_id
> - test_name —> OK (same as insert)
> - ti_exec_flow —> inserted as “STD”, selected it contained the same value
> as test_name
>
> Does this ring a bell?  Is there a JIRA for this (hopefully fixed in a
> newer C*)?  We’re running 3.0.6.  Thanks everyone.
>
> CREATE TABLE mars.test_instances_by_run_submission (
> run_submission_id timeuuid,
> rs_bucket_num int,
> id timeuuid,
> active_task_id timeuuid,
> active_task_name_path text,
> exec_end timestamp,
> exec_gpath text,
> exec_host_id uuid,
> exec_start timestamp,
> exec_state text,
> exec_subbuild_id timeuuid,
> exec_vco text,
> fstatus text,
> grid_job_id uuid,
> legacy_assert boolean,
> legacy_core boolean,
> source_gpath text,
> task_output_capture_state text,   # NEW
> test_id uuid,
> test_instance_group_id uuid,
> test_name text,
> ti_exec_flow text,
> PRIMARY KEY ((run_submission_id, rs_bucket_num), id)
> ) WITH CLUSTERING ORDER BY (id ASC)
>
> -
> To unsubscribe, e-mail: user-unsubscr...@cassandra.apache.org
> For additional commands, e-mail: user-h...@cassandra.apache.org
>
>

Re: Corrupt insert during ALTER TABLE add

[Max C.]

Correction — we’re running C* 3.0.8.  DataStax Python driver 3.4.1.

> On Sep 13, 2018, at 1:11 pm, Max C.  wrote:
> 
> I ran “alter table” today to add the “task_output_capture_state” column (see 
> below), and we found a few rows inserted around the time of the ALTER TABLE 
> did not contain the same values when selected as when they were inserted.
> 
> When the row was selected, what we saw was:
> - test_id —> OK (same as insert)
> - test_instance_group_id —> inserted as UNSET_ID, selected it contained the 
> same value as test_id
> - test_name —> OK (same as insert)
> - ti_exec_flow —> inserted as “STD”, selected it contained the same value as 
> test_name
> 
> Does this ring a bell?  Is there a JIRA for this (hopefully fixed in a newer 
> C*)?  We’re running 3.0.6.  Thanks everyone.
> 
> CREATE TABLE mars.test_instances_by_run_submission (
>run_submission_id timeuuid,
>rs_bucket_num int,
>id timeuuid, 
>active_task_id timeuuid,
>active_task_name_path text,
>exec_end timestamp,
>exec_gpath text,
>exec_host_id uuid,
>exec_start timestamp,
>exec_state text,
>exec_subbuild_id timeuuid,
>exec_vco text,
>fstatus text,
>grid_job_id uuid,
>legacy_assert boolean,
>legacy_core boolean,
>source_gpath text,
>task_output_capture_state text,   # NEW
>test_id uuid,
>test_instance_group_id uuid,
>test_name text,
>ti_exec_flow text,
>PRIMARY KEY ((run_submission_id, rs_bucket_num), id)
> ) WITH CLUSTERING ORDER BY (id ASC)
> 
> -
> To unsubscribe, e-mail: user-unsubscr...@cassandra.apache.org
> For additional commands, e-mail: user-h...@cassandra.apache.org

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

Corrupt insert during ALTER TABLE add

[Max C.]

I ran “alter table” today to add the “task_output_capture_state” column (see 
below), and we found a few rows inserted around the time of the ALTER TABLE did 
not contain the same values when selected as when they were inserted.

When the row was selected, what we saw was:
- test_id —> OK (same as insert)
- test_instance_group_id —> inserted as UNSET_ID, selected it contained the 
same value as test_id
- test_name —> OK (same as insert)
- ti_exec_flow —> inserted as “STD”, selected it contained the same value as 
test_name

Does this ring a bell?  Is there a JIRA for this (hopefully fixed in a newer 
C*)?  We’re running 3.0.6.  Thanks everyone.

CREATE TABLE mars.test_instances_by_run_submission (
run_submission_id timeuuid,
rs_bucket_num int,
id timeuuid, 
active_task_id timeuuid,
active_task_name_path text,
exec_end timestamp,
exec_gpath text,
exec_host_id uuid,
exec_start timestamp,
exec_state text,
exec_subbuild_id timeuuid,
exec_vco text,
fstatus text,
grid_job_id uuid,
legacy_assert boolean,
legacy_core boolean,
source_gpath text,
task_output_capture_state text,   # NEW
test_id uuid,
test_instance_group_id uuid,
test_name text,
ti_exec_flow text,
PRIMARY KEY ((run_submission_id, rs_bucket_num), id)
) WITH CLUSTERING ORDER BY (id ASC)

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

Re: Alter table

[Jeff Jirsa]

(My advice stands, I still believe it to be safe in all modern versions not 
impacted by 13004, but)

There’s little difference between adding columns and removing columns - if 
you’re afraid of writes while you add columns, you should be afraid of writes 
while you remove columns.

(But I don’t think you should be afraid of either)

-- 
Jeff Jirsa


> On Aug 1, 2018, at 6:43 PM, Visa  wrote:
> 
> Thanks for all the inputs! We’ll stick to the current approach then.
> 
> How about dropping columns - If we also stop writes beforehand, we should be 
> safe from data alignment issue after dropping columns?
> 
> Thanks,
> Li
> 
>> On Jul 31, 2018, at 04:14, James Shaw  wrote:
>> 
>> in a heavy transaction PROD env, it is risk, considering c* has a lot of 
>> bugs.
>> the DDL has to be replicated to all nodes,  use nodetool describecluster to 
>> check schema version same on all nodes, if not,  you may restart that node 
>> which DDL not replicated.
>> in new version, DDL is none or all,  you may not get it success.
>> 
>> It is similar to rdbms,  alter table in a heavy transaction PROD env, may 
>> get resource busy error.
>> 
>> in non-prod, we always apply new DDL without stop applications, never had 
>> issue.
>> 
>> Thanks,
>> 
>> James
>> 
>> 
>>> On Tue, Jul 31, 2018 at 1:37 AM, Jeff Jirsa  wrote:
>>> This is safe (and normal, and good) in all versions except those impacted 
>>> by https://issues.apache.org/jira/browse/CASSANDRA-13004 
>>> 
>>> So if you're on 2.1, 2.2, or 3.11 you're fine
>>> 
>>> If you're on 3.0 between 3.0.0 and 3.0.13, you should upgrade first (to 
>>> newest 3.0, probably 3.0.17)
>>> If you're on a version between 3.1 and 3.10, you should upgrade first (to 
>>> newest 3.11, probably 3.11.3)
>>> 
>>> - Jeff
>>> 
>>> 
>>>> On Mon, Jul 30, 2018 at 10:16 PM, Visa  wrote:
>>>> Hi all,
>>>> 
>>>> I have one question about altering schema. If we only add columns, is it 
>>>> ok to alter the schema while the writes to the table are happening at the 
>>>> same time? We can control that the writes will not touch the new columns 
>>>> until the schema change is done. Or better to stop the writes to that 
>>>> table first.
>>>> 
>>>> Thanks!
>>>> 
>>>> Li
>>>> -
>>>> To unsubscribe, e-mail: user-unsubscr...@cassandra.apache.org
>>>> For additional commands, e-mail: user-h...@cassandra.apache.org
>>>> 
>>> 
>> 

Re: Alter table

[Visa]

Thanks for all the inputs! We’ll stick to the current approach then.

How about dropping columns - If we also stop writes beforehand, we should be 
safe from data alignment issue after dropping columns?

Thanks,
Li

> On Jul 31, 2018, at 04:14, James Shaw  wrote:
> 
> in a heavy transaction PROD env, it is risk, considering c* has a lot of bugs.
> the DDL has to be replicated to all nodes,  use nodetool describecluster to 
> check schema version same on all nodes, if not,  you may restart that node 
> which DDL not replicated.
> in new version, DDL is none or all,  you may not get it success.
> 
> It is similar to rdbms,  alter table in a heavy transaction PROD env, may get 
> resource busy error.
> 
> in non-prod, we always apply new DDL without stop applications, never had 
> issue.
> 
> Thanks,
> 
> James
> 
> 
>> On Tue, Jul 31, 2018 at 1:37 AM, Jeff Jirsa  wrote:
>> This is safe (and normal, and good) in all versions except those impacted by 
>> https://issues.apache.org/jira/browse/CASSANDRA-13004 
>> 
>> So if you're on 2.1, 2.2, or 3.11 you're fine
>> 
>> If you're on 3.0 between 3.0.0 and 3.0.13, you should upgrade first (to 
>> newest 3.0, probably 3.0.17)
>> If you're on a version between 3.1 and 3.10, you should upgrade first (to 
>> newest 3.11, probably 3.11.3)
>> 
>> - Jeff
>> 
>> 
>>> On Mon, Jul 30, 2018 at 10:16 PM, Visa  wrote:
>>> Hi all,
>>> 
>>> I have one question about altering schema. If we only add columns, is it ok 
>>> to alter the schema while the writes to the table are happening at the same 
>>> time? We can control that the writes will not touch the new columns until 
>>> the schema change is done. Or better to stop the writes to that table first.
>>> 
>>> Thanks!
>>> 
>>> Li
>>> -
>>> To unsubscribe, e-mail: user-unsubscr...@cassandra.apache.org
>>> For additional commands, e-mail: user-h...@cassandra.apache.org
>>> 
>> 
> 

Re: Alter table

[James Shaw]

in a heavy transaction PROD env, it is risk, considering c* has a lot of
bugs.
the DDL has to be replicated to all nodes,  use nodetool describecluster to
check schema version same on all nodes, if not,  you may restart that node
which DDL not replicated.
in new version, DDL is none or all,  you may not get it success.

It is similar to rdbms,  alter table in a heavy transaction PROD env, may
get resource busy error.

in non-prod, we always apply new DDL without stop applications, never had
issue.

Thanks,

James


On Tue, Jul 31, 2018 at 1:37 AM, Jeff Jirsa  wrote:

> This is safe (and normal, and good) in all versions except those impacted
> by https://issues.apache.org/jira/browse/CASSANDRA-13004
>
> So if you're on 2.1, 2.2, or 3.11 you're fine
>
> If you're on 3.0 between 3.0.0 and 3.0.13, you should upgrade first (to
> newest 3.0, probably 3.0.17)
> If you're on a version between 3.1 and 3.10, you should upgrade first (to
> newest 3.11, probably 3.11.3)
>
> - Jeff
>
>
> On Mon, Jul 30, 2018 at 10:16 PM, Visa  wrote:
>
>> Hi all,
>>
>> I have one question about altering schema. If we only add columns, is it
>> ok to alter the schema while the writes to the table are happening at the
>> same time? We can control that the writes will not touch the new columns
>> until the schema change is done. Or better to stop the writes to that table
>> first.
>>
>> Thanks!
>>
>> Li
>> -
>> To unsubscribe, e-mail: user-unsubscr...@cassandra.apache.org
>> For additional commands, e-mail: user-h...@cassandra.apache.org
>>
>>
>

Re: Alter table

[Jeff Jirsa]

This is safe (and normal, and good) in all versions except those impacted
by https://issues.apache.org/jira/browse/CASSANDRA-13004

So if you're on 2.1, 2.2, or 3.11 you're fine

If you're on 3.0 between 3.0.0 and 3.0.13, you should upgrade first (to
newest 3.0, probably 3.0.17)
If you're on a version between 3.1 and 3.10, you should upgrade first (to
newest 3.11, probably 3.11.3)

- Jeff


On Mon, Jul 30, 2018 at 10:16 PM, Visa  wrote:

> Hi all,
>
> I have one question about altering schema. If we only add columns, is it
> ok to alter the schema while the writes to the table are happening at the
> same time? We can control that the writes will not touch the new columns
> until the schema change is done. Or better to stop the writes to that table
> first.
>
> Thanks!
>
> Li
> -
> To unsubscribe, e-mail: user-unsubscr...@cassandra.apache.org
> For additional commands, e-mail: user-h...@cassandra.apache.org
>
>

Alter table

[Visa]

Hi all,

I have one question about altering schema. If we only add columns, is it ok to 
alter the schema while the writes to the table are happening at the same time? 
We can control that the writes will not touch the new columns until the schema 
change is done. Or better to stop the writes to that table first.

Thanks!

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

Re: Alter table gc_grace_seconds

[Gábor Auth]

Hi,

On Wed, Oct 4, 2017 at 8:39 AM Oleksandr Shulgin <
oleksandr.shul...@zalando.de> wrote:

> If you have migrated ALL the data from the old CF, you could just use
> TRUNCATE or DROP TABLE, followed by "nodetool clearsnapshot" to reclaim the
> disk space (this step has to be done per-node).
>

Unfortunately not all the data migrated, but to CFs came from one CF:
"only" the 80% for data migrated to another CF.

At the moment, the compaction works with:
compaction = {'class':
'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy',
'max_threshold': '32', 'min_threshold': '2'} AND gc_grace_seconds = 172800

But I don't know why it did not work and why it works now... :)

Bye,
Auth Gábor

Re: Alter table gc_grace_seconds

[Oleksandr Shulgin]

On Sun, Oct 1, 2017 at 4:14 PM, Gábor Auth  wrote:

> Hi,
>
> On Sun, Oct 1, 2017 at 3:44 PM Varun Barala 
> wrote:
>
>> This is the property of table and It's not written in sstables. If you
>> change gc_grace, It'll get applied for all the data.
>>
>
> Hm... I've migrated lot of data from `number_item` to `measurement` CF
> because of scheme upgrade. During the migration, the script created rows in
> the `measurement` CF and deleted the migrated rows in the `number_item` CF,
> one-by-one.
>
> I've just take a look on the sstables of `number_item` and it is full of
> deleted rows:
> {
>   "type" : "row",
>   "position" : 146160,
>   "clustering" : [ "humidity", "97781fd0-9dab-11e7-a3d5-7f6ef9a844c7" ],
>   "deletion_info" : { "marked_deleted" : "2017-09-25T11:51:19.165276Z",
> "local_delete_time" : "2017-09-25T11:51:19Z" },
>   "cells" : [ ]
> }
>
> How can I purge these old rows? :)
>
> I've tried: compact, scrub, cleanup, clearsnapshot, flush and full repair.
>

If you have migrated ALL the data from the old CF, you could just use
TRUNCATE or DROP TABLE, followed by "nodetool clearsnapshot" to reclaim the
disk space (this step has to be done per-node).

--
Alex

Re: Alter table gc_grace_seconds

[Gábor Auth]

Hi,

On Mon, Oct 2, 2017 at 1:43 PM Varun Barala <varunbaral...@gmail.com> wrote:

> Either you can change min_threshold to three in your case or you can
> change compaction strategy for this table.
>

I've changed:
alter table number_item with compaction = {'class':
'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy',
'max_threshold': '32', 'min_threshold': '2'};

The list of database files of `number_item` on one node:
-rw-r--r-- 1 cassandra cassandra 71353717 Oct  2 11:53 mc-48399-big-Data.db
-rw-r--r-- 1 cassandra cassandra 32843468 Oct  2 19:15 mc-48435-big-Data.db
-rw-r--r-- 1 cassandra cassandra 24734857 Oct  2 19:49 mc-48439-big-Data.db

I've initiated a compaction on the `number_item` CF. After the compaction:
-rw-r--r-- 1 cassandra cassandra 71353717 Oct  2 11:53 mc-48399-big-Data.db
-rw-r--r-- 1 cassandra cassandra 32843468 Oct  2 19:15 mc-48435-big-Data.db
-rw-r--r-- 1 cassandra cassandra 24734857 Oct  2 19:53 mc-48440-big-Data.db

Two of them untouched and one rewritten with the same content. :/

Bye,
Gábor Auth

Re: Alter table gc_grace_seconds

[Varun Barala]

https://docs.datastax.com/en/cassandra/3.0/cassandra/operations/opsConfigureCompaction.html

As pe STCS:
*This strategy triggers a minor compaction when there are a number of
similar sized SSTables on disk as configured by the table subproperty,
min_threshold.*
In your case you have *min_threshold=4 and 3 Big sstable and one small
sstable.*
Either you can change min_threshold to three in your case or you can change
compaction strategy for this table.

Thanks!!

On Mon, Oct 2, 2017 at 4:48 PM, Steinmaurer, Thomas <
thomas.steinmau...@dynatrace.com> wrote:

> Hello Justin,
>
>
>
> yes, but in real-world, hard to accomplish for high volume column families
> >= 3-digit GB. Even with the default of 10 days grace period, this may get
> a real challenge, to complete a full repair. J
>
>
>
> Possibly back again to the discussion that incremental repair has some
> flaws, full repair (-full option) in 3.0+ (2.2+?) isn’t behaving the same
> way as in 2.1 anymore, due to troubles when kicking off with –pr on several
> nodes at once.
>
>
>
> Regards,
>
> Thomas
>
>
>
> *From:* Justin Cameron [mailto:jus...@instaclustr.com]
> *Sent:* Montag, 02. Oktober 2017 08:32
> *To:* user@cassandra.apache.org
> *Subject:* Re: Alter table gc_grace_seconds
>
>
>
> >> * You should not try on real clusters directly.
>
> >Why not? :)
>
> It's highly recommended that you complete a full repair before the GC
> grace period expires, otherwise it's possible you could experience zombie
> data (i.e. data that was previously deleted coming back to life)
>
> See http://thelastpickle.com/blog/2016/07/27/about-deletes-
> and-tombstones.html for a good overview of the problem
>
>
>
>
>
> On Mon, 2 Oct 2017 at 16:51 Gábor Auth <auth.ga...@gmail.com> wrote:
>
> Hi,
>
> On Mon, Oct 2, 2017 at 5:55 AM Varun Barala <varunbaral...@gmail.com>
> wrote:
>
> *select gc_grace_seconds from system_schema.tables where keyspace_name =
> 'keyspace' and table_name = 'number_item;*
>
>
>
> cassandra@cqlsh:mat> DESCRIBE TABLE mat.number_item;
>
>
> CREATE TABLE mat.number_item (
>nodeid uuid,
>type text,
>created timeuuid,
>value float,
>PRIMARY KEY (nodeid, type, created)
> ) WITH CLUSTERING ORDER BY (type ASC, created ASC)
>AND bloom_filter_fp_chance = 0.01
>AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
>AND cdc = false
>AND comment = ''
>AND compaction = {'class': 
> 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy',
> 'max_threshold': '32', 'min_threshold': '4'}
>AND compression = {'chunk_length_in_kb': '64', 'class': '
> org.apache.cassandra.io.compress.LZ4Compressor'}
>AND crc_check_chance = 1.0
>AND dclocal_read_repair_chance = 0.1
>AND default_time_to_live = 0
>AND gc_grace_seconds = 3600
>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 = '99PERCENTILE';
>
> cassandra@cqlsh:mat> select gc_grace_seconds from system_schema.tables
> where keyspace_name = 'mat' and table_name = 'number_item';
>
> *gc_grace_seconds*
> --
> *3600*
>
> (1 rows)
>
>
> Bye,
> Gábor Auth
>
> --
>
> *Justin Cameron*
> Senior Software Engineer
>
>
>
> [image: Image removed by sender.] <https://www.instaclustr.com/>
>
>
> This email has been sent on behalf of Instaclustr Pty. Limited (Australia)
> and Instaclustr Inc (USA).
>
> This email and any attachments may contain confidential and legally
> privileged information.  If you are not the intended recipient, do not copy
> or disclose its content, but please reply to this email immediately and
> highlight the error to the sender and then immediately delete the message.
> The contents of this e-mail are intended for the named addressee only. It
> contains information that may be confidential. Unless you are the named
> addressee or an authorized designee, you may not copy or use it, or
> disclose it to anyone else. If you received it in error please notify us
> immediately and then destroy it. Dynatrace Austria GmbH (registration
> number FN 91482h) is a company registered in Linz whose registered office
> is at 4040 Linz, Austria, Freistädterstraße 313
> <https://maps.google.com/?q=4040+Linz,+Austria,+Freist%C3%A4dterstra%C3%9Fe+313=gmail=g>
>

RE: Alter table gc_grace_seconds

[Steinmaurer, Thomas]

Hello Justin,

yes, but in real-world, hard to accomplish for high volume column families >= 
3-digit GB. Even with the default of 10 days grace period, this may get a real 
challenge, to complete a full repair. ☺

Possibly back again to the discussion that incremental repair has some flaws, 
full repair (-full option) in 3.0+ (2.2+?) isn’t behaving the same way as in 
2.1 anymore, due to troubles when kicking off with –pr on several nodes at once.

Regards,
Thomas

From: Justin Cameron [mailto:jus...@instaclustr.com]
Sent: Montag, 02. Oktober 2017 08:32
To: user@cassandra.apache.org
Subject: Re: Alter table gc_grace_seconds

>> * You should not try on real clusters directly.
>Why not? :)

It's highly recommended that you complete a full repair before the GC grace 
period expires, otherwise it's possible you could experience zombie data (i.e. 
data that was previously deleted coming back to life)

See http://thelastpickle.com/blog/2016/07/27/about-deletes-and-tombstones.html 
for a good overview of the problem


On Mon, 2 Oct 2017 at 16:51 Gábor Auth 
<auth.ga...@gmail.com<mailto:auth.ga...@gmail.com>> wrote:
Hi,
On Mon, Oct 2, 2017 at 5:55 AM Varun Barala 
<varunbaral...@gmail.com<mailto:varunbaral...@gmail.com>> wrote:
select gc_grace_seconds from system_schema.tables where keyspace_name = 
'keyspace' and table_name = 'number_item;

cassandra@cqlsh:mat> DESCRIBE TABLE mat.number_item;

CREATE TABLE mat.number_item (
   nodeid uuid,
   type text,
   created timeuuid,
   value float,
   PRIMARY KEY (nodeid, type, created)
) WITH CLUSTERING ORDER BY (type ASC, created ASC)
   AND bloom_filter_fp_chance = 0.01
   AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
   AND cdc = false
   AND comment = ''
   AND compaction = {'class': 
'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 
'max_threshold': '32', 'min_threshold': '4'}
   AND compression = {'chunk_length_in_kb': '64', 'class': 
'org.apache.cassandra.io.compress.LZ4Compressor'}
   AND crc_check_chance = 1.0
   AND dclocal_read_repair_chance = 0.1
   AND default_time_to_live = 0
   AND gc_grace_seconds = 3600
   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 = '99PERCENTILE';

cassandra@cqlsh:mat> select gc_grace_seconds from system_schema.tables where 
keyspace_name = 'mat' and table_name = 'number_item';

gc_grace_seconds
--
3600

(1 rows)

Bye,
Gábor Auth
--
Justin Cameron
Senior Software Engineer

[Image removed by sender.]<https://www.instaclustr.com/>

This email has been sent on behalf of Instaclustr Pty. Limited (Australia) and 
Instaclustr Inc (USA).

This email and any attachments may contain confidential and legally privileged 
information.  If you are not the intended recipient, do not copy or disclose 
its content, but please reply to this email immediately and highlight the error 
to the sender and then immediately delete the message.
The contents of this e-mail are intended for the named addressee only. It 
contains information that may be confidential. Unless you are the named 
addressee or an authorized designee, you may not copy or use it, or disclose it 
to anyone else. If you received it in error please notify us immediately and 
then destroy it. Dynatrace Austria GmbH (registration number FN 91482h) is a 
company registered in Linz whose registered office is at 4040 Linz, Austria, 
Freistädterstraße 313

Re: Alter table gc_grace_seconds

[Gábor Auth]

Hi,

On Mon, Oct 2, 2017 at 8:41 AM Varun Barala  wrote:

> Might be possible C* is not compacting the sstables [
> https://stackoverflow.com/questions/28437301/cassandra-not-compacting-sstables
> ]
>

Oh, the other CF-s in the same keyspace are compacted, but the
`number_item` not.

[cassandra@dc02-rack01-cass01
number_item-524bf49001d911e798503511c5f98764]$ ls -l
total 172704
drwxr-xr-x 4 cassandra cassandra 4096 Oct  1 10:43 backups
-rw-r--r-- 1 cassandra cassandra15227 Oct  2 01:15
mc-48278-big-CompressionInfo.db
-rw-r--r-- 1 cassandra cassandra 46562318 Oct  2 01:15 mc-48278-big-Data.db
-rw-r--r-- 1 cassandra cassandra   10 Oct  2 01:15
mc-48278-big-Digest.crc32
-rw-r--r-- 1 cassandra cassandra  176 Oct  2 01:15
mc-48278-big-Filter.db
-rw-r--r-- 1 cassandra cassandra   119665 Oct  2 01:15
mc-48278-big-Index.db
-rw-r--r-- 1 cassandra cassandra 6368 Oct  2 01:15
mc-48278-big-Statistics.db
-rw-r--r-- 1 cassandra cassandra   92 Oct  2 01:15
mc-48278-big-Summary.db
-rw-r--r-- 1 cassandra cassandra   92 Oct  2 01:15 mc-48278-big-TOC.txt
-rw-r--r-- 1 cassandra cassandra20643 Oct  2 01:16
mc-48279-big-CompressionInfo.db
-rw-r--r-- 1 cassandra cassandra 62681705 Oct  2 01:16 mc-48279-big-Data.db
-rw-r--r-- 1 cassandra cassandra   10 Oct  2 01:16
mc-48279-big-Digest.crc32
-rw-r--r-- 1 cassandra cassandra  176 Oct  2 01:16
mc-48279-big-Filter.db
-rw-r--r-- 1 cassandra cassandra   162571 Oct  2 01:16
mc-48279-big-Index.db
-rw-r--r-- 1 cassandra cassandra 6375 Oct  2 01:16
mc-48279-big-Statistics.db
-rw-r--r-- 1 cassandra cassandra   92 Oct  2 01:16
mc-48279-big-Summary.db
-rw-r--r-- 1 cassandra cassandra   92 Oct  2 01:16 mc-48279-big-TOC.txt
-rw-r--r-- 1 cassandra cassandra20099 Oct  2 01:16
mc-48280-big-CompressionInfo.db
-rw-r--r-- 1 cassandra cassandra 62587865 Oct  2 01:16 mc-48280-big-Data.db
-rw-r--r-- 1 cassandra cassandra   10 Oct  2 01:16
mc-48280-big-Digest.crc32
-rw-r--r-- 1 cassandra cassandra  176 Oct  2 01:16
mc-48280-big-Filter.db
-rw-r--r-- 1 cassandra cassandra   158379 Oct  2 01:16
mc-48280-big-Index.db
-rw-r--r-- 1 cassandra cassandra 6375 Oct  2 01:16
mc-48280-big-Statistics.db
-rw-r--r-- 1 cassandra cassandra   92 Oct  2 01:16
mc-48280-big-Summary.db
-rw-r--r-- 1 cassandra cassandra   92 Oct  2 01:16 mc-48280-big-TOC.txt
-rw-r--r-- 1 cassandra cassandra   51 Oct  2 01:16
mc-48281-big-CompressionInfo.db
-rw-r--r-- 1 cassandra cassandra   50 Oct  2 01:16 mc-48281-big-Data.db
-rw-r--r-- 1 cassandra cassandra   10 Oct  2 01:16
mc-48281-big-Digest.crc32
-rw-r--r-- 1 cassandra cassandra  176 Oct  2 01:16
mc-48281-big-Filter.db
-rw-r--r-- 1 cassandra cassandra   20 Oct  2 01:16
mc-48281-big-Index.db
-rw-r--r-- 1 cassandra cassandra 4640 Oct  2 01:16
mc-48281-big-Statistics.db
-rw-r--r-- 1 cassandra cassandra   92 Oct  2 01:16
mc-48281-big-Summary.db
-rw-r--r-- 1 cassandra cassandra   92 Oct  2 01:16 mc-48281-big-TOC.txt

Now check both the list results. If they have some common sstables then we
> can say that C* is not compacting sstables.
>

Yes, exactly. How can I fix it?

Bye,
Gábor Auth

Re: Alter table gc_grace_seconds

[Gábor Auth]

Hi,

On Mon, Oct 2, 2017 at 8:32 AM Justin Cameron 
wrote:

> >> * You should not try on real clusters directly.
> >Why not? :)
>
> It's highly recommended that you complete a full repair before the GC
> grace period expires, otherwise it's possible you could experience zombie
> data (i.e. data that was previously deleted coming back to life)
>

It is a test cluster with test keyspaces. :)

Bye,
Gábor Auth

>

Re: Alter table gc_grace_seconds

[Varun Barala]

+1 Justing,

Might be possible C* is not compacting the sstables [
https://stackoverflow.com/questions/28437301/cassandra-not-compacting-sstables
].

You can this fact by doing below procedure:-

*Run this before compaction:-*
ls /var/lib/cassandra/data/mat/number_item-*/

Store result to some file [just to keep track of sstables]


*Run compaction:-*nodetool compact mat  number_item

*List directories again:-*
ls /var/lib/cassandra/data/mat/number_item-*/

Now check both the list results. If they have some common sstables then we
can say that C* is not compacting sstables.

Thanks!!

On Mon, Oct 2, 2017 at 2:32 PM, Justin Cameron 
wrote:

> >> * You should not try on real clusters directly.
> >Why not? :)
>
> It's highly recommended that you complete a full repair before the GC
> grace period expires, otherwise it's possible you could experience zombie
> data (i.e. data that was previously deleted coming back to life)
>
> See http://thelastpickle.com/blog/2016/07/27/about-deletes-
> and-tombstones.html for a good overview of the problem
>
>
> On Mon, 2 Oct 2017 at 16:51 Gábor Auth  wrote:
>
>> Hi,
>>
>> On Mon, Oct 2, 2017 at 5:55 AM Varun Barala 
>> wrote:
>>
>>> *select gc_grace_seconds from system_schema.tables where keyspace_name =
>>> 'keyspace' and table_name = 'number_item;*
>>>
>>
>> cassandra@cqlsh:mat> DESCRIBE TABLE mat.number_item;
>>
>>
>> CREATE TABLE mat.number_item (
>>nodeid uuid,
>>type text,
>>created timeuuid,
>>value float,
>>PRIMARY KEY (nodeid, type, created)
>> ) WITH CLUSTERING ORDER BY (type ASC, created ASC)
>>AND bloom_filter_fp_chance = 0.01
>>AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
>>AND cdc = false
>>AND comment = ''
>>AND compaction = {'class': 
>> 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy',
>> 'max_threshold': '32', 'min_threshold': '4'}
>>AND compression = {'chunk_length_in_kb': '64', 'class': '
>> org.apache.cassandra.io.compress.LZ4Compressor'}
>>AND crc_check_chance = 1.0
>>AND dclocal_read_repair_chance = 0.1
>>AND default_time_to_live = 0
>>AND gc_grace_seconds = 3600
>>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 = '99PERCENTILE';
>>
>> cassandra@cqlsh:mat> select gc_grace_seconds from system_schema.tables
>> where keyspace_name = 'mat' and table_name = 'number_item';
>>
>> gc_grace_seconds
>> --
>> 3600
>>
>> (1 rows)
>>
>> Bye,
>> Gábor Auth
>>
> --
>
>
> *Justin Cameron*Senior Software Engineer
>
>
> 
>
>
> This email has been sent on behalf of Instaclustr Pty. Limited (Australia)
> and Instaclustr Inc (USA).
>
> This email and any attachments may contain confidential and legally
> privileged information.  If you are not the intended recipient, do not copy
> or disclose its content, but please reply to this email immediately and
> highlight the error to the sender and then immediately delete the message.
>

Re: Alter table gc_grace_seconds

[Justin Cameron]

>> * You should not try on real clusters directly.
>Why not? :)

It's highly recommended that you complete a full repair before the GC grace
period expires, otherwise it's possible you could experience zombie data
(i.e. data that was previously deleted coming back to life)

See
http://thelastpickle.com/blog/2016/07/27/about-deletes-and-tombstones.html for
a good overview of the problem


On Mon, 2 Oct 2017 at 16:51 Gábor Auth  wrote:

> Hi,
>
> On Mon, Oct 2, 2017 at 5:55 AM Varun Barala 
> wrote:
>
>> *select gc_grace_seconds from system_schema.tables where keyspace_name =
>> 'keyspace' and table_name = 'number_item;*
>>
>
> cassandra@cqlsh:mat> DESCRIBE TABLE mat.number_item;
>
>
> CREATE TABLE mat.number_item (
>nodeid uuid,
>type text,
>created timeuuid,
>value float,
>PRIMARY KEY (nodeid, type, created)
> ) WITH CLUSTERING ORDER BY (type ASC, created ASC)
>AND bloom_filter_fp_chance = 0.01
>AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
>AND cdc = false
>AND comment = ''
>AND compaction = {'class':
> 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy',
> 'max_threshold': '32', 'min_threshold': '4'}
>AND compression = {'chunk_length_in_kb': '64', 'class':
> 'org.apache.cassandra.io.compress.LZ4Compressor'}
>AND crc_check_chance = 1.0
>AND dclocal_read_repair_chance = 0.1
>AND default_time_to_live = 0
>AND gc_grace_seconds = 3600
>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 = '99PERCENTILE';
>
> cassandra@cqlsh:mat> select gc_grace_seconds from system_schema.tables
> where keyspace_name = 'mat' and table_name = 'number_item';
>
> gc_grace_seconds
> --
> 3600
>
> (1 rows)
>
> Bye,
> Gábor Auth
>
-- 


*Justin Cameron*Senior Software Engineer





This email has been sent on behalf of Instaclustr Pty. Limited (Australia)
and Instaclustr Inc (USA).

This email and any attachments may contain confidential and legally
privileged information.  If you are not the intended recipient, do not copy
or disclose its content, but please reply to this email immediately and
highlight the error to the sender and then immediately delete the message.

Re: Alter table gc_grace_seconds

[Gábor Auth]

Hi,

On Mon, Oct 2, 2017 at 5:55 AM Varun Barala  wrote:

> *select gc_grace_seconds from system_schema.tables where keyspace_name =
> 'keyspace' and table_name = 'number_item;*
>

cassandra@cqlsh:mat> DESCRIBE TABLE mat.number_item;


CREATE TABLE mat.number_item (
   nodeid uuid,
   type text,
   created timeuuid,
   value float,
   PRIMARY KEY (nodeid, type, created)
) WITH CLUSTERING ORDER BY (type ASC, created ASC)
   AND bloom_filter_fp_chance = 0.01
   AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
   AND cdc = false
   AND comment = ''
   AND compaction = {'class':
'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy',
'max_threshold': '32', 'min_threshold': '4'}
   AND compression = {'chunk_length_in_kb': '64', 'class':
'org.apache.cassandra.io.compress.LZ4Compressor'}
   AND crc_check_chance = 1.0
   AND dclocal_read_repair_chance = 0.1
   AND default_time_to_live = 0
   AND gc_grace_seconds = 3600
   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 = '99PERCENTILE';

cassandra@cqlsh:mat> select gc_grace_seconds from system_schema.tables
where keyspace_name = 'mat' and table_name = 'number_item';

gc_grace_seconds
--
3600

(1 rows)

Bye,
Gábor Auth

Re: Alter table gc_grace_seconds

[Varun Barala]

Can you share result of query:-

*select gc_grace_seconds from system_schema.tables where keyspace_name =
'keyspace' and table_name = 'number_item;*

Thanks!!

On Mon, Oct 2, 2017 at 3:42 AM, Gábor Auth  wrote:

> Hi,
>
> On Sun, Oct 1, 2017 at 9:36 PM Varun Barala 
> wrote:
>
>> * You should not try on real clusters directly.
>>
>
> Why not? :)
>
> Did you change gc_grace for all column families?
>>
>
> Not, only on the `number_item` CF.
>
> > But not in the `number_item` CF... :(
>> Could you please explain?
>>
>
> I've tried the test case that you described and it is works (the compact
> removed the marked_deleted rows) on a newly created CF. But the same
> gc_grace_seconds settings has no effect in the `number_item` CF (millions
> of rows has been deleted during a last week migration).
>
> Bye,
> Gábor Auth
>
>

Re: Alter table gc_grace_seconds

[Gábor Auth]

Hi,

On Sun, Oct 1, 2017 at 9:36 PM Varun Barala  wrote:

> * You should not try on real clusters directly.
>

Why not? :)

Did you change gc_grace for all column families?
>

Not, only on the `number_item` CF.

> But not in the `number_item` CF... :(
> Could you please explain?
>

I've tried the test case that you described and it is works (the compact
removed the marked_deleted rows) on a newly created CF. But the same
gc_grace_seconds settings has no effect in the `number_item` CF (millions
of rows has been deleted during a last week migration).

Bye,
Gábor Auth

Re: Alter table gc_grace_seconds

[Varun Barala]

* You should not try on real clusters directly.

Did you change gc_grace for all column families?

> But not in the `number_item` CF... :(
Could you please explain?

Thanks!!

On Mon, Oct 2, 2017 at 2:24 AM, Gábor Auth  wrote:

> Hi,
>
> On Sun, Oct 1, 2017 at 7:44 PM Varun Barala 
> wrote:
>
>> Sorry If I misunderstood the situation.
>>
>
> Ok, I'm confused... :/
>
> I've just tested it on the same cluster and the compact removed the
> marked_deleted rows. But not in the `number_item` CF... :(
>
> Cassandra 3.11.0, two DC (with 4-4 nodes).
>
> Bye,
> Gábor Auth
>

Re: Alter table gc_grace_seconds

[Gábor Auth]

Hi,

On Sun, Oct 1, 2017 at 7:44 PM Varun Barala  wrote:

> Sorry If I misunderstood the situation.
>

Ok, I'm confused... :/

I've just tested it on the same cluster and the compact removed the
marked_deleted rows. But not in the `number_item` CF... :(

Cassandra 3.11.0, two DC (with 4-4 nodes).

Bye,
Gábor Auth

Re: Alter table gc_grace_seconds

[Varun Barala]

Sorry If I misunderstood the situation.


{
  "type" : "row",
  "position" : 146160,
  "clustering" : [ "humidity", "97781fd0-9dab-11e7-a3d5-7f6ef9a844c7" ],
  "deletion_info" : { "marked_deleted" : "2017-09-25T11:51:19.165276Z",
"local_delete_time" : "2017-09-25T11:51:19Z" },
  "cells" : [ ]
}


How did we come to know that TTL is applied by looking at this data?


I performed a basic test in my local:-


*# C* version* 3.0.14,

*# Table schema:-*
CREATE TABLE test.table1 (
id int,
ck1 text,
ck2 text,
nk1 text,
PRIMARY KEY (id,ck1, ck2)
);


*# insert statement:-*
insert into test.table1 (id, ck1, ck2, nk1) VALUES (1,'1','1','1');


*# performed flush to generate the sstable(json format):-*
  {
"partition" : {
  "key" : [ "1" ],
  "position" : 0
},
"rows" : [
  {
"type" : "row",
"position" : 30,
"clustering" : [ "1", "1" ],
"liveness_info" : { "tstamp" : "2017-10-01T17:28:56.889Z" },
"cells" : [
  { "name" : "nk1", "value" : "1" }
]
  }
]
  }

*# delete statement:-*
delete from test.table1 where id=1 and ck1='1' and ck2='1';

*# performed flush to generate new sstable(json format):-*
 {
"partition" : {
  "key" : [ "1" ],
  "position" : 0
},
"rows" : [
  {
"type" : "row",
"position" : 32,
"clustering" : [ "1", "1" ],
"deletion_info" : { "marked_deleted" : "2017-10-01T17:30:15.397Z",
"local_delete_time" : "2017-10-01T17:30:15Z" },
"cells" : [ ]
  }
    ]
  }


*# performed compaction to compact above two sstables (latest won,
tombstone has not been purged):-*  {
"partition" : {
  "key" : [ "1" ],
  "position" : 0
},
"rows" : [
  {
"type" : "row",
"position" : 32,
"clustering" : [ "1", "1" ],
"deletion_info" : { "marked_deleted" : "2017-10-01T17:30:15.397Z",
"local_delete_time" : "2017-10-01T17:30:15Z" },
"cells" : [ ]
  }
]
  }

*# changed gc_grace:-*
alter TABLE  test.table1 WITH gc_grace_seconds = 0;

*# performed compaction again(no sstable exists, since it has purged the
tombstones older than gc_grace):-*
no sstable exist in data dir


Please let me know If I'm missing some fact.

Thanks!!

On Mon, Oct 2, 2017 at 1:11 AM, Gábor Auth <auth.ga...@gmail.com> wrote:

> Hi,
>
> On Sun, Oct 1, 2017 at 6:53 PM Jonathan Haddad <j...@jonhaddad.com> wrote:
>
>> The TTL is applied to the cells on insert. Changing it doesn't change the
>> TTL on data that was inserted previously.
>>
>
> Is there any way to purge out these tombstoned data?
>
> Bye,
> Gábor Auth
>

Re: Alter table gc_grace_seconds

[Gábor Auth]

Hi,

On Sun, Oct 1, 2017 at 6:53 PM Jonathan Haddad  wrote:

> The TTL is applied to the cells on insert. Changing it doesn't change the
> TTL on data that was inserted previously.
>

Is there any way to purge out these tombstoned data?

Bye,
Gábor Auth

Re: Alter table gc_grace_seconds

[Gábor Auth]

Hi,

On Sun, Oct 1, 2017 at 6:53 PM Jonathan Haddad  wrote:

> The TTL is applied to the cells on insert. Changing it doesn't change the
> TTL on data that was inserted previously.
>

Oh! So that the tombstoned cell's TTL is equals with the CF's
gc_grace_seconds value and the repair will be remove it. Am I right?

Bye,
Gábor Auth

Re: Alter table gc_grace_seconds

[Jonathan Haddad]

The TTL is applied to the cells on insert. Changing it doesn't change the
TTL on data that was inserted previously.

On Sun, Oct 1, 2017 at 6:23 AM Gábor Auth <auth.ga...@gmail.com> wrote:

> Hi,
>
> The `alter table number_item with gc_grace_seconds = 3600;` is sets the
> grace seconds of tombstones of the future modification of number_item
> column family or affects all existing data?
>
> Bye,
> Gábor Auth
>
>

Re: Alter table gc_grace_seconds

[Varun Barala]

* Which C* version are you using?
* How many nodes are there in this cluster?

These tombstones will not be deleted if they are not older than
gc_grace_seconds.


On Sun, Oct 1, 2017 at 10:14 PM, Gábor Auth  wrote:

> Hi,
>
> On Sun, Oct 1, 2017 at 3:44 PM Varun Barala 
> wrote:
>
>> This is the property of table and It's not written in sstables. If you
>> change gc_grace, It'll get applied for all the data.
>>
>
> Hm... I've migrated lot of data from `number_item` to `measurement` CF
> because of scheme upgrade. During the migration, the script created rows in
> the `measurement` CF and deleted the migrated rows in the `number_item` CF,
> one-by-one.
>
> I've just take a look on the sstables of `number_item` and it is full of
> deleted rows:
> {
>   "type" : "row",
>   "position" : 146160,
>   "clustering" : [ "humidity", "97781fd0-9dab-11e7-a3d5-7f6ef9a844c7" ],
>   "deletion_info" : { "marked_deleted" : "2017-09-25T11:51:19.165276Z",
> "local_delete_time" : "2017-09-25T11:51:19Z" },
>   "cells" : [ ]
> }
>
> How can I purge these old rows? :)
>
> I've tried: compact, scrub, cleanup, clearsnapshot, flush and full repair.
>
> Bye,
> Gábor Auth
>
>

Re: Alter table gc_grace_seconds

[Gábor Auth]

Hi,

On Sun, Oct 1, 2017 at 3:44 PM Varun Barala  wrote:

> This is the property of table and It's not written in sstables. If you
> change gc_grace, It'll get applied for all the data.
>

Hm... I've migrated lot of data from `number_item` to `measurement` CF
because of scheme upgrade. During the migration, the script created rows in
the `measurement` CF and deleted the migrated rows in the `number_item` CF,
one-by-one.

I've just take a look on the sstables of `number_item` and it is full of
deleted rows:
{
  "type" : "row",
  "position" : 146160,
  "clustering" : [ "humidity", "97781fd0-9dab-11e7-a3d5-7f6ef9a844c7" ],
  "deletion_info" : { "marked_deleted" : "2017-09-25T11:51:19.165276Z",
"local_delete_time" : "2017-09-25T11:51:19Z" },
  "cells" : [ ]
}

How can I purge these old rows? :)

I've tried: compact, scrub, cleanup, clearsnapshot, flush and full repair.

Bye,
Gábor Auth

Re: Alter table gc_grace_seconds

[Varun Barala]

Hi,
This is the property of table and It's not written in sstables. If you
change gc_grace, It'll get applied for all the data. Thanks!!

C* stores this info inside schema_columnfamilies

Regards,
Varun Barala

On Sun, Oct 1, 2017 at 9:23 PM, Gábor Auth <auth.ga...@gmail.com> wrote:

> Hi,
>
> The `alter table number_item with gc_grace_seconds = 3600;` is sets the
> grace seconds of tombstones of the future modification of number_item
> column family or affects all existing data?
>
> Bye,
> Gábor Auth
>
>

Alter table gc_grace_seconds

[Gábor Auth]

Hi,

The `alter table number_item with gc_grace_seconds = 3600;` is sets the
grace seconds of tombstones of the future modification of number_item
column family or affects all existing data?

Bye,
Gábor Auth

ALTER TABLE implementation details

[Mikhail Sokolov]

Hi!
I'm using Cassandra 2.2 for my home-grown SAAS and currently
evaluating prospect of adding columns to tables on user requests.
So I have a couple of questions regarding ALTER TABLE statement:

1) how adding/removing columns implemented on cluster level?
2) in case of column addition how soon after executing the ALTER TABLE
statement updates to the newly added column can be sent?
2) how often a user can add/remove columns without affecting the
cluster's performance?

-- 
Mikhail Sokolov

Re: CQL Alter table does not propagate to all nodes.

[Erick Ramirez]

Lu,

Thanks for the letting me know that you figured it out.

Cheers,
Erick

Re: CQL Alter table does not propagate to all nodes.

[dlu66061]

Thank you very much, Erick. 

Yes, we are using NTP. But your other suggestions and links are very
helpful. I tried to grep MigrationStage from system.log and found Can't
send migration request: node /201.20.32.54 is down. around the time I ran
the CQL, although that server is actually up running fine. My instances are
on AWS and I think I should adjust the phi_convict_threshold property
higher.



--
View this message in context: 
http://cassandra-user-incubator-apache-org.3065146.n2.nabble.com/CQL-Alter-table-does-not-propagate-to-all-nodes-tp7597211p7597242.html
Sent from the cassandra-u...@incubator.apache.org mailing list archive at 
Nabble.com.

Re: alter table issues on 2.0.10

[DuyHai Doan]

 of this kind on
 cassandra 2.0.10

 ALTER TABLE notifications add unread_ids settimeuuid static

 ErrorMessage code= [Server error]
 message=java.lang.RuntimeException:
 java.util.concurrent.ExecutionException: java.lang.RuntimeException:
 org.apache.cassandra.exceptions.ConfigurationException: comparators do not
 match or are not compatible.

 Weird enough DESCRIBE COLUMNFAMILY notifications; shows that the column
 unread_ids is created after the error.

 Any idea if this is an actual bug or if I am doing something wrong?

 Tommaso

CQL Alter table does not propagate to all nodes.

[dlu66061]

I have a 3-node cluster running Cassandra 2.0.6 on CentOS 6.5, with Jave
1.7.0_51. 

I ran a CQL statement like alter table table_name with
gc_grace_seconds=864000; on node 1 in CQLSH, and it finished
instantaneously. desc keyspace listed the table with the new value for
gc_grace_seconds, and select * from system.schema_columnfamilies also
reveals the new value.

Then I ran CQLSH on the other two nodes. Node 2 got the change right away,
while node 3 still has the old value, even after 2 hours. As soon as I
restarted the Cassandra service on node 3, the new value showed up.

I have encountered this issue a couple of times. I searched this user group,
and saw two similar issues (
http://cassandra-user-incubator-apache-org.3065146.n2.nabble.com/default-time-to-live-tt7590662.html#a7590677
http://cassandra-user-incubator-apache-org.3065146.n2.nabble.com/default-time-to-live-tt7590662.html#a7590677
  
and 
http://cassandra-user-incubator-apache-org.3065146.n2.nabble.com/Notes-and-questions-from-performing-a-large-delete-tt7591649.html
http://cassandra-user-incubator-apache-org.3065146.n2.nabble.com/Notes-and-questions-from-performing-a-large-delete-tt7591649.html
 
). However, I did not find a clear answer.

Is this a bug? Is there something wrong with my settings? I don't feel this
should be the normal behavior.




--
View this message in context: 
http://cassandra-user-incubator-apache-org.3065146.n2.nabble.com/CQL-Alter-table-does-not-propagate-to-all-nodes-tp7597211.html
Sent from the cassandra-u...@incubator.apache.org mailing list archive at 
Nabble.com.

Re: CQL Alter table does not propagate to all nodes.

[Erick Ramirez]

Hello, dlu66061.

A common issue with schema disagreements is time drift on the nodes. Are
you using NTP?

The only other issue is when the nodes are not reachable at the time that
the schema update was being propagated ---
http://www.datastax.com/documentation/cassandra/2.0/cassandra/dml/dml_handle_schema_disagree_t.html

You should be able to see propagation by MigrationStage in the system.log
by grepping for the the schema UUID that is listed in nodetool
describecluster.

Depending on the state of the node that didn't get the update, you could
have also tried nodetool resetlocalschema which effectively deletes the
schema metadata on that node and forces it to get a copy from one of the
other nodes.

Cheers,
Erick

alter table issues on 2.0.10

[tommaso barbugli]

Hi,
I am seeing errors every time I make a schema migration of this kind on
cassandra 2.0.10

ALTER TABLE notifications add unread_ids settimeuuid static

ErrorMessage code= [Server error] message=java.lang.RuntimeException:
java.util.concurrent.ExecutionException: java.lang.RuntimeException:
org.apache.cassandra.exceptions.ConfigurationException: comparators do not
match or are not compatible.

Weird enough DESCRIBE COLUMNFAMILY notifications; shows that the column
unread_ids is created after the error.

Any idea if this is an actual bug or if I am doing something wrong?

Tommaso

Re: alter table issues on 2.0.10

[tommaso barbugli]

Hi,
it actually seems to be worse than what I thought; I get an exception in
cassandra logs every time I try to create a new table.

Cql query:
CREATE TABLE shard12 (feed_id ascii, activity_id timeuuid, activities
blob, created_at timestamp, group ascii, updated_at timestamp,
seen_at timestamp, read_at timestamp, PRIMARY KEY ((feed_id),
activity_id)) WITH read_repair_chance = 0.1 AND clustering order by
(activity_id desc) AND compaction = {'sstable_size_in_mb': 64, 'class':
'LeveledCompactionStrategy'};

This is the error:
java.lang.RuntimeException: java.util.concurrent.ExecutionException:
java.lang.RuntimeException:
org.apache.cassandra.exceptions.ConfigurationException: comparators do not
match or are not compatible.

Tommaso

2014-10-12 13:05 GMT+02:00 tommaso barbugli tbarbu...@gmail.com:

 Hi,
 I am seeing errors every time I make a schema migration of this kind on
 cassandra 2.0.10

 ALTER TABLE notifications add unread_ids settimeuuid static

 ErrorMessage code= [Server error]
 message=java.lang.RuntimeException:
 java.util.concurrent.ExecutionException: java.lang.RuntimeException:
 org.apache.cassandra.exceptions.ConfigurationException: comparators do not
 match or are not compatible.

 Weird enough DESCRIBE COLUMNFAMILY notifications; shows that the column
 unread_ids is created after the error.

 Any idea if this is an actual bug or if I am doing something wrong?

 Tommaso

Re: alter table issues on 2.0.10

[tommaso barbugli]

Here is the full stack trace

ERROR [Native-Transport-Requests:5802] 2014-10-12 22:48:12,237
QueryMessage.java (line 131) Unexpected error during query
java.lang.RuntimeException: java.util.concurrent.ExecutionException:
java.lang.RuntimeException:
org.apache.cassandra.exceptions.ConfigurationException: comparators do not
match or are not compatible.
at org.apache.cassandra.utils.FBUtilities.waitOnFuture(FBUtilities.java:413)
at
org.apache.cassandra.service.MigrationManager.announce(MigrationManager.java:285)
at
org.apache.cassandra.service.MigrationManager.announceNewColumnFamily(MigrationManager.java:223)
at
org.apache.cassandra.cql3.statements.CreateTableStatement.announceMigration(CreateTableStatement.java:121)
at
org.apache.cassandra.cql3.statements.SchemaAlteringStatement.execute(SchemaAlteringStatement.java:79)
at
org.apache.cassandra.cql3.QueryProcessor.processStatement(QueryProcessor.java:158)
at org.apache.cassandra.cql3.QueryProcessor.process(QueryProcessor.java:175)
at
org.apache.cassandra.transport.messages.QueryMessage.execute(QueryMessage.java:119)
at
org.apache.cassandra.transport.Message$Dispatcher.messageReceived(Message.java:306)
at
org.jboss.netty.channel.SimpleChannelUpstreamHandler.handleUpstream(SimpleChannelUpstreamHandler.java:70)
at
org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:564)
at
org.jboss.netty.channel.DefaultChannelPipeline$DefaultChannelHandlerContext.sendUpstream(DefaultChannelPipeline.java:791)
at
org.jboss.netty.handler.execution.ChannelUpstreamEventRunnable.doRun(ChannelUpstreamEventRunnable.java:43)
at
org.jboss.netty.handler.execution.ChannelEventRunnable.run(ChannelEventRunnable.java:67)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:744)
Caused by: java.util.concurrent.ExecutionException:
java.lang.RuntimeException:
org.apache.cassandra.exceptions.ConfigurationException: comparators do not
match or are not compatible.
at java.util.concurrent.FutureTask.report(FutureTask.java:122)
at java.util.concurrent.FutureTask.get(FutureTask.java:188)
at org.apache.cassandra.utils.FBUtilities.waitOnFuture(FBUtilities.java:409)
... 16 more
Caused by: java.lang.RuntimeException:
org.apache.cassandra.exceptions.ConfigurationException: comparators do not
match or are not compatible.
at org.apache.cassandra.config.CFMetaData.reload(CFMetaData.java:1052)
at
org.apache.cassandra.db.DefsTables.updateColumnFamily(DefsTables.java:377)
at
org.apache.cassandra.db.DefsTables.mergeColumnFamilies(DefsTables.java:318)
at org.apache.cassandra.db.DefsTables.mergeSchema(DefsTables.java:183)
at
org.apache.cassandra.service.MigrationManager$2.runMayThrow(MigrationManager.java:303)
at org.apache.cassandra.utils.WrappedRunnable.run(WrappedRunnable.java:28)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
... 3 more
Caused by: org.apache.cassandra.exceptions.ConfigurationException:
comparators do not match or are not compatible.
at
org.apache.cassandra.config.CFMetaData.validateCompatility(CFMetaData.java:1142)
at org.apache.cassandra.config.CFMetaData.apply(CFMetaData.java:1067)
at org.apache.cassandra.config.CFMetaData.reload(CFMetaData.java:1048)
... 10 more

2014-10-12 23:59 GMT+02:00 DuyHai Doan doanduy...@gmail.com:

 Hello Tommaso

  Can you give the full exception stack trace? Consider also filing a JIRA,
 it looks pretty fishy...

 On Sun, Oct 12, 2014 at 10:53 PM, tommaso barbugli tbarbu...@gmail.com
 wrote:

 Hi,
 it actually seems to be worse than what I thought; I get an exception in
 cassandra logs every time I try to create a new table.

 Cql query:
 CREATE TABLE shard12 (feed_id ascii, activity_id timeuuid,
 activities blob, created_at timestamp, group ascii, updated_at
 timestamp, seen_at timestamp, read_at timestamp, PRIMARY KEY
 ((feed_id), activity_id)) WITH read_repair_chance = 0.1 AND clustering
 order by (activity_id desc) AND compaction = {'sstable_size_in_mb': 64,
 'class': 'LeveledCompactionStrategy'};

 This is the error:
 java.lang.RuntimeException: java.util.concurrent.ExecutionException:
 java.lang.RuntimeException:
 org.apache.cassandra.exceptions.ConfigurationException: comparators do not
 match or are not compatible.

 Tommaso

 2014-10-12 13:05 GMT+02:00 tommaso barbugli tbarbu...@gmail.com:

 Hi,
 I am seeing errors every time I make a schema migration of this kind on
 cassandra 2.0.10

 ALTER TABLE notifications add unread_ids settimeuuid static

 ErrorMessage code= [Server error]
 message=java.lang.RuntimeException:
 java.util.concurrent.ExecutionException: java.lang.RuntimeException:
 org.apache.cassandra.exceptions.ConfigurationException: comparators do not
 match or are not compatible.

 Weird enough DESCRIBE COLUMNFAMILY notifications; shows that the column
 unread_ids

Re: Schema disagreement under normal conditions, ALTER TABLE hangs

[Josh Dzielak]

Thanks Rob. Let me add one thing in case someone else finds this thread - 

Restarting the nodes did not in and of itself get the schema disagreement 
resolved. We had to run the ALTER TABLE command individually on each of the 
disagreeing nodes once they came back up. 

On Tuesday, November 26, 2013 at 11:24 AM, Robert Coli wrote:

 On Mon, Nov 25, 2013 at 6:42 PM, Josh Dzielak j...@keen.io 
 (mailto:j...@keen.io) wrote:
  Recently we had a strange thing happen. Altering schema (gc_grace_seconds) 
  for a column family resulted in a schema disagreement. 3/4 of nodes got it, 
  1/4 didn't. There was no partition at the time, nor was there multiple 
  schema updates issued. Going to the nodes with stale schema and trying to 
  do the ALTER TABLE there resulted in hanging. We were eventually able to 
  get schema agreement by restarting nodes, but both the initial disagreement 
  under normal conditions and the hanging ALTER TABLE seem pretty weird. Any 
  ideas here? Sound like a bug? 
 
 Yes, that sounds like a bug. This behavior is less common in 1.2.x than it 
 was previously, but still happens sometimes. It's interesting that restarting 
 the affected node helped, in previous versions of hung schema issue, it 
 would survive restart. 
  
  We're on 1.2.8.
  
 
 
 Unfortunately, unless you have a repro path, it is probably not worth 
 reporting a JIRA. 
 
 =Rob
  
 
 
 
 
 

Re: Schema disagreement under normal conditions, ALTER TABLE hangs

[Robert Coli]

On Mon, Nov 25, 2013 at 6:42 PM, Josh Dzielak j...@keen.io wrote:

 Recently we had a strange thing happen. Altering schema (gc_grace_seconds)
 for a column family resulted in a schema disagreement. 3/4 of nodes got it,
 1/4 didn't. There was no partition at the time, nor was there multiple
 schema updates issued. Going to the nodes with stale schema and trying to
 do the ALTER TABLE there resulted in hanging. We were eventually able to
 get schema agreement by restarting nodes, but both the initial disagreement
 under normal conditions and the hanging ALTER TABLE seem pretty weird. Any
 ideas here? Sound like a bug?


Yes, that sounds like a bug. This behavior is less common in 1.2.x than it
was previously, but still happens sometimes. It's interesting that
restarting the affected node helped, in previous versions of hung schema
issue, it would survive restart.


 We're on 1.2.8.


Unfortunately, unless you have a repro path, it is probably not worth
reporting a JIRA.

=Rob

Schema disagreement under normal conditions, ALTER TABLE hangs

[Josh Dzielak]

Recently we had a strange thing happen. Altering schema (gc_grace_seconds) for 
a column family resulted in a schema disagreement. 3/4 of nodes got it, 1/4 
didn't. There was no partition at the time, nor was there multiple schema 
updates issued. Going to the nodes with stale schema and trying to do the ALTER 
TABLE there resulted in hanging. We were eventually able to get schema 
agreement by restarting nodes, but both the initial disagreement under normal 
conditions and the hanging ALTER TABLE seem pretty weird. Any ideas here? Sound 
like a bug?  

We're on 1.2.8.

Thanks,
Josh

--
Josh Dzielak • Keen IO • @dzello (https://twitter.com/dzello)

Re: Alter table drop column seems not working

[julien Campan]

You are right, the documentation says that this action is not supported.

I was surprised because the auto completion in cqlsh allows you to try it
and, moreover, you have an example of a drop column when you use help
alter_drop.

Maybe it would be nice to change at least the documentation and
auto-completion ?



2013/4/4 aaron morton aa...@thelastpickle.com

 I dont think it's supported
 http://www.datastax.com/docs/1.2/cql_cli/cql/ALTER_TABLE#dropping-typed-col

 Anyone else know?

 Cheers

-
 Aaron Morton
 Freelance Cassandra Consultant
 New Zealand

 @aaronmorton
 http://www.thelastpickle.com

 On 3/04/2013, at 8:11 PM, julien Campan julien.cam...@gmail.com wrote:

 Hi,

 I'm working with cassandra 1.2.2.

 When I try to drop a column , it's not working.

 This is what I tried :

 CREATE TABLE cust (
   ise text PRIMARY KEY,
   id_avatar_1 uuid,
   id_avatar_2 uuid,
   id_avatar_3 uuid,
   id_avatar_4 uuid
 ) ;


 cqlsh ALTER TABLE cust DROP id_avatar_1 ;

 ==Bad Request: line 1:17 no viable alternative at input 'DROP'
 ==Perhaps you meant to use CQL 2? Try using the -2 option when
 ==starting cqlsh.

 Can someone  tell me how to drop a column or if it is a bug ?

 Thank

Re: Alter table drop column seems not working

[aaron morton]

I dont think it's supported 
http://www.datastax.com/docs/1.2/cql_cli/cql/ALTER_TABLE#dropping-typed-col

Anyone else know?

Cheers

-
Aaron Morton
Freelance Cassandra Consultant
New Zealand

@aaronmorton
http://www.thelastpickle.com

On 3/04/2013, at 8:11 PM, julien Campan julien.cam...@gmail.com wrote:

 Hi,
 
 I'm working with cassandra 1.2.2. 
 
 When I try to drop a column , it's not working. 
 
 This is what I tried : 
 
 CREATE TABLE cust (
   ise text PRIMARY KEY,
   id_avatar_1 uuid,
   id_avatar_2 uuid,
   id_avatar_3 uuid,
   id_avatar_4 uuid
 ) ;
 
 
 cqlsh ALTER TABLE cust DROP id_avatar_1 ;
 
 ==Bad Request: line 1:17 no viable alternative at input 'DROP'
 ==Perhaps you meant to use CQL 2? Try using the -2 option when ==starting 
 cqlsh.
 
 Can someone  tell me how to drop a column or if it is a bug ? 
 
 Thank