sqoop1.4.7之删除时事务异常_warn evalsqltool: sql exception executing statemen-CSDN博客

本文链接：https://blog.csdn.net/jiandequn/article/details/108007817

本文探讨了SQOOP在执行删除操作时遇到的与MySQL二进制日志格式冲突的问题，详细介绍了错误原因及三种解决方案，包括修改MySQL全局变量、配置文件和SQOOP参数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

当mysql的binlog_format=STATEMENT 是，sqoop执行以下删除语句异常：


sqoop eval \
--connect 'jdbc:mysql://*:3306/yn_hadoop?useUnicode=true&characterEncoding=utf-8' \
--username ynhadoop \
--password * \
--query "delete from video_user_count_rank_day where t_date>='2020-08-03' and t_date<'2020-08-04';";

sqoop对mysql进行按条件查询删除时，报transaction isolation level is READ COMMITTED or READ UNCOMMITTED；如下：

2020-08-14 14:42:53,064 WARN tool.EvalSqlTool: SQL exception executing statement: java.sql.SQLException: 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.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4208)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2826)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1302)
	at org.apache.sqoop.tool.EvalSqlTool.run(EvalSqlTool.java:68)
	at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
	at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
	at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
	at org.apache.sqoop.Sqoop.main(Sqoop.java:252)

通过获取sqoop执行eval事务级别发现默认是读-提交；而mysql数据库默认是REPEATABLE_READ；

[hadoop@hadoop01 yn_hadoop_script]$ sqoop eval \
> --connect 'jdbc:mysql://192.168.65.100:3306/yn_hadoop?useUnicode=true&characterEncoding=utf-8' \
> --username ynhadoop \
> --password ynhd@123 \
> --query "select @@tx_isolation"
Warning: /usr/local/sqoop-1.4.7/../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /usr/local/sqoop-1.4.7/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/sqoop-1.4.7/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
2020-08-14 16:49:38,978 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
2020-08-14 16:49:39,011 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
2020-08-14 16:49:39,113 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
------------------
| @@tx_isolation | 
------------------
| READ-COMMITTED | 
------------------

解决办法有以下几种：

1、修改mysql 全局变量：

mysql> SET GLOBAL binlog_format=ROW; （也可设置为MIXED模式）

2、修改mysql配置my.cnf文件：

binlog_format=ROW; （也可设置为MIXED模式）

3、sqoop参数配置（推荐使用）


sqoop eval \
--connect 'jdbc:mysql://*:3306/yn_hadoop?useUnicode=true&characterEncoding=utf-8' \
--username ynhadoop \
--password * \
--metadata-transaction-isolation-level 'TRANSACTION_REPEATABLE_READ' \
--query "delete from video_user_count_rank_day where t_date>='2020-08-03' and t_date<'2020-08-04';";