Can't add an index to `text` type columns
Description
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: https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/18615#note_78861126
Proposal
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.