Error while restoring a Database from an SQL dump

Question

I am extremely new to MySQL and am running it on Windows. I am trying to restore a Database from a dumpfile in MySQL, but I get the following error:

$ >mysql -u root -p -h localhost -D database -o < dump.sql
ERROR: ASCII '0円' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '0円' is expected. Query: 'SQLite format 3'.

I tried $ > mysql -u root -p -h localhost -D database --binary-mode -o < dump.sql but this gave me the following ERROR at line 1: Unknown command '\☻'. It is a 500 Mb dump file, and when I view its contents using gVIM, all I can see is expressions and data which is not comprehensible. Also when I try to copy contents from the file to post here all I can copy is :SQLite format 3 This kind of seems strange.

Answer 1

The reference to --binary-mode (introduced in MySQL 5.6.3) is probably a distraction.

It doesn't sound like you're dealing with a mysqldump output file, there. Try the file utility.

shell> file dumpfile.sql
dumpfile.sql: ASCII text

If you don't get the ASCII text response, you're dealing with either something that isn't a dump file from mysqldump at all, or you're dealing with something that's been compressed (with gzip or bzip2, for example), which you'd need to uncompress before piping it into mysql.

If you see SQLite 3.x database then you definitely have your answer... it's a raw SQLite database, not a MySQL dump file.

Indeed, the first few bytes of a SQLite database are these:

53 51 4C 69 74 65 20 66 SQLite f
6F 72 6D 61 74 20 33 00 ormat 3^@

Note that the 16th octet here is 0x00, explaining the ERROR: ASCII '0円' appeared in the statement... message in this case. The suggestion that --binary-mode is appropriate is a false alarm.

---

Windows users: the 'file' utility is a tool from Unix, but the Windows version can be found here.

Answer 2

Windows

Create your dump files with this command

.\mysqldump [dbname] -r [filename.sql]

Using:

.\mysqldump --help
 -r, --result-file=name
 Direct output to a given file. This option should be used
 in systems (e.g., DOS, Windows) that use carriage-return
 linefeed pairs (\r\n) to separate text lines. This option
 ensures that only a single newline is used.

• SimZal: This is the correct answer. Powershell's > creates UTF-16 encoded file that is causing problems. Search for Powershell here: dev.mysql.com/doc/refman/5.7/en/mysqldump.html

Answer 3

I had this error once, after running mysqldump on Windows PowerShell like so:

mysqldump -u root p my_db --no-data --no-create-db --no-create-info --routines --triggers --skip-opt --set-gtid-purged=OFF > db_objects.sql

What I did was change it to this (pipe instead to Set-Content):

mysqldump -u root p my_db --no-data --no-create-db --no-create-info --routines --triggers --skip-opt --set-gtid-purged=OFF | Set-Content db_objects.sql

And the problem went away!

Answer 4

Me too in PowerShell

I encountered this issue when I was using PowerShell to call mysqldump and > to pipe the output to file. PowerShell was using the incorrect encoding when creating the file and I was presented with the same error when I tried to import the file using mysql .. < exported-file.sql

I found that setting the default encoding to UTF8 in the PowerShell session resolved this problem.

My resolution - Tested PowerShell 5.1:

$PSDefaultParameterValues["Out-File:Encoding"] = "utf8";

Example: How I was producing the export (simplified):

$cmdExportDB = "mysqldump --host $Host --databases $DbName -u $UID =p$PWD > $fileName";
Invoke-Expression "& $cmdExportDB";

Note: Discovered this does not work on PowerShell 4.0

My development environment was running 5.1, but prod is at 4.0 and my initial fix does not work in older versions of PowerShell.

Need to use | Set-Content -Encoding UTF8 $fileName

This was already suggested by Ifedi

Answer 5

Someone sent me a compressed gtar. Wasn't even too familiar with gtar, but it is another compression format.

$ file core_production-1432173533.sql.gtar
core_production-1432173533.sql.gtar: gzip compressed data, from Unix, last modified: Wed May 20 21:59:31 2015

However, I was able to decompress it the same as usual:

tar -zxvf core_production-1432173533.sql.gtar
$ file core_production-1432173533.sql
core_production-1432173533.sql: ASCII text, with very long lines

And then I could do the import:

mysql -u root -p -h localhost core_production < core_production-1432173533.sql

Answer 6

Solution: Extract the backup file and then restore this extracted sql dump.

Example :

Backup has been taken as dump.sql.gz file and extract it using gunzip cmd as follows,

shell> gunzip dump.sql.gz

And RESTORE extracted dump.sql file.

Ref: About MySQL binary and interactive mode.

http://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html#option_mysql_binary-mode

It Works for me and All set !!

Answer 7

In my case, the file was corrupted. The database was compressed with extension .bz2 but it was actually a .tar.bz2.

Decomprossing using bzip2 -dk doesn't output any error and generates the file. Using the command file on the file outputs bzip2 compressed data, block size = 900k so it doesn't even looks wrong to use it.

I had to use tar -xf myfile.bz2