MySQL 8.0.22 存在的一个数据导出的严重问题

MySQL 8.0.22 存在的一个数据导出的严重问题

 

报告给MySQL官方的报告：https://bugs.mysql.com/bug.php?id=101315

 

导出数据被无故替换：

数据表中存储的原始数据：
INSERT INTO fmmp.T_CHECK_RECORD(CHECK_RECORD_ID, STAMP, USER_ID, CHECK_POINTS_ID, IS_NORMAL, IS_LATENT_DANGER, LATENT_DANGER_TYPE, LATENT_DANGER_DESC, UNDETECTED_NUM, LON, LAT, RULE_ID, IS_DEAL, STARTDATE, ENDDATE, VALID_CHECK, UPDATESTAMP, AGENCY_ID) VALUES
(32261535, '2020-06-03 02:03:50', 109836, 168630, 1, 0, 8, '五楼行李房\r\n\r\n,合格,美爵酒店', 0, NULL, NULL, 456, 0, NULL, NULL, 1, '2020-07-21 20:41:34', 232243);

使用以下语句导出：

#!/bin/bash
mysql mysql -h localhost -P3306 -uroot -p123456 <<!  #连接数据库
    USE ${1};   #设置数据库名参数
    select * from T_CHECK_RECORD into outfile '/var/lib/mysql-files/T_CHECK_RECORD.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
!
exit;

生成的导出的结果变成了：
\2261535,"2020-06-03 02:03:50",109836,168630,1,0,8,"五楼行李房
\
,合格,美爵酒店",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:41:34",232243

可以明显的看到“32261535”被改成了“\2261535”，造成的原因可能是由于数据中包含了换行，而为了标识这一条数据是换行的，所以在开头加了“\”，这可以理解，但是为什么把我的数据改掉了呢？？？

同时它造成了数据导入的时候出错，由于在“LOAD DATA INFILE”的时候并没有对添加的这个“\”做处理，如果做了处理的话不会造成如下错误：

mysql -h localhost -uroot -p123456 -P3306 fmmp <<!
 LOAD DATA INFILE '/var/lib/mysql-files/T_CHECK_RECORD_SPLIT33' INTO TABLE T_CHECK_RECORD FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
!
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1366 (HY000) at line 1: Incorrect integer value: '' for column 'CHECK_RECORD_ID' at row 1

由于使用这种导出的一般都是比较大的文件，后面肯定是需要切割的，由于数据上的分行会造成一般的切割软件无法识别，所以建议对于使用“\r\n”显示存储，不行使数据换行。

建议：
1、不要改变用户的数据呀
2、是否考虑在导出的时候就按指定的行或大小生成文件呀，这样就免去了使用者需要借助其它工具去分割才能再导入
3、如果不考虑指定按行或大小生成文件的话，就按一条记录一行呀，如果客户的数据中有“\r\n”这样的数据，直接使用“\r\n”显示的存储，不要造成在生成的文件中换行，而在使用其它工具分割文件时产生错误。
4、在导入数据时，需要“set GLOBAL require_secure_transport=OFF;”，这会造成mysql无法再使用客户端访问，这对于在线数据库来说非常有问题，能否在导出的时候不采取这个措施或者仅对操作的表影响，而不要造成整个数据库无法访问？

----------------------------------------------------------------------

后来经过与官方MySQL Verification Team沟通，反复验证，最终发现是cat的显示问题：

After verification, the following statements are used to export:
mysql mysql -h localhost -P3306 -uroot -p123456
Use fmmp;
select * into outfile '/var/lib/mysql-files/T_ CHECK_ RECORD_ 05.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' from T_ CHECK_ RECORD WHERE CHECK_ RECORD_ ID IN (32341775,32341776,32341777,32341778);
!

use:
cat /var/lib/mysql-files/T_ CHECK_ RECORD_ 05.txt
It is shown as follows:
32341775,"2020-06-15 08:42:46",109925,168961,1,0,8,"负一层3号变压器房,合格,李惠生",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:50:35",232264
32341776,"2020-06-15 11:24:54",109926,169028,1,0,8,"4楼2号走火梯,合格,李志广",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:50:35",232264
\2341777,"2020-06-15 09:03:17",109933,169036,1,0,8,"1楼2号走火梯
,合格,雷洪波",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:50:35",232264
32341778,"2020-06-15 11:12:17",109926,168992,1,0,8,"18楼2号走火梯,合格,李志广",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:50:35",232264

use:
vim /var/lib/mysql-files/T_ CHECK_ RECORD_ 05.txt
It is shown as follows:
32341775,"2020-06-15 08:42:46",109925,168961,1,0,8,"负一层3号变压器房,合格,李惠生",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:50:35",232264
32341776,"2020-06-15 11:24:54",109926,169028,1,0,8,"4楼2号走火梯,合格,李志广",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:50:35",232264
32341777,"2020-06-15 09:03:17",109933,169036,1,0,8,"1楼2号走火梯^M\
,合格,雷洪波",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:50:35",232264
32341778,"2020-06-15 11:12:17",109926,168992,1,0,8,"18楼2号走火梯,合格,李志广",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:50:35",232264

Import using the following statement:
mysql -h localhost -uroot -p123456 -P3306 fmmp <<!
USE fmmp;
DELETE FROM T_ CHECK_ RECORD_ 01;
LOAD DATA INFILE '/var/lib/mysql-files/T_ CHECK_ RECORD_ 05.txt' IGNORE INTO TABLE T_ CHECK_ RECORD_ 01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
!

Export using the following statement:
mysql mysql -h localhost -P3306 -uroot -p123456
Use fmmp;
select * into outfile '/var/lib/mysql-files/T_ CHECK_ RECORD_ 06.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' from T_ CHECK_ RECORD_ 01;
!

use:
cat /var/lib/mysql-files/T_ CHECK_ RECORD_ 06.txt
result:
32341775,"2020-06-15 08:42:46",109925,168961,1,0,8,"负一层3号变压器房,合格,李惠生",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:50:35",232264
32341776,"2020-06-15 11:24:54",109926,169028,1,0,8,"4楼2号走火梯,合格,李志广",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:50:35",232264
\2341777,"2020-06-15 09:03:17",109933,169036,1,0,8,"1楼2号走火梯
,合格,雷洪波",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:50:35",232264
32341778,"2020-06-15 11:12:17",109926,168992,1,0,8,"18楼2号走火梯,合格,李志广",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:50:35",232264

use:
vim /var/lib/mysql-files/T_ CHECK_ RECORD_ 06.txt
result:
32341775,"2020-06-15 08:42:46",109925,168961,1,0,8,"负一层3号变压器房,合格,李惠生",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:50:35",232264
32341776,"2020-06-15 11:24:54",109926,169028,1,0,8,"4楼2号走火梯,合格,李志广",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:50:35",232264
32341777,"2020-06-15 09:03:17",109933,169036,1,0,8,"1楼2号走火梯^M\
,合格,雷洪波",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:50:35",232264
32341778,"2020-06-15 11:12:17",109926,168992,1,0,8,"18楼2号走火梯,合格,李志广",0,\N,\N,456,0,\N,\N,1,"2020-07-21 20:50:35",232264

After verification, it is the cat display problem, which does not affect the normal data, does not affect the import and export of data. I started to read the wrong file because the file is too large, did not use VIM to view, only used cat to view. At the same time, when I verify the import, I directly use the results of cat to verify, so the prompt data is incorrect.

I'm sorry for the trouble I brought to your work, and thank you for your support and help!

Do you have this function or have you considered the method of separating small files by line or size?
For example, use the split command.

split -a 6 -d -l 100000 T_ALARM_LAST_1.txt T_ALARM_LAST_SPLIT