Couldn't load the data for this visual

[zahirmohideen]

Hi


I have a power bi report connected to MariaDB data source ( on premises ) via direct mode. 
The underlying data set is a view .
The report stops working intermittently and resumes works after few days.


I am getting "invalid connection credentials" , when there is an issue. 
Checked the credentials from mariadb server via multiple user interfaces . Connects with no issues.

Gateway is up to date. Query Cache size in MariaDB looks good on the mariadb




Here is the error from PBI service.

<error>
Couldn't load the data for this visual

Couldn't retrieve the data for this visual. Please try again later.
Please try again later or contact support. If you contact support, please provide these details.

</error>

When I try to open the data source ( pbi file) now from PBI desktop , I get the following .

<error>
[ma-3.1.19][10.3.35-MariaDB-log]Prepared statement needs to be re-prepared
</error>

Here are the cache settings in the MariaDB .
------------------------------------------

MariaDB [(none)]> show variables like '%cache%';
+--------------------------------+----------------------+
| Variable_name | Value |
+--------------------------------+----------------------+
| aria_pagecache_age_threshold | 300 |
| aria_pagecache_buffer_size | 134217728 |
| aria_pagecache_division_limit | 100 |
| aria_pagecache_file_hash_size | 512 |
| binlog_cache_size | 32768 |
| binlog_file_cache_size | 16384 |
| binlog_stmt_cache_size | 32768 |
| have_query_cache | YES |
| host_cache_size | 279 |
| innodb_disable_sort_file_cache | OFF |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_total_cache_size | 640000000 |
| join_cache_level | 2 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| key_cache_file_hash_size | 512 |
| key_cache_segments | 0 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| metadata_locks_cache_size | 1024 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_strip_comments | OFF |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| stored_program_cache | 256 |
| table_definition_cache | 400 |
| table_open_cache | 2000 |
| table_open_cache_instances | 8 |
| thread_cache_size | 151 |
+--------------------------------+----------------------+
33 rows in set (0.001 sec)

MariaDB [(none)]> select version();
+---------------------+
| version() |
+---------------------+
| 10.3.35-MariaDB-log |
+---------------------+
1 row in set (0.000 sec)


MariaDB [(none)]> SHOW STATUS LIKE 'Qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031320 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
8 rows in set (0.000 sec)

[GilbertQ]

Hi @zahirmohideen 

There are always a lot of challenges when working with DirectQuery. It could be an issue with the gateway, MariaDB and a combination of the visual that you are trying to use. As well as potential filters which are trying to be passed through to your MariaDB.

I would suggest trying with a simple visual to see if that works without having an big filters applied. If that always works then it has to do with the way the data is being interacted with.

[zahirmohideen]

Hi @GilbertQ 

I tried with the simple visual as well . It is so random / intermittent. I tried both direct query / import . Still the same issue . Seems to be issue with Maria DB ODBC connector forcing the view to reparse / re-prepare .

Filed a case with MS . Since this is a 3rd party connector . They suggested me to file a request in the GitHub for Power BI 3rd party.