MySQL Connector NET doesn't working

[guro2525]

Hello Dear friends,

I have a next problem:  I didn't manage to get data from a database. This happened after changing the database server and a database version. Before changing everything work fine, it was "MariaDB 10.5", after changing become MariaDB 10.11.

If I try to create a new connection I get an error:

--We encountered an error while trying to connect. Details: “An error happened while reading data from the provider: ‘Object cannot be cast from DBNull to other types.’”--

If I try to refresh an existing connection I get some errors:

--DataSource.Error: An error happened while reading data from the provider: 'Object cannot be cast from DBNull to other types.'--

--DataSource.Error: An error happened while reading data from the provider: 'The given key was not present in the dictionary.'--(If there are some steps in Power Query).

I have a last version of  Power Bi, I can connect to the database through MySQL Workbench 8.0 CE without any problem.

I think that problem may be MySQL Connector NET 8.0.32, it's not a last version, last version is MySQL Connector NET 8.0.33, but it doesn't work.

Using last version of the this software, I get an next error:

--This connector requires one or more additional components to be installed before it can be used.

Microsoft Powe Bi didn't manage to help me, may somebody had appropriate experience an dcan help me.

Thank you so much.

Best Regards,

Guram

[felipeidrobo98]

Hello, I have the same problem

Finally, did you found a solution?

[v-yanjiang-msft]

Hi @guro2525 ,

For the problem " last version is MySQL Connector NET 8.0.33, but it doesn't work." It's a known issue from Oracle side. Oracle made a change, and that version does not correctly register itself on the system when you install it.

Please try the solution in this similar thread: 

powerbi - Power BI missing MySQL Connector, even though I installed it - Stack Overflow

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[guro2525]

Hello @v-yanjiang-msft!,

Thank you so much for your response!
Your solution solve this problem, but  using previous version I had another error:

--We encountered an error while trying to connect. Details: “An error happened while reading data from the provider: ‘Object cannot be cast from DBNull to other types.’”--

May be you have any experience how to solve this problem?

Best Refards,
Guram

[v-yanjiang-msft]

Hi @guro2525 ,

According to the error message"Object cannot be cast from DBNull to other types", please try the below solution in this blog

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[guro2525]

Dear @v-yanjiang-msft,

Thank you so much for your your response.
I'm not sure thath this is a cause of my problem, because MariDB connector easily connect my database without any problem. Is it was somethingg worng with database, I think that would be a problem not only for MySql connector.

Best Regards,
Guram

[v-yanjiang-msft]

Hi @guro2525 ,

>> MariDB connector easily connect my database without any problem.

Didn't the error happen when you connected?

What I highlighted in the image is about database instead of MySql connector.

Best regards,

Community Support Team_yanjiang

[guro2525]

Dear @v-yanjiang-msft,

Yes, when I use MariDB connector, I don't have this error "Object cannot be cast from DBNull to other types". I successfully connects the database everytime.

Using MySql connector I have this error, using MariDB connector I don't have this error.
That's why I think that the database dont't have a problem.

Best Regards,
Guram

[lbendlin]

Rewrite your custom query to avoid the casting.

[guro2525]

Dear @lbendlin,

Unfortunately, his error happenes when I try to connect database, I can't move on the enxt step and change comthing in query editor.

Best regards,
Guram

[lbendlin]

Wow, this is still an issue after all this time? Good thing we stayed on 8.0.28 ...

[lbendlin]

Have you considered using the MariaDB driver instead of the MySQL driver?

[guro2525]

Hello @lbendlin ,

Thank you so much for your response.
I've tried this method and it really works!

Unfortunatelly, MariaDB ODBC Connector doesn't have an advnace option where you can use SQL Statemnet (Custom Query).

Do you have any idea how to solve this?
I reaaly need ti because I use sutom queris 😞

[ghaani]

Hi. I,ve the same problem .Colud you find any solution for your problem ? 

[felipeidrobo98]

Hello ghaani, 

I,ve create a ODBC Connection and connect my sources again....☹️