Not able to connect to Vertica database

Check your current value of MaxClientSessions:

dbadmin=> SELECT current_value, default_value, description FROM configuration_parameters WHERE parameter_name = 'MaxClientSessions';
 current_value | default_value |                                        description
---------------+---------------+-------------------------------------------------------------------------------------------
 1             | 50            | Maximum number of client sessions per node; in addition five dbadmin sessions are allowed
(1 row)

You can only get 5 dbadmin sessions above that number...

Example:

dbadmin=> ALTER DATABASE DEFAULT SET MaxClientSessions = 0;
ALTER DATABASE

dbadmin=> \! vsql -U dbadmin
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> \! vsql -U dbadmin
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> \! vsql -U dbadmin
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> \! vsql -U dbadmin
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> \! vsql -U dbadmin
vsql: FATAL 4060:  New session rejected due to limit, already 5 sessions active

dbadmin=> SELECT user_name, COUNT(*) FROM sessions GROUP BY user_name;
 user_name | COUNT
-----------+-------
 dbadmin   |     5
(1 row)

References:
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/ConfiguringTheDB/GeneralParameters.htm
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/ManagementConsole/ManagingClientConnectionsOnMC.htm

Hi Jim,
Problem is that I'm not able to even get to VSQL prompt due to this error. I guess I need access to Vertica DB to run this SQL command, right? When I try below command to get VSQL prompt then I get this error - "vsql: FATAL 4060: New session rejected due to limit, already 5 sessions active"

Note that the MaxClientSessions parameter is per node. Maybe try connecting to a different node?

Or, you mentioned that you have MC. There is a tab in there where you can execute SQL. You are probably connecting to the DB as dbadmin.

See: https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/ManagementConsole/RunningQueries/RunningQueriesInMC.htm

Maybe increase the number of sessions allowed to connect (MaxClientSessions) or close some sessions.

Hi Jim,
We have a single node Vertica.
We do have MC, but when executing SQL, it says DB down, but it's not red (in status), it's showing up. I tried restarting using admintools, and it restarted sucessfully.
Unless, Vertica start taking commands, don't think how can I update "MaxClientSessions".
You said, close some connection. Do you mean virtually checking with everyone to see if they have MC or VSQL session left open?
Thanks
Raju

Well, I would find out why you have more than 5 dbadmin sessions open at the same time. That's not good. That user should be only logging in for admin purposes

Right after you re-start the DB there should be a least a few seconds where you can log in as dbadmin to up the
MaxClientSessions parameter. The max value is 1000 (fyi).

If people are leaving sessions open (i.e. leaving them idle for extended periods of time) I would make sure to enable the idele session timeout feature.

See: https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/ManagingClientConnections/OverviewClientConnections.htm

Hi Jim,
Thanks. I see following in vertica.log. This confirms what you indicated : session timeout = UNLIMITED.
2020-08-02 03:05:07.361 Init Session:0x7f4f1cffa700 @v_ptc_node0001: 00000/2705: Connection received: host=192.168.99.21 port=21283 (connCnt 6)
2020-08-02 03:05:07.361 Init Session:0x7f4f1cffa700 @v_ptc_node0001: 00000/4686: Authentication - sendAuthRequest: user=dbadmin database=ptc host=192.168.99.21 authType=3
2020-08-02 03:05:07.362 Init Session:0x7f4f1cffa700-a00000005c7b63 [Txn] Begin Txn: a00000005c7b63 'check_login_history'
2020-08-02 03:05:07.362 Init Session:0x7f4f1cffa700-a00000005c7b63 [Txn] Rollback Txn: a00000005c7b63 'check_login_history'
2020-08-02 03:05:07.362 Init Session:0x7f4f1cffa700 @v_ptc_node0001: 00000/4686: Authentication - sendAuthRequest: user=dbadmin database=ptc host=192.168.99.21 authType=0
2020-08-02 03:05:07.362 Init Session:0x7f4f1cffa700 @v_ptc_node0001: 00000/2703: Connection authenticated: user=dbadmin database=ptc host=192.168.99.21
2020-08-02 03:05:07.362 Init Session:0x7f4f1cffa700 @v_ptc_node0001: 00000/2609: Client pid: 1
2020-08-02 03:05:07.362 Init Session:0x7f4f1cffa700 @v_ptc_node0001: 00000/2608: Client label: jdbc-09.03.0100-ce682421-a9ce-497d-93f9-96c988a29259
2020-08-02 03:05:07.362 Init Session:0x7f4f1cffa700 @v_ptc_node0001: 00000/7400: Current idle session timeout = UNLIMITED
2020-08-02 03:05:07.362 Init Session:0x7f4f1cffa700 @v_ptc_node0001: 00000/8081: Current session grace period: UNLIMITED
2020-08-02 03:05:07.362 Init Session:0x7f4f1cffa700 [Session] New session rejected due to limit, already 5 sessions active
2020-08-02 03:05:07.362 Init Session:0x7f4f1cffa700 @v_ptc_node0001: {SessionRun} 08004/4060: New session rejected due to limit, already 5 sessions active
LOCATION: checkMyClientSessionLimit, /data/qb_workspaces/jenkins2/ReleaseBuilds/Hammermill/REL-10_0_0-x_hammermill/build/vertica/Session/SessionManager.cpp:958
2020-08-02 03:05:17.366 Init Session:0x7f50667fc700 @v_ptc_node0001: 00000/2705: Connection received: host=192.168.99.21 port=21785 (connCnt 6)
2020-08-02 03:05:17.366 Init Session:0x7f50667fc700 @v_ptc_node0001: 00000/4686: Authentication - sendAuthRequest: user=dbadmin database=ptc host=192.168.99.21 authType=3
2020-08-02 03:05:17.367 Init Session:0x7f50667fc700-a00000005c7b64 [Txn] Begin Txn: a00000005c7b64 'check_login_history'

Unfortunately, I still couldn't execute "ALTER DATABASE ptc SET MaxClientSessions = 100;" while restarted the datbase.
Can you suggest something else.

If I were you, I would shutdown MC. That should free up atleast one dbadmin login. Then restart the DB. Now you should be able to quickly log in via vsql as dbadmin to up the MaxClientSessions value.

But again, try and figure out why you have 5 dbadmin connections. Where are they coming from (i.e. client). Are they necessary? Could you use some sort of service account instead?

SELECT client_hostname, client_type, client_os_user_name, COUNT(*) session_cnt FROM user_sessions WHERE user_name = 'dbadmin' GROUP BY 1, 2, 3 ORDER BY 4 DESC;

Hi Jim,
I tried logging off from vertica console and even stop the service like:
systemctl stop vertica-consoled.
After that tried:
vsql -U dbadmin -w password
Still getting: vsql: FATAL 4060: New session rejected due to limit, already 5 sessions active.

Restart the DB, then try

Tried to stop DB like this:: /opt/vertica/bin/admintools -t stop_db -d ptc -p viavi -F, but getting,

Database ptc not stopped successfully for the following reason:
DB client operation "stop db" failed during connect-plaintext: ConnectionError: Severity: FATAL, Message: New session rejected due to limit, already 5 sessions active, Sqlstate: 08004, Routine: checkMyClientSessionLimit, File: /data/qb_workspaces/jenkins2/ReleaseBuilds/Hammermill/REL-10_0_0-x_hammermill/build/vertica/Session/SessionManager.cpp, Line: 958, Error Code: 4060

Yup

[dbadmin@nitro01 ~]$ /opt/vertica/bin/admintools -t start_db -d ptc -p password -F
Starting nodes:
v_ptc_node0001 (192.168.99.41)
Starting Vertica on all nodes. Please wait, databases with a large catalog may take a while to initialize.
Node Status: v_ptc_node0001: (DOWN)
Node Status: v_ptc_node0001: (DOWN)
Node Status: v_ptc_node0001: (DOWN)
Node Status: v_ptc_node0001: (DOWN)
Node Status: v_ptc_node0001: (UP)
Database ptc: Startup Succeeded. All Nodes are UP
[dbadmin@nitro01 ~]$ vsql -U dbadmin -w password
vsql: FATAL 4060: New session rejected due to limit, already 5 sessions active

Hmm. What version of Vertica is this?

I'd try to write a shell script that starts the database and immediately afterwards runs vsql to fire the ALTER DATABASE command. And fire a query that checks the running sessions, after that.

I just tested it - and apparently, it worked, even if I saw some error messages. MaxClientSessions was at 50 in my case before, and now is at 100.

Here's the script I tried:

#!/usr/bin/env bash
#set some environment variables
export VSQL_HOST=192.168.99.21
export VSQL_DATABASE=ptc
export VSQL_USER=dbadmin
export VSQL_PASSWORD=<dbadmin_s_password>
#start the database from the command line 
/opt/vertica/bin/admintools -t start_db -p $VSQL_PASSWORD -d $VSQL_DATABASE
#immediately after that, alter MaxClientSessions
vsql -c "ALTER DATABASE DEFAULT SET MaxClientSessions=100"
#check the active sessions with extended output (-x), to see what's going on
vsql -x -c "SELECT * FROM sessions"

And, to shed some light on what happened, I tried to grep vertica.log. Can you do that on your vertica.log, too? It will be in a different directory in your case ....

$ grep -i 'Connection received /home/dbadmin/sbx/v_sbx_node0001_catalog/vertica.log

:$ grep -i 'Connection received /home/dbadmin/sbx/v_sbx_node0001_catalog/vertica.log

Vertica version is 10.
Service Request ID:SD02739658

I need to correct the grep command I suggested. It's better to see when the connections were received; authenticated; and closed. So:
Better is:

$ grep -i connect /home/dbadmin/sbx/v_sbx_node0001_catalog/vertica.log 

[dbadmin@nitro01 ~]$ ./test.sh (it containts the script you shared)
Database 'ptc' is already running on port '5433'
You must stop it before starting database 'ptc'
vsql: FATAL 4060: New session rejected due to limit, already 5 sessions active
vsql: FATAL 4060: New session rejected due to limit, already 5 sessions active

And then, grep your vertica.log as I suggested, and share the outcome ....

You are getting 33 connections authenticated. 4 by "regular_user", the others by "dbadmin". 2 from "192.168.99.41" as "dbadmin" , the other connections from "192.168.99.21", by "dbadmin" and "regular_user".
You need to find out where these connections come from - as soon as the database is up . that seems to be the best course of action.

Yes, we have user "regular_user" to do certain tasks. Some java services would try connecting Vertica using this user. But, question is, how would I ever connect to Vertica database? Do you want me to stop connection request coming from 192.168.99.21?

Hi @Jim_Knicely , @marcothesane ,
Issue finally resolved with two step process -
1. Stop all client communicating with Vertica database. This allowed vsql -U dbadmin -w password to work
2. Increase dbadmin maxsyslogins (suggested by another Vertica support engineer part of the original ticket).
Thank you both for understanding my problem and help narrow down the issue. Learn a lot during the debug.

In addition,
admintools -t start_db with the -U option will start the DB in UNSAFE mode, and will not allow non-superuser to log into the DB.
So only Vertica superuser can connect.
Since the UNSAFE mode by default enable a read-only transaction mode, we will enable also writes in that specific session,
in order to change the MaxClientSessions configuration parameter, like in the following example:

admintools -t start_db -d VDB -U && vsql -c "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED READ WRITE; SELECT SET_CONFIG_PARAMETER ('MaxClientSessions', 500);"
Starting nodes:
v_vdb_node0001 (127.0.0.1)
Starting Vertica on all nodes. Please wait, databases with a large catalog may take a while to initialize.
Database VDB: Startup Succeeded. All Nodes are UP
SET_CONFIG_PARAMETER

Parameter set successfully
(1 row)

Thanks @mosheg, didn't know about starting Vertica in UNSAFE mode and use write option for that session only. Definitely keep this in my notes. Thanks again!

Hi everyone, I got the following error, trying to connect as a source from Vertica Database through Azure Data Factory. In test connection it takes at least 10 seconds, then this ad appears:

ERROR [08001] [Vertica][VerticaDSII] (160) Connection attempt failed: could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "10.92.22.20" and accepting TCP/IP connections on port 5433?

Help please

@fabmel1 Hi, I'm struggling with same issue here. Have you perhaps found the solution?