change_column fails while dropping constraint the first time

[rkr090]

The following migration always fails the first time but runs successfully the second time. I'm using version 4.2.0 of the adapter.

class ChangeColumnDefaultForIncidentInvestigation < ActiveRecord::Migration
  def change
    change_column :incident_investigations, :legal_counsel_contacted, :boolean, default: nil
    change_column :incident_investigations, :self_disclosure_required, :boolean, default: nil
    change_column :incident_investigations, :media_disclosure_required, :boolean, default: nil
    change_column :incident_investigations, :patient_disclosure_required, :boolean, default: nil
  end
end

== 20150323134223 CreateBulletins: migrated (0.0169s) =========================

  SQL (1.0ms)  EXEC sp_executesql N'INSERT INTO [schema_migrations] ([version]) VALUES (@0); SELECT CAST(SCOPE_IDENTITY() AS bigint) AS Ident', N'@0 nvarchar(4000)', @0 = N'20150323134223'  [["version", "20150323134223"]]
  SQL (1.8ms)  COMMIT TRANSACTION
Migrating to ChangeColumnDefaultForIncidentInvestigation (20150325081558)
  SQL (0.6ms)  BEGIN TRANSACTION
== 20150325081558 ChangeColumnDefaultForIncidentInvestigation: migrating ======
-- change_column(:incident_investigations, :legal_counsel_contacted, :boolean, {:default=>nil})
  Execute Procedure (16.2ms)  EXEC sp_helpconstraint N'incident_investigations', N'nomsg'
  SQL (2.6ms)  ALTER TABLE [incident_investigations] DROP CONSTRAINT DF_incident_investigations_legal_counsel_contacted
   (3.1ms)  EXEC sp_executesql N'EXEC sp_helpindex N''incident_investigations'''
   (2.7ms)  EXEC sp_executesql N'EXEC sp_helpindex N''incident_investigations'''
  SQL (2.0ms)  ALTER TABLE [incident_investigations] ALTER COLUMN [legal_counsel_contacted] bit
  SQL (2.4ms)  ALTER TABLE [incident_investigations] ADD CONSTRAINT DF_incident_investigations_legal_counsel_contacted DEFAULT NULL FOR [legal_counsel_contacted]
   -> 0.0310s
-- change_column(:incident_investigations, :self_disclosure_required, :boolean, {:default=>nil})
  Execute Procedure (17.8ms)  EXEC sp_helpconstraint N'incident_investigations', N'nomsg'
  SQL (2.7ms)  ALTER TABLE [incident_investigations] DROP CONSTRAINT DF_incident_investigations_self_disclosure_required
   (3.0ms)  EXEC sp_executesql N'EXEC sp_helpindex N''incident_investigations'''
   (2.7ms)  EXEC sp_executesql N'EXEC sp_helpindex N''incident_investigations'''
  SQL (2.0ms)  ALTER TABLE [incident_investigations] ALTER COLUMN [self_disclosure_required] bit
  SQL (2.4ms)  ALTER TABLE [incident_investigations] ADD CONSTRAINT DF_incident_investigations_self_disclosure_required DEFAULT NULL FOR [self_disclosure_required]
   -> 0.0327s
-- change_column(:incident_investigations, :media_disclosure_required, :boolean, {:default=>nil})
  Execute Procedure (17.0ms)  EXEC sp_helpconstraint N'incident_investigations', N'nomsg'
  SQL (2.6ms)  ALTER TABLE [incident_investigations] DROP CONSTRAINT DF__incident___media__6EF57B66
   (3.2ms)  EXEC sp_executesql N'EXEC sp_helpindex N''incident_investigations'''
   (2.8ms)  EXEC sp_executesql N'EXEC sp_helpindex N''incident_investigations'''
  SQL (17.3ms)  IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

undefined method `type' for nil:NilClass/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/quoting.rb:19:in `quote_default_value'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/schema_statements.rb:84:in `change_column'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:661:in `block in method_missing'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:631:in `block in say_with_time'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:631:in `say_with_time'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:651:in `method_missing'
/data/apps/aapc7atlis/releases/20151014165218/db/migrate/20150325081558_change_column_default_for_incident_investigation.rb:5:in `change'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:605:in `exec_migration'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:589:in `block (2 levels) in migrate'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:588:in `block in migrate'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:292:in `with_connection'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:587:in `migrate'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:764:in `migrate'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:994:in `block in execute_migration_in_transaction'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:1040:in `block in ddl_transaction'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/database_statements.rb:213:in `block in transaction'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/transaction.rb:184:in `within_new_transaction'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/database_statements.rb:213:in `transaction'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/transactions.rb:220:in `transaction'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:1040:in `ddl_transaction'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:993:in `execute_migration_in_transaction'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:955:in `block in migrate'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:951:in `each'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:951:in `migrate'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:819:in `up'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:797:in `migrate'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/tasks/database_tasks.rb:137:in `migrate'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/railties/databases.rake:44:in `block (2 levels) in <top (required)>'
NoMethodError: undefined method `type' for nil:NilClass
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/quoting.rb:19:in `quote_default_value'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/schema_statements.rb:84:in `change_column'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:661:in `block in method_missing'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:631:in `block in say_with_time'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:631:in `say_with_time'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:651:in `method_missing'
/data/apps/aapc7atlis/releases/20151014165218/db/migrate/20150325081558_change_column_default_for_incident_investigation.rb:5:in `change'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:605:in `exec_migration'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:589:in `block (2 levels) in migrate'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:588:in `block in migrate'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:292:in `with_connection'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:587:in `migrate'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:764:in `migrate'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:994:in `block in execute_migration_in_transaction'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:1040:in `block in ddl_transaction'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/database_statements.rb:213:in `block in transaction'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/transaction.rb:184:in `within_new_transaction'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/database_statements.rb:213:in `transaction'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/transactions.rb:220:in `transaction'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:1040:in `ddl_transaction'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:993:in `execute_migration_in_transaction'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:955:in `block in migrate'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:951:in `each'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:951:in `migrate'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:819:in `up'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:797:in `migrate'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/tasks/database_tasks.rb:137:in `migrate'
/data/apps/aapc7atlis/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/railties/databases.rake:44:in `block (2 levels) in <top (required)>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
rake stderr: Nothing written

[metaskills]

Is that table a view?

[rkr090]

Is that table a view?

No

[metaskills]

OK, then this is likely a bug with the schema cache. Will get this fixed in latest 4.2.x

[GrumpyRainbow]

I have run into this same issue while using version 4.2.8 of this gem.

I tinkered around with the code and found that clearing the table cache at the beginning of the change_column_default method in lib/active_record/connection_adapters/sqlserver/schema_statements.rb allowed the migration to run on the first try instead of having to run it again.

        def change_column_default(table_name, column_name, default)
          schema_cache.clear_table_cache!(table_name)
          remove_default_constraint(table_name, column_name)
          column_object = schema_cache.columns(table_name).find { |c| c.name.to_s == column_name.to_s }
          do_execute "ALTER TABLE #{quote_table_name(table_name)} ADD CONSTRAINT #{default_constraint_name(table_name, column_name)} DEFAULT #{quote_default_value(default, column_object)} FOR #{quote_column_name(column_name)}"
          schema_cache.clear_table_cache!(table_name)
        end

[metaskills]

Thank y'all!!! I'll have @GrumpyRainbow's fix released in v4.2.11 later today.

[AThomsen]

Sorry for reviving such an old issue. But was the original issue actually fixed in the 4.2 series?
The original problem was an error when calling change_column. The fix changes change_column_default but that method do not seem to be called from change_column.

It does in 5.2 though.