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?

Milker

Set --binary-mode to 1 if ASCII '\0' is expected. Query: ''. – Airdrome

just read the full error message – Airdrome

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. – Mountford

Answer 1

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.

Cranky

means I cant use an unzipped sql file? Actually I zipped my sql file to upload it to server as it was too large – Milker

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 ? – Cranky

Answer 2

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.

Marshland

Answer 3

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] 

Capernaum

Thanks, this worked for me. Command to unzip a .gz file: gzip -dk file.gz – Unity