Mysql2::Error: Specified key was too long; max key length is 3072 bytes #238

estebanutz · 2021-11-10T18:06:42Z

estebanutz
Nov 10, 2021

I'm getting this error when trying to run the migration:
Mysql2::Error: Specified key was too long; max key length is 3072 bytes

I'm running Rails 6.1.4.1, MySQL 8.0.26. The adapter is mysql2 and encoding utf8mb4. Any ideas on how to fix this issue? I looked quite bit online and tried several "solutions" without any luck. I can provide more information if needed. Thanks!

Answered by 0000sir

Nov 12, 2021

change the migration like this will make it work:

      #t.references :favoritable, polymorphic: true, null: false
      #t.references :favoritor, polymorphic: true, null: false
      t.integer :favoritor_id, null: false, index: true
      t.string :favoritor_type, null: false, limit: 50, index: true
      t.integer :favoritable_id, null: false, index: true
      t.string :favoritable_type, null: false, limit: 50, index: true

You won't have a class name longer than 50 normally~

View full answer

jonhue · 2021-11-11T07:19:45Z

jonhue
Nov 11, 2021
Maintainer

I haven't encountered this myself. A good strategy might be to apply the migration changes one by one and see which exact step fails.

1 reply

jonhue Nov 11, 2021
Maintainer

I'm happy to move this back to an issue when you find something that points to a problem with this library (or if more people appear to have this problem).

estebanutz · 2021-11-11T13:50:50Z

estebanutz
Nov 11, 2021
Author

@jonhue The step below fails:

add_index :favorites, ['favoritable_type', 'favoritable_id', 'favoritor_type', 'favoritor_id', 'scope'],name: 'uniq_favorites__and_favoritables', unique: true

1 reply

0000sir Nov 12, 2021

Got this happened too, mysql 8.0.20

0000sir · 2021-11-12T02:32:39Z

0000sir
Nov 12, 2021

full error message as below:

Mysql2::Error: Specified key was too long; max key length is 3072 bytes: CREATE UNIQUE INDEX `uniq_favorites__and_favoritables`  ON `favorites` (`favoritable_type`, `favoritable_id`, `favoritor_type`, `favoritor_id`, `scope`) 
/usr/local/bundle/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `_query'
/usr/local/bundle/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `block in query'
/usr/local/bundle/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `handle_interrupt'
/usr/local/bundle/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `query'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:187:in `block (2 levels) in execute'
/usr/local/bundle/gems/activesupport-5.2.2.1/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/usr/local/bundle/gems/activesupport-5.2.2.1/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/usr/local/bundle/gems/activesupport-5.2.2.1/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:186:in `block in execute'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:579:in `block (2 levels) in log'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:578:in `block in log'
/usr/local/bundle/gems/activesupport-5.2.2.1/lib/active_support/notifications/instrumenter.rb:23:in `instrument'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:569:in `log'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:185:in `execute'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/mysql/database_statements.rb:28:in `execute'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:382:in `add_index'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:871:in `block in method_missing'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:840:in `block in say_with_time'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:840:in `say_with_time'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:860:in `method_missing'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:604:in `method_missing'
/opt/www/db/migrate/20211112022725_acts_as_favoritor_migration.rb:21:in `up'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:777:in `up'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:817:in `exec_migration'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:798:in `block (2 levels) in migrate'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:797:in `block in migrate'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:416:in `with_connection'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:796:in `migrate'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:977:in `migrate'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:1292:in `block in execute_migration_in_transaction'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:1345:in `ddl_transaction'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:1291:in `execute_migration_in_transaction'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:1263:in `block in migrate_without_lock'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:1262:in `each'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:1262:in `migrate_without_lock'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:1210:in `block in migrate'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:1363:in `with_advisory_lock'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:1210:in `migrate'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:1036:in `up'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/migration.rb:1011:in `migrate'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/tasks/database_tasks.rb:172:in `migrate'
/usr/local/bundle/gems/activerecord-5.2.2.1/lib/active_record/railties/databases.rake:60:in `block (2 levels) in <main>'
/usr/local/bundle/gems/railties-5.2.2.1/lib/rails/commands/rake/rake_command.rb:23:in `block in perform'
/usr/local/bundle/gems/railties-5.2.2.1/lib/rails/commands/rake/rake_command.rb:20:in `perform'
/usr/local/bundle/gems/railties-5.2.2.1/lib/rails/command.rb:48:in `invoke'
/usr/local/bundle/gems/railties-5.2.2.1/lib/rails/commands.rb:18:in `<main>'
/usr/local/bundle/gems/bootsnap-1.4.1/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:21:in `require'
/usr/local/bundle/gems/bootsnap-1.4.1/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:21:in `block in require_with_bootsnap_lfi'
/usr/local/bundle/gems/bootsnap-1.4.1/lib/bootsnap/load_path_cache/loaded_features_index.rb:83:in `register'
/usr/local/bundle/gems/bootsnap-1.4.1/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:20:in `require_with_bootsnap_lfi'
/usr/local/bundle/gems/bootsnap-1.4.1/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:29:in `require'
/usr/local/bundle/gems/activesupport-5.2.2.1/lib/active_support/dependencies.rb:291:in `block in require'
/usr/local/bundle/gems/activesupport-5.2.2.1/lib/active_support/dependencies.rb:257:in `load_dependency'
/usr/local/bundle/gems/activesupport-5.2.2.1/lib/active_support/dependencies.rb:291:in `require'
bin/rails:4:in `<main>'

0 replies

0000sir · 2021-11-12T02:43:05Z

0000sir
Nov 12, 2021

change the migration like this will make it work:

      #t.references :favoritable, polymorphic: true, null: false
      #t.references :favoritor, polymorphic: true, null: false
      t.integer :favoritor_id, null: false, index: true
      t.string :favoritor_type, null: false, limit: 50, index: true
      t.integer :favoritable_id, null: false, index: true
      t.string :favoritable_type, null: false, limit: 50, index: true

You won't have a class name longer than 50 normally~

1 reply

estebanutz Nov 12, 2021
Author

Thanks @0000sir I'll try that migration and report back.

estebanutz · 2021-11-15T17:52:58Z

estebanutz
Nov 15, 2021
Author

@0000sir Thank you! The migration change worked for me.

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Mysql2::Error: Specified key was too long; max key length is 3072 bytes #238

{{title}}

Replies: 5 comments 3 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

Select a reply

Mysql2::Error: Specified key was too long; max key length is 3072 bytes #238

estebanutz Nov 10, 2021

Replies: 5 comments · 3 replies

jonhue Nov 11, 2021 Maintainer

jonhue Nov 11, 2021 Maintainer

estebanutz Nov 11, 2021 Author

0000sir Nov 12, 2021

0000sir Nov 12, 2021

0000sir Nov 12, 2021

estebanutz Nov 12, 2021 Author

estebanutz Nov 15, 2021 Author

estebanutz
Nov 10, 2021

Replies: 5 comments 3 replies

jonhue
Nov 11, 2021
Maintainer

jonhue Nov 11, 2021
Maintainer

estebanutz
Nov 11, 2021
Author

0000sir
Nov 12, 2021

0000sir
Nov 12, 2021

estebanutz Nov 12, 2021
Author

estebanutz
Nov 15, 2021
Author