Dump File MySQL 5.6.10

Question

I was trying to create a dump file from my MySQL database, but when I try it always gives me this error

Operation failed with exitcode 2

16:06:07 Dumping proactivetraindb (userstate)

Running: mysqldump.exe --defaults-extra-file="h:\docume~1\anarita\locals~1\temp\tmpde5rwy.cnf"  --user=root --max_allowed_packet=1G --host=localhost --port=3306 --default-character-set=utf8 "proactivetraindb" "userstate"

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1 (1064)

The version of MySQl is 5.6.10 but the version of MySQLDump is 5.5.16

Does someone know how to solve this?

Answer 1

For Mac it depends on your installation. If you used a dmg file to install it, you need to find the bin directory. mine was under /usr/local/mysql/bin You will find mysqldump file under this folder. Now provide this location in your Preferences -> Administrator -> Path to mysqldump Tool as /usr/local/mysql/bin/mysqldump You should be good to export now.

Answer 2

For compatibility, I needed MySQL 5.6.41 on my Windows 10 machine, so I used the 5.6.41 MySQL installer to install "All" (My Workbench, MySQL, connectors, etc).

Even though I used the installer for version 5.6.41, it installed Workbench 8.0, as well as version 8.0 of the various connectors, etc.
So of course, I got a similar error.

There seems to be no easy way to force lower version of MySQL Workbench to be installed instead (On my previous machine, before workbench 8.0, did NOT have this conflict when installed).

Solution that worked for me:

1. Find the version of mysqldump.exe that matches the version of MySQL you're using:

   • From Windows Explorer, I searched my machine for all versions of mysqldump.exe
   • Right-clicked each mysqldump.exe it found -> Properties -> Details tab.
   • Found the version 5.6.41 (matching the MySQL version I'm running)

   • Clicked back to the General tab, to see the folder path that that version of mysqldump.exe was installed in

   • C:\Program Files\MySQL\MySQL Server 5.6\bin, in my case (version 8.0 was installed in C:\Program Files\MySQL\MySQL Workbench 8.0 CE)

2. Now open MySQL Workbench..

   • Edit -> Preferences -> Administration -> Path to mysqldump Tool

   • Paste in the file path for the appropriate version of mysqldump.exe (including the file name!)

   • Click OK

   • Restart MySQL Workbench

This worked for me. However, I have no idea how to make this work if you have two versions of MySQL on your machine, say MySQL 5.6 and 5.7, or 8.0.
So when I upgrade (any/some/all of) my cloud servers to version 5.7, this looks to be another challenge to overcome. Since Workbench only allows a single mysqldump.exe file, to be used no matter which version of my local MySQL server is running... Or perhaps manually change the path location, each time open a connection that uses the Port to the other MySQL version (and remember to change it back after?).

For this reason, I Removed MySQL 5.7 completely, and will hence keep all cloud (and my local) servers at 5.6 for the time being - a Project for another day.

Answer 3

You are probably using Amazon RDS, right? You can just get the newer version of mysqldump, v5.6 and use it instead.

Download the portable zip here and extract just the mysql\mysqldump.exe there. http://sourceforge.net/projects/xampp/files/XAMPP%20Windows/1.8.3/

I tried it just now and it worked fine for me.

Answer 4

I had this problem on the Amazon EC2 acessing a RDS Mysql 5.6 database. I just ran

sudo apt-get upgrade

It didn't update mysql client to 5.6, but mysqldump now is working fine.

Answer 5

The reason for this is that MySQL 5.6 has removed support for "SET OPTION" and your mysql client tools are probably on older version. Most likely 5.5 or 5.1. There is more info about this issue on MySQL bugs website.

The quickest solution is to update your mysql client tools to 5.6 and your problem will be solved. If you are on Linux platform, here is a solution that worked great for me: http://www.markomedia.com.au/mysqldump-mysql-5-6-problem-solved/

Answer 6

Using Windows 7, a far easier solution for me was to find mysqldump.exe from; C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump.exe

copy it and then replace the MySQL workbench mysqldump.exe which is located in the root of workbench C:\Program Files\MySQL\MySQL Workbench CE 5.2.47\mysqldump.exe

Restart MySQL workbench and all should work fine.