fatkodima/online_migrations

Bad background migration behaviour when backfilling primary key id's

dfan1028 opened this issue · 2 comments

dfan1028 commented

Ruby version: 3.2.2
Rails version: 7.0.8
Gem version: 0.15.0

When migrating a primary key data type change from integer to bigint using the helper backfill_column_for_type_change_in_background:

initialize_column_type_change forces a default 0 value for reasons mentioned:
https://github.com/fatkodima/online_migrations/blame/0a1f67fc44b130f1ed58f77d7b8b25bf63634f72/lib/online_migrations/change_column_type_helpers.rb#L129

However this results in a bad query down the line in the relation used:

online_migrations/lib/online_migrations/background_migrations/copy_column.rb

Line 31 in 0a1f67f

.where.not(copy_from.index_with(nil))

This results in a query that attempts to copy from a table expecting null, but we have backfilled it with 0:

development> stmt.to_sql
=> "UPDATE \"projects\" SET \"id_for_type_change\" = \"projects\".\"id\" WHERE \"projects\".\"id_for_type_change\" IS NULL AND \"projects\".\"id\" IS NOT NULL"

Consequently, the background migration job completes successfully but doesn't actually copy any values to the new column.

Reproduction:

  1. Create a migration to initialize a type change on any primary key for any table:
initialize_column_type_change :projects, :id, :bigint
  1. Populate the background migration record for the background job cron to pick up:
backfill_column_for_type_change_in_background :projects, :id
  1. Migration and job completes with status successful but the new column created id_for_type_change is still all 0 value.
dfan1028 commented

I can temporarily get around this by writing my own background migration but there is a bug in the backfill_column_for_type_change_in_background helper.

fatkodima commented

Thanks for using the gem and for reporting! I will look at it shortly.

  • ❤️1