Error when trying to run create and select in cqlsh: NoHostAvailable

Question

I'm new to DSE and Cassandra. I have a cluster consists of 3 DC: "analytic", "dc2" , "trans".

All use the same profile DSE, with DSEAuth. So the problem is the LIST and CREATE command only work when I use cqlsh to DC trans. The other will replay NoHostAvailabe.

[root@bigdata-142-116 ~]# nodetool status
Datacenter: analytic
====================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address       Load       Tokens       Owns    Host ID                               Rack
UN  10.0.142.120  50.78 MiB  256          ?       78f58a89-30b3-4646-8762-f8ed528301a9  rack1
UN  10.0.142.121  126.94 MiB  256          ?       7229079d-12c9-4ef6-8753-b79edbcec8cf  rack1
UN  10.0.142.122  35.91 MiB  256          ?       e3c1e9c3-4bd1-4cd0-8479-69ba1b28676e  rack1
UN  10.0.142.123  52.7 MiB   256          ?       25c591fe-36cc-4923-82bc-c0944364b486  rack1
Datacenter: dc2
===============
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address       Load       Tokens       Owns    Host ID                               Rack
UN  10.0.142.116  2.33 GiB   256          ?       9aea87a8-be95-45ad-a9c8-91ad7e658dff  rack2
UN  10.0.142.117  2.64 GiB   256          ?       51e078ec-2434-401c-9db8-4adaaf263ed4  rack1
UN  10.0.142.118  2.76 GiB   256          ?       1a0ccb08-c65d-40ce-ae99-acb30f6e9d9a  rack1
UN  10.0.142.119  2.67 GiB   256          ?       d29708ca-729d-4727-a816-129b4fc72e04  rack1
Datacenter: trans
=================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address       Load       Tokens       Owns    Host ID                               Rack
UN  10.0.142.113  164.09 MiB  256          ?       8fd44e60-9b84-44af-aebf-26b3c6a3ab17  rack1
UN  10.0.142.114  130.3 MiB  256          ?       8eb9a807-ff1f-47d7-92f0-e876f0fb56ee  rack2
UN  10.0.142.115  113.3 MiB  256          ?       e5f9a5d7-03b7-406e-ab0e-e429301af543  rack1

# cqlsh -u cassandra -p cassandra 10.0.142.120
Connected to GDT2 Cluster at 10.0.142.120:9042.
cassandra@cqlsh> list ROLES ;
NoHostAvailable:

# cqlsh -u cassandra -p cassandra 10.0.142.115
Connected to GDT2 Cluster at 10.0.142.115:9042.
cassandra@cqlsh> LIST ROLES ;

 role      | super | login | options
-----------+-------+-------+---------
 cassandra |  True |  True |        {}
  sysadmin |  True |  True |        {}
      test | False |  True |        {}

(3 rows)

score 3 · Accepted Answer · answered Oct 22 '19 at 13:11

Describe your system_auth keyspace. Does it look like this?

> desc KEYSPACE system_auth ;

CREATE KEYSPACE system_auth WITH 
    replication = {'class': 'NetworkTopologyStrategy', 'trans': '1'}
    AND durable_writes = true;

Or even worse, does it look like this?

CREATE KEYSPACE system_auth WITH 
    replication = {'class': 'SimpleStrategy', 'replication_factor': '1'}
    AND durable_writes = true;

The problem here, is that it seems that the trans DC is the only DC which has the replica(s) for the system_auth.roles table. To fix that, I recommend this:

ALTER KEYSPACE system_auth WITH 
    replication = {'class': 'NetworkTopologyStrategy', 'trans': '3',
    'analytic': '3', 'dc2': '3'};

That will instruct all future writes to tables in the system_auth keyspace to write 3 replicas to each logical data center. Once that command is complete, run the following to ensure that all current replicas are moved to their proper places:

> consistency ALL
> SELECT * FROM resource_role_permissons_index ;
> SELECT * FROM role_permissions ;
> SELECT * FROM role_members ;
> SELECT * FROM roles;

The first command forces your query consistency level up to ALL, requiring a response from all replicas for success. That sounds like something you wouldn't want to do in Cassandra (and usually, you wouldn't). But reading at consistency ALL forces a read repair 100% of the time. The SELECT queries read all replicas and actually forces the repair. That will essentially trick Cassandra into repairing the replicas for you.

Also:

cqlsh -u cassandra -p cassandra

Don't use the default cassandra/cassandra user. Changing the password and/or disabling that account is one of the first things that should be done.

Thanks Aaron! Seems that this is what i miss in the document: https://docs.datastax.com/en/security/6.0/security/secSystemKeyspace.html The issue is resolved after i run ALTER on both system_auth and dse_security keyspaces. — Fiery, Oct 23 '19 at 02:30

Error when trying to run create and select in cqlsh: NoHostAvailable

1 Answers1

Linked