当前位置: 首页 > Mysql > 正文

mysql数据库ERROR 1665 (HY000)错误

Mysql数据库在使用LOAD DATA INFILE的方式加载数据的时候,遇到了ERROR 1064 (42000)错误。

mysql> load data infile '/tmp/FUL_D_STOCK_H_201512.sql' into table FUL_D_STOCK_H_201512 fields terminated by ',';
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

这个错误是因为当前的binlog是STATEMENT,STATEMENT模式不支持LOAD DATA INFILE,需要将binlog的保存方式修改成ROW模式。

mysql> show variables like 'BINLOG_FORMAT';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

查看参数文件发现binlog_format参数备注释掉了,而STATEMENT模式应该是默认的设置。

# binary logging is required for replication
log-bin=mysql-bin
expire_logs_days=3
binlog_format=ROW
binlog_cache_size = 64M

重启mysql数据库使之生效,或者在mysql命令行工具通过set命令来修改这个参数,这样可以不重启就可以生效。

[root@mysql-1 3308]# mysqladmin -uroot -p -S /mysql/data/3308/mysql.sock shutdown 
Enter password: 
160215 11:54:36 mysqld_safe mysqld from pid file /mysql/data/3308/mysql-1.pid ended
[1]+  Done                    mysqld_safe --defaults-file=/mysql/data/3308/my.cnf  (wd: ~)
(wd now: /mysql/data/3308)

[root@mysql-1 ~]# mysqld_safe --defaults-file=/mysql/data/3308/my.cnf &
[1] 8532
[root@mysql-1 ~]# 160220 09:43:57 mysqld_safe Logging to '/mysql/data/3308/mysql-1.err'.
160220 09:43:57 mysqld_safe Starting mysqld daemon with databases from /mysql/data/3308

查看设置是否生效。

mysql> show variables like 'BINLOG_FORMAT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.04 sec)

再次加载,问题解决。

mysql> load data infile '/tmp/FUL_D_STOCK_H_201512.sql' into table FUL_D_STOCK_H_201512 fields terminated by ',';
Query OK, 2000000 rows affected (35.04 sec)
Records: 2000000  Deleted: 0  Skipped: 0  Warnings: 0

 

本文固定链接: http://www.dbdream.com.cn/2016/04/mysql%e6%95%b0%e6%8d%ae%e5%ba%93error-1665-hy000%e9%94%99%e8%af%af/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2016年04月06日发表在 Mysql 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: mysql数据库ERROR 1665 (HY000)错误 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , , , , ,

mysql数据库ERROR 1665 (HY000)错误:目前有1 条留言

  1. 沙发
    shunzi:

    你网站打开慢死了,跟蜗牛似的

    2016-04-06 22:52 [回复]

发表评论

快捷键:Ctrl+Enter