MySQL ODBC connection works fine via a Gateway, but cannot configure a direct connection to MySQL

[ToddChitt]

After quite a bit of trial and error, I was finally able to get a MySQL ODBC DSN configured on both my local machine and the server on which the Gateway is installed such that my Power BI model can get data from the ODBC DSN. The data source is configured appropriately in the Gateway. Scheduled refreshed succeed. Life is good.

But there is a serious issue with the ODBC driver that is not the subject of this post.

And so, after a lot more trial and error, I was able to make a direct connection from Power BI Desktop to the MySQL database/server (running in AWS). Note, this is the same server, database, username and password as was configured in the ODBC DSN. So far, so good.

But after deploying this new model to the service, I tried to setup Scheduled Refresh. It requires a Gateway. OK, I can use that same Gateway that currently supports the ODBC data source. But after going through the setup, using the EXACT SAME settings as the ODBC driver (which works just fine) I get this error:

Any ideas? Thanks in advance.

[GilbertQ]

Hi @ToddChitt 

I would check to see if there is another version of the driver to use. In the past I have connected to a MySQL database through the gateway and I did not have any issues or challenges?

[ToddChitt]

@GilbertQ Thanks for the suggestion. I will give that a try. As of a week ago I was on the latest version of MySQL, AND I think the Gateway server is as well. If there is a disconnect between the two driver versions, could that be the issue, do you think? Does the version installed on the Gateway server even come into play here? In other words, who is making the query to MySQL to get data, the Gateway server, or Microsoft Power BI? I suspect the Gateway server.

I have the Gateway configured to "Allow refresh of cloud based data sources through this gateway."

[GilbertQ · Accepted solution]

Hi @ToddChitt 

You are right in that you need to have the driver installed on the Gateway. I would suggest logging into the gateway server, open the PBIX and make sure that it refreshes there successfully. This will ensure that it is using the driver that the gateway will use too.

[ToddChitt]

The gateway server is a bare bones machine to which I do not have access (controlled by the client). But it looks like bringing everybody up to the latest version of the MySQL drivers fixed some issues. (Did they jump from 5.X to 8.X version numbers?)