发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

5.7 与 8.0 对相同文件的 LOAD DATA 语句结果不同

恩施金华

8

主题

8

帖子

24

积分

新手上路

Rank: 1

积分
24
显示全部楼层
5.7 与 8.0 对相同文件的 LOAD DATA 语句结果不同

问题描述

某客户现场支持，由MySQL 5.7.21升级MySQL 8.0.25后，通过LOAD DATA导入文件，当同一会话连续导入不同的编码（UTF8/GB18030）文件时会出现乱码。数据库版本未升级之前，相同的导入操作在MySQL 5.7.21未出现乱码。
问题分析

1）查看简化后的 LOAD DATA语句
  1. greatsql> LOAD DATA LOCAL INFILE '/home/greatdb/TEST_UTF8_bak.txt' IGNORE INTO TABLE ASSP_SIS_PAYRES_IMP_BAK CHARACTER SET UTF8MB4 IGNORE 0 LINES  (@row) SET `D_NAME` = NULLIF(TRIM(CONVERT(UNHEX(SUBSTR(HEX(@row), 1,240)) USING UTF8MB4)),'');
  2. Query OK, 2 rows affected (0.01 sec)
  3. Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
  5. greatsql> LOAD DATA LOCAL INFILE '/home/greatdb/TEST_GB18030_bak.txt' IGNORE INTO TABLE ASSP_SIS_PAYRES_IMP_BAK CHARACTER SET GB18030 IGNORE 0 LINES  (@row) SET `D_NAME` = NULLIF(TRIM(CONVERT(UNHEX(SUBSTR(HEX(@row), 1,240)) USING GB18030)),'');
  6. Query OK, 2 rows affected (0.01 sec)
  7. Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
复制代码
2）查看表数据
  1. +----------+------------------------------------------------------+
  2. | AUTO_INC | D_NAME                                               |
  3. +----------+------------------------------------------------------+
  4. |        1 | xxx社会保险xxx                                        |
  5. |        2 | xxx市路桥区xxx                                        |
  6. |        4 | 鍙板窞甯傝矾妗ュ尯绀句細淇濋櫓浜嬩笟绠＄悊涓績             |
  7. |        5 | 鍙板窞甯傝矾妗ュ尯绀句細淇濋櫓浜嬩笟绠＄悊涓績             |
  8. +----------+------------------------------------------------------+
  9. 4 rows in set (0.00 sec)
复制代码
3）检查业务表的字符集与校验集，发现字符集为 utf8mb4 、校验集为 utf8mb4_bin
4）检查数据库的字符集与校验集
  1. greatsql> SHOW GLOBAL VARIABLES LIKE '%char%';
  2. +--------------------------------------+--------------------------------+
  3. | Variable_name                        | Value                          |
  4. +--------------------------------------+--------------------------------+
  5. | character_set_client                 | utf8mb4                        |
  6. | character_set_connection             | utf8mb4                        |
  7. | character_set_database               | utf8mb4                        |
  8. | character_set_filesystem             | binary                         |
  9. | character_set_results                | utf8mb4                        |
  10. | character_set_server                 | utf8mb4                        |
  11. | character_set_system                 | utf8mb3                        |
  12. | character_sets_dir                   | /opt/mysql3301/share/charsets/ |
  13. | validate_password_special_char_count | 1                              |
  14. +--------------------------------------+--------------------------------+
  15. 9 rows in set (0.01 sec)
  17. greatsql> SHOW GLOBAL VARIABLES LIKE '%coll%';
  18. +-------------------------------+--------------------+
  19. | Variable_name                 | Value              |
  20. +-------------------------------+--------------------+
  21. | collation_connection          | utf8mb4_bin        |
  22. | collation_database            | utf8mb4_bin        |
  23. | collation_server              | utf8mb4_bin        |
  24. | default_collation_for_utf8mb4 | utf8mb4_general_ci |
  25. +-------------------------------+--------------------+
  26. 4 rows in set (0.00 sec)
复制代码
程序在MySQL 5.7.21跑了很长时间，一直没有问题，把数据库升级MySQL 8.0.25后，新导入的数据出现部分乱码， 由此怀疑，MySQL 8.0定长数据导入LOAD DATA @row 出现BUG。
BUG场景：同一个会话 LOAD DATA多种字符集文件，使用@临时变量切割字段。将导致导入数据乱码，向MySQL官方提BUG,已证实为BUG（编号115824）
问题复现

MySQL: 8.0.25
  1. greatsql> SELECT VERSION();+-----------+| version() |+-----------+| 8.0.25    |+-----------+1 row in set (0.00 sec)table ddl:CREATE TABLE `assp_sis_payres_imp_bak` (  `AUTO_INC` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增列',  `D_NAME` varchar(210) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,  PRIMARY KEY (`AUTO_INC`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;greatsql> SHOW GLOBAL VARIABLES LIKE '%char%';
  2. +--------------------------------------+--------------------------------+
  3. | Variable_name                        | Value                          |
  4. +--------------------------------------+--------------------------------+
  5. | character_set_client                 | utf8mb4                        |
  6. | character_set_connection             | utf8mb4                        |
  7. | character_set_database               | utf8mb4                        |
  8. | character_set_filesystem             | binary                         |
  9. | character_set_results                | utf8mb4                        |
  10. | character_set_server                 | utf8mb4                        |
  11. | character_set_system                 | utf8mb3                        |
  12. | character_sets_dir                   | /opt/mysql3301/share/charsets/ |
  13. | validate_password_special_char_count | 1                              |
  14. +--------------------------------------+--------------------------------+
  15. 9 rows in set (0.01 sec)
  17. greatsql> SHOW GLOBAL VARIABLES LIKE '%coll%';
  18. +-------------------------------+--------------------+
  19. | Variable_name                 | Value              |
  20. +-------------------------------+--------------------+
  21. | collation_connection          | utf8mb4_bin        |
  22. | collation_database            | utf8mb4_bin        |
  23. | collation_server              | utf8mb4_bin        |
  24. | default_collation_for_utf8mb4 | utf8mb4_general_ci |
  25. +-------------------------------+--------------------+
  26. 4 rows in set (0.00 sec)greatsql> TRUNCATE TABLE assp_sis_payres_imp_bak;Query OK, 0 rows affected (0.03 sec)greatsql> SELECT charset(@row), @row;+---------------+------------+| charset(@row) | @row       |+---------------+------------+| binary        | NULL       |+---------------+------------+1 row in set (0.00 sec)greatsql> LOAD DATA LOCAL INFILE '/root/dba_zc/load/TEST_UTF8_bak.txt' IGNORE INTO TABLE ASSP_SIS_PAYRES_IMP_BAK CHARACTER SET UTF8MB4 IGNORE 0 LINES  (@row) SET `D_NAME` = NULLIF(TRIM(CONVERT(UNHEX(SUBSTR(HEX(@row), 1,240)) USING UTF8MB4)),'');Query OK, 2 rows affected (0.01 sec)Records: 2  Deleted: 0  Skipped: 0  Warnings: 0greatsql> SELECT charset(@row), @row;  +---------------+------------------------+| charset(@row) | @row                   |+---------------+------------------------+| utf8mb4       | XXX路桥区社会保XXX       |+---------------+------------------------+greatsql> LOAD DATA LOCAL INFILE '/root/dba_zc/load/TEST_GB18030_bak.txt' IGNORE INTO TABLE ASSP_SIS_PAYRES_IMP_BAK CHARACTER SET GB18030 IGNORE 0 LINES  (@row) SET `D_NAME` = NULLIF(TRIM(CONVERT(UNHEX(SUBSTR(HEX(@row), 1,240)) USING GB18030)),'');Query OK, 2 rows affected (0.01 sec)Records: 2  Deleted: 0  Skipped: 0  Warnings: 0greatsql> SELECT charset(@row), @row;  +---------------+-----------------------------------------+| charset(@row) | @row                                    |+---------------+-----------------------------------------+| gb18030       | XXX路桥区社会保XXX       |+---------------+-----------------------------------------+greatsql>  SELECT * FROM  ASSP_SIS_PAYRES_IMP_BAK;+----------+---------------------------------------------------------+| AUTO_INC | D_NAME                                                  |+----------+---------------------------------------------------------+|        1 | XXX路桥区社会保XXX                                        ||        2 | XXX路桥区社会保XXX                                        ||        4 | 鍙板窞甯傝矾妗ュ尯绀句細淇濋櫓浜嬩笟绠＄悊涓績                 ||        5 | 鍙板窞甯傝矾妗ュ尯绀句細淇濋櫓浜嬩笟绠＄悊涓績                 |+----------+---------------------------------------------------------+4 rows in set (0.00 sec)
复制代码
MySQL 5.7.21
  1. greatsql> SELECT VERSION();
  2. +------------+
  3. | version()  |
  4. +------------+
  5. | 5.7.21-log |
  6. +------------+
  7. 1 row in set (0.01 sec)
  9. table ddl:
  10. CREATE TABLE `assp_sis_payres_imp_bak` (
  11.   `AUTO_INC` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增列',
  12.   `D_NAME` varchar(210) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  13.   PRIMARY KEY (`AUTO_INC`)
  14. ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  16. greatsql>  SHOW GLOBAL VARIABLES LIKE '%char%';
  17. +--------------------------------------+--------------------------------+
  18. | Variable_name                        | Value                          |
  19. +--------------------------------------+--------------------------------+
  20. | character_set_client                 | utf8mb4                        |
  21. | character_set_connection             | utf8mb4                        |
  22. | character_set_database               | utf8mb4                        |
  23. | character_set_filesystem             | binary                         |
  24. | character_set_results                | utf8mb4                        |
  25. | character_set_server                 | utf8mb4                        |
  26. | character_set_system                 | utf8                           |
  27. | character_sets_dir                   | /opt/mysql3305/share/charsets/ |
  28. | validate_password_special_char_count | 1                              |
  29. +--------------------------------------+--------------------------------+
  30. 9 rows in set (0.00 sec)
  32. greatsql> SHOW GLOBAL VARIABLES LIKE '%coll%';
  33. +----------------------+--------------------+
  34. | Variable_name        | Value              |
  35. +----------------------+--------------------+
  36. | collation_connection | utf8mb4_general_ci |
  37. | collation_database   | utf8mb4_general_ci |
  38. | collation_server     | utf8mb4_general_ci |
  39. +----------------------+--------------------+
  40. 3 rows in set (0.00 sec)
  42. greatsql> SELECT charset(@row), @row;
  43. +---------------+------------+
  44. | charset(@row) | @row       |
  45. +---------------+------------+
  46. | binary        | NULL       |
  47. +---------------+------------+
  48. 1 row in set (0.00 sec)
  50. greatsql> LOAD DATA LOCAL INFILE '/root/dba_zc/load/TEST_UTF8_bak.txt' IGNORE INTO TABLE ASSP_SIS_PAYRES_IMP_BAK CHARACTER SET UTF8MB4 IGNORE 0 LINES  (@row) SET `D_NAME` = NULLIF(TRIM(CONVERT(UNHEX(SUBSTR(HEX(@row), 1,240)) USING UTF8MB4)),'');
  51. Query OK, 2 rows affected (0.01 sec)
  52. Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
  54. greatsql> SELECT charset(@row), @row;  
  55. +---------------+-----------------------+
  56. | charset(@row) | @row                  |
  57. +---------------+-----------------------+
  58. | utf8mb4       | XXX路桥区社会保XXX      |
  59. +---------------+-----------------------+
  61. greatsql> LOAD DATA LOCAL INFILE '/root/dba_zc/load/TEST_GB18030_bak.txt' IGNORE INTO TABLE ASSP_SIS_PAYRES_IMP_BAK CHARACTER SET GB18030 IGNORE 0 LINES  (@row) SET `D_NAME` = NULLIF(TRIM(CONVERT(UNHEX(SUBSTR(HEX(@row), 1,240)) USING GB18030)),'');
  62. Query OK, 2 rows affected (0.01 sec)
  63. Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
  65. greatsql> SELECT charset(@row), @row;  
  66. +---------------+-----------------------+
  67. | charset(@row) | @row                  |
  68. +---------------+-----------------------+
  69. | gb18030       | XXX路桥区社会保XXX      |
  70. +---------------+-----------------------+
  72. greatsql> SELECT * FROM  ASSP_SIS_PAYRES_IMP_BAK;                                                                                                                                                
  73. +---------------+-----------------------------+
  74. | AUTO_INC      | D_NAME                      |
  75. +---------------+-----------------------------+
  76. |        1 | XXX路桥区社会保XXX                 |
  77. |        2 | XXX路桥区社会保XXX                 |
  78. |        4 | XXX路桥区社会保XXX                 |
  79. |        5 | XXX路桥区社会保XXX                 |
  80. +---------------+-----------------------------+
  81. 4 rows in set (0.00 sec)
复制代码
BUG规避方案
通过SELECT``charset(@row), @row; 可以看到@row在执行LOAD DATA后在5.7.21和8.0.25是一样的，但最终的影响不一样。虽然MySQL官方确认此问题为BUG，但没有提供规避方案或者解决方案。通过万里工程师研究后，发现一种可行的规避方案。每次执行LOAD DATA命令前执行 [set @row=_binary'';] 进行规避。
  1. greatsql> SELECT VERSION();
  2. +-----------+
  3. | version() |
  4. +-----------+
  5. | 8.0.25    |
  6. +-----------+
  7. 1 row in set (0.00 sec)
  9. greatsql> SET @row=_binary'';
  10. Query OK, 0 rows affected (0.00 sec)
  12. greatsql> LOAD DATA LOCAL INFILE '/home/greatdb/TEST_UTF8_bak.txt' IGNORE INTO TABLE ASSP_SIS_PAYRES_IMP_BAK CHARACTER SET UTF8MB4 IGNORE 0 LINES  (@row) SET `D_NAME` = NULLIF(TRIM(CONVERT(UNHEX(SUBSTR(HEX(@row), 1,240)) USING UTF8MB4)),'');
  13. Query OK, 2 rows affected (0.01 sec)
  14. Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
  16. greatsql> SET @row=_binary'';
  17. Query OK, 0 rows affected (0.00 sec)
  19. greatsql> LOAD DATA LOCAL INFILE '/home/greatdb/TEST_GB18030_bak.txt' IGNORE INTO TABLE ASSP_SIS_PAYRES_IMP_BAK CHARACTER SET GB18030 IGNORE 0 LINES  (@row) SET `D_NAME` = NULLIF(TRIM(CONVERT(UNHEX(SUBSTR(HEX(@row), 1,240)) USING GB18030)),'');
  20. Query OK, 2 rows affected (0.01 sec)
  21. Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
  23. greatsql> SELECT * FROM assp_sis_payres_imp_bak;
  24. +----------+--------------------------------------------------+
  25. | AUTO_INC | D_NAME                                           |
  26. +----------+--------------------------------------------------+
  27. |        1 | XXX路桥区社会保XXX                 |
  28. |        2 | XXX路桥区社会保XXX                 |
  29. |        4 | XXX路桥区社会保XXX                 |
  30. |        5 | XXX路桥区社会保XXX                 |
  31. +----------+--------------------------------------------------+
  32. 4 rows in set (0.00 sec)
复制代码
问题总结

1.BUG原因
MySQL8.0重构定长数据导入LOAD DATA @row 出现BUG.同一个数据库会话，多次执行LOAD DATA @row命令，则第n次执行LOAD DATA @row 的字符集使用的是n-1次的字符集，当文件的字符集存在不同，例如先后处理GB18030、UTF8字符集的文件就会数据乱码。此问题MySQL官方已证实为BUG（编号115824）
2.BUG触发条件
触发条件：需同时满足以下三个条件才会触发此bug。
1）LOAD DATA命令使用类似 @row临时变量 进行数据处理，例如对定长记录按字节切割出多个字段：
  1. LINES (@row) SET COLUMN_NAME = NULLIF(TRIM(CONVERT(UNHEX(SUBSTR(HEX(@row),1,20)) USING GB18030))。
复制代码
2）在同一个连接中，多次执行LOAD DATA命令，且先后处理的文件字符集存在不同（例如GB18030和UTF8）。
3）使用MySQL 8.0。
3.BUG规避办法
由万里工程师提出，与MySQL官方社区沟通证实，涉及到满足上述BUG触发条件的场景，通过在每次执行LOAD DATA命令前执行 [set @row=_binary'';] 进行规避。
参考：https://bugs.mysql.com/bug.php?id=115824

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/18546504
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

上一篇: Mysql篇-三大日志

下一篇: 【GreatSQL优化器-02】索引和Sargable谓词

发表于 2024-11-15 07:01:46

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4