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

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?


Comments

  • Daniel_LeybovicDaniel_Leybovic Registered User
    >> 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_samurozi_samur Registered User
    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_LeybovicDaniel_Leybovic Registered User
    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_LeybovicDaniel_Leybovic Registered User
    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.


  • 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_samurozi_samur Registered User
    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=>
  • 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.
  • 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_LeybovicDaniel_Leybovic Registered User
    @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_GonzalezJoe_Gonzalez Registered User
    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_GonzalezJoe_Gonzalez Registered User
    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_tammasaumya_tamma Registered User
    Hi

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

    Thanks
    Saumya
  • Joe_GonzalezJoe_Gonzalez Registered User
    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_goesIt_goes Registered User
    Can You send me Your changes with a bit of instructions how You implemented it. My email withoutsaying@gmail.com
  • EswaraReddyEswaraReddy Registered User
    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
  • id10tid10t Registered User
    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_K_ Registered User
    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! 
  • MicoLiewMicoLiew Registered User

    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.

     

  • jkiran2020jkiran2020 Registered User

    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?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file