sqoop oracle删除,sqoop eval删除数据报错

1. 执行

sudo -u hdfs /usr/bin/sqoop eval --connect ${jdbc_basicstat} --username ${username_basicstat} --password ${password_basicstat} -e "DELETE FROM pos_dayeffect WHERE date<20150818"

2. 报错

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

3. 原因

mysql默认的binlog_format是STATEMENT，而在READ COMMITTED或READ UNCOMMITTED隔离级别下，innodb只能使用的binlog_format是ROW。

Mysql>show variables like 'binlog_format';

4. 解决办法有两个

1、在mysql里在线设置binlog_format为ROW，此时binlog会增大，但是一般来说对数据复制支持的更好，建议单机高性能环境下使用。

mysql> SET SESSION binlog_format=ROW;

或者

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

2、在activemq.xml的jdbcPersistenceAdapter里配置transactionIsolation=“4”，即TRANSACTION_REPEATABLE_READ，此时事务更严格，会影响性能，建议在集群、强实时一致、不强调单机性能的情况下使用。

5. 最终方案

由于sqoop eval的---query中只能写一个sql语句,因此

1.设置全局binlog_format为ROW

2.执行删除语句

3.把全局binlog改回STATEMENT

--设置binlog_format为ROW

sudo -u hdfs /usr/bin/sqoop eval --connect ${jdbc_basicstat} --username ${username_basicstat} --password ${password_basicstat} --query "set global binlog_format=ROW"

--执行删除数据的操作

sudo -u hdfs /usr/bin/sqoop eval --connect ${jdbc_basicstat} --username ${username_basicstat} --password ${password_basicstat} -e "DELETE FROM pos_dayeffect WHERE date<20150818"

--设置binlog_format为STATEMENT

sudo -u hdfs /usr/bin/sqoop eval --connect ${jdbc_basicstat} --username ${username_basicstat} --password ${password_basicstat} --query "set global binlog_format=STATEMENT"