Sysadmins of the North
Don't forget to share this post!

System.Collections.Generic.KeyNotFoundException “The given key was not present in the dictionary” with MySQL Connector/NET and utf8mb4 character set

An System.Collections.Generic.KeyNotFoundException “The given key was not present in the dictionary” can be the result of using a too old MySQL Connector/NET version in your ASP.NET web application. A KeyNotFoundException is thrown when an operation attempts to retrieve an element from a collection using a key that does not exist in that collection. An unsupported character set like utf8mb4 can be such a key, if your Connector/NET doesn’t support this character set. Luckily there is an easy workaround for this.

MySQL (Oracle) Connector/NET versions prior to 6.0.8, 6.1.6, 6.2.5, 6.3.6 lack a mapping for UTF8MB4 as charset. Connecting to a MySQL database and querying a table that has been created with CHARSET=utf8mb4 results in a .NET exception:

Exception Details: System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.

System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary. at System.Collections.Generic.Dictionary`2.get_Item(TKey key) at MySql.Data.MySqlClient.CharSetMap.GetCharacterSet(DBVersion version, String CharSetName) at MySql.Data.MySqlClient.CharSetMap.GetEncoding(DBVersion version, String CharSetName) at MySql.Data.MySqlClient.Driver.Configure(MySqlConnection connection) at MySql.Data.MySqlClient.MySqlConnection.Open() at ASP.mysql_data_aspx.MySQLConn()

A more extended exception is:

System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary. at System.Collections.Generic.Dictionary`2.get_Item(TKey key) at MySql.Data.MySqlClient.CharSetMap.GetCharacterSet(DBVersion version, String CharSetName) at MySql.Data.MySqlClient.MySqlField.SetFieldEncoding() at MySql.Data.MySqlClient.NativeDriver.GetColumnData(MySqlField field) at MySql.Data.MySqlClient.NativeDriver.GetColumnsData(MySqlField[] columns) at MySql.Data.MySqlClient.Driver.GetColumns(Int32 count) at MySql.Data.MySqlClient.ResultSet.LoadColumns(Int32 numCols) at MySql.Data.MySqlClient.ResultSet..ctor(Driver d, Int32 statementId, Int32 numCols) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlDataReader.Close() at MySql.Data.MySqlClient.MySqlCommand.ResetReader() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() at ASP.mysql_data_aspx.MySQLConn()

Googling for a fix, some suggestions were to add CharSet=utf8; to your connection string. Unfortunately I was unable to resolve the exception with this added.

This may interest you:   .NET Framework 4.6 allows side loading of Windows API Set DLL

The best way to fix this System.Collections.Generic.KeyNotFoundException with Connector/NET is to simply update your Connector/NET version. Support for utf8mb4 charset is built in in versions 6.0.8, 6.1.6, 6.2.5, 6.3.6+:

MySQL Connector/NET did not support the utf8mb4 character set. When attempting to connect to utf8mb4 tables or columns, an exception KeyNotFoundException was generated. (Bug #58244)

https://dev.mysql.com/doc/relnotes/connector-net/en/news-6-0-8.html

And MySQL Connector/NET now supports MySQL servers configured to use utf8mb4 as the default character set in version 8.0.9.

MySQL Connector/NET now supports MySQL servers configured to use utf8mb4 as the default character set.

https://dev.mysql.com/doc/relnotes/connector-net/en/news-8-0-9.html

If, for some reason, you cannot or will not update your MySQL Connector/NET version anytime soon, there is an easy workaround available; SET NAMES 'utf8'. Yes, that’s right: as your first statement, set the three session system variables character_set_client, character_set_connection, and character_set_results to the given character set utf8. You can even use latin1, but that may give some undesired encoding issues…

Imaging the following C# MySql.Data.MySqlClient test script for querying your MySQL database table:

string sql = "select * from aspnet_site_comments";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read()) {
	Response.Write(rdr[0]+ "<br/>");
}
rdr.Close();
}

catch (Exception ex) {
	Response.Write(ex.ToString());
}

If your MySQL database server has in its my.cnf server config:

character_set_server utf8mb4
collation_server = utf8mb4_unicode_ci

And your table aspnet_site_comments is created using ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci, it throws an System.Collections.Generic.KeyNotFoundException exception. Alter your code to first set the character_set_* to utf8 (don’t mind my pseudo-code, you’ll get the idea) as a workaround for this issue:

string setcharset = "SET NAMES 'utf8'";	// <-- !!
MySqlCommand charsetcmd = new MySqlCommand(setcharset, conn);
MySqlDataReader charsetrdr = charsetcmd.ExecuteReader();
charsetrdr.Close()

string sql = "select * from aspnet_site_comments";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read()) {
	Response.Write(rdr[0]+ "<br/>");
}
rdr.Close();
}

catch (Exception ex) {
	Response.Write(ex.ToString());
}

Only use this as a temporary fix, you should still update the Connector/NET version you use ASAP.

This may interest you:   How to flush MySQL query cache from time to time

That’s it!


buy me a coffee
Buy Me A Coffee

About the Author Jan Reilink

My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 15 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, WordPress, websites & optimization. Want to support me and donate? Use this link: https://paypal.me/jreilink.

follow me on:

Leave a Comment:

Skip to content