Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
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).
Comment | File | Size | Author |
---|---|---|---|
#15 | mysql_dumps_containing-2857480-14.patch | 1.89 KB | kfolsom |
#13 | mysql_dumps_containing-2857480-13.patch | 1.89 KB | helmo |
#9 | mysql_dumps_containing-2857480-9.patch | 1.64 KB | helmo |
#8 | mysql_gtid-issue_#2857480-comment_#8.patch | 1.49 KB | kfolsom |
#5 | mysql_gtid-issue#2857480_comment_#5.patch | 1.05 KB | jsedwick |
Comments
Comment #2
jsedwick CreditAttribution: jsedwick commentedWe resolved the issue with the included patch file.
Comment #3
helmo CreditAttribution: helmo at Initfour websolutions commentedThanks, but we will need to do some version checking on that... on older versions of mysql (e.g. 5.5) this results in:
docs link: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_...
Comment #4
colanComment #5
jsedwick CreditAttribution: jsedwick commentedOk, 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...
Comment #6
helmo CreditAttribution: helmo at Initfour websolutions commentedSorry, 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
Comment #7
jsedwick CreditAttribution: jsedwick commentedThanks, yeah - I noticed that happens on a backup task but not on a clone, for example. Odd.
Comment #8
kfolsom CreditAttribution: kfolsom commentedI'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.
Comment #9
helmo CreditAttribution: helmo at Initfour websolutions commentedI'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() ;)
Comment #10
kfolsom CreditAttribution: kfolsom commentedThanks 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?
Comment #11
helmo CreditAttribution: helmo at Initfour websolutions commentedthe possibilities got my mind overheating ... so I started drawing.
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...
Comment #12
kfolsom CreditAttribution: kfolsom commentedI 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.
Comment #13
helmo CreditAttribution: helmo at Initfour websolutions commentedI 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)
Comment #14
kfolsom CreditAttribution: kfolsom commentedMaybe 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.
Comment #15
kfolsom CreditAttribution: kfolsom commentedI'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.
Comment #16
helmo CreditAttribution: helmo at Initfour websolutions commentedThanks
Comment #18
helmo CreditAttribution: helmo at Initfour websolutions commented