Import data from MariaDB with text filter raises SQL syntax error (Reproducible!)

[haje01]

Hi,
I'm using PowerBI (version 2.92.706.0, 64-bit) and MariaDB as data source. I downloaded ODBC driver versions 3.1.11 from following link:
https://mariadb.com/downloads/?showall=1&tab=connectors&group=mariadbconnectors&product=ODBC%20conne...
 
Connection & Simple import job worked well. But when I add text filtering (equality check) in Power Query Editor, it raises SQL syntax error like this:
 
[ma-3.1.11][10.3.25-MariaDB-0ubuntu0.20.04.1-log]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LONG VARCHAR) = ? and `PARTNERS` is not null' at line 11
 
PARTERS is the column name which I add filter to.
 
It's very easy to reproduce the problem. In MariaDB just create a table like this:
 
create table test (name varchar(256), score int(11));
 
then, insert two rows:
 
insert into test values ('john', 90);
insert into test values ('nick', 80);
 
To get data from MariaDB in PowerBI, import 'test' table then click  'transform data'. In resulting Power Query Editor, add text filter for name 'nick'. Then close & load.
 
That's all, I hope you could reproduce the problem, and fix it SOON!
(In my opnion, it's related with length of VARCHAR. VARCHAR(10) column did not raised the problem)
 
Best.

[v-lili6-msft]

hi

I have tested on my side, I downloaded ODBC driver versions 3.1.12

It works well.

Please use ODBC driver versions 3.1.12 to have a try again

Regards,

Lin

[haje01]

Thank you @v-lili6-msft for your reply. I've installed new ODBC driver 3.1.12, still the error happens.

[ma-3.1.12][10.3.25-MariaDB-0ubuntu0.20.04.1-log]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LONG VARCHAR) = ? and `PARTNERS` is not null' at line 11

Did you created test table with VARCHAR(256)? I found that VARCHAR less than 255 in length is okay.

Best,

[v-lili6-msft]

hi

Yest, I tested use different lengths, all works well.

and my version is 10.5.9

Regards,

Lin

[haje01]

@v-lili6-msftIn 'Get Data' dialog, did you use MariaDB Connector or ODBC Connector ? I used MariaDB Connector.

[v-lili6-msft]

hi

i used ODBC Connector,

and now I test it with MariaDB Connector, could reproduce the problem.

I will report this issue, will update here once I get any information.

as a workaround, you could use ODBC Connector instead of MariaDB Connector.

Regards,

Lin

[haje01]

@v-lili6-msft 

I am glad that you have succeeded in reproducing that problem.

Thank you for your support.

Best.

[RoguytJeannin]

I also confirm that this issue also occurs when you are trying to concatenate two columns in PowerBI using the standard MariaDB Connector.

Switching to the ODBC one fixes this issue.

[haje01]

@RoguytJeannin 

We can't use ODBC Connector because we need to use DirectQuery.

Thank you.

[v-lili6-msft]

hi

I get this from PG:

We don't own Maria DB connector. The customer should talk to the owners of the connector. It does not look like a certified connector either.

you may need to connect it with Maria DB support.

Regards,

Lin

[haje01]

Ok, I reported the case on the MariaDB's forum:

https://mariadb.com/kb/en/powerbi-import-data-with-text-filter-raises-sql-syntax-error-reproducible/

I hope they can fix the problem.

Thank you for your support.