JRuby (please use github issues at http://bugs.jruby.org)
  1. JRuby (please use github issues at http://bugs.jruby.org)
  2. JRUBY-1905

ALTER TABLE failure using jdbcderby during rails migration adding a column with 'not null' constraint

    Details

    • Type: Bug Bug
    • Status: Resolved Resolved
    • Priority: Major Major
    • Resolution: Incomplete
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:
      MacOS X 10.4.11, Java 1.5.0_13, JRuby trunk r5512, rails 2.0.2, activerecord-jdbc-adapter 0.7.1, activerecord-jdbcderby-adapter 0.7.1
    • Number of attachments :
      4

      Description

      Migrating a set of migrations from mysql to derby I found that I couldn't create columns in a derby database with a 'NOT NULL' constraint unless I also set a default value.

      It is common in rails apps to set ":null => false" as a column constraint without specifying a default value.

      Perhaps this is not an error in the jdbcderby adaptor but instead a function of how Derby itself operates.

      Here's a simple test case.

      Create a simple rails testapp:

      $ rails testapp --database mysql
      

      Edit config/database.yml to use derby instead of mysql:

      $ cat config/database.yml 
      development:
        adapter: jdbcderby
        database: db/development.derby
        timeout: 5000
      
      test:
        adapter: jdbcderby
        database: db/test.derby
        timeout: 5000
      
      production:
        adapter: jdbcderby
        database: db/production.derby
        timeout: 5000
      

      Create a scaffold with a migration:

      $ jruby script/generate scaffold widget name:string description:text
      

      Here's what the schem looks like now:

      $ cat db/schema.rb                
      ActiveRecord::Schema.define(:version => 1) do
      
        create_table "widgets", :force => true do |t|
          t.string    "name"
          t.text      "description"
          t.timestamp "created_at"
          t.timestamp "updated_at"
        end
      
      end
      

      Create a new migration adding a column:

      $ cat db/migrate/002_add_field.rb 
      class AddField < ActiveRecord::Migration
        def self.up
          add_column :widgets, :color, :string, :null => false
        end
      
        def self.down
          remove_column :widgets, :function
        end
      end
      

      The migration fails:

      $ rake db:migrate
      (in /Users/stephen/dev/jruby/rails/testapp)
      == 2 AddField: migrating ======================================================
      -- add_column(:widgets, :color, :string, {:null=>false})
      rake aborted!
      ActiveRecord::ActiveRecordError: In an ALTER TABLE statement, the column 'COLOR' has been specified as NOT NULL and either the DEFAULT clause was not specified or was specified as DEFAULT NULL.: ALTER TABLE widgets ADD color varchar(256) NOT NULL
      

      If instead a default value is added to the new column definition:

      add_column :widgets, :color, :string, :null => false, :default => 'red'
      

      The migration succeeds.

        Activity

        Hide
        Stephen Bannasch added a comment -

        adds a test which adds a 32k :text column to entries, attempts to fix problem for derby (not working yet), also hsqldb not working with a different error than derby

        Show
        Stephen Bannasch added a comment - adds a test which adds a 32k :text column to entries, attempts to fix problem for derby (not working yet), also hsqldb not working with a different error than derby
        Hide
        Stephen Bannasch added a comment -

        Wanted you to know I was working on this.

        Am having some trouble fixing the problems.

        Latest patch applies to latest svn (r1001). It adds a test which tries to make a 40k :text column.

        There is an attempted fix for derby which isn't working yet (see error below). Also hsqldb is not passing the test (also see error below).

        in derby (value for inserted text field below is truncated for this comment) :

        ActiveRecord::StatementInvalid: ActiveRecord::ActiveRecordError: 
          A string constant starting with ''...........................................................&' is too long.: 
          INSERT INTO entries (body, content, rating, title, updated_on) VALUES(CAST('... ...' AS CLOB), NULL, NULL, NULL, '2008-05-26 03:10:31')
        

        In hsqldb

        ActiveRecord::StatementInvalid: ActiveRecord::ActiveRecordError: 
          Unexpected token in statement [ALTER TABLE entries ADD body longvarchar(40000]: ALTER TABLE entries ADD body longvarchar(40000)
        

        "ALTER TABLE entries ADD body longvarchar(40000)" certainly looks to be perfectly legal SQL for hsqldb.

        I'll keep looking into the problem ... any ideas would be appreciated.

        Show
        Stephen Bannasch added a comment - Wanted you to know I was working on this. Am having some trouble fixing the problems. Latest patch applies to latest svn (r1001). It adds a test which tries to make a 40k :text column. There is an attempted fix for derby which isn't working yet (see error below). Also hsqldb is not passing the test (also see error below). in derby (value for inserted text field below is truncated for this comment) : ActiveRecord::StatementInvalid: ActiveRecord::ActiveRecordError: A string constant starting with ''...........................................................&' is too long .: INSERT INTO entries (body, content, rating, title, updated_on) VALUES(CAST('... ...' AS CLOB), NULL, NULL, NULL, '2008-05-26 03:10:31') In hsqldb ActiveRecord::StatementInvalid: ActiveRecord::ActiveRecordError: Unexpected token in statement [ALTER TABLE entries ADD body longvarchar(40000]: ALTER TABLE entries ADD body longvarchar(40000) "ALTER TABLE entries ADD body longvarchar(40000)" certainly looks to be perfectly legal SQL for hsqldb. I'll keep looking into the problem ... any ideas would be appreciated.
        Hide
        Stephen Bannasch added a comment -

        Ooops, last two comments and attachment should be for [JRUBY--1960]. I'll add the comments and patch there.

        I'll take a look at this problem again too.

        Show
        Stephen Bannasch added a comment - Ooops, last two comments and attachment should be for [JRUBY--1960] . I'll add the comments and patch there. I'll take a look at this problem again too.
        Hide
        Charles Oliver Nutter added a comment -

        Hmm, so this one still seems to be dragging on. Stephen: your last comment says you'd take a look at this again...have you been able to? This bug seems to have gotten a bit drawn out, so perhaps we should file a new bug for the new breakage that resulted after your set of patches. If you need help, maybe we can get a demo app set up or work through it on IRC. I hate to see bugs drag on so long and have so much work done, but not quite there yet. What can we do to move this one forward?

        Show
        Charles Oliver Nutter added a comment - Hmm, so this one still seems to be dragging on. Stephen: your last comment says you'd take a look at this again...have you been able to? This bug seems to have gotten a bit drawn out, so perhaps we should file a new bug for the new breakage that resulted after your set of patches. If you need help, maybe we can get a demo app set up or work through it on IRC. I hate to see bugs drag on so long and have so much work done, but not quite there yet. What can we do to move this one forward?
        Hide
        Charles Oliver Nutter added a comment -

        Very old, partial fixes in place, ar-jdbc has had many releases since, and this bug should go there anyway.

        Show
        Charles Oliver Nutter added a comment - Very old, partial fixes in place, ar-jdbc has had many releases since, and this bug should go there anyway.

          People

          • Assignee:
            Charles Oliver Nutter
            Reporter:
            Stephen Bannasch
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: