由 ASCII '/0' 引起的MYSQL SOURCE错误
今天在群里面有一个朋友给出一个错误:
source test.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 --bin
ary-mode to 1 if ASCII '/0' is expected. Query: ''.
以前没见过这个错误,于是仔细看了一下。先根据报错查看--binary-mode的意思:
--binary-mode By default, ASCII '/0' is disallowed and '/r/n' is
translated to '/n'. This switch turns off both features,
and also turns off parsing of all clientcommands except
/C and DELIMITER, in non-interactive mode (for input
piped to mysql or loaded using the 'source' command).
This is necessary when processing output from mysqlbinlog
that may contain blobs.
意思就是ASCII '/0' 是不允许的,除非在binary mode下,为什么会不允许呢?
原因在于一个存文本模式的sql脚本不可能存在'/0','/0'对应ASCII的00,NUL,
我们知道在纯文本模式下,任何字符都对应自己的编码,即使是空格、换行、回车、制表符等
,00 NUL只会在二进制模式的文件中才有,当使用非交互模式的时候,比如
在mysqlbinlog|mysql -u root -p 时候是用会关闭,但是我测试了一下也不行
不管使用pipe管道还是重定向都不行:
[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: ''.
[root@testmy ~]# /mysqldata/mysql5.7/bin/mysql --socket=/mysqldata/mysql5.7/mysqld3307.sock <test.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: ''.
也许这种方式只有在导入ROW格式的binlog才会用到吧。
下面是重现方式,简单的代码:
#include
#include
int main(void)
{
FILE* fd;
char a='/0';
if(!(fd = fopen("test.sql","a+")))
{
perror("error:");
exit(1);
}
fputc(a,fd);
fputc('/n',fd);
fclose(fd);
}
向test.sql写入一个/0即可,就可以重现了。下面是一个ASCII部分控制字符的截图
详细见我转的文章:
http://blog.itpub.net/7728585/viewspace-2129010/