MySQL Dumps containing Global Transaction ID (GTID) Incompatible with Aegir

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_...

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

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.

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() ;)

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?

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...

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.

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)

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.

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.

Thanks