mysql - BLOB/TEXT column 'value' used in key specification without a key length

[bkeepers]

Originally raised in https://github.com/flippercloud/flipper/issues/557#issuecomment-1849946093:

BLOB/TEXT column 'value' used in key specification without a key length

Reproducable with mysql2 and trilogy adapters on Mysql 8.2.0

$ rails new flippermysql --dabatabse mysql && cd flippermysql
$ bundle add flipper-active_record && bundle
$ rails generate flipper:active_record && rails db:create && rails db:migrate
Created database 'flippermysql_development'
Created database 'flippermysql_test'
== 20231211190204 CreateFlipperTables: migrating ==============================
-- create_table(:flipper_features)
   -> 0.0077s
-- add_index(:flipper_features, :key, {:unique=>true})
   -> 0.0108s
-- create_table(:flipper_gates)
   -> 0.0099s
-- add_index(:flipper_gates, [:feature_key, :key, :value], {:unique=>true})
bin/rails aborted!
StandardError: An error has occurred, all later migrations canceled: (StandardError)

Mysql2::Error: BLOB/TEXT column 'value' used in key specification without a key length
/private/tmp/flippermysql/db/migrate/20231211190204_create_flipper_tables.rb:15:in `up'

Caused by:
ActiveRecord::StatementInvalid: Mysql2::Error: BLOB/TEXT column 'value' used in key specification without a key length (ActiveRecord::StatementInvalid)
/private/tmp/flippermysql/db/migrate/20231211190204_create_flipper_tables.rb:15:in `up'

Caused by:
Mysql2::Error: BLOB/TEXT column 'value' used in key specification without a key length (Mysql2::Error)
/private/tmp/flippermysql/db/migrate/20231211190204_create_flipper_tables.rb:15:in `up'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

\```
BLOB/TEXT column 'value' used in key specification without a key length
\```

cc @fer9305 @BrandonHicks-msr @gs-deliverists-io

[bkeepers]

The issue is with this index:

add_index :flipper_gates, [:feature_key, :key, :value], unique: true

Now that value is a text column, MySQL want's a prefix size that should be used in the index, per these docs

And here are the Rails docs for #add_index with prefix size:

Creating an index with specific key lengths for multiple keys

add_index(:accounts, [:name, :surname], name: 'by_name_surname', length: {name: 10, surname: 15})

[gs-deliverists-io]

@bkeepers thank you for looking into that!

below migration fixed the issue

class ChangeFlipperGatesValueToText < ActiveRecord::Migration[7.1]
  def up
    remove_index :flipper_gates, %i[feature_key key value]
    change_column :flipper_gates, :value, :text
    add_index :flipper_gates, [:feature_key, :key, :value], unique: true, length: {feature_key: 50, key: 50, value: 50}
  end

  def down
    change_column :flipper_gates, :value, :string
  end
end

I'm not any mysql expert, its just an experimentation

[bkeepers]

@gs-deliverists-io Thanks, I was just experimenting with the same thing.

I just ran the flipper-active_record test suite against mysql after making the change below and everything passes.

@fer9305 @BrandonHicks-msr @gs-deliverists-io can you all test this out and confirm that it works for you as well?

class ChangeFlipperGatesValueToText < ActiveRecord::Migration[7.1]
  def up
    remove_index :flipper_gates, [:feature_key, :key, :value], unique: true
    change_column :flipper_gates, :value, :text
    add_index :flipper_gates, [:feature_key, :key, :value], unique: true, length: { value: 255 }
  end

  def down
    change_column :flipper_gates, :value, :string
  end
end

[BrandonHicks-msr]

@bkeepers

The latest snippet you provided above worked for me. The only issue I had was related to the index (and maybe related to something I was previously missing). But changing it to the following resolved it:

remove_index(:flipper_gates, [:feature_key, :key, :value], unique: true) if index_exists?(:flipper_gates, [:feature_key, :key, :value], unique: true)

[fer9305]

@bkeepers it works for me!

[gs-deliverists-io]

Thank you @bkeepers ! Lovely work. Works fine now.

❤️