Mysql ERROR: ASCII '\0' while importing sql file on linux server

Question

I am getting following error while importing sql file

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: ''.

How do I fix this?

Set --binary-mode to 1 if ASCII '\0' is expected. Query: ''. — e4c5, Jul 14 '16 at 22:44
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

score 48 · Accepted Answer · edited May 23 '17 at 12:34

48

Try something like :

mysql -u root -p -h localhost -D database --binary-mode -o < dump.sql

and make sure your sql file is not zipped.

edited May 23 '17 at 12:34

Community

1
1

answered Jul 14 '16 at 23:01

Eric BELLION

586
5
5

means I cant use an unzipped sql file? Actually I zipped my sql file to upload it to server as it was too large – zish Jul 15 '16 at 00:27
1

Nope, you should be able to import unzipped file. Your file could be detected as binary because it not have been unzipped prior to MySQL import. Could you edit your question to include the command used to import ? – Eric BELLION Jul 15 '16 at 00:45

score 30 · Answer 2 · answered Dec 21 '16 at 14:17

30

I encountered this problem,the sql file was in a valid ISCII format, I solved as the following:

1- in shell use file command to detect type of data contained in the dump file:

file db.sql

got output like following:

db.sql: Little-endian UTF-16 Unicode text, with very long lines, with CRLF line terminators

2- convert the existing dump file to UTF8 (ASCII) using iconv:

iconv -f utf-16 -t utf-8 db.sql > db_utf8.sql

then import the new file.

answered Dec 21 '16 at 14:17

Zeid Al-Rashwani

545
5
8

1

thanks. It fixed problem that export from windows to mac. – saturngod Aug 08 '19 at 07:22

score 8 · Answer 3 · edited Nov 11 '22 at 20:46

I just had this issue because the file was gzipped. I unzipped it and had no further issue.

Adding to it: Path to db also should be configured correctly. Considering you are running find command on mac then use sudo and unzipped data file name could be put in below code at the place of [some_name_of_data.sql]

sudo find / -name [some_name_of_data.sql] -type d

or if you don't have sudo access as working in some company system then use below command:

find / -name [some_name_of_data.sql]

And something like this (eg. fin sqli /System/Volumes/Data/Users/clinto.abraham/projects/sme/Data/react-dev-2022-08-02.sql ) could be used to set the data in a docksal environment.

fin sqli [some_path_to_db]

Thanks, this worked for me. Command to unzip a `.gz` file: `gzip -dk file.gz` — The Unknown Dev, Mar 25 '21 at 15:19

Mysql ERROR: ASCII '\0' while importing sql file on linux server

3 Answers3

Linked