Docker-gitlab: Mysql2::Error: Illegal mix of collations upgrading to 9.3.x

[clamor]

Hi,
trying to migrate a 9.2.7 instance to 9.3.x, migration stops with:

Mysql2::Error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=': UPDATE merge_requests SET head_pipeline_id = ((SELECT max(ci_pipelines.id) FROM ci_pipelines WHERE ci_pipelines.ref = merge_requests.source_branch AND ci_pipelines.project_id = merge_requests.source_project_id)) WHERE merge_requests.id >= 1 AND merge_requests.id < 2/home/git/gitlab/vendor/bundle/ruby/2.3.0/gems/peek-mysql2-1.1.0/lib/peek/views/mysql2.rb:14:in `query'

Setting DB_COLLATION to utf8_general_ci, produces the same error. The database has been migrated over several versions of sameersbn/docker-gitlab and the DEFAULT_COLLATION_NAME of the database is utf8_unicode_ci and the tables have mixed collation settings: Collation is either utf8_unicode_ci or utf8_general_ci.

Any hints, what I can do?

Thanks
Bories

[maxc0d3r]

+1 for the issue. I am getting similar issue and migration stops with:

Mysql2::Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=': UPDATE ci_builds SET stage_id = (SELECT id FROM ci_stages
WHERE ci_stages.pipeline_id = ci_builds.commit_id
AND ci_stages.name = ci_builds.stage)
WHERE ci_builds.id >= 1 AND ci_builds.id < 140 AND ci_builds.stage_id IS NULL/home/git/gitlab/vendor/bundle/ruby/2.3.0/gems/peek-mysql2-1.1.0/lib/peek/views/mysql2.rb:14:in `query'

[solidnerd]

ping @splattael Any clue here ? Did upgrade to the latest version (9.3.3)

[maxc0d3r]

@clamor I just went ahead and changed collation of every table in gitlabhq_production database to utf8_unicode_ci explicitly. I also changed default collation to utf8_unicode_ci for the database as well. Things are looking good now.

[splattael]

@clamor I had a similar problem.

As @maxc0d3r stated changing collation to utf8_unicode_ci should work:

• Expose envvar DB_COLLATION=utf8mb4_unicode_ci
• Start mysql with --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
• Fix existing tables GitLab's MySQL guide

SET foreign_key_checks = 0;
SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS 'Copy & run these SQL statements:' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="gitlabhq_production" AND TABLE_COLLATION != "utf8mb4_unicode_ci" AND TABLE_TYPE="BASE TABLE";
SET foreign_key_checks = 1;

Note: I used utf8mb4_unicode_ci here but you can use utf8mb4_general_ci for each point above instead.

@clamor I hope this helps and sorry for the confusion :green_heart:

[pmoralesp]

Hi, I'm currently running 9.2.7 with mysql. It is safe to upgrade? or will be necessary some kind of patch?

[splattael]

@pmoralesp In case of DB_COLLATION (only MySQL!) just make sure that your MySQL server has the same collation as your DB_COLLATION envvar.

[maxc0d3r]

@splattael Just curious but in my case my database earlier had collation as utf8_general_ci and I had not set DB_COLLATION environment variable while deploying this new image, as the documentation mentioned that default value for this environment variable is utf8_general_ci (The PR also says the same - https://github.com/sameersbn/docker-gitlab/pull/1262). I am just wondering why did my initial deployment failed than.

[splattael]

@maxc0d3r Which collation did you provide to your MySQL server?

You should start your MySQL server with --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci then.

[maxc0d3r]

Earlier when upgrade to gitlab 9.3.3 was failing this was state of my database -

mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8 | utf8_general_ci |
+--------------------------+----------------------+

I went ahead and changed the collation to utf8_unicode_ci for all the tables and database as well and after that the issue was resolved for me.

[splattael]

@maxc0d3r Yes, as long as you use the same collation when starting your MySQL server and in DB_COLLATION you can use either utf8_unicode_ci or utf8_general_ci as suggested by GitLab's MySQL Guide.

[maxc0d3r]

@splattael So DB_COLLATION is a mandatory environment variable to be passed along while upgrading to 9.3.3, right? I was just confused because documentation mentioned that this environment variable has a default value and hence I didn't used it while deploying the new image.

[splattael]

@maxc0d3r Mh, DB_COLLATION actually defaults to utf8_general_ci which should be OK as long as your MySQL server is started with collation utf8_general_ci as well. This is crucial.

[splattael]

@maxc0d3r Having said that I suspect that you could change the collation of your existing tables to utf8_general_ci and remove DB_COLLATION (which defaults also to utf8_general_ci). This should work. It worked for me.

Edit: Fix spelling of collation twice :scream:

[splattael]

@solidnerd Maybe, we should link to GitLab's MySQL Guide in description of DB_COLLATION.
WDYT?

[clamor]

By now I had no luck.

Using utf8mb4 and utf8mb4_unicode_ci for table char set and collation lead to
1071 - Specified key was too long; max key length is 767 bytes

Trying to set utf8 and utf8_general_ci on the gitlab and the mysql image did not work out, as well as setting it to utf8 and utf8_unicode_ci. I will take a closer look to GitLab's MySQL Guide.

[splattael]

@clamor Use at least MySQL server version 5.7!

[clamor]

Ah, now it migrates. Thank you.

It might be a good idea to remove the references to sameersbn/mysql in README, since it is only mysql 5.5.54 and the only tag "latest" does not give any hint on that.

Again thank you, for the help.

[clamor]

Starting the mysql 5.7 container with --character-set-server=utf8 --collation-server=utf8_general_ci and using DB_COLLATION=utf8_general_ci in the gitlab container, there was no need to use any ALTER TABLE statements in the database.