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?
ERROR 3399: Failure in UDx RPC call InvokeSetupUDL(): timed out in receiving a UDx message
+1>> 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/
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/
+1Hi!
I tried to reproduce your problem and I agreed with you that some problem in InvokeSetupUDL() (user defined load functionality?):
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=>
+1Ok after a some investigation:
VerticaUDl.h::369
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.
+2Thanks 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.
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.
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=>
+2Hm... 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.
(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.
+2If 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.
+1@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:
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)
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!
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!
+1Hi
I am facing the same issue. Can you please let me know whats the solution to this.
Thanks
Saumya
I am facing the same issue. Can you please let me know whats the solution to this.
Thanks
Saumya
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
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
Can You send me Your changes with a bit of instructions how You implemented it. My email withoutsaying@gmail.com
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
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
Hi EswaraReddy!
You got HTTP error code 401:
What kind of authentication method is used - Kerberos?
Can you post output of curl?
Example:
cheers
You got HTTP error code 401:
Unknown exception: http response code 401.http://www.checkupdown.com/status/E401.html
HTTP Error 401 UnauthorizedSo 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
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!
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!
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.
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?
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
what is the meaning of isutf8
+1What is issue of 3399?
What have you tried?
Can you specify a few sample rows, and the expected output?
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
