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();
        }
    }
}

You did not show any code with a line of `Database.Open();` . Opening a database connection is not a cast and does not involve other types so your error is very, very likely elsewhere — Ňɏssa Pøngjǣrdenlarp, Oct 13 '22 at 18:35
I did a little decompilation and found that `LoadCharacterSets` is being called when the connection is opened the first time. It's execting `SHOW COLLATION` and reading the record set. Apparently,. it's getting a `NULL` for the `id` column,, and `Convert.Int32` is throwing because of that. If you execute that statement in a query tool, what do you get (please [edit] the question and add the record set)? — madreflection, Oct 13 '22 at 19:01
That said, consider switching to [MySqlConnector](https://www.nuget.org/packages/MySqlConnector). It's a better library for MySQL/MariaDB in general. — madreflection, Oct 13 '22 at 19:04
ŇɏssaPøngjǣrdenlarp check it now. Hope that now you can help — AdamA, Oct 13 '22 at 20:36
Do not try to re-use the same connection throughout the program. ADO.Net already does connection pooling for you, and this interferes with it in a way that makes things slower. — Joel Coehoorn, Oct 13 '22 at 20:36
madreflection after running SHOW COLLATION I got huge table so here is link to google drive, it is in excel: [link](https://docs.google.com/spreadsheets/d/173r_HQ_KUH6NM7BGN41otiNVLXQPgwjU/edit?usp=sharing&ouid=116320123815863972557&rtpof=true&sd=true). There are really null values. Don't know why because on my PC there are no null values. — AdamA, Oct 13 '22 at 21:03
So, [this is interesting](https://mariadb.com/kb/en/how-do-i-set-accent-insensitivity/). This doesn't appear to be a misconfiguration. But even the latest MySql.Data package still does this query without checking for NULL, so I don't know why they would have done that. MySqlConnector doesn't do this as far as I can tell. Try it out. — madreflection, Oct 13 '22 at 22:19
So I updated to MySqlConnectionStringBuilder but still the same result. Everything ok on my PC but same error message on Win Server. — AdamA, Oct 14 '22 at 21:29
*"updated to MySqlConnectionStringBuilder"* - That's *not at all* what was suggested. You're still using the MySql.Data package. — madreflection, Oct 14 '22 at 22:30
So I removed MySql.Data.MySqlClient and added MySqlConnector. Now it finally works everywhere even without MySqlConnectionStringBuilder. So it is enough to just change the libraries. Thank you a lot. I will change the code in question to old one and you can write changing libraries as the answer. Then i will mark it as correct one. Thank you again, it really helped me a lot. — AdamA, Oct 15 '22 at 07:37

Miguel McFly · Accepted Answer · 2023-01-11T00:25:02.080

14

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

edited Jan 11 '23 at 00:25

answered Dec 09 '22 at 00:00

Miguel McFly

156
5

Can you elaborate a little bit on what all needs to be switched over going from mysqlclient to mysqlconnector? – user2280032 Jan 10 '23 at 06:12
Even if MySqlConnector is preferable (better performance and defaults), PR pushed (https://github.com/mysql/mysql-connector-net/pull/51) to correct that. Using CharacterSet=utf8mb4 with 10.10.1+ is a must-have even if accepted until https://jira.mariadb.org/browse/MDEV-30164 is released. – Diego Dupin Apr 21 '23 at 12:21

Ville · Answer 2 · 2022-12-03T22:13:37.310

8

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.

edited Dec 03 '22 at 22:13

answered Dec 03 '22 at 22:12

Ville

81
3

score 2 · Answer 3 · answered Dec 16 '22 at 10:14

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!

score 0 · Answer 4 · answered Dec 07 '22 at 08:49

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.

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

4 Answers4

Linked

Related