使用mysql-workbench从MSSQL迁移到MySQL，wbcopytables随着“插入数据：不正确的字符串value”的失败而不断失败。

Question

我已经阅读了几乎所有有关此问题的文章，但我仍然无法制作wbcopytables 8.0.29使用一些特定表。

MySQL-WorkBench生成的脚本看起来像这样：

/usr/bin/wbcopytables \
 --odbc-source="DRIVER={FreeTDS};SERVER=172.17.0.2;PORT=1433;DATABASE=;UID=SA;;TDS_VERSION=7.1" \
 --source-rdbms-type=Mssql \
 --target="[email protected]:3306" \
 --source-password="$arg_source_password" \
 --target-password="$arg_target_password" \
 --force-utf8-for-source \
 --source-ssh-password="$arg_source_ssh_password" \
 --target-ssh-password="$arg_target_ssh_password" \
 --thread-count=$arg_worker_count \
 $arg_truncate_target \
 $arg_debug_output \
--table '[YourDB]' '[dbo].[EQUIP]' '`dbo`' '`EQUIP`' '-' '-' 'CAST([EQUIP_IDENT] as NVARCHAR(6)) as [EQUIP_IDENT], CAST([NAME] as NVARCHAR(24)) as [NAME], CAST([DESCRIP] as NVARCHAR(35)) as [DESCRIP], CAST([EQP_TYPE] as NVARCHAR(3)) as [EQP_TYPE], CAST([FLEET_IDENT] as NVARCHAR(3)) as [FLEET_IDENT], [CHKLIST_IDENT], [FUEL_NORMAL_HOUR], [FUEL_FILL_HOUR], [FUEL_CRITICAL], [FUEL_CAPACITY], CAST([GPS_FLAG] as NVARCHAR(1)) as [GPS_FLAG], CAST([MDT_FLAG] as NVARCHAR(1)) as [MDT_FLAG], CAST([SMU_FLAG] as NVARCHAR(1)) as [SMU_FLAG], CAST([SERIAL_NUMBER] as NVARCHAR(40)) as [SERIAL_NUMBER], CAST([MODEL_NUMBER] as NVARCHAR(40)) as [MODEL_NUMBER], CAST([MANUFACTURER] as NVARCHAR(15)) as [MANUFACTURER], CAST([BUSINESS_UNIT] as INT) as [BUSINESS_UNIT], CAST([OWNER] as NVARCHAR(20)) as [OWNER], [MANUFACTURER_DATE], [WENCO_ENTRY_DATE], CAST([COST_CODE] as NVARCHAR(5)) as [COST_CODE], CAST([WORKORDER] as NVARCHAR(8)) as [WORKORDER], CAST([ANI] as INT) as [ANI], CAST([IP_ADDRESS] as NVARCHAR(15)) as [IP_ADDRESS], CAST([IP_PORT] as INT) as [IP_PORT], CAST([EQMODEL_CODE] as NVARCHAR(15)) as [EQMODEL_CODE], CAST([HIDE_FROM_FLEET_CONTROL] as NVARCHAR(1)) as [HIDE_FROM_FLEET_CONTROL], CAST([ACTIVE] as NVARCHAR(1)) as [ACTIVE], CAST([TEST] as NVARCHAR(1)) as [TEST], CAST([DEVICE_NAME] as NVARCHAR(32)) as [DEVICE_NAME], CAST([OS_VERSION] as NVARCHAR(12)) as [OS_VERSION], CAST([MDT_VERSION] as NVARCHAR(12)) as [MDT_VERSION], CAST([DMP_ADDRESS] as NVARCHAR(8)) as [DMP_ADDRESS], CAST([CHANNEL] as SMALLINT) as [CHANNEL], CAST([COMMENT] as NVARCHAR(255)) as [COMMENT], CAST([MDT_TEXT] as NVARCHAR(35)) as [MDT_TEXT], CAST([V2X_MAC_ADDRESS] as NVARCHAR(17)) as [V2X_MAC_ADDRESS], [NEXT_INSPECTION_ALERT_SMU_HOURS], [NEXT_INSPECTION_ALERT_TRAVELED_DISTANCE], CAST([AHS_GUID] as NVARCHAR(36)) as [AHS_GUID], [VC_SCOPE_IDENT]'

我刚刚添加了- force-utf8-for-source flag（默认情况下未出现），否则它会失败，而error失败在Charset转换期间，WSTRING：Success。

如果我运行此脚本，它会失败，因为列的一行中有一个“ñ” 注释，

...
12:35:19 [INF][      copytable]: Statement execution failed: Incorrect string value: '\xF1ones' for column `dbo`.`EQUIP`.`COMMENT` at row 85:
[... insert body here ...]
ERROR:`dbo`.`EQUIP`:Inserting Data: Incorrect string value: '\xF1ones' for column `dbo`.`EQUIP`.`COMMENT` at row 85
ERROR:`dbo`.`EQUIP`:Failed copying 113 rows
...

即使我可以

INSERT INTO `dbo`.`EQUIP` (`EQUIP_IDENT`, `NAME`, `DESCRIP`, `EQP_TYPE`, `FLEET_IDENT`, `CHKLIST_IDENT`, `FUEL_NORMAL_HOUR`, `FUEL_FILL_HOUR`, `FUEL_CRITICAL`, `FUEL_CAPACITY`, `GPS_FLAG`, `MDT_FLAG`, `SMU_FLAG`, `SERIAL_NUMBER`, `MODEL_NUMBER`, `MANUFACTURER`, `BUSINESS_UNIT`, `OWNER`, `MANUFACTURER_DATE`, `WENCO_ENTRY_DATE`, `COST_CODE`, `WORKORDER`, `ANI`, `IP_ADDRESS`, `IP_PORT`, `EQMODEL_CODE`, `HIDE_FROM_FLEET_CONTROL`, `ACTIVE`, `TEST`, `DEVICE_NAME`, `OS_VERSION`, `MDT_VERSION`, `DMP_ADDRESS`, `CHANNEL`, `COMMENT`, `MDT_TEXT`, `V2X_MAC_ADDRESS`, `NEXT_INSPECTION_ALERT_SMU_HOURS`, `NEXT_INSPECTION_ALERT_TRAVELED_DISTANCE`, `AHS_GUID`, `VC_SCOPE_IDENT`) 
VALUES ('Ñ','Á','á','S','325',3,19.000000,4.000000,2.000000,300.000000,'N','M','N',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,201,NULL,NULL,NULL,'N','N','N',NULL,NULL,NULL,NULL,1,NULL,'PALA-C01',NULL,NULL,NULL,NULL,NULL);
Query OK, 1 row affected (0.007 sec)

添加

从sql Server 2019-15.0 它们。 4198.2

1> SELECT *
2> FROM INFORMATION_SCHEMA.COLUMNS
3> WHERE TABLE_NAME = 'EQUIP' AND COLUMN_NAME = 'COMMENT'
4> GO

TABLE_CATALOG  TABLE_SCHEMA  TABLE_NAME  COLUMN_NAME  ORDINAL_POSITION COLUMN_DEFAULT  IS_NULLABLE DATA_TYPE  CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_PRECISION_RADIX NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_CATALOG  CHARACTER_SET_SCHEMA  CHARACTER_SET_NAME  COLLATION_CATALOG  COLLATION_SCHEMA  COLLATION_NAME                DOMAIN_CATALOG  DOMAIN_SCHEMA  DOMAIN_NAME
-------------- ------------- ----------- ------------ ---------------- --------------- ----------- ---------- ------------------------ ---------------------- ----------------- ----------------------- ------------- ------------------ ---------------------- --------------------- ------------------- ------------------ ----------------- ----------------------------- --------------- -------------- ------------
YourDB         dbo           EQUIP       COMMENT                    35 NULL            YES         nvarchar                        255                    510              NULL                    NULL          NULL               NULL NULL                   NULL                  UNICODE             NULL               NULL              SQL_Latin1_General_CP1_CI_AS  NULL            NULL           NULL                                                                                                            

Mariadb 10.7.3

SHOW FULL COLUMNS FROM EQUIP WHERE Field = 'COMMENT';
+---------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field   | Type         | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+---------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| COMMENT | varchar(255) | utf8mb4_unicode_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+---------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+

也将所有内容设置为utf8mb4和utf8MB4_UNICODE_CI in Mariadb：

SHOW VARIABLES LIKE 'CHAR%'; 

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

SHOW VARIABLES LIKE 'COLLATION%';

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+

据我了解，据我了解> > > UT8MB4尚未以farem_set_system的支持，这就是为什么它显示utf8mb3（可能是这个

问题

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA  WHERE SCHEMA_NAME = "dbo";
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_unicode_ci     |
+----------------------------+------------------------+

？代码>设备

SELECT CCSA.CHARACTER_SET_NAME 
FROM INFORMATION_SCHEMA.TABLES T, NFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY CCSA 
WHERE CCSA.COLLATION_NAME = T.TABLE_COLLATION AND T.TABLE_SCHEMA = "dbo" AND T.TABLE_NAME = "EQUIP";

+--------------------+--------------------+
| CHARACTER_SET_NAME | COLLATION_NAME     |
+--------------------+--------------------+
| utf8mb4            | utf8mb4_unicode_ci |
+--------------------+--------------------+

列注释

SELECT CHARACTER_SET_NAME, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = "dbo" AND TABLE_NAME = "EQUIP" AND COLUMN_NAME = "COMMENT";

+--------------------+--------------------+
| CHARACTER_SET_NAME | COLLATION_NAME     |
+--------------------+--------------------+
| utf8mb4            | utf8mb4_unicode_ci |
+--------------------+--------------------+

我也尝试cast 注释字段 at varchar（255）代码>和nvarchar（255），也根本不施放它，但没有任何作用。

有人知道什么可能失败吗？它是wbcopytables配置问题，还是在MSSQL/Mariadb中？