System.Collections.Generic.KeyNotFoundException: The given key '21836' was not present in the dictionary

[andreabertanzon]

Hello, Having troubles installing packages (and even Install-DBOScript) towards a MySQL Database.

This is the stack trace:

System.Collections.Generic.KeyNotFoundException: The given key '21836' was not present in the dictionary.
   at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at MySql.Data.MySqlClient.MySqlField.SetFieldEncoding()
   at MySql.Data.MySqlClient.MySqlField.set_CharacterSetIndex(Int32 value)
   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, Boolean force)
   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.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at DBOps.MySql.MySqlTableJournal.GetTableVersion(Func`1 dbCommandFactory)
   at DBOps.MySql.MySqlTableJournal.StoreExecutedScript(SqlScript script, Func`1 dbCommandFactory)
   at DbUp.Support.ScriptExecutor.<>c__DisplayClass18_0.<Execute>b__0(Func`1 dbCommandFactory)
   at DbUp.Engine.Transactions.NoTransactionStrategy.Execute(Action`1 action)
   at DbUp.Engine.Transactions.DatabaseConnectionManager.ExecuteCommandsWithManagedConnection(Action`1 action)
   at DbUp.Support.ScriptExecutor.Execute(SqlScript script, IDictionary`2 variables)
[18:03:28][Install-DBOScript] Upgrade failed due to an unexpected exception:
System.Collections.Generic.KeyNotFoundException: The given key '21836' was not present in the dictionary.
   at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at MySql.Data.MySqlClient.MySqlField.SetFieldEncoding()
   at MySql.Data.MySqlClient.MySqlField.set_CharacterSetIndex(Int32 value)
   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, Boolean force)
   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.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at DBOps.MySql.MySqlTableJournal.GetTableVersion(Func`1 dbCommandFactory)
   at DBOps.MySql.MySqlTableJournal.StoreExecutedScript(SqlScript script, Func`1 dbCommandFactory)
   at DbUp.Support.ScriptExecutor.<>c__DisplayClass18_0.<Execute>b__0(Func`1 dbCommandFactory)
   at DbUp.Engine.Transactions.NoTransactionStrategy.Execute(Action`1 action)
   at DbUp.Engine.Transactions.DatabaseConnectionManager.ExecuteCommandsWithManagedConnection(Action`1 action)
   at DbUp.Support.ScriptExecutor.Execute(SqlScript script, IDictionary`2 variables)
   at DbUp.Engine.UpgradeEngine.PerformUpgrade()

The code:

$sqlParams= @{
            ConnectionString   = $ConnectionString
        Schema             = $Schema
        Type               = $RDBMS 
        OutputFile         = $OutputLogFile 
        SchemaVersionTable = $SchemaVersionTable
        }
$package = New-DBOPackage -Build $i -Name "test-$i" -ScriptPath "$DatabaseSchemasPath\sprint$i"
$package | Install-DBOPackage @sqlParams -Verbose

The code seems to work if I change the connection string and Type to sqlserver and adapt the queries to T-SQL.

[nvarscar]

There are some good answers in this StackOverflow thread, perhaps try some of them (aside from upgrading the MySql.Data version)? In the past, DbUp, which DBOps is built upon, was pretty strict with which driver versions it supports. If none of the suggestions work, I'll look into upgrading the driver, which might also require updating DbUp version.

[andreabertanzon]

Thanks for the reply, Unfortunately I tried all the answers (except mysql.data.dll) and nothing worked. For example adding configs in the connection string made also the Invoke-DBOQuery not work anymore(same key error), while previously it was working at least with existing databases and tables (gave errors while creating tables though)

[nvarscar]

Unfortunately, the upstream project (DbUp) has a dependency on a pretty outdated version of the MySQL driver. You can try to update the driver to the most recent version manually and see if DBOps can work with a version outside of supported version range. To do so

1. Edit the dependency file on your machine to allow for higher versions.
2. Load up the new library into your runtime using Add-Type -Path .\library.dll. Do not use or load DBOps into your runtime just yet.
3. Try to run your code.

[nvarscar]

Good news! I can confirm that the more recent MySQL library is properly working with DBOps and I will be updating it soon with a minor version bump

[nvarscar]

Starting from version 0.9.0, MySQL library is supported up to version "MaximumVersion": "8.0.31". Anything outside of the supported range you can still use, but you'll have to pre-load it manually before running the deployment.