androidandroid-roomandroid-room-migration

Room auto-migration with rename columns failing (NOT NULL constraint failed, bad generated Migration class)

I recently renamed columns in my Room entity, by prefixing the word setting_ on the columns:

diff of the changes

Amongst some other changes this is the auto migration impl it has produced:

class AppDatabase_AutoMigration_15_16_Impl extends Migration {
private final AutoMigrationSpec callback = new SettingsRenamedColumnsAutoMigration();

public AppDatabase_AutoMigration_15_16_Impl() {
    super(15, 16);
}

public void migrate(@NonNull SupportSQLiteDatabase database) {
    database.execSQL("ALTER TABLE `merchants` ADD COLUMN `accessLevel` INTEGER NOT NULL DEFAULT 0");
    database.execSQL("CREATE TABLE IF NOT EXISTS `merchantSettings` (`setting_id` INTEGER NOT NULL, `setting_group_id` INTEGER, `setting_type` TEXT NOT NULL, `setting_name` TEXT NOT NULL, `setting_value` TEXT NOT NULL, `setting_data_type` TEXT NOT NULL, `setting_created_at` INTEGER NOT NULL, `setting_updated_at` INTEGER NOT NULL, PRIMARY KEY(`setting_id`), FOREIGN KEY(`setting_group_id`) REFERENCES `merchants`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )");
    database.execSQL("CREATE INDEX IF NOT EXISTS `index_merchantSettings_setting_group_id` ON `merchantSettings` (`setting_group_id`)");
    database.execSQL("CREATE TABLE IF NOT EXISTS `_new_branchSettings` (`setting_id` INTEGER NOT NULL, `setting_branch_id` INTEGER, `setting_type` TEXT NOT NULL, `setting_name` TEXT NOT NULL, `setting_value` TEXT NOT NULL, `setting_data_type` TEXT NOT NULL, `setting_created_at` INTEGER NOT NULL, `setting_updated_at` INTEGER NOT NULL, PRIMARY KEY(`setting_id`), FOREIGN KEY(`setting_branch_id`) REFERENCES `branches`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )");
    database.execSQL("INSERT INTO `_new_branchSettings` (`setting_type`,`setting_updated_at`) SELECT `setting_type`,`updated_at` FROM `branchSettings`");
    database.execSQL("DROP TABLE `branchSettings`");
    database.execSQL("ALTER TABLE `_new_branchSettings` RENAME TO `branchSettings`");
    database.execSQL("CREATE INDEX IF NOT EXISTS `index_branchSettings_setting_branch_id` ON `branchSettings` (`setting_branch_id`)");
    DBUtil.foreignKeyCheck(database, "branchSettings");
    database.execSQL("CREATE TABLE IF NOT EXISTS `_new_branches` (`id` INTEGER NOT NULL, `name` TEXT NOT NULL, `address` TEXT, `lat` TEXT, `lng` TEXT, `is_default` INTEGER, `email` TEXT, `phone` TEXT, `clover_category_id` TEXT, `clover_gift_item` TEXT, `clover_cashless_item` TEXT, `prep_time` INTEGER, `opening_hours` TEXT, `currency_code` TEXT, `created_at` INTEGER NOT NULL, `updated_at` INTEGER NOT NULL, PRIMARY KEY(`id`))");
    database.execSQL("INSERT INTO `_new_` (`id`,`name`,`address`,`lat`,`lng`,`is_default`,`email`,`phone`,`prep_time`,`opening_hours`,`currency_code`,`created_at`,`updated_at`) SELECT `id`,`name`,`address`,`lat`,`lng`,`is_default`,`email`,`phone`,`prep_time`,`opening_hours`,`currency_code`,`created_at`,`updated_at` FROM `branches`");
    database.execSQL("DROP TABLE `branches`");
    database.execSQL("ALTER TABLE `_new_` RENAME TO `branches`");
    this.callback.onPostMigrate(database);
}

}

With the following auto migration spec:

AutoMigration(
        from = 15,
        to = 16,
        spec = AppDatabase.SettingsRenamedColumnsAutoMigration::class
    )


    @RenameColumn.Entries(
    RenameColumn(
        tableName = "branchSettings",
        fromColumnName = "id",
        toColumnName = "setting_id"
    ),
    RenameColumn(
        tableName = "branchSettings",
        fromColumnName = "branch_id",
        toColumnName = "setting_branch_id"
    ),
    RenameColumn(
        tableName = "branchSettings",
        fromColumnName = "name",
        toColumnName = "setting_name"
    ),
    RenameColumn(
        tableName = "branchSettings",
        fromColumnName = "value",
        toColumnName = "setting_value"
    ),
    RenameColumn(
        tableName = "branchSettings",
        fromColumnName = "data_type",
        toColumnName = "setting_data_type"
    ),
    RenameColumn(
        tableName = "branchSettings",
        fromColumnName = "created_at",
        toColumnName = "setting_created_at"
    ),
    RenameColumn(
        tableName = "branchSettings",
        fromColumnName = "updated_at",
        toColumnName = "setting_updated_at"
    ),
)
@DeleteColumn.Entries(
    DeleteColumn(tableName = "branches", columnName = "checkout_category_id")
)
class SettingsRenamedColumnsAutoMigration : AutoMigrationSpec

This error is affecting every updater I think:

android.database.sqlite.SQLiteConstraintException: NOT NULL constraint failed: _new_branchSettings.setting_name (code 1299 SQLITE_CONSTRAINT_NOTNULL)
    at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
    at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:831)
    at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:756)
    at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:66)
    at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1806)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1733)
    at androidx.sqlite.db.framework.FrameworkSQLiteDatabase.execSQL(FrameworkSQLiteDatabase.java:255)
    at loylap.core.sdk.data.local.db.AppDatabase_AutoMigration_15_16_Impl.migrate(AppDatabase_AutoMigration_15_16_Impl.java:31)
    at androidx.room.RoomOpenHelper.onUpgrade(RoomOpenHelper.kt:91)

I don't know enough MySQL/Room to confirm but it looks like the 15->16 Migration class has at least 2 issues. It creates a _new_branchSettings table, but only inserts 2 random columns (setting_type and setting_updated_at). Should it not be inserting everything here? Hence the error - setting_name is supposed to be not null.

Also, I am very suspicious of INSERT INTO ``_new_`` ... What is _new_ and who created it? To me that looks like a typo and it should be inserting into _new_branches.

Solution

  • This does appear to be a possible bug. You may wish to report this as a bug.

    As you have suggested the INSERT INTO appears to be omitting columns and thus the values, due to the columns being omitted, will be null.

    I've looked at the INSERT INTO ``_new_`` and that (without extremely close inspection) appears to be fine albeit not the best naming convention (perhaps a bug but with just the 1 it probably will not cause an issue).

    Personally, perhaps being even more suspicious than yourself, my solution would be to manually migrate, which would change the INSERT INTO for the new branch settings table to insert default values if nulls are found (just correcting by extracting from and assigning values to all columns would be enough).

    A Solution as a manual migration rather than an AutoMigration, would be to use (note not tested so it is *in-principle code) :-

      database.execSQL("ALTER TABLE `merchants` ADD COLUMN `accessLevel` INTEGER NOT NULL DEFAULT 0");
  database.execSQL("CREATE TABLE IF NOT EXISTS `merchantSettings` (`setting_id` INTEGER NOT NULL, `setting_group_id` INTEGER, `setting_type` TEXT NOT NULL, `setting_name` TEXT NOT NULL, `setting_value` TEXT NOT NULL, `setting_data_type` TEXT NOT NULL, `setting_created_at` INTEGER NOT NULL, `setting_updated_at` INTEGER NOT NULL, PRIMARY KEY(`setting_id`), FOREIGN KEY(`setting_group_id`) REFERENCES `merchants`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )");
  database.execSQL("CREATE INDEX IF NOT EXISTS `index_merchantSettings_setting_group_id` ON `merchantSettings` (`setting_group_id`)");
  
  /* SPLIT to make the code more readable */
  database.execSQL(
          "CREATE TABLE IF NOT EXISTS `_new_branchSettings` " +
                  "(`setting_id` INTEGER NOT NULL, " +
                  "`setting_branch_id` INTEGER, " +
                  "`setting_type` TEXT NOT NULL, " +
                  "`setting_name` TEXT NOT NULL, " +
                  "`setting_value` TEXT NOT NULL, " +
                  "`setting_data_type` TEXT NOT NULL, " +
                  "`setting_created_at` INTEGER NOT NULL, " +
                  "`setting_updated_at` INTEGER NOT NULL, " +
                  "PRIMARY KEY(`setting_id`), " +
                  "FOREIGN KEY(`setting_branch_id`) REFERENCES `branches`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE " +
                  ")"
  );
  /* SPLIT to make the code more readable */
  database.execSQL("INSERT INTO `_new_branchSettings` (" +
          "`setting_id`," +
          "`setting_branch_id`," +
          "`setting_type`," +
          "`setting_name`," +
          "`setting_value`," +
          "`setting_data_type`," +
          "`setting_created_at`," +
          "`setting_updated_at`" +
          ") " +
          "SELECT " +
          "`id`," +
          "`branch_id`," +
          "coalesce(`settng_type`,'SETTING_TYPEWASNULL')," +
          "coalesce(`name`,'NAMEWASNULL')," +
          "coalesce(`value`,'VALUEWASNULL')," +
          "coalesce(`data_type`,'DATA_TYPEWASNULL')," +
          "coalesce(`created_at`,-9999)," +
          "coalsce(`updated_at`,-8888) " +
          "FROM `branchSettings`" +
          "");
  /* Above INSERT replaces the following suspicious INSERT INTO */
  //database.execSQL("INSERT INTO `_new_branchSettings` (`setting_type`,`setting_updated_at`) SELECT `setting_type`,`updated_at` FROM `branchSettings`");
  database.execSQL("DROP TABLE `branchSettings`");
  database.execSQL("ALTER TABLE `_new_branchSettings` RENAME TO `branchSettings`");
  database.execSQL("CREATE INDEX IF NOT EXISTS `index_branchSettings_setting_branch_id` ON `branchSettings` (`setting_branch_id`)");
  DBUtil.foreignKeyCheck(database, "branchSettings");

  database.execSQL("CREATE TABLE IF NOT EXISTS `_new_branches` (`id` INTEGER NOT NULL, `name` TEXT NOT NULL, `address` TEXT, `lat` TEXT, `lng` TEXT, `is_default` INTEGER, `email` TEXT, `phone` TEXT, `clover_category_id` TEXT, `clover_gift_item` TEXT, `clover_cashless_item` TEXT, `prep_time` INTEGER, `opening_hours` TEXT, `currency_code` TEXT, `created_at` INTEGER NOT NULL, `updated_at` INTEGER NOT NULL, PRIMARY KEY(`id`))");


  database.execSQL("INSERT INTO `_new_` (`id`,`name`,`address`,`lat`,`lng`,`is_default`,`email`,`phone`,`prep_time`,`opening_hours`,`currency_code`,`created_at`,`updated_at`) SELECT `id`,`name`,`address`,`lat`,`lng`,`is_default`,`email`,`phone`,`prep_time`,`opening_hours`,`currency_code`,`created_at`,`updated_at` FROM `branches`");


  database.execSQL("DROP TABLE `branches`");
  database.execSQL("ALTER TABLE `_new_` RENAME TO `branches`");
  //this.callback.onPostMigrate(database); /* I believe not needed in actual code as generated code should do this as needed */