Running GitLab with utf8mb4 and MySQL 5.5 and 5.6

The current stable version of MySQL for Debian Linux is still 5.5 and thus it is still a highly used version. Unfortunately, with the requirement for GitLab to use utf8mb4, it is complicated to run it with MySQL 5.5 and 5.6. Especially the (manual) update process is about to fail as soon as the database migrations try to apply.

The problem with utf8mb4 is the byte length. A common error message on adding an index to a VARCHAR field in utf8mb4 is:

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

This is because MySQL needs more space with a 4 byte character than with a 3 byte character, so a VARCHAR(255) can only contain 191 characters.

You can change this behavior in order to increase this limit to 3072 bytes by using the following line in your my.cnf:
innodb_large_prefix = 1

However, this does only affect tables that use DYNAMIC or COMPRESSED row format. By default, MySQL 5.5 and 5.6 use the COMPACT row format. They do, however, support DYNAMIC and COMPRESSED, but you can’t force them to use one of these formats by default.

So what you need to do in order to run the MySQL migrations successfully after an update is change your character set and database collation to utf8 (not utf8mb4).
Then you can successfully run the MySQL migrations.
After that you need to change the row format of any new added table to DYNAMIC and convert the character set and database collation to utf8mb4.

You need also to make sure to run InnoDB with correct settings:

innodb_file_per_table		= 1
innodb_file_format		= Barracuda
innodb_file_format_max		= Barracuda
innodb_large_prefix		= 1

For me, before running the database migration, I use the following command to switch the database back to utf8:
sudo mysql --defaults-extra-file="/etc/mysql/debian.cnf" -e "ALTER DATABASE gitlabhq_production CHARACTER SET utf8 COLLATE utf8_unicode_ci;"

After the migration, I check which tables need to be changed to utf8mb4 and the DYNAMICrow format:

sudo mysql --defaults-extra-file="/etc/mysql/debian.cnf" -s -r -e "SELECT CONCAT('ALTER TABLE \`', TABLE_NAME,'\` ROW_FORMAT = DYNAMIC, CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS 'Copy & run these SQL statements:' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'gitlabhq_production' AND TABLE_TYPE = 'BASE TABLE' AND TABLE_COLLATION != 'utf8mb4_general_ci'"
sudo mysql --defaults-extra-file="/etc/mysql/debian.cnf" -e "ALTER DATABASE gitlabhq_production CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"Code-Sprache: JavaScript (javascript)

Assuming that your database name is gitlabhq_production and you can use a file /etc/mysql/debian.cnf in order to login.

The first query returns an ALTER TABLE query for every table, which needs to be altered afterwards. Just run these queries and everything works fine.

After that – as last step – run the add_limits for MySQL:
sudo -u git -H bundle exec rake add_limits_mysql RAILS_ENV=production

Now everything is ready to be used with utf8mb4.