Successfully running GitLab with utf8mb4 and MySQL 5.5 and 5.6

Warning: This page is a part of an archive now and will be removed in the future.

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:

Source Code

  1. innodb_file_per_table = 1
  2. innodb_file_format = Barracuda
  3. innodb_file_format_max = Barracuda
  4. 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 DYNAMIC row format:

Source Code

  1. 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'"
  2. sudo mysql --defaults-extra-file="/etc/mysql/debian.cnf" -e "ALTER DATABASE gitlabhq_production CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

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. :)
About the Author
Ich bin Webentwickler in Stuttgart und administriere Server seit vielen Jahren. In diesem Blog erstelle ich hauptsächlich Tutorials für andere Webentwickler, Webdesigner und Serveradministratoren.
I’m a web developer in Stuttgart, Germany, and server administrator since many years. This blog mainly contains a tutorial set for other web developer, web designer and server administrators.

330 times read

Comments 0

There are not any items at the moment.

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.