Can't add an index to `text` type columns
We can't add an index to
text type columns.
For example, if we want to add an index to
artifacts_file column in
ci_builds table, the migration code will be the follwing code.
add_concurrent_index :ci_builds, :artifacts_file
This will generate the following line in schema.rb.
add_index "ci_builds", ["artifacts_file"], name: "index_ci_builds_on_artifacts_file", using: :btree
So far so good in the PostgreSQL world.
But there is a problem that MySQL strictly requires
TEXT type column to have
length option. Without the
length, I got the following error message
Mysql2::Error: BLOB/TEXT column 'artifacts_file' used in key specification without a key length
This error can be resolvable by adding
length option in schema.rb. I've already confirmed that the following code will pass all MySQL rspec tests.
add_index "ci_builds", ["artifacts_file"], name: "index_ci_builds_on_artifacts_file", length: 20, using: :btree
But we can't manually edit schema.rb today.
db:check-schema-pg job in our CI pipeline will fail.
Reference: !18615 (comment 78861126)
At the moment, I have no good idea. Probably having two kinds of schema would make sense. schema_postgres.rb is for PostgreSQL. schema_mysql.rb is for MySQL.