关于mysql:技术分享-load-data导致主键丢失的神秘问题

53次阅读

共计 8127 个字符，预计需要花费 21 分钟才能阅读完成。

欢送来到 GreatSQL 社区分享的 MySQL 技术文章，如有疑难或想学习的内容，能够在下方评论区留言，看到后会进行解答

  • GreatSQL 社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。
  • 1、发现问题
  • 2、复现问题
  • 3、查看导入文件
  • 4、问题起因
  • 5、解决问题
  • 6、总结

1、发现问题

在一次数据迁徙的工作中，小玲将源端数据库中数据导出为 CSV 文件，而后通过 load data 导入数据到 MySQL，后果惊奇地发现 id 字段失落了，就像这个样子：

mysql> select * from t2;
+----+-------+---------------------+
| id | col1  | col2                |
+----+-------+---------------------+
                 |
            || TfdESTA
              |TESTA
          |4 | TEfdfdSTA
 | 5 | TEST5 | TESfddfdsfdsfdsfTA
      |TEST6 | TESffdfdfddTA
+----+-------+---------------------+
6 rows in set (0.00 sec)

指标数据库版本与表构造如下：

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.25    |
+-----------+
1 row in set (0.00 sec)


mysql> show create table t2;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `col1` varchar(69) DEFAULT NULL,
  `col2` varchar(79) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------

小玲沉着一下之后，通过以下语句验证了主键 id 并没有真的失落，仿佛只是呈现了某种显示谬误：

mysql> select * from t2 where id=1;
+----+-------+------+
| id | col1  | col2 |
+----+-------+------+
  |1 | TEST1 | TA
+----+-------+------+
1 row in set (0.00 sec)

于是小玲决定排查整个操作流程，搞清楚问题的起因。

2、复现问题

首先创立一个表 t1

mysql> CREATE TABLE `t1` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `col1` varchar(60)  DEFAULT NULL,
    ->   `col2` varchar(70) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.01 sec)

在 windows 环境下，通过记事本筹备一个新的测试文件 t1.txt，在 linux 环境下查看 t1.txt 内容如下：

great@great-PC:~/Downloads/windows$ cat t1.txt
test1,test1
test2,test2
test3,test3
test4,test4
test5,test5
test6,test6

进行 load，并查看数据。发现并没有错乱。

mysql> load data infile "/home/great/Downloads/windows/t1.txt" \
       into table t1 FIELDS TERMINATED BY ',' (col1,col2);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t1;
+----+-------+--------+
| id | col1  | col2   |
+----+-------+--------+
 | 1 | test1 | test1
 | 2 | test2 | test2
 | 3 | test3 | test3
 | 4 | test4 | test4
 | 5 | test5 | test5
 | 6 | test6 | test6
+----+-------+--------+
6 rows in set (0.00 sec)

然而有点奇怪的就是右侧的边线不见了，手动插入一个数据，再查问看下数据状况。

mysql> insert into t1(col1,col2) values('test7','test7');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+-------+--------+
| id | col1  | col2   |
+----+-------+--------+
 | 1 | test1 | test1
 | 2 | test2 | test2
 | 3 | test3 | test3
 | 4 | test4 | test4
 | 5 | test5 | test5
 | 6 | test6 | test6
|  7 | test7 | test7  |
+----+-------+--------+
7 rows in set (0.00 sec)

mysql> select * from t1 where id=7;
+----+-------+-------+
| id | col1  | col2  |
+----+-------+-------+
|  7 | test7 | test7 |
+----+-------+-------+
1 row in set (0.00 sec)

解析一下 binlog 日志 

...

### INSERT INTO `test`.`t1`
### SET
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2='test6' /* VARSTRING(240) meta=240 nullable=1 is_null=0 */
###   @3='test6\r' /* VARSTRING(280) meta=280 nullable=1 is_null=0 */
# at 1223
#210923 11:19:09 server id 12345  end_log_pos 1250      Xid = 271
COMMIT/*!*/;

...

### INSERT INTO `test`.`t1`
### SET
###   @1=7 /* INT meta=0 nullable=0 is_null=0 */
###   @2='test7' /* VARSTRING(240) meta=240 nullable=1 is_null=0 */
###   @3='test7' /* VARSTRING(280) meta=280 nullable=1 is_null=0 */
# at 1500
#210923 11:19:15 server id 12345  end_log_pos 1527      Xid = 272
COMMIT/*!*/;

能够显著看进去理论插入 txt 的数据 test6 的时候并不是 test6, 而是 test\r。而咱们手动插入的 test7，则的确为 test7。

再查看下理论插入的数据的 16 进制值。

mysql> select id,hex(col2) from t1;
+----+--------------+
| id | hex(col2)    |
+----+--------------+
|  1 | 74657374310D |
|  2 | 74657374320D |
|  3 | 74657374330D |
|  4 | 74657374340D |
|  5 | 74657374350D |
|  6 | 74657374360D |
|  8 | 7465737437   |
+----+--------------+
7 rows in set (0.00 sec)

load 进来的数据前面比手动插入的失常数据多了 0D。

mysql> select  hex('\r') from t1;
+-----------+
| hex('\r') |
+-----------+
| 0D        |
| 0D        |
| 0D        |
| 0D        |
| 0D        |
| 0D        |
| 0D        |
+-----------+
7 rows in set (0.00 sec)


mysql> select  unhex('0D') from t1;
+-------------+
| unhex('0D') |
+-------------+
           |
           |
           |
           |
           |
           |
           |
+-------------+
7 rows in set (0.00 sec)

到这里基本上就很显著了，是因为 txt 文件中的符号导致的。而不同于结尾查问的结果显示那么不标准的起因，是因为 col2 的 length 都是 5。导入 length 不同的数据，就能够显著看出差别。

mysql> load data infile "/home/great/Downloads/windows/oracle_objects.txt" \
       into table t1 FIELDS TERMINATED BY ',' (col1,col2);
Query OK, 2088 rows affected (0.02 sec)
Records: 2088  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select * from t1   limit 30  ;
+----+-----------------------------+------------+
| id | col1                        | col2       |
+----+-----------------------------+------------+
     | test1                       | test1
     | test2                       | test2
     | test3                       | test3
     | test4                       | test4
     | test5                       | test5
     | test6                       | test6
|  8 | test7                       | test7      |
     | A                           | TABLE
     | A1                          | TABLE
     | AAA                         | TABLE
     | ABC123                      | TABLE
     | ABCDEF                      | TABLE
     | ACTIVE_ALARMS               | TABLE
 |15 | ADDAUTH                     | PROCEDURE
 |16 | ADDROLEAUTH                 | PROCEDURE
     | AGENT_AVAIL_PRIV            | TABLE
     | AGE_STAT_FORBIDTIMELOGIN    | TABLE
     | AGE_STAT_NOTFORBIDTIMELOGIN | TABLE
     | AGE_STAT_QUERYLARGE         | TABLE
     | APP_SCHEDULE_INFO           | TABLE
 |22 | AP_CLEARMUTEXROLL           | PROCEDURE
 |23 | AP_CLEARROLEBYSTSNUM        | PROCEDURE
 |24 | AP_CLEARROLEBYTEL           | PROCEDURE
 |25 | AP_CLEARSUPERROLL           | PROCEDURE
 |26 | AP_CREATE_RDDLV_FILE        | PROCEDURE
 |27 | AP_DEALACTLOG_OPERIP        | PROCEDURE
 |28 | AP_LOC_CRMBI_MSGSEND_LOG    | PROCEDURE
 |29 | AP_MENDCLICK                | PROCEDURE
 |30 | AP_PUB_UNLOCK_VERIFYCODE    | PROCEDURE
 |31 | AP_SETDBUSERANDPASS         | PROCEDURE
+----+-----------------------------+------------+
30 rows in set (0.00 sec)

3、查看导入文件

确认是导入的文件问题，咱们查看下这个文件的 16 进制的状况。

这里应用的是 vscode 插件 hexdump，后果如下 

  Offset: 00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F
00000000: 74 65 73 74 31 2C 74 65 73 74 31 0D 0A 74 65 73    test1,test1..tes
00000010: 74 32 2C 74 65 73 74 32 0D 0A 74 65 73 74 33 2C    t2,test2..test3,
00000020: 74 65 73 74 33 0D 0A 74 65 73 74 34 2C 74 65 73    test3..test4,tes
00000030: 74 34 0D 0A 74 65 73 74 35 2C 74 65 73 74 35 0D    t4..test5,test5.
00000040: 0A 74 65 73 74 36 2C 74 65 73 74 36 0D 0A          .test6,test6..

察看能够看到除了 0D 还有 0A

mysql> select  hex('\n') ;
+-----------+
| hex('\n') |
+-----------+
| 0A        |
+-----------+
1 row in set (0.00 sec)

能够得悉，导入的 txt 文件中，每一行的开端是 \r\n 作为换行的。

在不同的零碎中，对于换行符有着不同的示意形式。

以下来自维基百科

应用软件以及操作系统对于换行字符的示意形式：

以 ASCII 为根底的或兼容的字符集应用别离 LF（Line feed，U+000A）或 CR（Carriage Return，U>+000D）或 CR+LF；上面列出各零碎换行字符编码的列表

LF：在 Unix 或 Unix 兼容零碎（GNU/Linux，AIX，Xenix，Mac OS X，…）、BeOS、Amiga、RISC OS

CR+LF：DOS（MS-DOS、PC-DOS 等）、微软视窗操作系统（Microsoft Windows）、大部分非 Unix 的零碎

CR：Apple II 家族，Mac OS 至版本 9

4、问题起因

由上可知，是因为 txt 文本中，每一行的开端应用 \r\n 作为换行，而 linux 零碎应用 \n 作为换行，因而 \r 作为一个字符被插入到表中。

\r 在 mysql 中被解决为 A carriage return character，因而会呈现文章头的状况，查问后果不标准。

5、解决问题

在应用 load_data 导入数据的时候，能够应用 lines terminated by ‘\r\n’ 来通知 mysql，\r\n 是整个作为换行符来应用的。

这样来从新 load 一下。

mysql> show master status;
+---------------+----------+--------------+------------------+---------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+---------------+----------+--------------+------------------+---------------------------------------------+
| binlog.000013 |      192 |              |                  | b5457ec7-f50a-11eb-ac22-2cf05daaf63e:1-6182 |
+---------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> load data infile "/home/great/Downloads/windows/t1.txt" \
       into table t1 fields terminated by ','lines terminated by '\r\n'(col1,col2);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t1;
+----+-------+-------+
| id | col1  | col2  |
+----+-------+-------+
|  1 | test1 | test1 |
|  2 | test2 | test2 |
|  3 | test3 | test3 |
|  4 | test4 | test4 |
|  5 | test5 | test5 |
|  6 | test6 | test6 |
+----+-------+-------+
6 rows in set (0.00 sec)

解析下二进制日志看下理论插入的状况 

...
### INSERT INTO `test`.`t1`
### SET
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2='test6' /* VARSTRING(240) meta=240 nullable=1 is_null=0 */
###   @3='test6' /* VARSTRING(280) meta=280 nullable=1 is_null=0 */
# at 532
#210923 14:39:18 server id 12345  end_log_pos 559       Xid = 385
COMMIT/*!*/;
...

插入的数值正确。

6、总结

整个问题是因为 linux 零碎和 windows 零碎的换行符不统一导致的。再导入时候须要思考不同零碎之间的文件是否存在解决差别，防止导致不可预知的结果。

[参考文档]

1.String Literals(https://dev.mysql.com/doc/ref…)

2.LOAD DATA Statement(https://dev.mysql.com/doc/ref…)

Enjoy GreatSQL :)

文章举荐：

技术分享 | MGR 最佳实际 (MGR Best Practice)
https://mp.weixin.qq.com/s/66…

技术分享 | 万里数据库 MGR Bug 修复之路
https://mp.weixin.qq.com/s/Ia…

Macos 零碎编译 percona 及局部函数在 Macos 零碎上运算差别
https://mp.weixin.qq.com/s/jA…

技术分享 | 利用 systemd 治理 MySQL 单机多实例
https://mp.weixin.qq.com/s/iJ…

产品 | GreatSQL，打造更好的 MGR 生态
https://mp.weixin.qq.com/s/By…

产品 | GreatSQL MGR 优化参考
https://mp.weixin.qq.com/s/5m…

对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

Gitee:
https://gitee.com/GreatSQL/Gr…

GitHub:
https://github.com/GreatSQL/G…

微信 &QQ 群：

可搜寻增加 GreatSQL 社区助手微信好友，发送验证信息“加群”退出 GreatSQL/MGR 交换微信群

QQ 群：533341697
微信小助手：wanlidbc

本文由博客一文多发平台 OpenWrite 公布！

正文完
mysql
发表至： mysql
2021-12-15
 0
关于mysql:基于DataX的数据同步上DataX介绍以及安装
关于mysql:想进阿里必须啃透的12道MySQL面试题
关于mysql:MySQL性能与调优
关于mysql:mysql判断json数组是否存在交集