Syndesis: Google Sheets connections fail after a while

[tmielke]

This is a...

[ ] Feature request
[ ] Regression (a behavior that used to work and stopped working in a new release)
[x ] Bug report  
[ ] Documentation issue or request

The problem

Connections to Google Sheets seem to fail after a period of activity.
I deployed an integration that consumes from Google Sheets (using different polling intervals) and it ran successfully for a period of about an hour but then started to raise error

Caused by: [com.google.api.client.googleapis.json.GoogleJsonResponseException - 401 Unauthorized

and
Request had invalid authentication credentials. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project

but nothing was reconfigured in Syndesis nor in my Google API settings.

What is interesting, I seem to be unable to re-use the connection at a later stage.
I stopped the Integration, waited a few minutes and restarted but the same error is thrown immediately then.
I created a new integration using the same Google Sheets connection and it fails immediately with this error as well.
I re-validated the same connection, it validates successfully. But the connection can still not be used in an integration.
I create a new connection to Google Sheets (using same Google API settings like clientID and secret) and use that in my integration, it works (but also fails after roughly an hour).

Request and Response Data

Full error reads:

2019-05-08 13:38:24.092 WARN 1 --- [ream-0-0://data] o.a.c.c.g.s.s.GoogleSheetsStreamConsumer : Consumer Consumer[google-sheets-stream-google-sheets-stream-0-0://data?accessToken=RAW(...)&applicationName=sheets-syndesis&clientId=...&clientSecret=...&delay=600000&majorDimension=ROWS&maxResults=5&range=B4%3AB4&splitResults=false&spreadsheetId=...] failed polling endpoint: google-sheets-stream-google-sheets-stream-0-0://data?accessToken=RAW(...)&applicationName=sheets-syndesis&clientId=...&clientSecret=...&delay=600000&majorDimension=ROWS&maxResults=5&range=B4%3AB4&splitResults=false&spreadsheetId=.... Will try again at next poll. Caused by: [com.google.api.client.googleapis.json.GoogleJsonResponseException - 401 Unauthorized
{
"code" : 401,
"errors" : [ {
"domain" : "global",
"location" : "Authorization",
"locationType" : "header",
"message" : "Invalid Credentials",
"reason" : "authError"
} ],
"message" : "Request had invalid authentication credentials. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.",
"status" : "UNAUTHENTICATED"
}]
com.google.api.client.googleapis.json.GoogleJsonResponseException: 401 Unauthorized
{
"code" : 401,
"errors" : [ {
"domain" : "global",
"location" : "Authorization",
"locationType" : "header",
"message" : "Invalid Credentials",
"reason" : "authError"
} ],
"message" : "Request had invalid authentication credentials. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.",
"status" : "UNAUTHENTICATED"
}
at com.google.api.client.googleapis.json.GoogleJsonResponseException.from(GoogleJsonResponseException.java:146) ~[google-api-client-1.22.0.jar!/:1.22.0]
at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:113) ~[google-api-client-1.22.0.jar!/:1.22.0]
at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:40) ~[google-api-client-1.22.0.jar!/:1.22.0]
at com.google.api.client.googleapis.services.AbstractGoogleClientRequest$1.interceptResponse(AbstractGoogleClientRequest.java:321) ~[google-api-client-1.22.0.jar!/:1.22.0]
at com.google.api.client.http.HttpRequest.execute(HttpRequest.java:1065) ~[google-http-client-1.22.0.jar!/:1.22.0]
at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:419) ~[google-api-client-1.22.0.jar!/:1.22.0]
at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:352) ~[google-api-client-1.22.0.jar!/:1.22.0]
at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:469) ~[google-api-client-1.22.0.jar!/:1.22.0]
at org.apache.camel.component.google.sheets.stream.GoogleSheetsStreamConsumer.poll(GoogleSheetsStreamConsumer.java:80) ~[connector-google-sheets-1.6.11.fuse-730003-redhat-00001.jar!/:1.6.11.fuse-730003-redhat-00001]
at org.apache.camel.impl.ScheduledPollConsumer.doRun(ScheduledPollConsumer.java:174) [camel-core-2.21.0.fuse-730078-redhat-00001.jar!/:2.21.0.fuse-730078-redhat-00001]
at org.apache.camel.impl.ScheduledPollConsumer.run(ScheduledPollConsumer.java:101) [camel-core-2.21.0.fuse-730078-redhat-00001.jar!/:2.21.0.fuse-730078-redhat-00001]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_201]
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [na:1.8.0_201]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_201]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [na:1.8.0_201]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_201]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_201]
at java.lang.Thread.run(Thread.java:748) [na:1.8.0_201]

Tasks involved / Steps to Reproduce

1. Deploy an integration that consumes from Google Sheets
2. Let it run for 2 hours
3. Observe logs for errors

[oscerd]

You have to create the app for this just one time. Then use the clientId and clientSecret exactly one time in the settings page. In that case you'll have the refresh token and the connection will continue to work. If you re-validate the connection or re-use the same clientId and clientSecret associated with that app, you won't get the refresh token as response and the connection will expire after a while. This is a google API policy. We experienced the same in gmail and google-calendar too. The idea is that you create an app and use the clientId and clientSecret just one time. There is no need to re-validate them or use that somewhere else. Just one time.

[christophd]

This is because of a limitation of the Google API OAuth2 connectivity. The problem is that you have reused an already existing Google API client credential to setup the Google Sheets connectivity and more important these client credentials most likely have been used before.

Google API provides the refresh token only one single time and this is the 1st time the credentials are used. In case you reuse the credentials Syndesis will not receive the refresh token and the communication will fail once the obtained access token gets expired.

So the fix is to use a fresh Google API OAuth client credential. Then Syndesis is provided with the refresh token (saves it to the database) and is able to refresh expired access tokens.

[christophd]

@oscerd almost simultaneously commented on this. Our ideas are the same 😄

[oscerd]

Perfect timing :-D With @zregvart we spent like 48 hours trying to understand what was happening :-D

[zregvart]

That helps me nothing, you folk are from the future:

[tmielke]

Many thanks for the explanation. I re-ran tests based on your input and it seems to work now that I use the one connection only once.
This issue can be resolved, I will raise a docs bug to highlight this restriction.