cannot access database because of MaxClientSessions = 0;

avi120

Hi Experts

I ran the following command because I wanted to to stop the database and did not want to have open sessions

vsql -U vertica -c "ALTER DATABASE aaa SET MaxClientSessions = 0;"

What I did not expect is that I will not be able to change this configuration. I keep getting "New session rejected due to limit, already 5 sessions active".

How can I get out of this?
What is the correct way to gracefully terminate open sessions and prevent new ones from opening? I do not want to use the -F option when stopping the database

Thanks!

Vertiguy

On that API we limit the connections per node to 5 for the dbadmin, try connecting to another node in the cluster. If single node maybe kill the sessions at OS level.

avi120

Thanks! I killed one of the processes and was able to change the value back to 50.

What is the proper way to gracefully disconnect all sessions and prevent new ones from starting before I stop the database?

Jim_Knicely

@avi120 - Is the "vertica" user your admin user? If so, I'm curious why you had so many sessions open with that user.

Prior to running the "ALTER DATABASE aaa SET MaxClientSessions = 0;", you can close all sessions or all of the sessions for a particular user.

See:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/CLOSE_ALL_SESSIONS.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/CLOSE_USER_SESSIONS.htm