vsql windows client dropping connection

Bruce_H

For convenience, I have been using the VSQL windows client to connect to a remote Vertica server: 
   vsql -h <myserver>.cloudapp.net -U dbadmin -w <mypassword>

But time and time again as I'm working I notice that the server connection has dropped - forcing me to wait for a minute, then finally it resets and allows me to continue by re-typing the query. This is what I see:

dbadmin=> select count(*) from <sometable>;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

I don't have the same problem when I'm SSH-ed into the server and using the server-side VSQL. The path of least resistance is to use that instead. But it would be much simpler for me if I could rely on the Windows VSQL keeping its connection alive.

Daniel_Leybovic

Hi Bruce!

Have you antivirus software(may be NODE32?) or some network filters on your computer? Disable antivirus and check if you still get this message. Define to all  pc "guards" do not check vsql.

Prasanta_Pal

Check the tcp_keepalive_time settings in server side, find the optimum value, you may change this by the below command:
# echo 600 > /proc/sys/net/ipv4/tcp_keepalive_time

Bruce_H

thanks for responding Prasanta. I see the current value is 7200, which is 2 hours I believe. It seems to me that the timeout occurs far in advance of 2 hours.

Bruce_H

Thanks for your response Daniel. I do use Windows Defender as my virus scanner and disabling it had no effect on this problem. Windows Firewall is configured to allow access to the VSQL process. I haven't found issues connecting to Vertica, the issue remains that timeouts occur as I described above.

Prasanta_Pal

Try by changing to lower value as I said, see how it goes

Daniel_Leybovic

Hi Bruce!

vsql based on psql  and a main problem of psql for same disconnections on Windows its antivirus/firewalls software. That was my idea, but if you disabled all "guards" and it do not helps so I dont know what to advice. Try to force a SSL connection, may be it will help.

PS
Look, if you have no problems with ssh, so its a client side problem (99.9%).
SSH - secured shell
http://twigstechtips.blogspot.co.il/2011/12/postgresql-fix-for-connecting-to-server.html

Bruce_H

I did as you suggested, setting it to 600, rebooting. I tried it after 15 minutes, but the connection had died by then. Is it so much to ask that the connection stay up? I can use SSH and then VSQL locally on the vertica server, where I've never had an issue with the connection timeout, but its less convenient.