Problem with migration CreateActiveHashcashStamps and MySQL

[vakuum]

When I run the migration 20240215143453_create_active_hashcash_stamps.rb with MySQL 8.0, the following error occurs:

Specified key was too long; max key length is 3072 bytes

Reproducing the error:

$ bundle exec rails c
Loading staging environment (Rails 6.1.7.8)

> class CreateActiveHashcashStamps < ActiveRecord::Migration[5.2]
  def change
    create_table :active_hashcash_stamps do |t|
      t.string :version, null: false
      t.integer :bits, null: false
      t.date :date, null: false
      t.string :resource, null: false
      t.string :ext, null: false
      t.string :rand, null: false
      t.string :counter, null: false
      t.string :request_path
      t.string :ip_address

      if t.respond_to?(:jsonb)
        t.jsonb :context # SQLite JSONB support from version 3.45 (2024-01-15)
      elsif t.respond_to?(:json)
        t.json :context
      end

      t.timestamps
    end
    add_index :active_hashcash_stamps, [:ip_address, :created_at], where: "ip_address IS NOT NULL"
    add_index :active_hashcash_stamps, [:counter, :rand, :date, :resource, :bits, :version, :ext], name: "index_active_hashcash_stamps_unique", unique: true
  end
end

> CreateActiveHashcashStamps.new.change
-- create_table(:active_hashcash_stamps)
   (164.1ms)  CREATE TABLE `active_hashcash_stamps` (`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, `version` varchar(255) NOT NULL, `bits` int NOT NULL, `date` date NOT NULL, `resource` varchar(255) NOT NULL, `ext` varchar(255) NOT NULL, `rand` varchar(255) NOT NULL, `counter` varchar(255) NOT NULL, `request_path` varchar(255), `ip_address` varchar(255), `context` json, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL)
   -> 0.1654s
-- add_index(:active_hashcash_stamps, [:ip_address, :created_at], {:where=>"ip_address IS NOT NULL"})
   (65.8ms)  CREATE INDEX `index_active_hashcash_stamps_on_ip_address_and_created_at` ON `active_hashcash_stamps` (`ip_address`, `created_at`)
   -> 0.0663s
-- add_index(:active_hashcash_stamps, [:counter, :rand, :date, :resource, :bits, :version, :ext], {:name=>"index_active_hashcash_stamps_unique", :unique=>true})
   (1.5ms)  CREATE UNIQUE INDEX `index_active_hashcash_stamps_unique` ON `active_hashcash_stamps` (`counter`, `rand`, `date`, `resource`, `bits`, `version`, `ext`)
/.../bundle/ruby/3.2.0/gems/mysql2-0.5.6/lib/mysql2/client.rb:151:in `_query': Mysql2::Error: Specified key was too long; max key length is 3072 bytes (ActiveRecord::StatementInvalid)
/.../bundle/ruby/3.2.0/gems/mysql2-0.5.6/lib/mysql2/client.rb:151:in `_query': Specified key was too long; max key length is 3072 bytes (Mysql2::Error)

Used MySQL version:

$ mysql
...
Server version: 8.0.39-0ubuntu0.24.04.2 (Ubuntu)
...

Workaround

If I reduce the size of the string columns that are part of the index index_active_hashcash_stamps_unique with limit: 100, the error no longer occurs:

$ bundle exec rails c
Loading staging environment (Rails 6.1.7.8)

> class CreateActiveHashcashStamps < ActiveRecord::Migration[5.2]
  def change
    create_table :active_hashcash_stamps do |t|
      t.string :version, null: false, limit: 100
      t.integer :bits, null: false
      t.date :date, null: false
      t.string :resource, null: false, limit: 100
      t.string :ext, null: false, limit: 100
      t.string :rand, null: false, limit: 100
      t.string :counter, null: false, limit: 100
      t.string :request_path
      t.string :ip_address

      if t.respond_to?(:jsonb)
        t.jsonb :context # SQLite JSONB support from version 3.45 (2024-01-15)
      elsif t.respond_to?(:json)
        t.json :context
      end

      t.timestamps
    end
    add_index :active_hashcash_stamps, [:ip_address, :created_at], where: "ip_address IS NOT NULL"
    add_index :active_hashcash_stamps, [:counter, :rand, :date, :resource, :bits, :version, :ext], name: "index_active_hashcash_stamps_unique", unique: true
  end
end

> CreateActiveHashcashStamps.new.change
-- create_table(:active_hashcash_stamps)
   (77.0ms)  CREATE TABLE `active_hashcash_stamps` (`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, `version` varchar(100) NOT NULL, `bits` int NOT NULL, `date` date NOT NULL, `resource` varchar(100) NOT NULL, `ext` varchar(100) NOT NULL, `rand` varchar(100) NOT NULL, `counter` varchar(100) NOT NULL, `request_path` varchar(255), `ip_address` varchar(255), `context` json, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL)
   -> 0.0785s
-- add_index(:active_hashcash_stamps, [:ip_address, :created_at], {:where=>"ip_address IS NOT NULL"})
   (50.1ms)  CREATE INDEX `index_active_hashcash_stamps_on_ip_address_and_created_at` ON `active_hashcash_stamps` (`ip_address`, `created_at`)
   -> 0.0509s
-- add_index(:active_hashcash_stamps, [:counter, :rand, :date, :resource, :bits, :version, :ext], {:name=>"index_active_hashcash_stamps_unique", :unique=>true})
   (43.1ms)  CREATE UNIQUE INDEX `index_active_hashcash_stamps_unique` ON `active_hashcash_stamps` (`counter`, `rand`, `date`, `resource`, `bits`, `version`, `ext`)
   -> 0.0436s
=> nil

[alexisbernard]

Thanks for reporting the issue.