Why MySQL Source Fails with ASCII '\0' and How to Resolve It

When executing source test.sql MySQL may abort with the message “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.” This error appears when a NUL byte is present in the SQL script.

The --binary-mode switch disables the default prohibition of ASCII '\0' and stops the automatic conversion of \r\n to \n. It also disables parsing of most client commands except \C and DELIMITER in non‑interactive mode, which is required when processing binary‑format data such as blobs from mysqlbinlog.

In plain‑text SQL files a NUL byte (ASCII 0) should never appear because every character maps directly to its encoding. The NUL byte only exists in binary files; therefore MySQL rejects it unless --binary-mode is turned on.

Attempts to feed a file containing a NUL byte via a pipe or redirection still fail because the client runs in non‑interactive mode without --binary-mode. For example:

[root@testmy ~]# cat test.sql | /mysqldata/mysql5.7/bin/mysql --socket=/mysqldata/mysql5.7/mysqld3307.sock
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: ''.

The error typically occurs when importing ROW‑format binlog files, where NUL bytes may be present.

To reproduce the problem, create a minimal C program that writes a NUL character followed by a newline into test.sql:

#include <stdio.h>
#include <stdlib.h>
int main(void) {
    FILE *fd;
    char a = '\0';
    if(!(fd = fopen("test.sql", "a+"))) {
        perror("error:");
        exit(1);
    }
    fputc(a, fd);
    fputc('
', fd);
    fclose(fd);
    return 0;
}

Running the compiled program creates test.sql containing a NUL byte. Executing source test.sql then triggers the same error shown above.

Below is a screenshot of the ASCII control characters that cause the failure: