All geek questions in one place

MySQL dump dump 5.6.5-m8

We are working with MySQL version 5.6.5-m8 dev.
When we tried to reset the database, we received the following error:

user @ ubuntu-11: ~ $ mysqldump -u root -p my_schema> dump.sql
Enter password:
mysqldump: Failed to execute 'SET OPTION SQL_QUOTE_SHOW_CREATE = 1': you have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to "OPTION SQL_QUOTE_SHOW_CREATE = 1" on line 1 (1064)

Can anyone advise a problem?

+6
source share
6 answers

MySQL 5.6 has stopped and removed SET OPTION syntax support, but even the latest mysqldump 5.5.30 (at the time of writing) still generates SET OPTION . In other words, mysqldump 5.5 cannot reset the MySQL 5.6 server.

See: http://bugs.mysql.com/bug.php?id=67507

The initial question may have been a temporary issue with 5.6.5-m8, but I thought that people who find this question could use this information.

+11
source

I had this problem. It turns out that the mysqldump client I used was several versions behind the server and used outdated syntax. Check if you have old binaries that were previously in your path - possibly in / usr / bin / - and look for newer ones in the same place as the executable binary mysql - maybe / usr / local / mysql.

+4
source

If you are in a situation where you cannot upgrade mySQL (or touch existing tools), try this approach:

  • Find mysqldump (mine is in / usr / bin / mysqldump)
  • Get a copy of it and type in the hex editor (I used HxD)
  • Find “SET OPTION SQL_QUOTE_SHOW_CREATE” as a plain text string (ANSI, 1-byte-char) and run the “OPTION” bytes with spaces (0x20 in hexadecimal format)
  • Use this modified version of mysqldump !

Then pretend that you didn’t just solve the problem with a serious hack, and tried to forget about it on your way to get more coffee. He worked for several months for me!

+3
source

Received the same error. I had binaries in / usr / bin left over from a previous installation of MySQL 5.1. I checked that the binaries are also in my MySQL 5.6 bin folder, and then I blew away the / usr / bin versions. After that, mysqldump worked fine.

+2
source

+1 for Burkhan Khalid's answer

Run it with --force, output it to dbdump.sql, edit it for any incompatible statements.

Command line example:

mysqldump -uusername --ppassword --force dbname> dbdump.sql

edit your dbdump.sql to fix any incompatible statement in mysql versions. For me it was a timestamp expression: from 5.6 by default 'CURRENT_TIMESTAMP' to the value CURRENT_TIMESTAMP by default 5.5.

then import dbdump.sql or do whatever you need to do is ok.

+1
source

Workbench is not installed in the same location as MYSQL. Workbench installs with its OWN copy of sqldump IN ADDITION to the copy installed with MySQL.

WORKBENCH "C: \ Program Files \ MySQL \ MySQL Workbench CE 5.2.47 \ sqldump.exe" = 5.5.16 MYSQL "C: \ Program Files \ MySQL \ MySQL Server 5.6 \ bin \ sqldump.exe" = 5.6.10.

I copied the MYSQL version to my workbench directory and now everything works fine :)

Hope this helps! Here is the link Link

0
source

Source: https://habr.com/ru/post/918917/

More articles:


All Articles