[NetCore/Linux/Microsoft.Data.SqlClient] SQLite Error: 'index UC_Version already exists'.

[gepa21]

1. I am testing with the sample from https://fluentmigrator.github.io/articles/quickstart.html (i will attach the code but it's basically just the sample with no modifications) FluentMigratorTest.zip
2. Using netcoreapp 2.2
3. In windows it runs fine but,

4. in ubuntu ( with published self-contained targeting linux-64) Runs ok the first time and every time after that i get :

   VersionUniqueMigration migrating

   Beginning Transaction CreateIndex VersionInfo (Version) !!! SQLite Error 1: 'index UC_Version already exists'. While Processing: "CREATE UNIQUE INDEX "UC_Version" ON "VersionInfo" ("Version" ASC)" !!! +- SQLite Error 1: 'index UC_Version already exists'. Rolling back transaction

Unhandled Exception: System.Exception: SQLite Error 1: 'index UC_Version already exists'. While Processing: "CREATE UNIQUE INDEX "UC_Version" ON "VersionInfo" ("Version" ASC)" ---> Microsoft.Data.Sqlite.SqliteException: SQLite Error 1: 'index UC_Version already exists'. at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db) at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext() at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery() at FluentMigrator.Runner.Processors.SQLite.SQLiteProcessor.ExecuteNonQuery(String sql) --- End of inner exception stack trace --- at FluentMigrator.Runner.Processors.SQLite.SQLiteProcessor.ExecuteNonQuery(String sql) at FluentMigrator.Runner.StopWatch.Time(Action action) at FluentMigrator.Runner.MigrationRunner.ExecuteExpressions(ICollection1 expressions) at FluentMigrator.Runner.MigrationRunner.ApplyMigrationUp(IMigrationInfo migrationInfo, Boolean useTransaction) at FluentMigrator.Runner.VersionLoader.LoadVersionInfo() --- End of stack trace from previous location where exception was thrown --- at Microsoft.Extensions.Internal.ActivatorUtilities.ConstructorMatcher.CreateInstance(IServiceProvider provider) at Microsoft.Extensions.Internal.ActivatorUtilities.CreateInstance[T](IServiceProvider provider, Object[] parameters) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProvider provider) at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType) at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider) at System.Lazy1.ViaFactory(LazyThreadSafetyMode mode) at System.Lazy1.ExecutionAndPublication(LazyHelper executionAndPublication, Boolean useDefaultConstructor) at System.Lazy1.CreateValue() at FluentMigrator.Runner.MigrationRunner.IsMigrationStepNeededForUpMigration(IMigrationInfo migration, Int64 targetVersion) at FluentMigrator.Runner.MigrationRunner.<>c__DisplayClass59_0.b__0(KeyValuePair2 pair) at System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext() at FluentMigrator.Runner.MigrationRunner.MigrateUp(Int64 targetVersion, Boolean useAutomaticTransactionManagement) at FluentMigratorTest.Program.Main(String[] args) Aborted (core dumped)

[eskitek]

I ran into a similar error today, running on Windows against a MySql database. I'm not sure if the root cause is the same as that in the original post, though.

      2021-10-07 04:08:40.499 [P7796:T1] [Information] VersionUniqueMigration migrating
      2021-10-07 04:08:40.500 [P7796:T1] [Information] Beginning Transaction
      2021-10-07 04:08:40.521 [P7796:T1] [Information] CreateIndex VersionInfo (Version)
      2021-10-07 04:08:40.523 [P7796:T1] [Information] CREATE UNIQUE INDEX `UC_Version` ON `VersionInfo` (`Version` ASC)
      2021-10-07 04:08:40.525 [P7796:T1] [Error] Duplicate key name 'UC_Version'
      MySql.Data.MySqlClient.MySqlException (0x80004005): Duplicate key name 'UC_Version'
         at MySqlPacket MySql.Data.MySqlClient.MySqlStream.ReadPacket()
         at int MySql.Data.MySqlClient.NativeDriver.GetResult(ref int affectedRow, ref long insertedId)
         at int MySql.Data.MySqlClient.Driver.GetResult(int statementId, ref int affectedRows, ref long insertedId)
         at ResultSet MySql.Data.MySqlClient.Driver.NextResult(int statementId, bool force)
         at bool MySql.Data.MySqlClient.MySqlDataReader.NextResult()
         at MySqlDataReader MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
         at MySqlDataReader MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
         at int MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()

This happened during an integration test run via CI that's been working fine for months, during which we migrate the database up and down using FluentMigrator.

I believe the problem happens because VersionUniqueMigration both adds a unique index on the Version column and adds the AppliedOn column. And the thing that decides whether VersionUniqueMigration needs to run checks whether the AppliedOn column exists. That means that if a previous run created the Version unique index, but did not create the AppliedOn column, subsequent runs will always fail with Duplicate key name 'UC_Version' because it's trying to create an index that already exists.

I'm not sure how this particular database got in a state where the VersionInfo table and UC_Version existed but the AppliedOn column did not - this seems like an edge case where the VersionUniqueMigration either got terminated at the exact right second or failed while trying to create the AppliedOn column. Starting with a "fresh" database again solved the problem.

A solution to this issue might be to split up the creation of the Version unique index and the addition of the AppliedOn into separate migrations - and deciding whether to run each based on the existence of the thing it creates. Another option might be to change VersionUniqueMigration so that it only creates UC_Version if it does not exist.