从MySQL 5.7迁移至8.0时出现1062主键重复及1292时间戳错误

将运行MySQL 5.7.41服务器的数据库备份（gzip压缩的dump文件）导入MySQL 8.0.32-0ubuntu0.22.04.2新服务器时，执行导入命令：

zcat /home/myuser/DB_230312_100450.sql.gz | mysql -u username dbname

出现以下错误：

ERROR 1062 (23000) at line 5409: Duplicate entry '48506-2011-03-27 03:00:00' for key 'user_logindates.user_id'

但原数据中用户48506并没有2011-03-27 03:00:00的记录，该时间戳仅存在于其他用户数据中，无重复；原库执行CHECK TABLE user_logindates结果正常。

单独导出该表并执行INSERT语句时，出现新错误：

[22001][1292] Data truncation: Incorrect datetime value: '2011-03-27 02:50:11' for column 'timestamp' at row 1097

该表结构如下：

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_logindates` (
  `user_id` mediumint(8) unsigned NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `user_id` (`user_id`,`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

已检查新旧服务器的时区、字符集及sql_mode，两者基本一致，仅新服务器sql_mode缺少NO_AUTO_CREATE_USER指令：

Settings:
    sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
collation_server        = utf8_unicode_ci
character_set_server    = utf8

针对上述异常，从MySQL 5.7迁移至8.0前，需完成以下操作：

1. 统一时区配置并处理夏令时冲突 #

错误涉及的2011-03-27大概率是夏令时切换导致的时区转换异常，需：

• 确保新旧服务器的系统时区和MySQL的time_zone参数完全一致（建议统一设为UTC规避夏令时问题，或使用带明确时区的格式如Europe/Paris）
• 导出数据时添加--tz-utc=0参数，禁用mysqldump的时区转换，保留timestamp原始存储值：

  mysqldump --tz-utc=0 -u root -p dbname > db_dump.sql
  

• 若需保留本地时区，导入前在新服务器执行：

  SET time_zone = '你的目标时区';
  

2. 对齐sql_mode配置 #

虽然NO_AUTO_CREATE_USER在MySQL 8.0已被废弃，但需确保其他sql_mode参数完全匹配原库：

• 在新服务器临时设置与原库一致的sql_mode：

  SET GLOBAL sql_mode = 'IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
  

• 导入完成后，再根据MySQL 8.0最佳实践调整sql_mode（如移除已废弃的NO_AUTO_CREATE_USER）

3. 修复timestamp字段兼容性问题 #

MySQL 8.0对timestamp的校验更严格，尤其是夏令时切换产生的无效时间区间，需：

• 导出表时使用--complete-insert参数，确保INSERT语句包含所有字段的明确值，避免依赖默认值引发的转换错误：

  mysqldump --complete-insert -u root -p dbname user_logindates > user_logindates.sql
  

• 若存在无效夏令时时间，可先在原库将timestamp字段转为datetime类型（datetime不依赖时区，直接存储原始字符串），导出后再在新库转换回timestamp：

  -- 原库执行
  ALTER TABLE user_logindates MODIFY COLUMN timestamp DATETIME NOT NULL;
  -- 新库导入后执行
  ALTER TABLE user_logindates MODIFY COLUMN timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
  

4. 验证备份数据完整性 #

• 检查备份文件中user_logindates表的数据，确认是否存在重复的user_id+timestamp组合：

  zcat DB_230312_100450.sql.gz | grep -A 1 -B 1 "48506.*2011-03-27"
  

• 使用mysqldump的--checksum参数生成校验和，验证备份文件未损坏：

  mysqldump --checksum -u root -p dbname > db_dump.sql
  

5. 使用官方迁移工具完成收尾 #

跨大版本迁移完成后，执行官方提供的mysql_upgrade工具，自动处理系统表升级和兼容性检查：

mysql_upgrade -u username -p

内容的提问来源于stack exchange，提问作者merlin