How can you change the default string length (varchar) of all created columns?
When you create a migration in rails and specify the rows, the default column length is 255. Is there a way to set all generated columns that do not specify a length to default to some other number?
Overall 255 is great, but in my situation I need to switch the encoding for string columns to utf8mb4, and when I break this indexes, since the index cannot be longer than 767 bytes:
Mysql2::Error: Specified key was too long; max key length is 767 bytes
Since utf8 is stored as 3 bytes, so 3 * 255 = 765
, but utf8mb4 is 4 bytes, so 4 * 255 = 1020
that will fail.
So, as soon as I change database.yml
to use the encoding utf8mb4
when I reload the schema objects, they fail when they try to load the indexes.
source to share
It is possible to force Rails to use 191 length 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 re-create the database and run all migrations:
rake db:drop
rake db:create
rake db:migrate
Make sure you are using rake db:migrate
instead rake db:schema:load
. We need to force Rails to run all migrations again, since the existing one schema.rb
already reflects a size of 255. When you migrate with the new defaults, you will have a new schema.rb
one that uses a length of 191 so you can simply load the schema from now on.
source to share
You can do two things:
-
you can limit your index, so it will only check the first x-characters:
add_index(:name1, :name2, length: 250)
-
change the whole line with a different limit
change_column :table1, :name1, :string, :limit => 250
change_column :table1, :name2, :string, :limit => 250
source to share