AWDWR2 ibm_db sessions migration fails

Also posted here: http://rubyforge.org/forum/forum.php?thread_id=...

Trekking along fine through “Agile Web Development With Rails” 2nd Ed with ibm_db 0.9.1, rails 1.2.6, and ruby 1.8.6 [i386-mswin32] connected to DB2 v9.5 ESE… until I get to page 97 of the book, where we put sessions support into our demonstration rails application:

> rake db:sessions:create
exists db/migrate
create db/migrate/004_add_sessions.rb
> rake db:migrate --trace
(in C:/Rails/depot)
** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:migrate
== AddSessions: migrating =====================================================
-- create_table(:sessions)
rake aborted!
ActiveRecord::StatementInvalid: [IBM][CLI Driver][DB2/NT] SQL0601N The name of the object to be created is identical to the existing name "JRP.SESSIONS" of typ SQLCODE=-601: CREATE TABLE sessions (id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 100) PRIMARY KEY, session_id varchar(255) DEFAULT NULL, data clob DEFAULT NULL, updated_at timestamp DEFAULT NULL)
...

Looking into db/migrate/004_add_sessions.db we see:

class AddSessions < ActiveRecord::Migration
def self.up
create_table :sessions do |t|
t.column :session_id, :string
t.column :data, :text
t.column :updated_at, :datetime
end

add_index :sessions, :session_id
add_index :sessions, :updated_at
end

def self.down
drop_table :sessions
end
end

Rolling back drops the sessions table, rolling forward gets the same error. I’d like to move forward in this chapter, is it a ibm_db problem, a rails problem, or a silly me problem? I’d hate to think the rails people didn’t know that “sessions” was a DB2 reserved word (although I thought DB2 v9.5 had no reserved words, per http://tinyurl.com/2xnykn ).

Thanks for your help. —jrp

Dropped the sessions table, and re-ran rake db:migrate (duh), here’s the trace:

** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:migrate
== AddSessions: migrating =====================================================
-- create_table(:sessions)
   -> 1.1010s
-- add_index(:sessions, :session_id)
   -> 0.0500s
-- add_index(:sessions, :updated_at)
rake aborted!
ActiveRecord::StatementInvalid: [IBM][CLI Driver][DB2/NT] SQL0601N  The name of
the object to be created is identical to the existing name "JRP.ROR_SNOISSES_IDX
 SQLCODE=-601: CREATE  INDEX ror_snoisses_idx ON sessions (updated_at)
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.6/lib/active_record/connection_
adapters/abstract_adapter.rb:128:in `log'
c:/ruby/lib/ruby/gems/1.8/gems/ibm_db-0.9.1-mswin32/lib/active_record/connection
_adapters/ibm_db_adapter.rb:517:in `execute'
c:/ruby/lib/ruby/gems/1.8/gems/ibm_db-0.9.1-mswin32/lib/active_record/connection
_adapters/ibm_db_adapter.rb:946:in `add_index'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.6/lib/active_record/migration.r
b:275:in `send'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.6/lib/active_record/migration.r
b:275:in `method_missing'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.6/lib/active_record/migration.r
b:259:in `say_with_time'
c:/ruby/lib/ruby/1.8/benchmark.rb:293:in `measure'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.6/lib/active_record/migration.r
b:259:in `say_with_time'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.6/lib/active_record/migration.r
b:273:in `method_missing'
./db/migrate//004_add_sessions.rb:10:in `real_up'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.6/lib/active_record/migration.r
b:212:in `send'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.6/lib/active_record/migration.r
b:212:in `migrate'
c:/ruby/lib/ruby/1.8/benchmark.rb:293:in `measure'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.6/lib/active_record/migration.r
b:212:in `migrate'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.6/lib/active_record/migration.r
b:335:in `migrate'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.6/lib/active_record/migration.r
b:330:in `each'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.6/lib/active_record/migration.r
b:330:in `migrate'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.6/lib/active_record/migration.r
b:297:in `up'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.6/lib/active_record/migration.r
b:288:in `migrate'
c:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.6/lib/tasks/databases.rake:4
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.2/lib/rake.rb:399:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.2/lib/rake.rb:399:in `execute'
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.2/lib/rake.rb:399:in `each'
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.2/lib/rake.rb:399:in `execute'
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.2/lib/rake.rb:369:in `invoke'
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.2/lib/rake.rb:362:in `synchronize'
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.2/lib/rake.rb:362:in `invoke'
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.2/lib/rake.rb:1935:in `run'
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.2/lib/rake.rb:1935:in `each'
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.2/lib/rake.rb:1935:in `run'
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.7.2/bin/rake:7
c:/ruby/bin/rake.bat:20:in `load'
c:/ruby/bin/rake.bat:20

So rails doesn’t seem to be setting a name for the indexes, so it uses the same name for both indexes. Surprise!

The obvious fix:

db/migrate/004_add_sessions.rb

class AddSessions < ActiveRecord::Migration
  def self.up
    create_table :sessions do |t|
      t.column :session_id, :string
      t.column :data, :text
      t.column :updated_at, :datetime
    end

    add_index :sessions, :session_id, :name => "ROR_SNOISSES_IDX_ID" 
    add_index :sessions, :updated_at, :name => "ROR_SNOISSES_IDX_UP" 
  end

  def self.down
    drop_table :sessions
  end
end

Please see the answer posted on RubyForge rubyibm forum: http://rubyforge.org/forum/message.php?msg_id=3…

Thanks,
Alex

Verified to be fixed in ibm_db 0.9.2. Thanks, Alex!