Not able to connect to Vertica database

rajuk99rajuk99 Community Edition User ✭
edited August 2020 in General Discussion

Hi Vertica team,
I'm getting below error everytime I try to connect Vertica database using console like,
vsql -U dbadmin -w
vsql: FATAL 4060: New session rejected due to limit, already 5 sessions active
Vertica admintools and management console continue to report database is up but can't allow new connection. Please help.
Thanks
Raju

Tagged:
«1

Answers

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited August 2020

    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

  • rajuk99rajuk99 Community Edition User ✭

    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"

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited August 2020

    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.

  • rajuk99rajuk99 Community Edition User ✭

    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

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited August 2020

    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

  • rajuk99rajuk99 Community Edition User ✭

    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.

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited August 2020

    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;

  • rajuk99rajuk99 Community Edition User ✭
    edited August 2020

    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.

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    Restart the DB, then try

  • rajuk99rajuk99 Community Edition User ✭
    edited August 2020

    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

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    Is this a production environment? Shouldn't be with 1 node. I would just kill the Vertica process. Then restart. Vertica will recover. Then try once again to up the param.

  • rajuk99rajuk99 Community Edition User ✭

    This is Dev environment NOT production. Do you mean like this?
    1. ps aux |grep vertica
    2. kill -9 any running vertica processes
    3. restart using admintools
    I have created a case number with Vertica also.

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    Yup

  • rajuk99rajuk99 Community Edition User ✭
    edited August 2020

    [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

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    Hmm. What version of Vertica is this?

  • marcothesanemarcothesane - Select Field - Administrator

    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

  • rajuk99rajuk99 Community Edition User ✭

    Vertica version is 10.
    Service Request ID:SD02739658

  • marcothesanemarcothesane - Select Field - Administrator

    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
  • rajuk99rajuk99 Community Edition User ✭

    [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

  • marcothesanemarcothesane - Select Field - Administrator

    You can't start an already running database. You'd have to repeat what you did before:
    1. ps aux |grep vertica
    2. kill -9 any running vertica processes
    Then, your ./test.sh .

  • marcothesanemarcothesane - Select Field - Administrator

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

  • rajuk99rajuk99 Community Edition User ✭
    edited August 2020

    What I did:
    1. ps aux |grep vertica
    2. kill -9 any running vertica processes (note: one process I couldn't kill was - 0.0 0.0 112812 968 pts/0 S+ 16:07 0:00 grep --color=auto vertica) since, it's process ID keep changing.
    Then,
    [dbadmin@nitro01 ~]$ ./test.sh
    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: (INITIALIZING)
    Node Status: v_ptc_node0001: (UP)
    Database ptc: Startup Succeeded. All Nodes are UP
    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
    Attached is the vertica.log

  • marcothesanemarcothesane - Select Field - Administrator

    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.

  • rajuk99rajuk99 Community Edition User ✭

    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?

  • rajuk99rajuk99 Community Edition User ✭

    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.

  • moshegmosheg Vertica Employee Administrator

    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)

  • rajuk99rajuk99 Community Edition User ✭

    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!

  • fabmel1fabmel1 Community Edition User

    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

  • LazzkaLazzka Vertica Customer

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

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    @Lazzka - When specifying the Server Name in Azure Data Factory, is that server name or IP address reachable, or is there a firewall rule blocking port 5433?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...