How to fix System.Collections.Generic.KeyNotFoundException “The given key was not present in the dictionary” Exception 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.

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
• 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());
}Code language: C# (cs)

How to: Test MySQL database connectivity in ASP.NET, PHP, ASP

MySQL Connector/ODBC C# test script

Are you looking for rock solid, eco-friendly, .NET hosting? Look no further! UmbHost offers powerful hosting services for websites and businesses of all sizes, and is powered by 100% renewable energy!

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

character_set_server utf8mb4
collation_server = utf8mb4_unicode_ciCode language: SQL (Structured Query Language) (sql)

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());
}Code language: C# (cs)

Only use this as a temporary fix, you should still update Connector/NET ASAP. That’s it!

Show Your Support

If you want to step in to help me cover the costs for running this website, that would be awesome. Just use this link to donate a cup of coffee ☕($10 USD or €10 EUR for example). And please share the love and help others make use of this website. Thank you very much! <3 ❤️