Power BI stopped loading Azure-hosted MySQL data. Stuck on "Evaluating..."

[archinate]

Hello,

In the last week or so, my Power BI dashboards have become stuck refreshing/loading data from our Azure-hosted MySQL database. I can verify that the connection to our database has been successful because Power BI will list available tables and views. However, the data will never load into the Power BI file.

In this case, I am trying to load a table with only 535 rows. The preview never loads.  The Load screen has that table stuck on "Evaluating".

Our dashboards that have beeing using this server for months will no longer refresh data. The Azure server appears to be functioning normally and I have been able to connect to this server and get data from other database tools. Furthermore, I have been able to successfully connect and load data from other sources hosted on AWS.

Any suggestions?

[Greg_Deckler]

Very little to go on here, do you have a Pro account to open a support ticket?

[archinate]

Hi @Greg_Deckler

Not much to go on, indeed! I can find no indicator as to why Power BI is getting stuck on "Evaluating" for my Azure database. As I mentioned, my other apps can connect successfully. I have tried resetting my permissions, etc.

I am using the standard (re: free) Power BI account.

My issue seems related to this post: https://community.powerbi.com/t5/Integrations-with-Files-and/Long-time-quot-Evaluating-quot-for-no-a...

Some of these users have said that downgrading to earlier releases (July/June) solved the issue for them and that Aug/Sept have resulted in problems. I have tried downgrading without success.

Best,

-Nate

[archinate]

I believe I have found the cause of my issue, but (I am unsure of the best solution):

When connecting to a table, my server log shows that Power BI is using MySQL's INFORMATION_SCHEMA. There are many other databases on our server (500 or so) so when PowerBI queries INFORMATION_SCHEMA, PowerBI is opening all tables across all databases on the entire database server which floods the MySQL server's table_open_cache limit on Azure and degrades performance. This seems to be quite inefficient for Power BI to access a table in this manner.

With that said... if I use a NATIVE SQL query to a table or a view, I am able to load my data without flooding my table_open_cache. However this is not a perfect solution because my users will need to approve native database queries (or manually uncheck this security in their options). I was hoping to avoid this step.

Here is an excerpt from my server log of the command Power BI is sending that floods the table_open_cache on Azure. Note the use of INFORMATION_SCHEMA to get the table:

# Time: 2018-10-01T02:47:10.331546Z
# User@Host: nmiller[nmiller] @  [XXXXXXXXXXX]  Id:   351
# Query_time: 952.882748  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 28168
SET timestamp=1538362030;
select
    fkcol.REFERENCED_COLUMN_NAME as PK_COLUMN_NAME,
    fkcol.TABLE_SCHEMA AS FK_TABLE_SCHEMA,
    fkcol.TABLE_NAME AS FK_TABLE_NAME,
    fkcol.COLUMN_NAME as FK_COLUMN_NAME,
    fkcol.ORDINAL_POSITION as ORDINAL,
    concat(fkcon.CONSTRAINT_SCHEMA, '_', fkcon.CONSTRAINT_NAME) as FK_NAME
from
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fkcon
        inner join
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkcol
        on fkcon.CONSTRAINT_SCHEMA = fkcol.CONSTRAINT_SCHEMA
        and fkcon.CONSTRAINT_NAME = fkcol.CONSTRAINT_NAME
where fkcol.REFERENCED_TABLE_SCHEMA = '00_projectsdb' and fkcol.REFERENCED_TABLE_NAME = 'list_databases'
    and fkcol.ORDINAL_POSITION = fkcol.POSITION_IN_UNIQUE_CONSTRAINT
order by concat(fkcon.CONSTRAINT_SCHEMA, '_', fkcon.CONSTRAINT_NAME), fkcol.ORDINAL_POSITION;