Error: Database errors occurred: server closed the connection unexpectedly

vijayakumar

We are frequently getting an error: server closed the connection unexpectedly while loading data through informatica from stage table to target table in same vertica database . I have created ODBC connection for vertica.

Oleg_S

I have the same issue when use COPY to load csv file - and have to start database via admintools as it is shut down afther such error.

[Deleted User]

Hi Vijay and Oleg,

Hm...  That's certainly an unfortunate issue.  It's not one that I've seen before personally, though.  It probably has something to do with the particular commands that you're running or the data that you're loading.  Do you have a reproducer?; a script or example that always causes this error.

Also, what version of Vertica are you running?  (Both the server version and the client-driver version.)  I know we've seen (and fixed) similar, though not identical, issues in past versions of Vertica.  As always, we recommend trying with the latest version.

Adam

Oleg_S

I am running Vertica v6.1.3-0. I am not using Client, I run COPY directly on server (one node). I can reproduce this error with teh same flat files. Visually I do not see what can cause the issue. COPY command is simple, with " FROM :input_file DELIMITER ',' TRAILING NULLCOLS SKIP 1 ENCLOSED BY '"' NULL '' DIRECT". Should COPY simply reject bad row if any, without causing database shut down? Exact error is: "server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request."

[Deleted User]

Hi Oleg,

Generally the database should not shut down as a regular error condition.  COPY should simply reject bad rows as you say.

As you say, that's a simple statement; nothing about it strikes me as odd.  What about the table and the data?  For example, does it occur if you do something like:

""" input_file.txt
i,j
1,2
3,4
5,6
"""

""" SQL statements
CREATE TABLE t (i int, j int);
COPY t FROM '/path/to/input_file.txt' DELIMITER ',' TRAILING NULLCOLS SKIP 1 ENCLOSED BY '"' NULL '' DIRECT;
SELECT * FROM t;
"""

Or do you need a more-complicated table definition or more-complicated file?

One more question:  After the database shuts down, is there a more-detailed error message in vertica.log?

Incidentally, if you have the Enterprise Edition, I would strongly encourage you to file a support case for this issue.  Anything involving "database shuts down unexpectedly" generally gets their attention in a hurry :-)  And they have better tools than I do here for figuring out what might have gone wrong.  That said, it's useful for both me and them if you can narrow things down to the simplest environment (statement, table, data files, database setup, etc) that causes this issue.  Since we certainly run this statement and much more complicated statements in-house to test COPY; so there must be something unusual somewhere...

Thanks,
Adam

Oleg_S

OK, I found what caused an issue - bad data. I see one column in csv file had character "^@" repeated too many times way more exciding the target column size. I removed that from source file, and all works OK. The issue with COPY remains - COPY should not crash and shut down database even if there are bad data.

[Deleted User]

Yep, that's correct.  It's also something that we do test for -- invalid and corrupt data of all kinds are quite common and are known to work with Vertica.  (Well, at least they don't crash Vertica; you may get a rejection or an error, depending on the form of corruption.)  So your case must be new in some way.  We should absolutely fix it; but we can only fix it if we know what's going wrong, so it would be great if you could get in touch with us per my reply below.

Oleg_S

Hi Adam, My table definition was very simple. After I removed "nulls" from flat file, everything worked OK. To reproduce teh scenario, I think, it will be sufficient to create simple table, and simple flat file, one of columns should have trmendous number of nulls ("null" in that specific case is a special character looking like ^@. To create it in editor, like vi, click ctrl + V + shift + 2). Sso it should be something like blah|^@^@^@^@^@^@^@^@^@^@^@^@text| 1111 where second "dirty" column should be much much bigger then column definition in the table. In my case I had column "name" varchar(256), but that "null" symbol was replicated on several screens. That caused the issue I was describing above. Thanks, Oleg

[Deleted User]

Hi Oleg,

Thanks for the info.  Unfortunately, it doesn't help -- we're quite familiar with both issues related to null bytes and issues related to oversized fields, and have tested for both quite a bit.

If you have a specific reproducer for this, feel free to let us know.  Otherwise, we'll keep an eye out.

Adam

Oleg_S

Hi Adam, Please let me know where can I send this file which causes database shut down. BTW, I am an HP employee, so you can ping me directly Thanks, Oleg

vijayakumar

Hi,

I'm loading data through Informatica ETL tool. We are loading around 30 tables parallel. On that 1 or 2 table load failed due to this issue. but After re-running same table load succeeded without any error and on while running on next day all tables loads completed. This error we are getting occasionally. But we need to fix this issue. We have not using copy command on any of these tables.

Server version: 6.1.2

Thanks,
Vijay

[Deleted User]

Hi all,

Vijay, have you considered upgrading to Vertica 6.1.3?  We did actually fix an issue like this between 6.1.2 and 6.1.3.

Oleg, I can't find you...  There should only be one person with my name at HP Vertica; you can look me up there.  FYI, we only provide support (even internally -- HP's huge, there are so many of you and only a few of us!) to people with an EE license and support contract.  If you have one, you're actually better off going through our Support folks.  Regardless, though, if the server's crashing, we kinda want to fix that :-)

Adam