Error Handling
AnsweredHi,
something I find very difficult to understand is the handling of errors. Often a problem occurs with the facebook api.
{"error":{"code":1,"message":"An unknown error occurred","error_subcode":99}}'.
Basically this error occurs when there are too many API-Calls.
Everytime, when this error happens, the whole procedure stops and all data will rollbacked. This is unfortunate, because we lose the progress of hours.
We like to get an exception handling which does nothing and just tries again:
WHILE(reportDone=FALSE)
BEGIN
async_status = (
SELECT
"xmlTable.async_status"
FROM
(exec "Facebook".invokeHTTP(endpoint=>'https://graph.facebook.com/v3.1/'||reportID,action=>'GET',requestContentType=>'application/json')) w,
XMLTABLE(XMLNAMESPACES( 'http://www.w3.org/2001/XMLSchema-instance' as "xsi" ),'/root' PASSING JSONTOXML('root',to_chars(w.result,'UTF-8'))
COLUMNS
"idColumn" FOR ORDINALITY,
"id" STRING PATH 'id',
"account_id" STRING PATH 'account_id',
"async_status" STRING PATH 'async_status',
"async_percent_completion" STRING PATH 'async_percent_completion',
"date_start" STRING PATH 'date_start',
"date_stop" STRING PATH 'date_stop'
) "xmlTable")
;
async_percent_completion = (
SELECT
"xmlTable.async_percent_completion"
FROM
(exec "Facebook".invokeHTTP(endpoint=>'https://graph.facebook.com/v3.1/'||reportID,action=>'GET',requestContentType=>'application/json')) w,
XMLTABLE(XMLNAMESPACES( 'http://www.w3.org/2001/XMLSchema-instance' as "xsi" ),'/root' PASSING JSONTOXML('root',to_chars(w.result,'UTF-8'))
COLUMNS
"idColumn" FOR ORDINALITY,
"id" STRING PATH 'id',
"account_id" STRING PATH 'account_id',
"async_status" STRING PATH 'async_status',
"async_percent_completion" STRING PATH 'async_percent_completion',
"date_start" STRING PATH 'date_start',
"date_stop" STRING PATH 'date_stop'
) "xmlTable");
IF(async_percent_completion='100' AND async_status='Job Completed')
BEGIN
reportDone = TRUE;
END
ELSE
-- Falls der Report noch nicht fertig ist, 10 Sekunden warten und dann die Abfrage erneut starten
BEGIN
CALL "SYSADMIN.logMsg"(
"level" => 'INFO'/* Mandatory */,
"context" => 'FACEBOOKAsync'/* Mandatory */,
"msg" => 'Waiting for Report ' ||reportID|| ' to be done. This is wait nr.'|| WaitingCounter/* Mandatory */
);
DECLARE EXCEPTION e = SQLEXCEPTION
'Something happend'
SQLSTATE
'666'
;
RAISE variables.e;
EXCEPTION e
IF (e.state =
'xxxxx'
)
//in this trivial example, we'll always hit this branch and just log the exception
RAISE SQLWARNING e.exception;
ELSE
RAISE e.exception;
WaitingCounter = WaitingCounter+1;
CALL "SYSADMIN.sleep"("millis" => 1000*10);
END
So everytime if the error
{"error":{"code":1,"message":"An unknown error occurred","error_subcode":99}}
happens, ignore it. If there is another error, log it, and the break out of the current loop.
Any ideas?
Thank you and best regards,
Jonas
-
Comment actionsOfficial comment
Hi Jonas,
We recently created templates to let you concentrate more on the data, rather than development. The recent Facebook connector, available in 2.1, already encapsulates the retry behavior. Basically, we implement it as an endless loop, which exits immediately, if the request was successful, or decreases the counter until zero, and then fires an error. There is a growing sleep interval as well, which would allow the API to recover.
We also don't retry in case authentication was not successful. So, in case of error 401, the error is immediately thrown.
Below is the snippet, which we use in almost the same form in many connectors.
Best,
EugeneBegin
Declare integer retryCount = Nvl(maxRetries, 10);
while (true)
Begin
Begin
select JsonToXml('root', result), to_chars(result, 'UTF-8') From (
exec facebook_ws.invokeHTTP (
action => Nvl(action, 'GET'),
endpoint => endpoint,
requestContentType => 'application/json'
)
) a;
break;
Exception exc
If (Cast(exc.exception as string) Like '%' || '[*HTTP Response:%' || 401 || '%*]%')
Begin
Raise exc.exception;
End
retryCount = retryCount - 1;
If (retryCount = 0)
Begin
Raise exc.exception;
End
call SYSADMIN.logMsg (
level => 'WARN'
,context => 'com.datavirtuality.connectors.facebook'
,msg => 'Error occurred for endpoint ' || endpoint || ': Will start attempt ' || retryCount || ' more times. Error message: ' || cast (exc.exception as string)
);
call SYSADMIN.sleep(millis => 120000 * (10 - retryCount));
End
End
End; -
Hi Eugene,
thank you for your response. In our case, except the update on the new server version, nothing else was updated. So we still don't have the new connector.
If I understand your code correctly, it means that after a successfull SELECT-Statement the next statement is "break", which means it exits the block.
If the SELECT-Statement fails, it jumps directly to the exception block. Then it reaches the end of the while-loop, and since while(true) is always true, it starts again, right?This snippet of code is, independently of the update, important and very helpful for us.
-
Hi Jonas,
Yes, you read it perfectly well! One small remark. Thanks to
retryCount
the code will not iterate endlessly, for example, in case of permanent API failure due to... e.g. size of requested data. We get a response that a "Job failed due to Facebook internal error" from time to time, and we can only assume that this is due to the fact it cannot process all requested data.
So, the retryCount will decrease with each error, and when it will be zero (thus, after 10 attempts), we will exit the loop on
Raise exc.exception;
Ultimately 3 options possible: 1. Success, 2. Immediate error due to error 401, 3. Re-thrown error after 10 retries.
Apart from the main subject, I can go ahead and install the templates on your server by cloning the credentials from the existing Facebook datasource, if you'd like to give them a try.
Best,
Eugene
Please sign in to leave a comment.
Comments
3 comments