sqoop1.4.7の削除時のトランザクション異常

mysqlのbinlog_がformat=STATEMENT Yes、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

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