Server closed the connection unexpectedly while loading data from CSV using Alteryx Designer
Satya
Vertica Customer
I am loading data from a CSV file into Vertica database using Alteryx designer and after loading few thousands of records the Alteryx job fails with the following error.
Error: Output Data (7): DataWrap2ODBC::SendBatch: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Insert into "Schema"."table"("column1", "column2", ....... "column") Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
I have used different Vertica ODBC drivers and issue is the same. I compared all the settings with my colleague who was able to run the job successfully and there is no difference. We have created support request for Alteryx team thinking that it might be an issue with the tool but they confirmed it's not the issue with Alteryx. Vertica Admins are not seeing any drop down of nodes on Vertica server. Even Vertica documentation doesn't have proper explanation on why this error occurs
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ErrorCodes/SqlState-08003.htm
Appreciate any help on this issue.
Alteryx Designer V 2018.4.5
Vertica Analytic Database v9.2.1-11
ODBC Version - 7.1.2, 9.1.1, 9.2.1, 9.3.1
Answers
@Sathya : What is ResultBufferSize set to ?
Hi @SruthiA
ResultBufferSize is set to 131072. Also I have tried increasing it to 600000 but same result
@Sathya : Can you set ResultBufferSize 0 and try loading csv file to a test table?
@SruthiA - I created a new test table on the target vertica database and made changes to the buffersize. It loaded few more thousands of records but failed again with the same error.
@Satya Probably enabling ODBC trace logging should help and provide some clue. If you can, please open a support case and share debug logs.
@Satya, please share more details table structure and sample data too when you open the support case. thanks.
@SruthiA , @siddyash Sure I will ope a support case. I will share the entire file after opening support case. But meanwhile here are some errors I see in the logs
2a1a6c12-abd2-4 28cf0-28950 EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3
SQLHANDLE 0x000002A6AF7BC130
SQLSMALLINT 1
SQLWCHAR * 0x000000D50FFFE730 [ 5] "HY000"
SQLINTEGER * 0x000000D50FFFEF98 (0)
SQLWCHAR * 0x000000D50FFFE740 [ 137] "server closed the connection unexpectedly\ a\ 9This probably means the server terminated abnormally\ a\ 9before or while processing the request.\ a"
SQLSMALLINT 1023
SQLSMALLINT * 0x000000D50FFFEF80 (137)
2a1a6c12-abd2-4 28cf0-28cc0 EXIT SQLDisconnect with return code -1 (SQL_ERROR)
HDBC 0x000002A6AF7BAA40
2a1a6c12-abd2-4 28cf0-28cc0 ENTER SQLFreeHandle
SQLSMALLINT 2
SQLHANDLE 0x000002A6AF7BAA40
2a1a6c12-abd2-4 28cf0-28cc0 EXIT SQLFreeHandle with return code -1 (SQL_ERROR)
SQLSMALLINT 2
SQLHANDLE 0x000002A6AF7BAA40
2a1a6c12-abd2-4 28cf0-28cc0 ENTER SQLFreeHandle
SQLSMALLINT 1
SQLHANDLE 0x000002A6AF7BA960
2a1a6c12-abd2-4 28cf0-28cc0 EXIT SQLFreeHandle with return code -1 (SQL_ERROR)
SQLSMALLINT 1
SQLHANDLE 0x000002A6AF7BA960
Hello Sathya,
I tried executing a job on Alteryx Designer v 2020.3.4 and with 2020.2.3 along with different versions of Vertica ODBC clients which you have specified i.e 7.1.2, 9.1.1, 9.2.1, 9.3.1 and 10.0.1 by processing about a million records from a .csv file into Vertica cluster database table.
I did not encounter any issues for job failure after loading a certain set of records. I would suggest you please update the Alteryx Designer version from 2018.4.5 to the latest one and let me know if you still face the job failure.
Thank You
Shahzeb
shahzeb.shafi@microfocus.com
@Shahzeb I think it's not the problem with Alteryx, my colleauge is able to load the same data without any server connection termination issues.