Server closed the connection unexpectedly

Shell Programming and Scripting
1

Hi,

I have a bash script which connects HP Vertica DB and executes few sql files in sequential manner. One sql file has commands related to one table(stage , base schema tables)and contains 7-8 commands such as creating temp table, inserting data into tables etc.
For few tables, which have high volume of data, I'm facing a weird issue.

For those tables, the sql file execution happens partially and returns the below error :

"server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost "

However, in database there are no errorful transactions. Even the query(during which error was returned) completed normally in database side.

Also, the next sql file in the sequence is picked up for the execution normally.

If i execute the same sql file directly inside DB, no issue comes.

Anyone can help identifying the issue?

Thanks in advance

2

Certainly not a script problem with the script.
Maybe there is a limit (e.g. max transfer size) exceeded in the DB server?
Check all its error logs.
Also there might be a bug (or a tunable limit) in the HP-UX.

3

Some quick things to ponder
-execute script with same user id as interactive execution?
-shell used in script; may be different
-limits on batch script execution

4

-When I execute in interactive db session, I dont get the error. The server from which shell script is invoked and the server where the vertica DB is installed are different.

  • shell used is ksh; but I have single shell script for 60 tables for an application, which returns error for those tables which have huge data and takes lot of time to execute sql commands. Smaller tables don't get this error.
  • no limits imposed in shell script
5

Is this some kind of network time-out?

What is the physical distance between these servers?

What is the network infrastructure between them?

6

Most likely there is a time-out on the DB server.
Like this:
https://my.vertica.com/docs/8.1.x/HTML/index.htm\#Authoring/AdministratorsGuide/ManagingClientConnections/SetClientConnections.htm?Highlight=timeout

7

For the db user, max idle session timeout is set to unlimited.

I got the below points for fixing the issue(at linux server side).

Update the settings as below :

echo 600 > /proc/sys/net/ipv4/tcp_keepalive_time
echo 60 > /proc/sys/net/ipv4/tcp_keepalive_intvl 
echo 20 > /proc/sys/net/ipv4/tcp_keepalive_probes

I did the changes(didn't restart the linux server) but still getting the same error. Restart of server is required for above changes to be applied?

8

By this point we're only wild guessing.

Please show your code.