Ruby- Mysql2 encoding utf8mb4 creating problems for indexing(hence affecting performance)

Question

I am trying to create a composite index on my table because the below table is giving me performance issues and it has a character encoding of ut8mb4 and I am getting the following error

Mysql2::Error: Specified key was too long

The entire error is given below:-

== 20161103114941 AddIndexOnKeyAndIdOnInvitees: migrating - Shard: master =====
-- add_index(:invitees, [:key, :created_at])
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Specified key was too long; max key length is 767 bytes: CREATE  INDEX `index_invitees_on_key_and_created_at`  ON `invitees` (`key`, `created_at`) 
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:299:in `query'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:299:in `block in execute'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:473:in `block in log'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activesupport-4.2.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/abstract_adapter.rb:15:in `instrument'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:467:in `log'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:299:in `execute'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/connection_adapters/mysql2_adapter.rb:231:in `execute'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:529:in `add_index'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:662:in `block in method_missing'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:632:in `block in say_with_time'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:632:in `say_with_time'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:652:in `method_missing'
/var/www/production_hobnob_copy/db/migrate/20161103114941_add_index_on_key_and_id_on_invitees.rb:3:in `change'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:606:in `exec_migration'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:590:in `block (2 levels) in migrate'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:589:in `block in migrate'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:292:in `with_connection'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:588:in `migrate'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:765:in `migrate'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:995:in `block in execute_migration_in_transaction'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:1043:in `ddl_transaction'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:994:in `execute_migration_in_transaction'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:956:in `block in migrate'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:952:in `each'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:952:in `migrate'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/migration.rb:86:in `migrate_with_octopus'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:820:in `up'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/migration.rb:113:in `block in up_with_octopus'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:246:in `block (2 levels) in run_queries_on_shard'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:509:in `using_shard'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:245:in `block in run_queries_on_shard'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:492:in `keeping_connection_proxy'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:244:in `run_queries_on_shard'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:253:in `block in send_queries_to_multiple_shards'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:252:in `map'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:252:in `send_queries_to_multiple_shards'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/migration.rb:112:in `up_with_octopus'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:798:in `migrate'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/migration.rb:104:in `block in migrate_with_octopus'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:246:in `block (2 levels) in run_queries_on_shard'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:509:in `using_shard'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:245:in `block in run_queries_on_shard'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:492:in `keeping_connection_proxy'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:244:in `run_queries_on_shard'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:253:in `block in send_queries_to_multiple_shards'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:252:in `map'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:252:in `send_queries_to_multiple_shards'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/migration.rb:103:in `migrate_with_octopus'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/tasks/database_tasks.rb:137:in `migrate'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/railties/databases.rake:44:in `block (2 levels) in <top (required)>'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/bin/ruby_executable_hooks:15:in `eval'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/bin/ruby_executable_hooks:15:in `<main>'
Tasks: TOP => db:migrate

I went through the following post in this forum:-

Rails creating schema_migrations - Mysql2::Error: Specified key was too long

and the solution given is to convert the character encoding to utf8 which is not possible in my case. Is there a work around for this.

Any suggestions or help will be very helpful..

Thanks a lot

Rick James · Accepted Answer · 2016-11-12T16:22:31.237

3

In older versions, indexes were limited to 767 bytes. This was enough room for VARCHAR(255) CHARACTER SET utf8, which was popular.

VARCHAR(255) CHARACTER SET utf8mb4 needs more than that. Newer versions have raised the limit, so it will work.

Solutions:

Upgrade mysql.
Decrease 255 -- Look at your data and use a sensible number, not the overworked 255. If that is less than 191, your indexes will work.
Get 5.6.3 / 5.5.14 (or later) and...

Instructions (not needed for 5.7.7 and later)

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC;

Then maybe you can index (255) utf8mb4.

"key too long" only indirectly impacts "performance" in that you can't add the index.

edited Nov 12 '16 at 16:22

answered Nov 03 '16 at 23:52

Rick James

135,179
13
127
222

I understand but because I can't index the query it's taking a long time to execute..I will try what you said and get back..thanks for the answer – SemperFi Nov 04 '16 at 04:07
MySql version we are using is 5.6 and we cant decrease 255. Can you please provide the workaround? – SemperFi Nov 04 '16 at 06:10
You have strings longer than 191? Or you can't do `ALTER TABLE`? – Rick James Nov 04 '16 at 18:29
1

Added the instructions. – Rick James Nov 04 '16 at 18:37

Ruby- Mysql2 encoding utf8mb4 creating problems for indexing(hence affecting performance)

1 Answers1