mysql 同步

[wanghaisheng]

在解析binlog得到批量插入 批量更新时总会出现 主外键关联的错误 https://github.com/aaaweisen/senge-example/issues/1 分别尝试了如下2种添加跳过主外键检查 的方式 queryRunner.update(connection, "SET autocommit=0;"); queryRunner.update(connection, "SET unique_checks=0;"); queryRunner.update(connection, "SET foreign_key_checks=0;");

// queryRunner.update(connection, "SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;"); // queryRunner.update(connection, "SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;"); // queryRunner.update(connection, "SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;"); // queryRunner.update(connection, "SET NAMES utf8;"); // queryRunner.update(connection, "SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;"); // queryRunner.update(connection, "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;"); // queryRunner.update(connection, "SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';"); // queryRunner.update(connection, "SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;");

        System.out.println( "2222"+queryRunner.query(connection, "SELECT  @@FOREIGN_KEY_CHECKS;",new KeyedHandler<String>()));

        Map<Integer, Map<String, Object>> integerMapMap =
                queryRunner.insertBatch(connection, updateSQL, new KeyedHandler<Integer>(), params);

        queryRunner.update(connection, "SET autocommit=1;");
        queryRunner.update(connection, "SET unique_checks=1;");
        queryRunner.update(connection, "SET foreign_key_checks=1;");

// queryRunner.update(connection, "SET SQL_MODE=@OLD_SQL_MODE;"); // queryRunner.update(connection, "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;"); // queryRunner.update(connection, "SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;"); // queryRunner.update(connection, "SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;"); // queryRunner.update(connection, "SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;"); // queryRunner.update(connection, "SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;"); // queryRunner.update(connection, "SET SQL_NOTES=@OLD_SQL_NOTES; ");

但apache dbutils中始终还是会报主外键关联的错误

[wanghaisheng]

在aesop的https://github.com/Flipkart/aesop/tree/master/data-layers/data-layer-mysql com.flipkart.aesop.mysqldatalayer.delete.MySQLDeleteDataLayer MySQLUpsertDataLayer 中添加如下内容

        jdbcTemplate.update("SET autocommit=0;",event.getFieldMapPair());
        jdbcTemplate.update("SET unique_checks=0;",event.getFieldMapPair());
        jdbcTemplate.update("SET foreign_key_checks=0;",event.getFieldMapPair());

        jdbcTemplate.update(deleteQuery, nullValueColumnMapping);

        jdbcTemplate.update("SET autocommit=1;",event.getFieldMapPair());
        jdbcTemplate.update("SET unique_checks=1;",event.getFieldMapPair());
        jdbcTemplate.update("SET foreign_key_checks=1;",event.getFieldMapPair());