ODBCLoader() : ERROR 3399: Failure in UDx RPC call InvokeSetupUDL(): timed out in receiving a UDx message

[afsungur]

I am trying to use ODBCLoader() ( User Defined Loader ) in the Vertica Extension Package, but I am getting a timeout error. I am gonna explain it in below.

I can run queries which takes more than 60 seconds, in isql, like that:

I can also execute queries which takes more than 60 seconds in the python by using pyodbc.

When I try to run similar queries in the vsql by using ODBCLoader() I am getting timeout error as shown in the below. Firstly, I thought that it is similar to mysql connection, query timout problem but it is not related to the data source. Because remote odbc data source mysql and vertica gives same error. Just after 60 seconds passed, it throws an exception as shown in the below.

I edited the ODBCLoader.cpp code and set the SQL_ATTR_QUERY_TIMEOUT more than 60 seconds but it does not effect. What can be the problem? In the Cpp code is there any parameter which overrides the default query timeout?

My ODBC configuration is in below:

[aseering]

This bug also recently popped up on the user forums:

https://community.vertica.com/vertica/topics/error_3399_failure_in_udx_rpc_call_invokesetupudl_timed_out_in_receiving_a_udx_message?utm_content=reply_link&utm_medium=email&utm_source=reply_notification

[aseering]

The underlying issue here is that UDx calls can't block indefinitely. Most likely SQLExecDirect() is taking a very long time to return, and the function call is timing out.

Unfortunately, there is currently no way for setup() to indicate that it wants to keep taking time. So the solution to this would be to move that bit of code into process(); then modify it to use asynchronous ODBC calls and to have process() periodically return KEEP_GOING. This will, of course, only work properly for drivers that handle asynchronous calls correctly. (Also drivers whose thread-safety is implemented a little more tightly -- process() sees more concurrency than setup(), typically; and I've already seen occasional crashes with some client drivers that explicitly don't claim to support multi-threaded environments.)

For what it's worth, now that Vertica supports UDLs written in Java, I'm of the opinion that JDBC would be a better tool than ODBC to implement this connector. ODBC has some issues regarding thread-safety, consistent standardization, and configuration that I think JDBC does in a way that's better for this application. I unfortunately don't have time to port this code at the moment. But if someone wants a good weekend project, go for it :-)

[aseering]

Incidentally, in the absence of a general fix, workarounds are discussed at the above forum post.

[RomainLeclair]

Hi! The link of the forum above does not work anymore. There is therefore no clearly identified solution ? :) Thanks :

[h-serizawa]

@RomainLeclair Please increase the value of UDxFencedBlockTimeout configuration parameter. The default value is 60 which means 60sec.

ALTER DATABASE DEFAULT SET UDxFencedBlockTimeout = <New value>;

[RomainLeclair]

@h-serizawa Thanks you for the information, I was able to find this one with a little digging :) Unfortunately I have a new problem ... I try to copy a big table but I have this error that comes back at random timing

[Vertica][VJDBC](3399) ERROR: Failure in UDx RPC call InvokeSetupUDL(): UDx side process has exited abnormally, caused by: NonTransientException: [Vertica][VJDBC](3399) ERROR: Failure in UDx RPC call InvokeSetupUDL(): UDx side process has exited abnormally

I try to play with the different connection parameters but for the moment always the same problem..

[h-serizawa]

@RomainLeclair Can you provide detailed information to break down the issue and reproduce it?

1. Log messages in UDxFencedProcesses.log and vertica.log related to this transaction.
2. Any error happened on the remote database.
3. Data size and row count you are trying to copy from the remote database to Vertica.

[RomainLeclair]

@h-serizawa Hi, For the first two points, i do not have acces to these files. I will make the request and get back to you as soon as I have this information :) 3.

• Data Size : 38186.81Mb
• Row Count: 211,354,083

Do not hesitate if you need more information or other