Migration goes wrong in production - all is good in development

rubyonrails-talk
1

I added a migration (my twentieth, I believe, and first to fail) with the intent to changing the overtime column from a boolean to an integer.

class ChangeOvertimeToIntegerOnTimeMaterial < ActiveRecord::Migration[8.0]
  def self.up
    add_column :time_materials, :over_time, :integer

    # backfill the column with the correct data
    execute "UPDATE time_materials SET over_time = 0 WHERE overtime = false"
    execute "UPDATE time_materials SET over_time = 2 WHERE overtime = true"

    # apply appropriate constraints and defaults once it's backfilled
    change_column_default :time_materials, :over_time, 0
    # change_column_null :time_materials, :over_time, false

    # swap the new column in place of the old column
    rename_column :time_materials, :overtime, :old_overtime
    rename_column :time_materials, :over_time, :overtime

    # once you've verified that everything is correct, drop the old column
    remove_column :time_materials, :old_overtime
  end
  def down
   ...

  end
end

When I run the migration, I get an error that says SQLite3::SQLException: no such column: over_time. I’m not sure why this error is happening. I’m using Rails 8.0.0.alpha and Ruby 3.2.2.

In development all is good but when I run the migration in production I get the error.

StandardError: An error has occurred, this and all later migrations canceled: (StandardError)

SQLite3::SQLException: no such column: over_time
/rails/db/migrate/20241112050328_change_overtime_to_integer_on_time_material.rb:6:in `up'

Caused by:
ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: over_time (ActiveRecord::StatementInvalid)
/rails/db/migrate/20241112050328_change_overtime_to_integer_on_time_material.rb:6:in `up'

Caused by:
SQLite3::SQLException: no such column: over_time (SQLite3::SQLException)
/rails/db/migrate/20241112050328_change_overtime_to_integer_on_time_material.rb:6:in `up'
Tasks: TOP => db:prepare
(See full trace by running task with --trace)
I, [2024-11-12T13:32:44.307748 #8]  INFO -- : Migrating to ChangeOvertimeToIntegerOnTimeMaterial (20241112050328)
== 20241112050328 ChangeOvertimeToIntegerOnTimeMaterial: migrating ============
-- add_column(:time_materials, :over_time, :integer)
   -> 0.0013s
-- execute("UPDATE time_materials SET over_time = 0 WHERE overtime = false")
bin/rails aborted!
2

There’s a couple of things going wrong in this. First, you have some references to true and false in your SQL. They don’t work that way, not in any database. Depending on the engine you are using, a true false boolean column may be represented by 1 or 0, Y or N, T or F, etc. at the raw SQL layer. To accurately interpret an existing value, you need to move up a layer into the ActiveRecord representation of that table.

TimeMaterial.where(overtime: false).update_all(over_time: 0)

The other problem you have is that you are attempting to reference a new column before ActiveRecord is aware of it. The column information is up to date outside the current block, but inside it, you still have the old version of that table cached. You can either move this into two migrations – one for the new column, and a second to do the data-munging – or you can use an ActiveRecord method to update the table definition on the fly, right after you add that column.

add_column :time_materials ...
TimeMaterial.reset_column_information
TimeMaterial.where(overtime: false).update_all(over_time: 0)

But if you are using SQLite, then your boolean true and false are already 1 and 0, so this may already be a difference without distinction. The SQLite adapter considers a boolean column to be a number field with a length of 1. You can change the length of the field so that it is treated as an integer, without changing the value in the table.

Walter

3 Likes
3

knew it - and that is why thou shall not blindly c/p any “very good solution” off somebody’s blog (doesn’t DHH talk about blunt instruments somewhere) :laughing:

happy I didn’t throw myself at the mercy of the Rails Core Team by inserting an “issue” in rails/rails :wink:

now the ‘damage’ is containable to - well almost anybody - c’est la’vie

thx a million Walter - for taking the time to “debug” my c/p’ed code - won’t make that mistake again

one very important take-away! didn’t know the .reset_column_information - that sure will come in handy down the road!

4

@walterdavis wondering if you could shed some light on the reason why I was able to ‘sneak’ this in when in development? It’s uncomforting knowing that ones migrations might pass during development but fail in production.

This one in particular b/c it made quite a bit of havoc on my deployment routine and sent me on a patching production-database-pulled-into-development-and-back mission that lasted into the wee hours (learned my lesson the hard way you might add)

Once again - thank you very much for stepping up and sharing

5

That’s hard to say without more information. Are you using SQLite in both environments? Is the deployed version of the engine exactly the same as your local environment? Check the details with:

sqlite3 --version

at the command line in each machine.

If you are not using SQLite3 in production, what are you using? My statement that “no engine uses a bare true or false” might have been over-broad, and maybe SQLite does in fact do that, but if I am recalling the error message correctly, I think that your error did mention SQLite, so I doubt that’s the thing.

See, making a comparison in raw SQL between a table value and some arbitrary value that isn’t otherwise defined should throw an error, but maybe in the case where you were running it, the fact that false wasn’t defined at all actually meant that it was being compared as if it was nil (in Ruby) or NULL (in SQL). So your example worked “from a certain point of view”.

The real, pedantic result should have been something along the lines of false is not a defined value or named variable, and I am throwing a fit and terminating processing at this point. That’s an accurate statement, and ideal from the perspective of wanting any computer program in any language to be as unambiguous as possible.

When you don’t define your variables, but it works anyway, that’s where you end up with much larger problems. Particularly when you are going to update all rows in a database, in production. That way lies madness.

Walter

1 Like
6

I did MySQL from 2008-2019-ish then “transcended” to PostgreSQL - but with the noise of late pounding on the “SQLite drum running all sorts of wonderful stuff” I put my chips on (what color SQLite might fly) early 2024 - and have mostly been happy.

Keeping dev and prod 1:1 is one of my OCD’s :wink: - bar 1-2 ENV’s

Walter - thank you for considering this issue; I’ll file it under “stupid endeavours - miraculously saved by the community” and be on my merry way :slight_smile:

In case you’re in Denmark or planning a trip to go there - ping me :red_gift_envelope: