使用DBeaver迁移MySQL 5.5到8.0时遭遇1406数据截断错误求助

问题描述 #

用DBeaver把MySQL 5.5数据库迁到MySQL 8时，ncm库下的NetLog表（包括NetLog-2023-05-29这类按日期分区的表）出现导入报错：

SQL Error [1406] [22001]: Data truncation: Data too long for column 'creds' at row 4

已知源库和目标库的creds列定义完全一致：varchar(200)、latin1_swedish_ci、允许为空，且该列最长内容仅68字节，同时报错提示的第4行并不是实际有问题的行，需要排查这两个异常的原因。

附NetLog-2023-05-29表结构 #

CREATE TABLE `NetLog-2023-05-29` (
 `row_number` int(3) NOT NULL DEFAULT '1',
 `recordID` int(12) NOT NULL AUTO_INCREMENT,
 `netID` int(6) NOT NULL,
 `subNetOfID` varchar(15) CHARACTER SET latin1 DEFAULT NULL,
 `timeout` datetime DEFAULT NULL,
 `ID` int(6) unsigned zerofill NOT NULL DEFAULT '000000',
 `callsign` varchar(15) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `Fname` varchar(25) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `grid` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
 `traffic` varchar(15) CHARACTER SET latin1 DEFAULT '',
 `tactical` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
 `logdate` datetime NOT NULL COMMENT 'Check-in Date & Time',
 `activity` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '',
 `latitude` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
 `longitude` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
 `email` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
 `Lname` varchar(25) CHARACTER SET latin1 DEFAULT NULL,
 `netcontrol` varchar(5) CHARACTER SET latin1 DEFAULT NULL,
 `active` varchar(7) COLLATE utf8_unicode_ci DEFAULT '',
 `frequency` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
 `comments` varchar(3000) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `creds` varchar(200) CHARACTER SET latin1 DEFAULT NULL,
 `timeonduty` int(8) DEFAULT '0',
 `netcall` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
 `status` int(1) NOT NULL DEFAULT '0',
 `Mode` varchar(7) COLLATE utf8_unicode_ci DEFAULT NULL,
 `logclosedtime` datetime DEFAULT NULL,
 `county` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `state` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
 `district` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 `firstLogIn` int(1) DEFAULT NULL,
 `tt` int(2) unsigned zerofill DEFAULT NULL,
 `pb` int(1) NOT NULL,
 `phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `dttm` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When row was created',
 `ipaddress` varchar(20) CHARACTER SET utf8 NOT NULL,
 `band` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
 `w3w` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `home` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `cat` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Custom',
 `section` varchar(25) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Section',
 `testnet` text COLLATE utf8_unicode_ci NOT NULL,
 `team` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `aprs_call` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
 `country` text COLLATE utf8_unicode_ci NOT NULL,
 `facility` varchar(250) COLLATE utf8_unicode_ci NOT NULL COMMENT 'example: Hospital Name',
 `onSite` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Is the station on site? Yes, No',
 `delta` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'Indicates a change in location',
 `city` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'City of callsign',
 PRIMARY KEY (`recordID`),
 KEY `callsign` (`callsign`),
 KEY `netID` (`netID`,`callsign`),
 KEY `id_netID` (`netID`,`ID`) USING BTREE,
 KEY `callsign_index` (`callsign`)
) ENGINE=MyISAM AUTO_INCREMENT=133749 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Amateur Radio Net Check-in Log'

问题分析与解决 #

一、68字节内容触发截断错误的原因 #

1. 字符集转码膨胀：虽然creds列指定了latin1字符集，但表的默认字符集是utf8（表定义里DEFAULT CHARSET=utf8）。DBeaver默认可能会按表的默认字符集来处理数据，而非字段单独指定的latin1，导致latin1编码的内容被转成utf8后字节数膨胀——比如部分特殊字符从1字节变成3字节，原本68字节的内容转码后可能超过200字节的限制。
2. 严格模式差异：MySQL 8默认开启STRICT_TRANS_TABLES严格模式，而MySQL 5.5大概率没开。非严格模式下MySQL会自动截断过长内容不报错，严格模式则直接抛出1406错误。
3. DBeaver导入设置问题：默认导入逻辑可能开启了自动字符集转换，导致字段实际写入时的字符集与定义不符。

二、报错行号不准确的原因 #

• 批量提交的缓冲区机制：DBeaver一般会批量提交数据（比如一次发100行），当缓冲区里某一行出错时，MySQL只会返回当前批次的起始行号（第4行可能是这个批次的第一行），而非实际出错的那一行。
• 引擎行计数差异：源库用的是MyISAM引擎，目标库如果是MySQL 8默认的InnoDB，两者的行号计算逻辑不一样，导致报错行号和实际数据行不匹配。

解决办法 #

1. 强制指定字段字符集：在DBeaver导入向导里，手动把creds列的字符集设为latin1，避免自动转成表默认的utf8。
2. 临时关闭严格模式：在目标库执行以下语句，导入完成后再改回原设置（不建议长期关闭）：

SET @@SESSION.sql_mode = REPLACE(@@SESSION.sql_mode, 'STRICT_TRANS_TABLES', '');

3. 临时扩容字段：把目标库creds列临时改成varchar(600)（足够容纳转码后的膨胀内容），导入完成后再改回varchar(200)（确认内容实际长度符合要求）。
4. 验证源数据：在源库执行以下语句，用字节数和字符数双重统计creds列的最大长度，确认转码后的潜在风险：

SELECT recordID, creds, LENGTH(creds) AS 字节数, CHAR_LENGTH(creds) AS 字符数 FROM NetLog ORDER BY 字节数 DESC LIMIT 10;

内容的提问来源于stack exchange，提问作者Keith D Kaiser