I receive an error when running mysqldump to backup my databases.

Document ID : KB000033330
Last Modified Date : 14/02/2018
Show Technical Document Details

Problem:

When trying to do a MySQLDump in NFA 9.3.0+, ADA 10.2+, and UCM 3.9+ you can get an error when you execute the command like this:

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) 

When upgrading to one of the versions above, your Mysql version will be upgrading from Mysql 5.1  to 5.6.  

The directory name will change from <Install Directory>\Mysql51\ to <Install Directory>\Mysql\

This problem above can occur if the PATH variable is still pointing towards the the old <Install Directory>\Mysql51\bin directory and is listed before the new <Install Directory>\Mysql\bin bin directory.

 

Resolution:

  1. Go to 'Computer' and right click it and select 'Properties' to enter the System page.
  2. On the Left hand pane, click on 'Advanced system settings'.
  3. Select the 'Advanced' tab and then the 'Environment Variables' button.
  4. In the bottom pane under 'System Variables', select 'Path' and then edit.
  5. Find the variable that points to <x:\installdir\mysql51\bin> and remove it.
  6. Save the save variable by clicking 'Ok' and close all Command Prompts.
  7. The mysqldump will now use the new \Mysql\bin directory and complete without error. 

**To Remove all remaining instances of MySQL 5.1 please see this document TEC1208863**