Cassandra: Adding new column to the table

Question

Hi I just added a new column Business_sys to my table my_table:

   ALTER TABLE my_table ALTER business_sys TYPE set<text>;

But again I just droped this column name because I wanted to change the type of column:

  ALTER TABLE my_table DROP business_sys;

Again when I tried to add the same colmn name with different type am getting error message

"Cannnot add a collection with the name business_sys because the collection with the same name and different type has already been used in past"

I just tried to execute this command to add a new column with different type-

  ALTER TABLE my_table ADD business_sys list<text>;

What did I do wrong? I am pretty new to Cassandra. Any suggestions?

Mosier

Answer 1

You're running into CASSANDRA-6276. The problem is when you drop a column in Cassandra that the data in that column doesn't just disappear, and Cassandra may attempt to read that data with its new comparator type.

From the linked JIRA ticket:

Unfortunately, we can't allow dropping a component from the comparator, including dropping individual collection columns from ColumnToCollectionType. If we do allow that, and have pre-existing data of that type, C* simply wouldn't know how to compare those...

...even if we did, and allowed [users] to create a different collection with the same name, we'd hit a different issue: the new collection's comparator would be used to compare potentially incompatible types.

The JIRA suggests that this may not be an issue in Cassandra 3.x, but I just tried it in 3.0.3 and it fails with the same error.

What did I do wrong? I am pretty new to Cassandra. Any suggestions?

Unfortunately, the only way around this one is to use a different name for your new list.

Millsaps

It works when you drop the column, recreate a non collection column such as int with the same name and then drop that again. After that you can add the same name column with a different collection type and cassandra allows the operation. Dirty workaround though! – Woebegone

@ken Thanks a lot for this comment. This really worked and solved my problem. – Overturf

I just tried the suggestion by @Woebegone in Cassandra 3 and 4 (Beta) and this approach does not work unfortunately. According to the Datastax engineers it's also not possible: community.datastax.com/questions/8018/… – Squiffy

Answer 2

EDIT: I've tried this out in Cassandra and ended up with inconsistent missing data. Best way to proceed is to change the column name as suggested in CASSANDRA-6276. And always follow documentation guidelines :)

-WARNING- According to this comment from CASSANDRA-6276, running the following workaround is unsafe.

Elaborating on @masum's comment - it's possible to work around the limitation by first recreating the column with a non-collection type such as an int. Afterwards, you can drop and recreate again using the new collection type.

From your example, assuming we have a business_sys set:

ALTER TABLE my_table ADD business_sys set<text>;
ALTER TABLE my_table DROP business_sys;

Now re-add the column as int and drop it again:

ALTER TABLE my_table ADD business_sys int;
ALTER TABLE my_table DROP business_sys;

Finally, you can re-create the column with the same name but different collection type:

ALTER TABLE my_table ADD business_sys list<text>;

Cod

You never should add the column with the same name as was dropped, but different type... – Flowery

Answer 3

Cassandra doesn't allow you to recreate a column with the same name and the same datatype, but there is an workaround to fix it.

Once you have dropped the column with SET type, you can recreate it with only another "default" type such as varchar or interger. After recreating with one of those types, you can drop the column once again and finally recreate with the proper type.

I illustrated it below

ALTER TABLE my_table DROP business_sys;           # the drop you've done
ALTER TABLE my_table ADD business_sys varchar;    # recreating with another type
ALTER TABLE my_table DROP business_sys;           # dropping again
ALTER TABLE my_table ADD business_sys list<text>; # recreating with proper type

Gaily