Migration failed for one user only what could went wrong?

[canavci312]

Hello, Sorry taking your time. I recently added few columns to my table and run migration. Tests seemed to be passing and it worked on couple devices that I tested which had previous version. However I got crashlogs from a user who couldnt migrate. I believe this happened to only 1 user, I'm wondering what could have gone wrong so that I can prevent it in the future and what is the best way for me to recover this user's database.

 from18To19: (m, schema) async {
            await transaction(() async {
              await m.createTable(schema.intensityProgressionPresetTable);
              await m.addColumn(schema.completedMesocycleTable,
                  schema.completedMesocycleTable.progressionConfig);

              await m.alterTable(
                TableMigration(schema.mesocycleTable, newColumns: [
                  schema.mesocycleTable.lastSetToFailure,
                  schema.mesocycleTable.progressionConfig,
                  schema.mesocycleTable.autoProgressWeight
                ]),
              );
            });
          }

Error received
SqliteException(1): while executing no such column: "auto_progress_weight" - should this be a string literal in single-quotes? SQL logic error (code 1) Causing statement: INSERT INTO tmp_for_copy_mesocycle_table ("id" "name" "created_at" "number_of_weeks" "days_in_week" "starting_set" "default_rest_time" "default_rep_range_min" "default_rep_range_max" "cycle_count" "auto_progress_weight" "last_set_to_failure" "progression_config" "next_workout" "mesocycle_type"

[simolus3]

It's not clear to me how this could have happened either. To help me investigate, do you have access to the full error message (ideally with the entire statement).

My best guess would have been that a partial migration has already been applied, but that makes no sense here since you're using a transaction.

[canavci312]

I managed to find his database and debug it. It seems like he is coming from an older version and throws error because of this. Does it mean if I alter the table in new version, do I need to delete the older one?

     if (from <= 8) {
        await m.alterTable(TableMigration(mesocycleTable));
      } 

Is it because I didnt have step by step migrations before? Or should I also keep this in mind for step by step migrations aswell.

Also I realized if I have create in earlier versions, addColumn also doesnt work since the newly added table has column already.
Didnt know the migrations were this complex. 😮‍💨

[simolus3]

Is it because I didnt have step by step migrations before? Or should I also keep this in mind for step by step migrations aswell.

This is very likely related to step-by-step migrations not being used at these older versions.

Tests seemed to be passing

Did you use the tests generated by make-migrations? We have a test there that tests all combinations of schema migrations:

drift/examples/migrations_example/test/drift/default/migration_test.dart

Lines 26 to 39 in 7f18724

	final versions = GeneratedHelper.versions;
	for (final (i, fromVersion) in versions.indexed) {
	group('from $fromVersion', () {
	for (final toVersion in versions.skip(i + 1)) {
	test('to $toVersion', () async {
	final schema = await verifier.schemaAt(fromVersion);
	final db = Database(schema.newConnection());
	await verifier.migrateAndValidate(db, toVersion);
	await db.close();
	});
	}
	});
	}
	});

This should have caught the issue you were seeing, do you have a test like this?

Didnt know the migrations were this complex. 😮‍💨

I know... My intention was to make them easier (or, at least, predictable) with reliable tests and step-by-step migrations.

[canavci312]

Yes I think step by step migrations makes things a lot safer. Unfortunately my project migrated to it from version 16 so in this case, I had no test for version 1 to 16.