Indexing string columns is broken on some MySQL setups
Summary
As reported by @rtripault in https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/14414#note_41248492
Running the migration introduced in that MR results in this error on some MySQL databases:
Mysql2::Error: Specified key was too long; max key length is 767 bytes: CREATE INDEX `index_projects_on_repository_storage` ON `projects` (`repository_storage`)
The issue is the collation used by the database. a 4-byte collation breaks indexing the column. 3 bytes (e.g., utf8
) is fine. Apparently, we advised people to switch to utf8mb4 at some point in teh past.
However, other columns are affected too. In total:
-
merge_requests.merge_commit_sha
abcfdd5c 2017-09-28 -
projects.repository_storage
3ce81ec6 2017-09-21 -
user_custom_attributes.key
e9eae3eb 2017-09-28 -
user_custom_attributes.value
e9eae3eb 2017-09-28
Steps to reproduce
Set the database collation to utf8mb4
, run the migration
What is the current bug behavior?
Adding the index fails
What is the expected correct behavior?
The indexing operation should succeed.
Possible fixes
We could include a predated migration to reduce the column size so it can be indexed? 128 characters should be more than enough for a repository storage name.
We could implement https://gitlab.com/gitlab-org/gitlab-ce/issues/38238 to normalize the data, turning this column into a foreign key shard_id
(in time for %10.1?)
We could specify a list of supported collations and exclude utf8mb4? https://gitlab.com/gitlab-org/gitlab-ce/blob/master/config/database.yml.mysql is quite confused about which collation to use.
/cc @yorickpeterse