Failures on migration with change_column, remove_column on jdbcsqlite3

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Critical Critical
  • Resolution: Fixed
  • Affects Version/s: JRuby 1.1.2
  • Fix Version/s: None
  • Component/s: None
  • Labels:
    None
  • Environment:
    KUbuntu 8.04
    java version "1.6.0"
    OpenJDK Runtime Environment (build 1.6.0-b09)
    OpenJDK 64-Bit Server VM (build 1.6.0-b09, mixed mode)

Description

When running a migration like:

change_column :flights, :flight_date, :string

I get the error:

ActiveRecord::StatementInvalid: ActiveRecord::ActiveRecordError: near "ALTER": syntax error: ALTER TABLE flights ALTER COLUMN flight_date text(0)

When I try to hack around it by migrating like:

remove_column :flights, :flight_date
add_column :flights, :flight_date, :string

I get:

ActiveRecord::StatementInvalid: ActiveRecord::ActiveRecordError: duplicate column name: flight_date: ALTER TABLE flights ADD flight_date text(10)

Queerer still, this behavior spans across migrations (if I drop a column in one and try to add a new column with the same name I still get the duplicate), so it kind of breaks the whole migrations mechanism.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Dylan Stamat added a comment -

Hey Ben ! This syntax looks incorrect: "ALTER TABLE flights ALTER COLUMN flight_date text(0)"
Afaik... in order to change the column type, you'd need to use some temp table trickery.
Just looked in active_record/connection_adapters/sqlite_adapter.rb, and that's the technique there as well.

So, looks like the jdbc_sqlite3.rb file needs to be updated a bit in order for change_column to work properly.
You're probably running into the same thing with the "remove then add" approach as well.

Show
Dylan Stamat added a comment - Hey Ben ! This syntax looks incorrect: "ALTER TABLE flights ALTER COLUMN flight_date text(0)" Afaik... in order to change the column type, you'd need to use some temp table trickery. Just looked in active_record/connection_adapters/sqlite_adapter.rb, and that's the technique there as well. So, looks like the jdbc_sqlite3.rb file needs to be updated a bit in order for change_column to work properly. You're probably running into the same thing with the "remove then add" approach as well.
Hide
Permalink
Nate Sutton added a comment -

Still broken when tested with jruby 1.1.5, jdbc-sqlite3-3.5.8, and activerecord-jdbcsqlite3-adapter-0.9.

Steps to reproduce:
1) jruby -S rails myapp -d sqlite3
2) change manually sqlite3 to jdbcsqlite3 in config/database.yml
3) jruby -S rake db:create:all
4) jruby script/generate model Duck feather:string
5) jruby -S script/generate migration change_feathers_to_text
6) add in the generated migration's up method: change_column :ducks, :feathers, :text
7) jruby -S rake db:migrate

You will then receive the error:
ActiveRecord::ActiveRecordError: near "ALTER": syntax error: ALTER TABLE ducks ALTER COLUMN feathers text

8) change the migration to read: remove_column :ducks, :feathers; add_column :ducks, :feathers, :text
9) jruby -S rake db:migrate

8 & 9 don't result in the error the original description describes.

In activerecord-jdbc-adapter-0.9 in lib/jdbc_adapter/jdbc_sqlite3.rb it needs to have it's change_column method changed to behave similarly to the change_column method in activerecord-2.2.2 in lib/active_record/connection_adapters/sqlite_adapter.rb. That's using the temporary table trickery mentioned by the previous commenter.

Show
Nate Sutton added a comment - Still broken when tested with jruby 1.1.5, jdbc-sqlite3-3.5.8, and activerecord-jdbcsqlite3-adapter-0.9. Steps to reproduce: 1) jruby -S rails myapp -d sqlite3 2) change manually sqlite3 to jdbcsqlite3 in config/database.yml 3) jruby -S rake db:create:all 4) jruby script/generate model Duck feather:string 5) jruby -S script/generate migration change_feathers_to_text 6) add in the generated migration's up method: change_column :ducks, :feathers, :text 7) jruby -S rake db:migrate You will then receive the error: ActiveRecord::ActiveRecordError: near "ALTER": syntax error: ALTER TABLE ducks ALTER COLUMN feathers text 8) change the migration to read: remove_column :ducks, :feathers; add_column :ducks, :feathers, :text 9) jruby -S rake db:migrate 8 & 9 don't result in the error the original description describes. In activerecord-jdbc-adapter-0.9 in lib/jdbc_adapter/jdbc_sqlite3.rb it needs to have it's change_column method changed to behave similarly to the change_column method in activerecord-2.2.2 in lib/active_record/connection_adapters/sqlite_adapter.rb. That's using the temporary table trickery mentioned by the previous commenter.
Hide
Permalink
Ippei UKAI added a comment -

Got the same error while installing Redmine 0.8.4 at http://redmine.rubyforge.org/svn/tags/0.8.4/db/migrate/044_set_language_length_to_five.rb with the following configuration:

  • jruby 1.3.1 (ruby 1.8.6p287) (2009-06-15 2fd6c3d) (OpenJDK Client VM 1.6.0)
  • activerecord-jdbc-adapter (0.9.1)
  • activerecord-jdbcsqlite3-adapter (0.9.1)
  • jdbc-sqlite3 (3.6.3.054)
Show
Ippei UKAI added a comment - Got the same error while installing Redmine 0.8.4 at http://redmine.rubyforge.org/svn/tags/0.8.4/db/migrate/044_set_language_length_to_five.rb with the following configuration:
  • jruby 1.3.1 (ruby 1.8.6p287) (2009-06-15 2fd6c3d) (OpenJDK Client VM 1.6.0)
  • activerecord-jdbc-adapter (0.9.1)
  • activerecord-jdbcsqlite3-adapter (0.9.1)
  • jdbc-sqlite3 (3.6.3.054)
Hide
Permalink
Nick Sieger added a comment -

We've got a bunch of sqlite3 fixes in 0.9.2, please try that release when it's available.

Show
Nick Sieger added a comment - We've got a bunch of sqlite3 fixes in 0.9.2, please try that release when it's available.
Hide
Permalink
Daniel Harrington added a comment -

fyi: 0.9.2. works fine for me.

Show
Daniel Harrington added a comment - fyi: 0.9.2. works fine for me.
Hide
Permalink
Emmanuel Pirsch added a comment -

This issue is note resolved in 0.9.2.

I'm trying to install Redmine 0.8.6 and got this while running it's migrations...

== 44 SetLanguageLengthToFive: migrating ======================================
– change_column(:users, :language, :string, {:limit=>5, :default=>""})
rake aborted!
ActiveRecord::ActiveRecordError: near "(": syntax error: CREATE TABLE altered_users (id INTEGER PRIMARY KEY AUTOINCREMEN
T NOT NULL(0), login VARCHAR(30) DEFAULT '' NOT NULL, hashed_password VARCHAR(40) DEFAULT '' NOT NULL, firstname VARCHAR
(30) DEFAULT '' NOT NULL, lastname VARCHAR(30) DEFAULT '' NOT NULL, mail VARCHAR(60) DEFAULT '' NOT NULL, mail_notificat
ion BOOLEAN DEFAULT 't' NOT NULL, admin BOOLEAN DEFAULT 'f' NOT NULL, status integer(0) DEFAULT 1 NOT NULL, last_login_o
n DATETIME, language VARCHAR(2) DEFAULT '', auth_source_id integer(0), created_on DATETIME, updated_on DATETIME)

And here are my installed gems.

      • LOCAL GEMS ***

activerecord-jdbc-adapter (0.9.2)
activerecord-jdbcsqlite3-adapter (0.9.2)
jdbc-sqlite3 (3.6.3.054)
jruby-jars (1.4.0)
jruby-openssl (0.6)
rake (0.8.7)
rspec (1.2.9)
sources (0.0.1)
warbler (0.9.14)

Running : jruby 1.4.0 (ruby 1.8.7 patchlevel 174) (2009-11-02 69fbfa3) (Java HotSpot(TM) Client VM 1.6.0_16) [x86-java]

on Windows 7.

Show
Emmanuel Pirsch added a comment - This issue is note resolved in 0.9.2. I'm trying to install Redmine 0.8.6 and got this while running it's migrations...
== 44 SetLanguageLengthToFive: migrating ====================================== – change_column(:users, :language, :string, {:limit=>5, :default=>""}) rake aborted! ActiveRecord::ActiveRecordError: near "(": syntax error: CREATE TABLE altered_users (id INTEGER PRIMARY KEY AUTOINCREMEN T NOT NULL(0), login VARCHAR(30) DEFAULT '' NOT NULL, hashed_password VARCHAR(40) DEFAULT '' NOT NULL, firstname VARCHAR (30) DEFAULT '' NOT NULL, lastname VARCHAR(30) DEFAULT '' NOT NULL, mail VARCHAR(60) DEFAULT '' NOT NULL, mail_notificat ion BOOLEAN DEFAULT 't' NOT NULL, admin BOOLEAN DEFAULT 'f' NOT NULL, status integer(0) DEFAULT 1 NOT NULL, last_login_o n DATETIME, language VARCHAR(2) DEFAULT '', auth_source_id integer(0), created_on DATETIME, updated_on DATETIME)
And here are my installed gems.
      • LOCAL GEMS ***
activerecord-jdbc-adapter (0.9.2) activerecord-jdbcsqlite3-adapter (0.9.2) jdbc-sqlite3 (3.6.3.054) jruby-jars (1.4.0) jruby-openssl (0.6) rake (0.8.7) rspec (1.2.9) sources (0.0.1) warbler (0.9.14)
Running : jruby 1.4.0 (ruby 1.8.7 patchlevel 174) (2009-11-02 69fbfa3) (Java HotSpot(TM) Client VM 1.6.0_16) [x86-java] on Windows 7.

People

  • Assignee:
    Unassigned
    Reporter:
    Ben Hughes
Vote (1)
Watch (2)

Dates

  • Created:
    Updated:
    Resolved: