MySQL Bugs: #111326: An error happened while reading data from the provider: ‘Object cannot be cast f

Bug #111326	An error happened while reading data from the provider: ‘Object cannot be cast f
Submitted:	8 Jun 2023 7:48	Modified:	28 Jun 2023 10:02
Reporter:	Guram jalaghonia	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	8.0.32	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Hello Dear Support,

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

How to repeat:
This error repeats all the I try to connect a database using old-existing connection or try to use old one.

The "Object cannot be cast from DBNull to other types" exception is a duplicate of bug #109331.

As per comments on that case:

* the Oracle MySQL team does not support MariaDB with MySQL Connector/NET (MySql.Data)
* users recommend an alternative NuGet package that is compatible

Dear Bradley Grainger,

Thank you so much for your response.
I see, the only reason why I decided to contacted MySQL team that before refreshing Database, I used MySQL connector and it worked well.

Can you give me more details about NuGet package, what is it, where I can find it and how should I use it?

Best Regards,
Guram

Guram,

If you're receiving this error inside Microsoft Power BI, I think you need to contact the creator of the Connector you're using to connect Power BI to MariaDB. If the connector is advertised as supporting MariaDB, then provide your exception message (and a link to https://bugs.mysql.com/bug.php?id=109331) to let them know that it's not working. Their support team will need to resolve this issue. (If their connector is written in C#, they can switch to the "MySqlConnector" NuGet package in order to fix this issue.)

If your Power BI connector doesn't provide support for MariaDB, you will need to find one that does.

This problem has arisen in MariaDB versions 10.10.x or so (see -> https://jira.mariadb.org/browse/MDEV-27009) due to the Collation table and the addition of nullable columns.

If switching to another connector is not an option, you can apply the following fix to the file mysql-connector-net\MySQL.Data\src\Driver.cs in Method LoadCharacterSetsAsync:

 if (!Convert.IsDBNull(reader["id"])) {
                CharacterSets[Convert.ToInt32(reader["id"], NumberFormatInfo.InvariantInfo)] =
                    reader.GetString(reader.GetOrdinal("charset"));
            }

We're simply checking for the null id value and skipping the hash table add for now.

I've tested this with complex T4 Templates in VS.NET 2022 that uses MySql.Data.

You'll have to make sure to add the reference to the newly compiled MySql.Data.dll file directly, for any consuming client. Target the .NET 4.x framework if using with T4 templates within VS.NET. Or, for other clients with different framework requirements, generate a NuGet package using VS.NET. Note, you may have to uninstall the official connector to get rid of the GAC entries.

I've used the latest source as of Jun 27/2023.

Cheers.

Hello Dear Slam,

Thank you so much for your response.
I understood that the main problem is updated version of MariaDB.
I'm not so tech to understand how to fix the problem.
May be it's possible to give me fixed connector?

Best regards,
Guram