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

ozi_samur

I installed the odbc_loader vertica extension from vertica extension github page. But I see that I executed the " copy x odbc ..  " statement and then after 60 seconds Vertica gives this error:

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

I edited the ODBCLoader.cpp and put the "sleep(100);" and then I see same exception after 60 seconds. 

If there is no network data transfer, I mean idle situation, Vertica kills the UDx statements?

Daniel_Leybovic

>> after 60 seconds
Looks like server side configuration and not Vertica.
Requested host isn't reachable for some time interval(60 secs in your case) and server refuses a connection.

1. Did you validate your ODBC DSN outside of Vertica? Do you succeed to connects to non-vertica db via isql ? Validate connection outside of Vertica. If its works outside, so it should work in UDF.

2. Do you use in tunneling/redirection/forwarding? (looks like SSH "timeout", but ODBC doesn't use in SSH)
http://ocaoimh.ie/2008/12/10/how-to-fix-ssh-timeout-problems/
http://docs.oseems.com/general/application/ssh/disable-timeout
http://dan.hersam.com/2007/03/05/how-to-avoid-ssh-timeouts/

ozi_samur

I can access via isql and i can run queries against mysql, oracle and remote vertica database. No problem, queries can run more than 60 seconds, but with this Vertica Extension Package we could not achieve to run queries more than 60 seconds. If it takes 59 seconds it completed successfully. So, there is no problem about ODBC. isql works well. 

Someone also create an issue about this for Vertica Extension Package odbcloader:
https://github.com/vertica/Vertica-Extension-Packages/issues/35

I think when Vertica calls the setup() procedure in the user defined source, parser and when this procedure don't return in 60 seconds, Vertica kills the related module completely. May be Vertica assumes that any setup should not take more than 60 seconds and when it happens, kill the process. 

Daniel_Leybovic

Hi!

I tried to reproduce your problem and I agreed with you that some problem in InvokeSetupUDL() (user defined load functionality?):

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

/scratch_a/release/vbuild/vertica/OSS/UDxFence/UDxRPCMessages.h
"Unknown UDL input state" == 0

daniel=> create table target (id int, c char);
CREATE TABLE
daniel=> copy target with source ODBCSource() PARSER ODBCLoader(connect='DSN=MySQL', query='select * from source');
 Rows Loaded 
-------------
           3
(1 row)

daniel=> copy target with source ODBCSource() PARSER ODBCLoader(connect='DSN=MySQL', query=$$select 0, 'x'$$);
 Rows Loaded 
-------------
           1
(1 row)

daniel=> select * from target;
 id | c 
----+---
  1 | a
  2 | b
  3 | c
  0 | x
(4 rows)

daniel=> copy target with source ODBCSource() PARSER ODBCLoader(connect='DSN=MySQL', query='select sleep(62)');
ERROR 3399:  Failure in UDx RPC call InvokeSetupUDL(): timed out in receiving a UDx message
daniel=> 

Daniel_Leybovic

Ok after a some investigation:

VerticaUDl.h::369

virtual StreamState process(ServerInterface &srvInterface, DataBuffer &input, InputState input_state, DataBuffer &output) = 0;

 353      * process() must not block indefinitely.  If it cannot proceed for an
 354      * extended period of time, it should return KEEP_GOING.  It will be
 355      * called again shortly.  Failure to do this will, among other things,
 356      * prevent the query from being canceled by the user.

[Deleted User]

Thanks for the investigation -- yes, I believe you have correctly identified the problem.  The UDx framework requires that all function calls return regularly; otherwise it assumes that they have hung and kills them so that they don't hold resources and block other queries from running.

It looks like our documentation does not specifically cover how to change this timeout.  However, our documentation does cover Vertica Configuration Parameters generally (how to list them and how to set them):

https://my.vertica.com/docs/7.0.x/HTML/index.htm#Attic/Troubleshooting/SET_CONFIG_PARAMETER.htm?High...

And that table self-documents as follows:

=> select * from configuration_parameters where parameter_name ilike 'udx%';
 node_name |     parameter_name     | current_value | default_value | change_under_support_guidance | change_requires_restart |                                description                                
-----------+------------------------+---------------+---------------+-------------------------------+-------------------------+----------------------------------------------------------------------------
 ALL       | UDxFencedBlockTimeout  | 60            | 60            | t                             | f                       | Number of seconds to wait for UDx to finish a block of data before give up
 ALL       | UDxFencedCancelTimeout | 2             | 2             | t                             | f                       | Number of seconds to wait for UDx to finish cancel related clean up work
(2 rows)

These are flagged as "change under support guidance"; this generally means that you should file a Support case if you are able to and if you feel that you need to change it, otherwise you may get yourself into trouble and Support will be sad that they couldn't help you avoid that trouble.  (Often, though not always, these are workarounds where something else is probably the real issue.)

The disadvantage of changing a timeout is, of course, that if the UDx (or the remote database, etc) has actually hung, the Vertica query will sit there for a longer time, holding resources and potentially (depending on your resource-pool config and your workload) preventing other queries from running.

The proper fix here involves a code change to that UDx; that would best be discussed on the github post.

ozi_samur

Adam thanks for the reply. But your suggestion did not work for me. I changed the parameter and also even it is not necessary to restart database, I did it. But it kicks me out after 60 seconds:

dbadmin=> dbadmin=> select parameter_name, current_Value, description from configuration_parameters where parameter_name like 'UD%';      parameter_name     | current_Value |                                description ------------------------+---------------+----------------------------------------------------------------------------  UDxFencedBlockTimeout  | 70            | Number of seconds to wait for UDx to finish a block of data before give up  UDxFencedCancelTimeout | 2             | Number of seconds to wait for UDx to finish cancel related clean up work (2 rows)  dbadmin=> select clock_timestamp(); Copy test WITH SOURCE ODBCSource() PARSER ODBCLoader(connect='DSN=mysql_source', query='select sleep(65);'); select clock_timestamp();         clock_timestamp -------------------------------  2014-02-18 18:59:19.332364+03 (1 row)  ERROR 3399:  Failure in UDx RPC call InvokeSetupUDL(): timed out in receiving a UDx message         clock_timestamp -------------------------------  2014-02-18 19:00:19.482935+03 (1 row)  dbadmin=>

[Deleted User]

Hm...  That's unfortunate.  I think I know what's going on, but I don't think there's much to be done about it here.

(That parameter affects process().  I thought, incorrectly on further investigation, that it affected setup() as well.)

The fix there would be a code change to the UDx -- move the SQL query execution to process().  (Probably replace the SQLExecDirect() call in setup() with a SQLPrepare() call in setup() and a SQLExecute() call in process().  The code in process() would have to have an "if ()" to make sure that it's only run once, since process() is called many times.)

ODBCLoader is a community-supported and open-source project.  I can't guarantee that this change would work well with all drivers.  (I don't believe that SQLExecDirect() is required to block until the query in question has finished executing, as it appears to be doing in this particular case.)  If you want to try it (or if someone here wants to implement it and make it available to others to try out via a github pull request), you're certainly welcome and encouraged to do so.

[Deleted User]

If you have an Enterprise license, you're certainly welcome to open a general support case about the issue of UDx's not being able to sit in setup() for longer than 60 seconds.  They might or might not be able to help further; but, doesn't hurt to ask.

Daniel_Leybovic

@ozi

Im not familiar with MySQL, but when I did a migration from MySQL to Vertica, I remember i got some strange blocks when did a SELECT. I don't remember exactly a problem and solution, but a brief search gives me some link:
http://stackoverflow.com/questions/1619564/is-there-a-way-to-guarantee-non-blocking-reads-in-mysql

Syntax:

SELECT...

[...]

[FOR UPDATE | LOCK IN SHARE MODE]]

may be its MySQL issue? (I will try to check, but as I wrote Im not familiar with MySQL)

Joe_Gonzalez

I just worked with customer support to take the source code for ODBCLoader and modify it so that the SQL calls are all in the "process" function instead of the "setup" function.  This, combined with setting the UDxFencedBlockTimeout to 120 in configuration_parameters, allowed the ODBCLoader() function to operate beyond the 60 second timeout.  If anyone needs more info on the solution please let me know!

Joe_Gonzalez

Adam - Basically the solution is taking all code but one line from setup() and moving it to process() within an if statement.  I can provide the updated code to anyone who is interested.  I left it as a CPP file, recompiled, dropped the original odbcloader library and installed it with the updates.  Works like a charm!

saumya_tamma

Hi

I am facing the same issue. Can you please let me know whats the solution to this.

Thanks
Saumya

Joe_Gonzalez

Hi Saumya
If you send me your email address I will email you the updated .CPP file.  It will not let me attach a file here.
Joe

It_goes

Can You send me Your changes with a bit of instructions how You implemented it. My email withoutsaying@gmail.com

EswaraReddy

Hi Soumya,
 connecitng wuth dbamdin only

step 1 : vsql -f /opt/vertica/packages/hdfs_connectors/install.sql

Step 2 : CREATE EXTERNAL TABLE testTable (Dt VARCHAR(10),Duration VARCHAR(10),SessionDuration VARCHAR(10)) AS COPY SOURCE hdfs(url='http://xxxxx/webhdfs/v1/tmp/test_webhdfs_venkat.txt',username='dbadmin');


Step 3: select * from testTable

The following error i am getting . could you please help: 

20:15:24  [SELECT - 0 row(s), 0.000 secs]  [Error Code: 3399, SQL State: VP001]  [Vertica][VJDBC](3399) ERROR: Failure in UDx RPC call InvokePlanUDL(): Error calling planUDL() in User Defined Object [Hdfs] at [src/Hdfs.cpp:691], error code: 0, message: [Last error message before give up: Unknown exception: http response code 401. The URL: http://r36yn20.bnymellon.net:50070/webhdfs/v1/tmp/test_webhdfs_venkat.txt?user.name=gdpm04m&op=G.... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]

Please help me 



Regards,
Eswara Reddy N

id10t

Hi  EswaraReddy!

You got HTTP error code 401:

Unknown exception: http response code 401.

http://www.checkupdown.com/status/E401.html

HTTP Error 401 Unauthorized

So you not authorized to use in service. Check credentials.
What kind of authentication method is used - Kerberos?
Can you post output of curl?

Example:

curl -i -L "http://hadoopNameNode:50070/webhdfs/v1/tmp/test.txt?op=OPEN&user.name=hadoopUserName"

you can read about testing webhdfs here - http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/HadoopIntegrationGuide/HDFSConnector/Testi...


cheers

K_

Hi Joe, 

Would it possible to provide us with the code? We are seeing the same error message. We are on 7.1.1. Email address is verticaq@grr.la.

Thanks! 

MicoLiew

Hi,

 

I executed the following from Vertica to a Hadoop Cluster :-

 

COPY dbo.tbl_log_raw SOURCE Hdfs(url='http://IPADDRESS:14000/webhdfs/v1/log/Log_007aT/*.txt',username='root');

 

The error message that I retrieve is as follows :-

 

 14:36:24  [COPY - 0 row(s), 60.164 secs]  [Error Code: 3399, SQL State: VP001]  [Vertica][VJDBC](3399) ERROR: Failure in UDx RPC call InvokePlanUDL(): timed out in receiving a UDx message

 

Please help me with this. How can I resolve this?

 

Thanks.

 

jkiran2020

same error for me too. I have used COPY tbl SOURCE Hdfs(....

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

Any resolution for this?

divinebrother

I WANT TO MATCH COLUMN A NAME AS NARENDRA MODI COLUMN B NAME AS MODI NARENDRA HOW TO CREATE SCORE OF MATCH RECORDS PUBLIC.SIMILAR CURRENTLY I AM USING BUT FACING ISSUE OF 3399

divinebrother

what is the meaning of isutf8

marcothesane

What is issue of 3399?

What have you tried?

Can you specify a few sample rows, and the expected output?