ALTER TABLE returns "ConfigurationException: Column family ID"

Question

I am getting error while executing alter table script.

ALTER TABLE user.employee ADD salary text;

ServerError: java.lang.RuntimeException: java.util.concurrent.ExecutionException: org.apache.cassandra.exceptions.ConfigurationException: Column family ID mismatch (found e5da3980-83eb-11ec-8c56-1b3845d1a791; expected c8ac48d0-83eb-11ec-8c56-1b3845d1a791)

When I describe table ,I am seeing newly created column present. But I am bot able to access the new column.Its throwing below error InvalidRequest: Error from server: code=2200 [Invalid query] message="Undefined name xxxxxxxxx in selection clause"

We have close to 100GB of data.

Answer 1

This looks like the same question asked on https://community.datastax.com/questions/13220/ so I'm re-posting my answer here.

This exception indicates that you have a schema disagreement in your cluster:

ConfigurationException: Column family ID mismatch (\
    found e5da3980-83eb-11ec-8c56-1b3845d1a791; \
    expected c8ac48d0-83eb-11ec-8c56-1b3845d1a791 \
)

In my experience, the most common cause of this problem is that you dropped and re-created the table without waiting for the schema to propagate to all nodes in the cluster in between the DROP and CREATE. Alternatively, it's possible that you've tried to create the table and assumed it didn't work then tried to create it again.

In any case, Cassandra thinks the table was created at 05:48 GMT but found a version created at 05:49 GMT. For what it's worth:

• e5da3980-83eb-11ec-8c56-1b3845d1a791 = February 2, 2022 at 5:49:33 AM GMT
• c8ac48d0-83eb-11ec-8c56-1b3845d1a791 = February 2, 2022 at 5:48:44 AM GMT

You'll need to resolve the schema disagreement. Depending on the Cassandra version you can either (a) run nodetool resetlocalschema on nodes which have a different schema version based on the output of nodetool describecluster, or (b) perform a rolling restart of all nodes. Cheers!

Answer 2

ExecutionException: org.apache.cassandra.exceptions.ConfigurationException: Column family ID mismatch (found e5da3980-83eb-11ec-8c56-1b3845d1a791; expected c8ac48d0-83eb-11ec-8c56-1b3845d1a791)

Has that column been deleted/added more than once? Cassandra (especially the pre 3.0 versions) is notorious for problems with that.

Check the output of nodetool describecluster. Are there multiple schema versions being reported?

If there are multiple schema versions, then run a rolling restart of the cluster. That's a sure-fire way to force schema agreement. Check the table, and see if that column is there. If not, try to add it.

The other solution, would be to try adding it with a different name (ex: "salary2").