rails: MySQL UTF8MB4 breaks ActiveRecord schema setup

this will set default mysql string column length to 191 instead of 255 which is the new index limit on utf8mb4 (aka real utf8).

# config/initializers/mysqlpls.rb
require 'active_record/connection_adapters/abstract_mysql_adapter'

module ActiveRecord
  module ConnectionAdapters
    class AbstractMysqlAdapter
      NATIVE_DATABASE_TYPES[:string] = { :name => "varchar", :limit => 191 }
    end
  end
end

Update: please note that either updating MySQL config and patching table creation to create dynamic row as mentioned here or using newer MySQL is a better long term solution.

Or just use PostgreSQL.

Solved. mysql 5.6 rails 4.1

in your my.cnf:

innodb_large_prefix             = 1
innodb_file_format              = barracuda
innodb_file_per_table           = 1

and you must create the table with the option ROW_FORMAT=DYNAMIC (by default it’s COMPACT)

here I found a monkey patch http://3.1415.jp/mgeu6lf5

config/initializers/ar_innodb_row_format.rb

ActiveSupport.on_load :active_record do
  module ActiveRecord::ConnectionAdapters   
    class AbstractMysqlAdapter 
      def create_table_with_innodb_row_format(table_name, options = {}) 
        table_options = options.reverse_merge(:options => 'ENGINE=InnoDB ROW_FORMAT=DYNAMIC')

        create_table_without_innodb_row_format(table_name, table_options) do |td|
         yield td if block_given? 
        end
      end
      alias_method_chain :create_table, :innodb_row_format 
    end
  end
end

FYI rails 5.2 monkey patch

What I’ve done and what I’ve learned:

• switched the database.yml encoding to utf8mb4
• left all my tables utf8
• left the database utf8 (ensures new tables are utf8 by default)
• converted individual columns as necessary to utf8mb4
• switched to schema.sql dumps and imports so the column encodings are preserved properly

This seems to work great without any weird Rails issues - if you can give up schema.rb, which I’ve always found more annoying than helpful in a large production app anyways.

I have worked around this by ALTER DATABASE nnnnn CHARACTER SET utf8mb3 But I would like an out-of-box solution so I don’t need to change the characterset in the database.

For those of you thinking “wasn’t this fixed in 8744632, why isn’t it working?”. The fix was removed in #23168, for some reason…

I think the point of the monkey patch is because it’s easy to forget to always add those options… or new team members replace old ones and no one knows that special options are always required, etc…

Sure you can do it manually, but if you want to do it once and forget it (though document the monkey patch somewhere) the patch makes sense. My solution above has also proven to work well in practice.

I still see the Mysql2::Error: Specified key was too long; max key length is 767 bytes: CREATE UNIQUE INDEX when performing rake db:create && rake db:migrate with Rails 4.