load data导致主键丢失的神秘问题

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.txttest1,test1test2,test2test3,test3test4,test4test5,test5test6,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: 0mysql> 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 = 271COMMIT/*!*/;...### 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 = 272COMMIT/*!*/;

可以明显看出来实际插入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: 0mysql> 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 0F00000000: 74 65 73 74 31 2C 74 65 73 74 31 0D 0A 74 65 73    test1,test1..tes00000010: 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,tes00000030: 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: 0mysql> 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 = 385COMMIT/*!*/;...

插入的数值正确。

6、总结

整个问题是由于linux系统和windows系统的换行符不一致导致的。再导入时候需要思考不同系统之间的文件是否存在处理差异，避免导致不可预知的后果。

[参考文档]

1. String Literals(https://dev.mysql.com/doc/refman/8.0/en/string-literals.html)
2. LOAD DATA Statement(https://dev.mysql.com/doc/refman/8.0/en/load-data.html)

Enjoy GreatSQL 🙂