Cassandra can not delete role or user which in the role or user list

Question

create ROLE testROLE with PASSWORD = 'test';
create ROLE testROLE with PASSWORD = 'test';

list ROLES shows the roles successfully.

DROP ROLE testROLE;
InvalidRequest: code=2200 [Invalid query] message="testrole doesn't exist"
DROP ROLE test1;
InvalidRequest: code=2200 [Invalid query] message="test1 doesn't exist"

Image of CQLSH output.

The role showed in the list but can not be deleted. How this can happen???

Answer 1

When not quoted, role names do not preserve case, as you can see from the list roles output. If your role names are case-sensitive, you'll need to enclose them in double quotes (like you would for keyspace or table names).

Did you edit the output you pasted? I ask because you shouldn't be able to run the same CREATE ROLE statement twice as shown. If so, was anything else elided? I suspect that you were able to successfully drop the role once, but not a second time (that's the expected behaviour & what I'm seeing).

Answer 2

From this: When migrating from Cassandra 2 to Cassandra 3, once all nodes have been migrated, one should delete the legacy tables system_auth.users, system_auth.credentials and system_auth.permissions.

If not done, Cassandra will write new roles to the new table, but try and read roles from the old one, which gives the behaviour described in the question.