9

Ive recently been playing with Mysql Workbench and the Export function has one little annoying feature that is not needed (by me of course). The set-gtid-purged function is set to AUTO by default in the GUI and it seems everytime you want to export without this parameter - you have to change it to OFF in Data Exports - Advanced options. To make it worse - the setting is not remembered.

Wanted to check if anyone has found a way to set it to OFF permanently for every DB/conenction i make?

I even checked the wb_options.xml file and the feature is set to OFF but this doesn't seem to stop it for some reason.

<value type="string" key="wb.admin.export.option:set-gtid-purged">OFF</value>

Thanks.

b0uncyfr0
  • 177
  • 2
  • 2
  • 10
  • simi-related... I went through this troubleshooting thinking I had a legacy MySQL server that didn't support set-gtid-purged because it kept throwing an error. Found out that I had configured MySQL Workbench to use the `mysql` binary as `mysqldump`, so of course it complained. – Matthew Poer Jun 13 '18 at 14:22

4 Answers4

9

I've found a temporary solution for this on Linux/Ubuntu

  1. Open /usr/lib/mysql-workbench/modules/wb_admin_export.py
  2. Search for "--set-gtid-purged=OFF"
  3. Delete or comment that line (#) and the if statement above it.
  4. Restart MySQL Workbench

Using windows, the path to the file will vary and I will update my answer if someone can give it to me.

Mark Bucknell
  • 407
  • 4
  • 18
  • 3
    Mac file location is `/Applications/MySQLWorkbench.app/Contents/Resources/plugins/wb_admin_export.py`, line number 1861 and 1862. – Matthew Poer Jun 13 '18 at 13:55
  • In my case y have only commented the line 1883 (MySQL Workbench 8.0.13) #if self._compatibility_params: – André DLC Dec 26 '18 at 10:55
9

I want to do exactly what you are looking for but in AWS RDS. so for your case what you must do is:

Solution

  1. Update config file Update /etc/my.cnf and add

    [mysqldump] set-gtid-purged=OFF

  2. No need to restart. If you dont have access to that .cnf file then update the update directadmin.conf with the following instruction:

    extra_mysqldump_options=--set-gtid-purged=OFF

  3. Is possible to run the following command in shell to achieve point number 2:

    echo "extra_mysqldump_options=--set-gtid-purged=OFF" >> /usr/local/directadmin/conf/directadmin.conf service directadmin restart

Official and useful references

MySQL Dump

MySQL enable/disable GTID (Global Transaction Ids)

MySQL extra options during DUMP

Andre Leon Rangel
  • 1,659
  • 1
  • 16
  • 28
7

On a Mac, running Mysql 5.7, I found the following to work:

In Workbench, select:

Server -> Data Export -> Advanced Options

change "set-gtid-purged - Add 'SET @@GLOBAL.GTID_PURGED' to the output." from AUTO to OFF

Then export your data.

The exported SQL files should no longer have the line:

"SET @@SESSION.SQL_LOG_BIN= 0;"

and now it imports successfully.

Blisterpeanuts
  • 844
  • 1
  • 10
  • 16
0

If you are using only mysqldump in Ubuntu Server

Try append this to etc/mysql/conf.d/mysqldump.cnf

set-gtid-purged=OFF
column-statistics=0

May looks like this:

mysqldump.cnf setting for set-gtid-purged=OFF and column-statistics=0

JorgeM
  • 617
  • 5
  • 11