6

This seems pretty straight forward but I'm not sure what is going wrong.

I'm attempting to do the following in my Rails migration:

change_column :foo, :bar, :text, :limit => 16777215

I'm getting the following error

Mysql::Error: BLOB/TEXT column 'bar' can't have a default value: ALTER TABLE `foo` CHANGE `bar` `email_contents` text(16777215) DEFAULT '' NOT NULL

The only thing I can figure is causing an issue is that this change_column is occurring shortly after I added the column to foo and had to change it from type :string to type :text in the first place. These each come from their own migration scripts and look like this:

add_column :foo, :bar, :string, :null => false

and 

change_column :foo, :bar, :text

As an experiment, I tried changing the first change_column (change_column :foo, :bar, :text) and discovered that this successfully alters the table. Unfortunately I cannot change either of the previous migrations and can only add new ones in our current implementation so that will not work in production. The question is, what is allowing me to change the column once but not twice?

Update Tried the first suggestion but got the following:

Mysql::Error: BLOB/TEXT column 'bar' can't have a default value: ALTER TABLE `foo` CHANGE `bar` `bar` text(16777215) DEFAULT ''
keybored
  • 5,194
  • 13
  • 45
  • 70

6 Answers6

12

try

change_column :foo, :bar, :text, :limit => 16777215, :default => nil, :null => true

mkly
  • 2,253
  • 2
  • 18
  • 23
3

In case someone comes across this post and find this useful. I had the same problem the other way of avoid it is to change mysql configuration so that sql-mode is not strict, i.e. doesn't include STRICT_TRANS_TABLES which it does by default.

Nikhil
  • 16,194
  • 20
  • 64
  • 81
john
  • 141
  • 4
1

For me this seems to be a result of going from MySQL 5.5.x to 5.6.x

Note: Someone should go read Semantic Versioning 2.0.0

My fix was pretty simple...

WAS

change_column :my_table, :my_column, :mediumtext #=> Migrations Explosion

IS

change_column :my_table, :my_column, :mediumtext, default: nil #=> All good in the mysql 5.6.21
Community
  • 1
  • 1
user160917
  • 9,211
  • 4
  • 53
  • 63
1

Do you have other migrations where a :limit is specified with :text that work?

Possible that :text doesn't accept a :limit, and it simply maps to a specific MySQL datatype, if I read this correctly.

Map of Rails migration types, and MySQL data types: http://www.orthogonalthought.com/blog/index.php/2007/06/mysql-and-ruby-on-rails-datatypes/

MySQL TEXT types (no mention of LIMIT, but doesn't rule it out I suppose: http://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html

jefflunt
  • 33,527
  • 7
  • 88
  • 126
  • Yes, in my posting where I mention my experiment what I missed was that the experiment was to change the old line to the one I'm trying to use in this new migration and it DOES work. They appear to not work in succession, however. – keybored May 09 '11 at 21:05
0

This worked for me:

    change_column :delayed_jobs, :handler, :text, 
      limit: 16777215, null: true, default: nil

I had to add default: nil and then Rails was OK setting null: true, which removes the default defined in an earlier migration.

Martin Streicher
  • 1,983
  • 1
  • 18
  • 18
0

Try this:

change_column :foo, :bar, :text, :limit => 16777215, :null => true

When :null => false is used (as in the old migration), Rails adds the DEFAULT bit to the ALTER TABLE statement. But, like the error says, TEXT columns can't have a DEFAULT. By changing it to :null => true in the new migration the problem should go away.

Jordan Running
  • 102,619
  • 17
  • 182
  • 182