GTID (Global Transaction ID) was first added to MySQL 5.6 to assist in failover and point-in-time backup recovery. We're using 5.6.34 and we have enabled this feature in MySQL in our hosting environment.

We found that any Aegir task that tries to import a database dump containing a GTID, will fail (this includes imports, migrates, clones, and backups).

Members fund testing for the Drupal project. Drupal Association Learn more

Comments

jsedwick created an issue. See original summary.

jsedwick’s picture

We resolved the issue with the included patch file.

helmo’s picture

Thanks, but we will need to do some version checking on that... on older versions of mysql (e.g. 5.5) this results in:

Could not write database backup file mysqldump (error: mysqldump: unknown variable 'set-gtid-purged=OFF' )

docs link: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_...

colan’s picture

Status: Active » Needs work
jsedwick’s picture

Ok, here is a solution that works on our setup that includes some version checking. If MySQL version is less than 5.6, omit the GTID option. I only tested this on our setup that's using MySQL 5.6.34...

helmo’s picture

Sorry, I get:
Drush command terminated abnormally due to an unrecoverable error. Error: Call to a member function getAttribute() on null in /var/aegir/.drush/provision/db/Provision/Service/db/mysql.php, line 253

jsedwick’s picture

Thanks, yeah - I noticed that happens on a backup task but not on a clone, for example. Odd.

kfolsom’s picture

Status: Needs work » Needs review
Issue tags: +provision, +gtid, +mysql.php
FileSize
1.49 KB

I'm working with jsedwick on this. Here's a new version of the patch that checks for the version number of mysqldump and only sets the GTID option if the version is 5.6 or greater. It works within our Aegir infrastructure using MySQL 5.6.34. I've tested an identical code snippet against a stand-alone MySQL 5.5 database, using mysqldump from MySQL versions 5.5.x, 5.6.x, and 5.7.x. Each version accomplished a successful dump, with the 5.6.x and 5.7.x versions of the command including the --set-gtid-purged=OFF option.

helmo’s picture

I've improved the patch a bit more ... #8had the issue that it tests the client version. In a remote db server setup the server version can be different.

#5 had a better strategy using getAttribute() ... after looking a bit further I found that it just lacked a call to connect() ;)

kfolsom’s picture

Thanks for the feedback and patch update. Your update checks the database server version. What happens in the case of the version of the mysqldump command being < 5.6? Wouldn't that still cause a failure and require a version check for the command itself, since the command won't recognize the option?

helmo’s picture

the possibilities got my mind overheating ... so I started drawing.

+-----------------+--------+---------------------------+
| client \ server | < 5.6  |          >= 5.6           |
+-----------------+--------+---------------------------+
| < 5.6           | OK     | impossible?               |
| >= 5.6          | OK     | add --set-gtid-purged=OFF |
+-----------------+--------+---------------------------+

What happens when the client is < 5.6 and the server >= 5.6?
Adding the set-gtid-purged option would give an error I assume...

kfolsom’s picture

I tested the 5.5 client with 'set-gtid-purged' on the command line and the command indeed fails. It didn't have a chance to interact with the database, so the database version isn't really relevant in this case.

The patch I provided in comment #8 tests for the client being < 5.6 and doesn't include the 'set-gtid-purged' option when invoking the command.

I think this patch covers the bases fairly well. If the client is < 5.6 and the database is >= 5.6, as long as the 'set-gtid-purged' option isn't given to the client, the database dump will work. At least it did when I tested the 5.5 client against a >= 5.6 database. I'd imagine if the client is too old, it might fail, but that's beyond the scope of the current problem and opens a whole other can of worms. =)

Let me know if I can provide any more information or adapt the patch to be more suitable in some way.

helmo’s picture

Status: Needs review » Needs work
FileSize
1.89 KB

I was just simplifying the #8 patch to use version_compare when I tested Debian stretch with mariaDB.

It gets more complicated :( ... MariaDB does not have the '--set-gtid-purged' option but does match >= 5.6 (Debian stretch now has 10.1.23-MariaDB)

kfolsom’s picture

Maybe rather than trying to detect the version it would be worth creating a setting to allow users to specify that they are/aren't using a database that implements the GTID? It could also be an installation question, but if the database situation changed later, that would be problematic. If that sounds reasonable, I'd be glad to give it a shot on our installation and then update the patch.

kfolsom’s picture

I've created a new patch that adds a Drush variable called 'provision_mysqldump_suppress_gtid_restore' which, when set to TRUE, will cause the '--set-gtid-purged=OFF' option to be included on the command line for the 'mysqldump' command. So rather than trying to detect the version of MySQL, this allows anyone encountering the GTID restore problem to take action to address the issue. The default for the variable is FALSE.

helmo’s picture

Status: Needs work » Reviewed & tested by the community

Thanks