search

rails-sqlserver / activerecord-sqlserver-adapter

SQL Server Adapter For Rails
MIT License
972 stars 560 forks source link

Error Setting Primary Key through Migration #166

Closed jazzdan closed 12 years ago

jazzdan commented 12 years ago

I ran across an error today migrating my SQL Server 2008 R2 database. The migration in question looks like this:

class MakeHistoryIdPrimaryKey < ActiveRecord::Migration
  def change
    change_column :history_records, :history_id, :primary_key
  end
end

Here is my gemfile for good measure:

source 'http://rubygems.org'

gem 'rails', '3.1.1'

# Bundle edge Rails instead:
# gem 'rails', :git => 'git://github.com/rails/rails.git'

gem 'sqlite3', '1.3.4' #for *nix c ruby
gem 'tiny_tds', '0.5.1' #for MSSQL
gem 'activerecord-sqlserver-adapter' #for MSSQL
gem 'devise'
gem 'cancan' # see this link for implementation https://github.com/ryanb/cancan/wiki/Defining-Abilities
gem 'aasm'
gem 'acts_as_audited', '2.0.0.rc7'
gem 'sunspot_rails'
gem 'paperclip', '~> 2.3'
gem 'chunky_png'
gem 'capistrano'

group :assets do
#  gem 'handlebars_assets'
  gem 'uglifier'
end

gem 'jquery-rails'
gem 'therubyracer'
gem 'devise_cas_authenticatable', :git => 'git://github.com/nbudin/devise_cas_authenticatable.git'
#gem 'devise_cas_authenticatable'
# Use unicorn as the web server
gem 'unicorn'
gem 'newrelic_rpm'

And here is the output when I try to run the migration:

$ rake db:migrate RAILS_ENV=test --trace
(in /var/www/minerva/releases/20120217171102)
** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
New Relic Agent not running.
** Invoke db:load_config (first_time)
** Invoke rails_env (first_time)
** Execute rails_env
** Execute db:load_config
** Execute db:migrate
  EXECUTE (0.8ms)  USE [minerva_test]
   (1.3ms)  SELECT [schema_migrations].[version] FROM [schema_migrations]
Migrating to DeviseCreateUsers (20110728020923)
Migrating to CreateBuildings (20110729022442)
Migrating to CreateMachineTypes (20110729030133)
Migrating to CreatePrimaryUses (20110729030252)
Migrating to CreateInventoryRecords (20110807194952)
Migrating to AddAdminToUser (20110807230446)
Migrating to RenameStatusToState (20110808161244)
Migrating to CreateAuditTable (20110808164753)
Migrating to InstallActsAsAudited (20110808170141)
Migrating to CreateDeployments (20110818141843)
Migrating to AddSignatureToDeployments (20110819130833)
Migrating to AddCsrToDeployments (20110819163405)
Migrating to AddSignatureMetadataToDeployments (20110819165305)
Migrating to FixSignatureColumnName (20110819165447)
Migrating to AddAvatarColumnsToUser (20110823184028)
Migrating to ChangeSignatureAvatarUpdatedAtToSignatureUpdatedAt (20110830221214)
Migrating to ChangeDeploymentsToEvents (20110830235817)
Migrating to CompleteInventoryRecordSchema (20110913225644)
Migrating to RemoveEvents (20111003152152)
Migrating to CreateEvents (20111003154501)
Migrating to CreateEventActions (20111003155304)
Migrating to FixRecordId (20111003161029)
Migrating to ChangeEventActionsToEventCategorizations (20111004223609)
Migrating to EffingTypoInSchema (20111004231332)
Migrating to CreateHistoryRecords (20111019163447)
Migrating to LowerCaseInventoryRecordsFields (20111019165014)
Migrating to RemoveAdminColumnFromUserTable (20111020181433)
Migrating to AddCasAuthenticatableToUsers (20111027152809)
Migrating to RenameEncryptedPasswordToPassword (20111027185500)
Migrating to DropUsersTable (20111027193453)
Migrating to AddCasUsersTable (20111027195115)
Migrating to AddDeviseTraitsToUsersTable (20111027204955)
Migrating to AddEmailColumnToUsersTable (20111027210107)
Migrating to AddIndexToInventoryRecords (20111103180558)
Migrating to AddRoleToUsers (20111104201009)
Migrating to ConformWithPaulsColumnNames (20111202200923)
Migrating to AddOrderIdToInventoryRecords (20111202201556)
Migrating to RevertConformWithPaulsColumnNames (20111202204909)
Migrating to SetDefaultRole (20111230062021)
Migrating to CreateImages (20120105214814)
Migrating to ConsolidateLastNameAndFirstNametoPerson (20120201012224)
Migrating to AddDescriptionToImages (20120201014447)
Migrating to AddUserMetadata (20120201014834)
Migrating to ConsolidateLastNameAndFirstNameToPersonInHistory (20120203144825)
Migrating to MakeHistoryIdPrimaryKey (20120203193431)
  EXECUTE (0.7ms)  BEGIN TRANSACTION
==  MakeHistoryIdPrimaryKey: migrating ========================================
-- change_column(:history_records, :history_id, :primary_key)
   (4.3ms)  EXEC sp_executesql N'EXEC sp_helpconstraint ''history_records'',''nomsg'''
  EXECUTE (1.3ms)  ALTER TABLE [history_records] ALTER COLUMN [history_id] int NOT NULL IDENTITY(1,1) PRIMARY KEY
TinyTds::Error: Incorrect syntax near the keyword 'IDENTITY'.: ALTER TABLE [history_records] ALTER COLUMN [history_id] int NOT NULL IDENTITY(1,1) PRIMARY KEY
  EXECUTE (2.1ms)  IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
rake aborted!
An error has occurred, this and all later migrations canceled:

TinyTds::Error: Incorrect syntax near the keyword 'IDENTITY'.: ALTER TABLE [history_records] ALTER COLUMN [history_id] int NOT NULL IDENTITY(1,1) PRIMARY KEY
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:354:in `do'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:354:in `raw_connection_do'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:320:in `block (2 levels) in do_execute'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver_adapter.rb:485:in `with_sqlserver_error_handling'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:320:in `block in do_execute'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/connection_adapters/abstract_adapter.rb:244:in `block in log'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activesupport-3.1.1/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/connection_adapters/abstract_adapter.rb:239:in `log'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:319:in `do_execute'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/schema_statements.rb:91:in `block in change_column'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/schema_statements.rb:91:in `each'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/schema_statements.rb:91:in `change_column'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:447:in `block in method_missing'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:422:in `block in say_with_time'
/home/deployer/.rvm/rubies/ruby-1.9.2-p290/lib/ruby/1.9.1/benchmark.rb:295:in `measure'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:422:in `say_with_time'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:442:in `method_missing'
/var/www/minerva/releases/20120217171102/db/migrate/20120203193431_make_history_id_primary_key.rb:3:in `change'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:391:in `block (2 levels) in migrate'
/home/deployer/.rvm/rubies/ruby-1.9.2-p290/lib/ruby/1.9.1/benchmark.rb:295:in `measure'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:391:in `block in migrate'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:185:in `with_connection'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:375:in `migrate'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:507:in `migrate'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:687:in `block (2 levels) in migrate'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:742:in `call'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:742:in `block in ddl_transaction'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:52:in `block in transaction'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/core_ext/database_statements.rb:37:in `transaction_with_retry_deadlock_victim'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:52:in `transaction'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/transactions.rb:208:in `transaction'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:742:in `ddl_transaction'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:686:in `block in migrate'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:671:in `each'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:671:in `migrate'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:549:in `up'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:530:in `migrate'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/railties/databases.rake:161:in `block (2 levels) in <top (required)>'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/task.rb:205:in `call'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/task.rb:205:in `block in execute'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/task.rb:200:in `each'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/task.rb:200:in `execute'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/task.rb:158:in `block in invoke_with_call_chain'
/home/deployer/.rvm/rubies/ruby-1.9.2-p290/lib/ruby/1.9.1/monitor.rb:201:in `mon_synchronize'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/task.rb:151:in `invoke_with_call_chain'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/task.rb:144:in `invoke'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:116:in `invoke_task'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:94:in `block (2 levels) in top_level'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:94:in `each'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:94:in `block in top_level'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:133:in `standard_exception_handling'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:88:in `top_level'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:66:in `block in run'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:133:in `standard_exception_handling'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:63:in `run'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/bin/rake:33:in `<top (required)>'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/bin/rake:19:in `load'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/bin/rake:19:in `<main>'
Tasks: TOP => db:migrate

Let me know if there is any other information I can provide that would be helpful. Thank you for all your hard work and effort!

jazzdan commented 12 years ago

It appears that history_id is already set as the primary key on my MSSQL box, could that be the problem?

metaskills commented 12 years ago

Yup, that could be why. Perhaps write a migration that does not use change but instead does an up/down.

jazzdan commented 12 years ago

That did the trick, thanks!