MySqlConnection.Open() System.InvalidCastException: Object cannot be cast from DBNull to other types

Question

I have simple connectionstring to MySql (MariaDB 5.5.5-10.11.0) written in c#:

MySqlConnection Database = new MySqlConnection("Server=127.0.0.1; Port=3306; Database=test; Uid=user; Pwd=MyPassword; Ssl Mode=Required; convert zero datetime=True;");

Everything works fine on two computers (Windows 10 and Windows 11). But when I try to launch this app on Windows Server 2022 I get this error:

System.InvalidCastException: Object cannot be cast from DBNull to other types.
   at System.DBNull.System.IConvertible.ToInt32(IFormatProvider provider)
   at System.Convert.ToInt32(Object value, IFormatProvider provider)
   at MySql.Data.MySqlClient.Driver.LoadCharacterSets(MySqlConnection connection)
   at MySql.Data.MySqlClient.Driver.Configure(MySqlConnection connection)
   at MySql.Data.MySqlClient.MySqlConnection.Open()
   at MariaDB.Program.StartAPI()

Error is thrown on Database.Open();

MariaDB is installed and running, Ssl is working, user's pemissions are granted, port is correct. Any ideas please?

Whole program:

using System;
using MySql.Data.MySqlClient;

namespace MariaDB
{
    internal class Program
    {
        MySqlConnection Database = new MySqlConnection("Server=127.0.0.1; Port=3306; Database=test; Uid=user; Pwd=MyPassword; Ssl Mode=Required; convert zero datetime=True;");

        static void Main(string[] args)
        {
            Program p = new Program();
            p.OpenDB();
        }

        private void OpenDB()
        {
            Database.Open();
            Console.WriteLine("Ok");
            Console.ReadLine();
        }
    }
}

Answer 1

The same thing happened to me. Change MySql.Data.MySqlClient to MySqlConnector and the problem was solved

The database I use is already in production so it was easier for me to change the connector instead of downgrading my database

I detail a little what I did:

• My project was working on Visual Studio 2017 with .NET Core 2.1. I had to update to Visual Studio 2022 and change the .NET Core to version 3.1
• In NuGet package manager uninstall MySql.Data and install MySqlConnector version 2.2.2
• Follow the recommendations of the official page MySqlConnector

Answer 2

This is caused by MariaDB 10.10.1 making ID field Nullable in Information_Schema.Collations and adding a bunch of Collations that have null for an ID.

https://jira.mariadb.org/browse/MDEV-27009

One possible workaround is to use MariaDB 10.9 or older.

Answer 3

Here is how the MySql.Data load collations:

This method assume that the "id" column's value is not null. Unfortunately, from the version 10.10, MariaDB switch the "Id" column to "Allow Null" and there are a lot of row in Collations with NULL Id. That causes the "Object cannot be cast from DBNull to other types" exception.

Seem 10.9 is the only option for now if you wish to use the MySql.Data package and wait for a fix in MySql.Data.

Answer 4

I tried latest RC as well and it seems MariaDB will have this problem forever or atleast current MySql.Data.MySqlClient have a problem with it.

The suggested answer is either rolling back to 10.9 or using a totally different connector like MysqlConnector.

To switch to MysqlConnector, simply install that in your project via NuGet or if you want to build it yourself download it from Git.

Then in your app.config or web.config add the new dataProvider so .net knows about it. ex, in app.config/web.config add:

<configuration><system.data><DbProviderFactories>
<remove invariant="MySqlConnector"/>
      <add name="MySqlConnector" invariant="MySqlConnector" description="Async MySQL ADO.NET Connector" type="MySqlConnector.MySqlConnectorFactory, MySqlConnector, Culture=neutral" />
</DbProviderFactories>

then use it in your connectionStrings make sure you use dataProvider="MysqlConnector"

Special thanx to Ville & Miguel McFly!

Answer 5

Suffering from the same issue and Oracle to not merging the related PR, i made a fork & a nuget package to deal with it. Migrating to MysqlConnector is not always a possibility.

Forked package: https://www.nuget.org/packages/MediReport.MySql.Data/8.1.0

Original PR: https://github.com/mysql/mysql-connector-net/pull/51/commits/7a7aeec16807a17e07d55de1ae83b37aa9a960a7

Answer 6

If you can't migrate to MySqlConnector try this and let me know if it helped. Just published pre-pre-alpha version of package trying to fix this issue.

NuGet: https://www.nuget.org/packages/Jf.MySql.Data.Collations/

Sources: https://github.com/jeffraska/Jf.MySql.Data.Collations/

The package is in "works on my computer" state. This means .NET framework 4.5, really old MySql.Data 6.9.7 library and latest MariaDB 10.11.4 currently available in Debian repository. So don't expect miracles.

It uses standard Command Interceptor functionality of MySql.Data library (https://dev.mysql.com/doc/connector-net/en/connector-net-interceptors.html) to alter all SHOW COLLATION queries and replacing it with SHOW COLLATION WHERE id IS NOT NULL.

Also the package have functionality to alter MySql.Data's internal charset mapping to allow reading utf8mb3 fields in pre 8.0.28 versions of MySql.Data .

Answer 7

MariaDB recently released fix for it https://jira.mariadb.org/browse/MDEV-31608, in 10.11.7 and later.

If you can't use MySqlConnector, you can use latest MariaDB server version. (But if you can use MySqlConnector, it is very much worth trying, it is a better one in terms of quality, and performance)