Refresh Failure

[Anonymous]

Hi.

I'm trying to refresh my dataset through Power BI service and get the following error:

Underlying error code: -2147467259
Underlying error message: ODBC: ERROR [08001] timeout expired

DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind: Odbc
Microsoft.Data.Mashup.ValueError.DataSourcePath: database=mydb;driver={PostgreSQL Unicode(x64)};port=5432;server=myserver
Microsoft.Data.Mashup.ValueError.OdbcErrors: #table({"SQLState", "NativeError", "Message"}, {})
Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error
Cluster URI: WABI-WEST-US-B-PRIMARY-redirect.analysis.windows.net
Activity ID: 809cbaf9-dd22-43ca-90ef-270f4977994e
Request ID: bab96e33-3aa3-ac4d-f4e0-5ff2287f0a19
Time: 2021-05-13 16:54:20Z

I have a PostgreSQL database that is connected as an ODBC data source. I'm also using a gateway (personal mode) for the refresh. It used to work well, but something is broken now, though I changed nothing. Both my gateway and the database are available. When I refresh the report from the desktop app, it works.

I see that it says 'timeout expired', but it takes less than a minute to faile.

Please help me understand what might be the issue here.

Thanks in advance.

[v-kkf-msft]

Hi @Anonymous ,

I think the ODBC: ERROR [08001] occurred because the data source connection timed out. Try to increase the CommandTimeout and the ConnectionTimeout parameters to see if it works.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-kkf-msft ,

Thank you for looking into this.

I've set the timeout values as you advised and republished the data source, but I get the same error still. 
When I hit the refresh button in Power BI service, it takes around 2 minutes, then fails.

Underlying error code: -2147467259
Underlying error message: ODBC: ERROR [08001] timeout expired

DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind: Odbc
Microsoft.Data.Mashup.ValueError.DataSourcePath: [commandtimeout=#duration(0,2,0,0),ConnectionTimeout=#duration(0,2,0,0)];database=cience-hubspot;driver={PostgreSQL Unicode(x64)};port=5432;server=157.245.182.184
Microsoft.Data.Mashup.ValueError.OdbcErrors: #table({"SQLState", "NativeError", "Message"}, {})
Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error
Cluster URI: WABI-WEST-US-B-PRIMARY-redirect.analysis.windows.net
Activity ID: 809cbaf9-dd22-43ca-90ef-270f4977994e
Request ID: e859ea15-8ae7-8da9-d518-c103d9c7b3a5
Time: 2021-05-18 18:03:38Z

[Anonymous]

Ok, the refresh doesn't work again. But now I have the same timeout error on some of the reports, and a new one on others:

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"ODBC: ERROR [08001] timeout expired\n"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"Odbc"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"database=mydb;driver={PostgreSQL Unicode(x64)};port=5432;server=myserver"}},{"code":"Microsoft.Data.Mashup.ValueError.OdbcErrors","detail":{"type":1,"value":"#table({\"SQLState\", \"NativeError\", \"Message\"}, {})"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: owners.
Cluster URI: WABI-WEST-US-B-PRIMARY-redirect.analysis.windows.net
Activity ID: dfce0d2c-7e1e-4979-8010-7e4f8f4103a5
Request ID: bda0df3d-8156-4e06-8ba5-e194b7441688
Time: 2021-05-14 17:03:58Z

 Though the data source and the gateway are the same for all the reports.

[GilbertQ]

Hi @Anonymous 

Could you have a look at your ODBC conneciton if there is an option to specify how long the timeout value is?

Also if you can make sure that your PostgreSQL is not slow in returning the query.

And finally to make sure that there are enough resources on your gateway to process the data quickly enough.

[Anonymous]

Hi @GilbertQ 

The refresh started to work again, and I still can't understand what was the reason for not working. I've edited one of my data source files and republished it (went to Settings -> Current File Privacy, and ticked Ignore the Privacy Levels option; I've also set 'Support row reduction clauses' to 'LIMIT and OFFSET' in the data source settings). Then I've published the file and it refreshed successfully.

However, I did nothing to my other data sources, which are basically the same and created only because there are several separate reports, while an ODBC data source can only be used by one report.

So I don't know why all started working...

I couldn't figure out if there is an option to set the timeout value.
My queries aren't slow.
Talking about the gateway resources - could you tell me how to check this?