| Bug #101315 | There is a serious problem of data export in MySQL 8.0.22 | ||
|---|---|---|---|
| Submitted: | 26 Oct 2020 7:52 | Modified: | 28 Oct 2020 11:06 |
| Reporter: | w sq | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Command-line Clients | Severity: | S3 (Non-critical) |
| Version: | 8.0.22 | OS: | CentOS (6.10) |
| Assigned to: | CPU Architecture: | Any | |
[26 Oct 2020 8:15]
w sq
At the same time, it causes errors when importing data. Because the added "\" is not processed during "load data infile", the following errors will not be caused if the processing is done: 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
[26 Oct 2020 8:19]
w sq
Since the exported files are usually large files, they must be cut later. Because the branches on the data will cause the general cutting software to be unable to recognize, it is recommended that the "\r\n" display storage should not be used to wrap the data.
[26 Oct 2020 8:27]
w sq
The previous version is wrong. It should be exported and imported from 8.0.22
[26 Oct 2020 12:45]
MySQL Verification Team
Hi Mr. sq, Thank you for your bug report. However, we need to try to repeat the problem that you are experiencing. Hence, we would require the output from the SHOW CREATE TABLE for that particular table. Also, in that output, only first column is problematic, while that VARCHAR columns is quite OK , since \r and \n represent carriage return and new line , respectively. Regarding the first column, you have probably defined it too small for the value that you have inserted. In that case, this is not a bug.
[26 Oct 2020 14:11]
w sq
It should not be the case you said. If I do not have the '\ R / N' field, it will be fine if it is the same size or even larger. It can also be said that if it is exported from the database, there is no problem that the value you said is too small. Because it is only exported, it is like that in the exported file, it does not mean that it cannot be imported. Of the following three data, the middle one is normal data: 应该不是您说的那种情况，由于我没有出现'\r\n'字段的情况下是同样大小或者说甚至更大的情况下都没有问题，也可以这样说，即然是从数据库中导出的，就不存在您说的值太小的问题，由于只是导出，在导出的文件中是那样的，并不是说导入不了。 下面三条数据中，中间的一条是正常的数据： 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 CREATE TABLE `T_CHECK_RECORD` ( `CHECK_RECORD_ID` bigint NOT NULL AUTO_INCREMENT COMMENT '巡检记录ID', `STAMP` datetime NOT NULL COMMENT '巡检日期(时间)', `USER_ID` bigint NOT NULL COMMENT '巡检人员ID', `CHECK_POINTS_ID` bigint NOT NULL COMMENT '检查点ID', `IS_NORMAL` tinyint NOT NULL COMMENT '是否正常时间巡检(0-否;1-是)', `IS_LATENT_DANGER` tinyint NOT NULL COMMENT '是否有隐患(0-否;1-是)', `LATENT_DANGER_TYPE` int NOT NULL COMMENT '检查结果', `LATENT_DANGER_DESC` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '结果描述', `UNDETECTED_NUM` int NOT NULL DEFAULT '0' COMMENT '漏检次数', `LON` decimal(28,10) DEFAULT NULL COMMENT '经度', `LAT` decimal(28,10) DEFAULT NULL COMMENT '纬度', `RULE_ID` bigint DEFAULT '0' COMMENT '检查记录对应的规则id', `IS_DEAL` tinyint DEFAULT '0' COMMENT '隐患记录是否被处理', `STARTDATE` datetime DEFAULT NULL COMMENT '检查所属区间开始时间', `ENDDATE` datetime DEFAULT NULL COMMENT '检查所属区间结束时间', `VALID_CHECK` tinyint DEFAULT '0' COMMENT '是否有效检查(有效时间内第一人)', `UPDATESTAMP` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后更新时间', `AGENCY_ID` bigint DEFAULT NULL, PRIMARY KEY (`CHECK_RECORD_ID`,`STAMP`) USING BTREE, KEY `IDX_CHECK_RECORD2` (`USER_ID`,`STAMP`) USING BTREE, KEY `IDX_CHECK_RECORD_DATE` (`CHECK_POINTS_ID`,`RULE_ID`,`STARTDATE`) USING BTREE, KEY `IDX_CHECK_RECORD_POINT_STAMP` (`CHECK_POINTS_ID`,`RULE_ID`,`STAMP`,`VALID_CHECK`,`IS_LATENT_DANGER`) USING BTREE, KEY `IDX_CHECK_RECORD` (`AGENCY_ID`,`IS_NORMAL`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=42561359 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT COMMENT='巡检记录' /*!50100 PARTITION BY RANGE (to_days(`STAMP`)) (PARTITION p201710 VALUES LESS THAN (736999) ENGINE = InnoDB, PARTITION p201711 VALUES LESS THAN (737029) ENGINE = InnoDB, PARTITION p201712 VALUES LESS THAN (737060) ENGINE = InnoDB, PARTITION p201801 VALUES LESS THAN (737091) ENGINE = InnoDB, PARTITION p201802 VALUES LESS THAN (737119) ENGINE = InnoDB, PARTITION p201803 VALUES LESS THAN (737150) ENGINE = InnoDB, PARTITION p201804 VALUES LESS THAN (737180) ENGINE = InnoDB, PARTITION p201805 VALUES LESS THAN (737211) ENGINE = InnoDB, PARTITION p201806 VALUES LESS THAN (737241) ENGINE = InnoDB, PARTITION p201807 VALUES LESS THAN (737272) ENGINE = InnoDB, PARTITION p201808 VALUES LESS THAN (737303) ENGINE = InnoDB, PARTITION p201809 VALUES LESS THAN (737333) ENGINE = InnoDB, PARTITION p201810 VALUES LESS THAN (737364) ENGINE = InnoDB, PARTITION p201811 VALUES LESS THAN (737394) ENGINE = InnoDB, PARTITION p201812 VALUES LESS THAN (737425) ENGINE = InnoDB, PARTITION p201901 VALUES LESS THAN (737456) ENGINE = InnoDB, PARTITION p201902 VALUES LESS THAN (737484) ENGINE = InnoDB, PARTITION p201903 VALUES LESS THAN (737515) ENGINE = InnoDB, PARTITION p201904 VALUES LESS THAN (737545) ENGINE = InnoDB, PARTITION p201905 VALUES LESS THAN (737576) ENGINE = InnoDB, PARTITION p201906 VALUES LESS THAN (737606) ENGINE = InnoDB, PARTITION p201907 VALUES LESS THAN (737637) ENGINE = InnoDB, PARTITION p201908 VALUES LESS THAN (737668) ENGINE = InnoDB, PARTITION p201909 VALUES LESS THAN (737698) ENGINE = InnoDB, PARTITION p201910 VALUES LESS THAN (737729) ENGINE = InnoDB, PARTITION p201911 VALUES LESS THAN (737759) ENGINE = InnoDB, PARTITION p201912 VALUES LESS THAN (737790) ENGINE = InnoDB, PARTITION p202001 VALUES LESS THAN (737821) ENGINE = InnoDB, PARTITION p202002 VALUES LESS THAN (737850) ENGINE = InnoDB, PARTITION p202003 VALUES LESS THAN (737881) ENGINE = InnoDB, PARTITION p202004 VALUES LESS THAN (737911) ENGINE = InnoDB, PARTITION p202005 VALUES LESS THAN (737942) ENGINE = InnoDB, PARTITION p202006 VALUES LESS THAN (737972) ENGINE = InnoDB, PARTITION p202007 VALUES LESS THAN (738003) ENGINE = InnoDB, PARTITION p202008 VALUES LESS THAN (738034) ENGINE = InnoDB, PARTITION p202009 VALUES LESS THAN (738064) ENGINE = InnoDB, PARTITION p202010 VALUES LESS THAN (738095) ENGINE = InnoDB, PARTITION p202011 VALUES LESS THAN (738125) ENGINE = InnoDB, PARTITION p202012 VALUES LESS THAN (738156) ENGINE = InnoDB) */;
[26 Oct 2020 14:16]
w sq
Among the following three data, the middle one is abnormal data: 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
[26 Oct 2020 14:20]
w sq
I would like to take this opportunity to express my great appreciation for your timely help and assistance.
[26 Oct 2020 14:24]
w sq
You can generate the corresponding table according to the table statement I gave, and then export and import to understand the problem.
[27 Oct 2020 14:24]
MySQL Verification Team
Hi Mr. sq, Can we have the exact INSERT command that produced this output. This is only in regard of the INT problem. Regarding the other issue, you can always escape the attributes.
[28 Oct 2020 6:32]
w sq
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
[28 Oct 2020 9:28]
MySQL Verification Team
Hi, So you agree this is not a bug. > Do you have this function or have you considered the method of separating small files by line or size? No, separating files is really not something RDBMS should worry about. Kind regards Bogdan
[28 Oct 2020 11:06]
w sq
Well, this is not a bug belonging to MySQL 8.0.22, I'm very sorry!!! Thank you for your reply and support!!!
Description: The exported data was replaced without reason How to repeat: The exported data was replaced without reason: Raw data stored in the data table: 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); Export using the following statement: #!/bin/bash MySQL MySQL - H localhost - p3306 - uroot - p123456 Use ${1}; ා set database name parameter 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; The result of the generated export becomes: \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 We can clearly see that "32261535" has been changed to "\2261535". The reason may be that the data contains line breaks. In order to identify that this piece of data is newline, we added "\" at the beginning. This is understandable, but why did I change my data??? Due to the use of this kind of export is generally relatively large files, the back must be cut. Suggestions: 1. Don't change the user's data 2. Do you want to consider generating a file according to the specified line or size when exporting, so as to avoid the need for users to use other tools to split and then import 3. If you don't consider specifying to generate files by line or size, you can use one record for one line. If there is data such as "\r\n" in the customer's data, directly use the storage displayed in "\r\n". Do not cause line breaks in the generated file, but generate errors when using other tools to split the file. 4. "Set global require" is required when importing data_ secure_ Transport = off; ", which will cause Mysql to no longer be accessible by the client, which is very problematic for online databases. Can you not take this measure when exporting or only affect the operation table, instead of making the whole database inaccessible?