GitLab MySQL migrations are using unsafe/impossible commands for GTID enabled MySQL
Summary
Whenever we upgrade to a new GitLab and run the migrations on our MySQL database, we get migration errors, which we all need to manually fix, one by one. Which can create inconsistency in what the MySQL schema should be and what it will be in the end. Next, to that, it requires manual steps on every upgrade with migrations.
The errors are there because we use a MySQL instance which has a replication instance with GTID enabled (which everybody that cares about consistency in their replication probably should).
There are two types of commands that migrations run that are not support by a MySQL instance that runs with GTID enabled (https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-concepts.html)
-
Mysql2::Error: Statement violates GTID consistency: CREATE TABLE ... SELECT.
MySQL with GTID doesn't support CREATE TABLE ... SELECT's, because it can create inconsistent data on your replica. -
CREATE TRIGGER
,Mysql2::Error: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
For consistency reasons, MySQL GTID doesn't allow TRIGGERS to be created by default.
I hope that in the future GitLab migrations would work on a MySQL instance that is configured for consistent replication (GTIDs). GitLab is a very important part of our development cycle, and we want to have a (consistent) replica in case our master fails.
PS. I know that MySQL is not recommended, but I think above commands could by bypassed to make it work.
Steps to reproduce
Run migrations from 9.1->9.2 and 9.2->9.3 with a MySQL instance that has GTID enabled.