Load data奇怪的问题

版权声明:本文为博主原创文章,未经博主允许不得转载。盗转不注明出处死妈 https://blog.csdn.net/ashic/article/details/79952564

本文永久连接:
http://fuxkdb.com/2018/03/03/Load-data%E5%A5%87%E6%80%AA%E7%9A%84%E9%97%AE%E9%A2%98/
盗转死妈

Load data奇怪的问题

公司一套所谓的BI库(5.5 innodb引擎),由于太大了,考虑转用TokuDB引擎,最近在测试,直接在一个新环境安装Percona5.7.21,然后恢复备份,转换为TokuDB表, 再做从库和原5.5库同步(5.5 是statement格式)
准备同步上,然后这套环境部署一下应用,再全面的测一下.
结果发现同步报错了

root@localhost 10:42:  [test]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 106.3.130.67
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.010346
          Read_Master_Log_Pos: 715054772
               Relay_Log_File: mysql-relay.000010
                Relay_Log_Pos: 376792000
        Relay_Master_Log_File: mysql-bin.010288
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%,union_log%.%,test.%
                   Last_Errno: 1300
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.010288, end_log_pos 378517485. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 376791811
              Relay_Log_Space: 74984691638
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1300
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.010288, end_log_pos 378517485. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 12
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 180402 00:07:09
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: a3f4b929-31a0-11e8-9714-f8bc123346cc:1-864
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)


查看告警日志

2018-04-02T00:06:53.815565+08:00 5 [Note] Multi-threaded slave statistics for channel '': seconds elapsed = 151; events assigned = 7924737; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 375; waited at clock conflicts = 0 waited (count) when Workers occupied = 0 waited when Workers occupied = 0
2018-04-02T00:07:09.876177+08:00 6 [ERROR] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.010288, end_log_pos 378517485; Error 'Invalid utf8 character string: ''' on query. Default database: 'BI'. Query: 'LOAD DATA INFILE '/data/mysqldata/3306/tmp/SQL_LOAD-a3f4b929-31a0-11e8-9714-f8bc123346cc-11-12025.data' IGNORE INTO  TABLE `520054_all_role` CHARACTER SET utf8 FIELDS TERMINATED BY ',' OPTIONALLY  ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n' (`openid`, `clientid`, `roleid`, `rolename`, `school`, `IP`, `snid`, `rolelogin_time`, `amount`, `val`, `consume_sum`, `own_after`, `first_payment_time`, `last_payment_time`, `last_login_time`, `first_pay_level`, `level`, `vip_level`, `first_pay_amount`, `mission_id`)', Error_code: 1300
2018-04-02T00:07:09.876335+08:00 5 [Warning] Slave SQL for channel '': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756
2018-04-02T00:07:09.876385+08:00 5 [Note] Slave SQL thread for channel '' exiting, replication stopped in log 'mysql-bin.010288' at position 376791811

总之就是Load data 这个文件/data/mysqldata/3306/tmp/SQL_LOAD-a3f4b929-31a0-11e8-9714-f8bc123346cc-11-12025.data出问题了呗

于是我手动执行(这里拷贝了一下文件到/tmp)

#mysql --default-character-set=utf8 --socket=/data/mysqldata/3306/mysql.sock -uroot -p"" --show-warnings test -e "LOAD DATA local INFILE '/tmp/SQL_LOAD-a3f4b929-31a0-11e8-9714-f8bc123346cc-11-12025.data' IGNORE INTO  TABLE 520054_all_role CHARACTER SET utf8 FIELDS TERMINATED BY ',' OPTIONALLY  ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\n' (openid, clientid, roleid, rolename, school, IP, snid, rolelogin_time, amount, val, consume_sum, own_after, first_payment_time, last_payment_time, last_login_time, first_pay_level, level, vip_level, first_pay_amount, mission_id)"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1300 (HY000) at line 1: Invalid utf8 character string: ''

报错
看文件也没看出啥, 我就好奇了, 怎么在5.5主库没问题,在这就有问题呢, 于是我把文件scp到主库, 在test库建同一个表,继续手动Load data, 没报错 导入成功了..

一开始我怀疑是SQL_MODE ,查了下两边都是SQL_MODE='' (我就不贴出来证明了)
我又怀疑字符集问题, 查了下两边也都一样..

这下尴尬了, 我比较low,没想到啥好方法, 只能人肉折半查找法, 把这个文件/data/mysqldata/3306/tmp/SQL_LOAD-a3f4b929-31a0-11e8-9714-f8bc123346cc-11-12025.data 先删一半,LOAD看报错不, 报错说明有问题的数据就在这半部分,没报错说明在删除的那半部分, 如此往复,最后终于找到了,请看!

#less /tmp/SQL_LOAD-a3f4b929-31a0-11e8-9714-f8bc123346cc-11-12025.data.final 
2001_74148261,1000032,3JM30C2EN,<U+1F602><U+1F602><U+1F602>,,124.152.204.12,2001.0,1510191299.0,,,29216.0,58.0,,,1520993335.0,,0.0,0.0,,

就这一行,5.5能导进去,5.7不行
你们猜<U+1F602>是啥? 是这个表情������,卧了个槽

看下建标语句

root@localhost 10:42:  [test]> show create table 520054_all_role\G
*************************** 1. row ***************************
       Table: 520054_all_role
Create Table: CREATE TABLE `520054_all_role` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `openid` varchar(100) NOT NULL COMMENT '用户平台账号',
  `clientid` int(11) NOT NULL COMMENT '区服ID',
  `roleid` varchar(64) NOT NULL COMMENT '用户区服唯一账号',
  `rolename` varchar(100) DEFAULT NULL,
  `school` varchar(64) DEFAULT NULL COMMENT '角色门派',
  `IP` varchar(64) DEFAULT NULL,
  `snid` int(11) NOT NULL COMMENT '平台ID',
  `rolelogin_time` int(11) NOT NULL COMMENT '建角时间',
  `amount` int(11) NOT NULL DEFAULT '0' COMMENT '金额',
  `val` int(11) NOT NULL DEFAULT '0' COMMENT '购买的游戏币(元宝 、钻石)数量',
  `consume_sum` int(11) NOT NULL DEFAULT '0' COMMENT '消耗钻石数',
  `own_after` int(11) NOT NULL DEFAULT '0' COMMENT '消耗后剩余钻石数',
  `first_payment_time` int(11) DEFAULT NULL COMMENT '最初支付时间戳',
  `last_payment_time` int(11) DEFAULT NULL COMMENT '最后支付时间戳',
  `last_login_time` int(11) DEFAULT NULL COMMENT '最后活跃时间戳',
  `first_pay_level` smallint(6) NOT NULL DEFAULT '0' COMMENT '首次付费等级',
  `level` smallint(6) NOT NULL DEFAULT '0' COMMENT '当前等级',
  `vip_level` smallint(6) NOT NULL DEFAULT '0' COMMENT '当前vip等级',
  `first_pay_amount` int(11) NOT NULL DEFAULT '0' COMMENT '最初付费金额',
  `mission_id` varchar(50) DEFAULT NULL COMMENT '最高关卡ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `openid_clientid` (`openid`,`clientid`),
  KEY `amount_snid` (`amount`,`snid`)
) ENGINE=InnoDB AUTO_INCREMENT=32227 DEFAULT CHARSET=utf8 COMMENT='所有角色表'
1 row in set (0.00 sec)

DEFAULT CHARSET=utf8 5.5能把这个emoji表情导进去(数据有没有问题另说),5.7不行

最后我再5.7把这个表建成utf8mb4,然后重新导入

#mysql --default-character-set=utf8mb4 --socket=/data/mysqldata/3306/mysql.sock -uroot -p"" --show-warnings fan -e "LOAD DATA local INFILE '/tmp/SQL_LOAD-a3f4b929-31a0-11e8-9714-f8bc123346cc-11-12025.data' IGNORE INTO  TABLE 520054_all_role CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' OPTIONALLY  ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\n' (openid, clientid, roleid, rolename, school, IP, snid, rolelogin_time, amount, val, consume_sum, own_after, first_payment_time, last_payment_time, last_login_time, first_pay_level, level, vip_level, first_pay_amount, mission_id)"
mysql: [Warning] Using a password on the command line interface can be insecure.

终于不报错了

这个案例告诉我们,升级前一定先搞个从库跑一段时间再说

阅读更多

没有更多推荐了,返回首页