MySQL Bugs: #54557: Can not restore back up files with Workbench 5.2

Bug #54557	Can not restore back up files with Workbench 5.2
Submitted:	16 Jun 2010 15:55	Modified:	13 Dec 2012 18:02
Reporter:	Greg DeBacker	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench: Administration	Severity:	S1 (Critical)
Version:	5.2.22	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	Backup, restore

Description:
Unable to restore mysqldump back up files. The sql files will create the structure but the txt files will not load the data. I am using mysql 5.1 and wb 5.2.

In the sql files I was given from the mysqldump there is the following version information

-- MySQL dump 10.9
-- Server version	4.1.22-community-nt-log

How to repeat:
In WB 5.2 OSS I have a connection that when I double-click on it it brings up my database in a SQL Editor.

I have a Data Model that when I double-click on it it brings up the model of my database.

When I double-click on Import/Export a box comes up with one server listed as mysqld@localhost. I select it and click OK. I then see my db schema listed on the "Select Database Objects to Export". If I select my schema I see all of my tables listed for export, but I don't want to export.

I click on "Import From Disk" and browse to the folder with the 968 pairs of sql and txt files. The backed up export schema appears in the left list under "Select database objects to import". I put a check mark by the schema and the click on it. In the box to right all of the tables appear. If I check one, all, or a few and then click on start import I get the same error for ever single one. Below is the error for one of the tables.

You'll notice that there is no database listed in between "--comments" and " <".

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

14:40:49 Restoring ecw_ring (abncodes)

Running: mysql.exe --defaults-extra-file=c:\windows\temp\tmpifqko1 --host=localhost --user=root --port=3306 --default-character-set=utf8 --comments < "{pathtofile}abncodes.sql"

ERROR 1046 (3D000) at line 18: No database selected

Operation failed with exitcode 1

14:40:49 Import of {pathtofile} has finished with 1 errors
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Also, if I select just a TXT file with the "Import Self Contained File Option" I get this error...

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

I get the same error using mysql.exe from the command line. If I covert the files to a CSV file with CR/LF line terminator I am able to load them in with out error using HeidiSQL (http://www.heidisql.com/)

Greg

Suggested fix:
Unknown

What exact version of MySQL Workbench, 5.2.x, do you use?

Version 5.2.22 OSS RC Revision 6091

The file was created with mysqldump 4.1.22-community-nt-log?. Thanks in advance.

At the top of each of the sql files is states:

MySQL dump 10.9
Server version	4.1.22-community-nt-log

I will also add that when I my connection from the SQL Development column the SQL Editor opens and the schema I'm trying to restore is selected as the default schema with out me making a selection.

Greg

Are you able to restore your file by just using MySQL command line client?

Using the mysql.exe from the command line the SQL files will create the table structure but the TXT files get the same syntax error. The SQL files work on the command line because I supply the database name on the command line.

From both WB and mysql.exe I receive the same error when trying to load the TXT files. {See below} The abncodes.txt is very small and the entire contents of the file is being displayed in the error. In other words, it does not seem to be interpreting the line terminator. What you are looking at is three records, each with 3 columns (2 values and NULL on the last record). This is just a guess. With larger files I don't see the entire contents. These TXT files use a line feed (ASCII 10) as terminator. Is it maybe expecting something else?

>>>>>>ERROR FROM BOTH WB AND MYSQL.EXE<<<<<<< 

ERROR 1064 (42000) at line 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 '199369	N	80069

199382	N	87081

201983	N' at line 1

Operation failed with exitcode 1

08:02:51 Import of {Path to file}\abncodes.txt has finished with 1 errors

You should use mysqlimport utility (see http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html) to load data from .txt files. Please, check if it allows you to load data.

I've tried the following commands using mysqlimport.exe without success. Each time I receive ERROR 29 File not found (Error code 2) The path and file name that is printed out with the error is correct. 

I've tried moving the txt file in to the root of C:. I've tried moving the txt in to the bin folder. I've tried moving mysqlimport.exe in to the L:\Dataimports folder. I have tried every combination of moving files in to the different folders to get this thing to work. The file name is spelled correctly and there are no permission issues. I have admin permissions.

mysqlimport.exe -u root -p******* eCW "abncodes.txt"
mysqlimport.exe -u root -p******* eCW "L:\Dataimports\\abncodes.sql"
mysqlimport.exe -u root -p******* eCW "L:\\Dataimports\\abncodes.sql"
mysqlimport.exe -u root -p******* eCW abncodes.txt
mysqlimport.exe -u root -p******* eCW L:\Dataimports\\abncodes.sql
mysqlimport.exe -u root -p******* eCW L:\\Dataimports\\abncodes.sql

I was able to get mysqlimport.exe to work. I didn't realize that the files needed to be in the C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data\{DBNAME} folder.

Just out of curiosity, once I had the files in that folder, I tried to use WB to restore. I still received the same errors with both the "Select database objects to import" option (e.g. No database selected and wrong syntax) and with the "Import self contained file" (e.g. wrong syntax)

I think this is just a valid feature request for Workbench:

Workbench should support importing data from the results of mysqldump with --tab option.

Obviously it does NOT recognize this dump "format" and does NOT support it now.

Hello,
I have had the same problem as you. The solution is that you have to enter the path to mysqldump.exe and mysql.exe. You can find these options under Edit ->Preferences->Administrator.

This is a duplicate of MySQL Bug #63853 -- which is closed as of Workbench 5.2.41.

Bug seems to be back with version 6.2 but can be solved by tip searching mysqldump.exe and mysql.exe