CODEWITHSUNDEEP
mysqlruby-on-rails
Adam
Adam

Reputation: 1477

How can you change the default string length (varchar) of all created columns?

When you create a migration in rails and specify strings the column length is, by default, 255. Is there a way to set all columns created that don't specify a length, to default to some other number?

Generally speaking 255 is fine, but in my situation I need to switch the encoding on string columns to utf8mb4 and when I do this indexes break as the index can't be longer than 767 bytes:

Mysql2::Error: Specified key was too long; max key length is 767 bytes

As utf8 is stored as 3 bytes so 3 * 255 = 765, but utf8mb4 is 4 bytes so 4 * 255 = 1020, which will fail.

So once I change the database.yml to use utf8mb4 encoding, when I reload the schema things fail when they try to load the indexes.

Upvotes: 4

Views: 4561

Answers (3)

infused
infused

Reputation: 24337

It is possible to force Rails to use a length of 191 for varchar columns. First, create an initializer where you will override the default varchar length. I created mine in config/initializers/schema_string_limit.rb:

ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter::NATIVE_DATABASE_TYPES[:string] = {
  name: 'varchar',
  limit: 191
}

Now recreate your database and run all the migrations:

rake db:drop
rake db:create
rake db:migrate

Make sure you use rake db:migrate and not rake db:schema:load. We need to force Rails to run all the migrations again because the existing schema.rb already reflects the 255 size. Once you've run through the migrations with the new defaults you'll have a new schema.rb that uses a length of 191 so you'll be able to simply load the schema from then on.

Upvotes: 8

Shalev Shalit
Shalev Shalit

Reputation: 1963

You can do two things:

  1. you can limit your index, so it will check only the first X chars:

    add_index(:name1, :name2, length: 250)

  2. change all string with different limit

    change_column :table1, :name1, :string, :limit => 250

    change_column :table1, :name2, :string, :limit => 250

Upvotes: 4

user4384619
user4384619

Reputation:

Doing this in the migration file is very easy.

create_table :articles do |t|
 t.string :title, :null => false
 t.text :details, :limit => 3000, :null => false
end

Upvotes: 0

Related Questions