You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTEGER) = ?

[dekiesel]

Hi,

I am getting this issue when filtering a column in PowerBI.

PowerBI: Version: 2.114.864.0 64-bit (Februar 2023)

MariaDB ODBC Connector: 3.1.17

Steps to reproduce:

Choose a table and select "Edit Query". In Power Query Editor select an Integer-Column and filter by any number. In the example faktentyp_didhas to be 1 to be selected.

let
    Source = MariaDB.Contents("mydb.cluster-ct9n3wlgjhhm.eu-central-1.rds.amazonaws.com", "performance_mart", true),
    fakt_reisezentrum_halbstundenwerte_Table = Source{[Name="fakt_reisezentrum_halbstundenwerte",Kind="Table"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(fakt_reisezentrum_halbstundenwerte_Table,{{"anzahl_kunden", "# Anzahl Kunden"}, {"summe_prozesszeit_empfang", "# Prozesszeit Empfang"}, {"summe_wartezeit", "# Wartezeit Gesamt"}, {"summe_transaktionszeit", "# Transaktionszeit Gesamt"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [faktentyp_did] = 1)
in
    #"Filtered Rows"

Click "Close & Apply". PowerBI will then try to get the data, but will fail with the following error:

The tracelog can be found here, it was too long for github.

[ilyagithub1]

@dekiesel Thank your detailed report and the trace log. What is the exact data type of the faktentyp_did column?

I cannot reproduce the issue with an INT column. My guess is that your faktentyp_did column has a data type other than integer.

I have created a simple test table with an integer column:

create table issue29(text_col varchar(255), int_col int);

Power BI generated the same CAST statement like in your log, but there were no runtime errors. It worked fine.

DataMashup.Trace Information: 24579 : {"Start":"2023-03-20T14:53:55.8239612Z","Action":"Engine/IO/Odbc/Command/ExecuteDirect","ResourceKind":"MariaDB","ResourcePath":"{\"MariaDB Data Source\":\"mariadbserver\"}","HostProcessId":"13084","CommandText":"select `text_col`,\r\n    `int_col`\r\nfrom `issue29`.`issue29`\r\nwhere cast(`int_col` as INTEGER) = ? and `int_col` is not null\r\nlimit 1000","ParameterCount":"1","Skip":"0","Take":"Infinite","Execution":"00:00:00.0874092","PageRowCount1":"1","Result":"00:00:00.0489453","ProductVersion":"2.115.663.0","ActivityId":"8f3eb0e7-9a62-47d3-8d33-795820f8f0d1","Process":"Microsoft.Mashup.Container.NetFX45","Pid":9284,"Tid":1,"Duration":"00:00:00.2435330"}

[dekiesel]

I just tried this:

create table powerbi_bug_test(text_col varchar(255), int_col int);

insert into powerbi_bug_test
values ("test1", 1),
        ("test2", 2)
;

The DB then says it's "int(11) NULL". I am getting the same error as before.

This is running in Amazon RDS, maybe that's the issue?

Here the DDL as returned by the DB after creation of the table:

CREATE TABLE `powerbi_bug_test` (
  `text_col` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `int_col` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

[ilyagithub1]

@dekiesel Thank you for the update.

What version of MariaDB do you run in Amazon RDS?

My test above was carried out in MariaDB Server 10.6.11.

[dekiesel]

@ilyagithub1

SHOW VARIABLES LIKE 'version'; says it's version 5.7.12-log, which probably means this is a mysql-DB.

Is this something that should work anyway?

[ilyagithub1]

@dekiesel Thank you for confirming your server version. It turns out you are trying to use MariaDB Connector to get data from a MySQL database.

For a MySQL database, please use the "MySQL database" connector option in the Get Data dialog - see the screenshot.

In Power BI, MariaDB and MySQL connectors use different database drivers underneath. Your issue may or may not be related to the underlying database driver. I might guess that after you switch to the native MySQL connector and driver, the issue might just disappear. Testing this assumption is out of scope of this support forum since it is dedicated to MariaDB Power BI Connector. But you are very welcome to share your results. Someone might run into the same issue, and your feedback is highly appreciated!