14

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.

  • 1
    Were you the one who took the backup? – Menelaos Bakopoulos Jun 18 '13 at 0:28
  • I was getting this error but got a fresh MySQL dump and tried re-importing and it worked fine. Our MySQL dump comes in two zipped parts that have to be concatenated and then unzipped. I think the initial unzipping was interrupted, resulting in a .sql file with weird characters and encodings. The second attempt worked fine. – Joshua Pinter Jul 19 '18 at 23:05
18

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.

  • I'm getting this error and when running file MySQL.sql it returns UTF-8 Unicode text, with very long lines. Any ideas? – Joshua Pinter Jul 19 '18 at 22:36
  • @JoshuaPinter try less -S MySQL.sql. What do you see? Does it look like a MySQL dump file? They are for the most part human-readable. (Use q to exit.) – Michael - sqlbot Jul 19 '18 at 23:12
  • 1
    Yes, first line looks like -- MySQL dump 10.13 Distrib 5.7.22, for Linux (x86_64). And moving down via space bar shows typical MySQL instructions. However, if I keep going down it freezes up on a certain line. The same line that appeared in the error message. I looked into it further and found out that the MySQL dump had not been unzipped properly the first time. Not sure what went wrong but when I re-unzip, it works fine. I added an answer about it here for others: stackoverflow.com/a/51432853/293280 Thanks so much for your help and quick reply. 👍 – Joshua Pinter Jul 19 '18 at 23:18
5

Windows

Create your dump files with this command

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

Using:

.\mysqldumb --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.
1

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!

1

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

0

Have you tried opening in notepad++ (or another editor) and converting/saving us to UTF-8?

See: https://stackoverflow.com/questions/7256049/notepad-converting-ansi-encoded-file-to-utf-8

Another option may be to use textwrangle to open and save the file as UTF-8: http://www.barebones.com/products/textwrangler/

0

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
0

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 !!

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.