MariaDB大表去重二次执行遇Workbench超时（Error 2013）求助

问题背景 #

在MariaDB 10.5.17环境下执行7100万行大表（Table1，232列、多索引）去重流程：

• 首次执行：创建无索引临时表Table2，插入Table1的8个唯一标识字段+自增ID，流程成功。
• 二次执行：删除Table2后重建并插入数据，MySQL Workbench显示查询运行至7200秒超时（Error Code: 2013），但实际15分钟后Table2已完成7100万行插入，原Workbench进程仍挂至超时，且查询进程在15分钟左右消失。
• 已尝试调整客户端超时为0/86400秒、带/不带COMMIT执行，问题依旧；测试环境仅单用户操作，查询INNODB_TRX时因未指定库报错。

解决方案 #

1. 修正服务器端超时配置 #

客户端超时设置无效，大概率是MariaDB服务器端的interactive_timeout和wait_timeout默认值为7200秒（2小时），到点主动断开连接。临时调整参数：

-- 全局生效，需重新连接客户端
SET GLOBAL interactive_timeout = 86400;
SET GLOBAL wait_timeout = 86400;

-- 当前会话生效
SET SESSION interactive_timeout = 86400;
SET SESSION wait_timeout = 86400;

生产环境可在my.cnf/my.ini中永久配置，避免重启后失效：

[mysqld]
interactive_timeout = 86400
wait_timeout = 86400

2. 分批插入替代全量插入 #

一次性插入7100万行易触发超时、占用过多资源，改用分批插入降低压力：

SET autocommit = 0;
SET @batch_size = 100000; -- 可根据服务器性能调整批次大小
SET @total_rows = (SELECT COUNT(*) FROM Table1);
SET @offset = 0;

WHILE @offset < @total_rows DO
    INSERT INTO Table2 (col1, col2, ..., col8, auto_id)
    SELECT col1, col2, ..., col8, auto_id FROM Table1 LIMIT @offset, @batch_size;
    
    SET @offset = @offset + @batch_size;
    COMMIT; -- 每批次提交一次，避免事务过大
END WHILE;

SET autocommit = 1;

若Table1有自增主键，也可按主键范围分批，比LIMIT OFFSET更高效：

SET @last_id = 0;
SET @batch_size = 100000;

WHILE EXISTS (SELECT 1 FROM Table1 WHERE auto_id > @last_id) DO
    INSERT INTO Table2 (col1, col2, ..., col8, auto_id)
    SELECT col1, col2, ..., col8, auto_id 
    FROM Table1 
    WHERE auto_id > @last_id 
    ORDER BY auto_id 
    LIMIT @batch_size;
    
    SET @last_id = (SELECT MAX(auto_id) FROM Table2);
    COMMIT;
END WHILE;

3. 优化临时表Table2与原表读取性能 #

• Table2配置优化：创建时指定合适的存储引擎与行格式，减少IO开销：

  CREATE TABLE Table2 (
      col1 VARCHAR(50),
      col2 INT,
      -- ... 其余6个标识字段
      auto_id BIGINT PRIMARY KEY -- 添主键避免后续排序无索引
  ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
  

• 原表添加覆盖索引：为Table1创建包含所需字段的覆盖索引，避免全表扫描：

  CREATE INDEX idx_cover_deduplicate ON Table1 (col1, col2, ..., col8, auto_id);
  

  插入完成后可删除该临时索引：

  DROP INDEX idx_cover_deduplicate ON Table1;
  

4. 排查进程异常与错误日志 #

• 查看错误日志：检查MariaDB错误日志（路径通常为/var/log/mariadb/mariadb.log或数据目录下的hostname.err），确认是否存在进程被OOM Killer终止、InnoDB异常等信息。
• 正确查询事务状态：需指定information_schema库查询事务锁信息：

  SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY 
  FROM information_schema.INNODB_TRX;
  

5. 更换客户端工具规避Workbench限制 #

MySQL Workbench自身可能存在隐性超时机制，改用命令行客户端执行插入：

# 连接时指定超时参数
mysql -u your_username -p -D your_database --connect-timeout=86400 --read-timeout=86400 --write-timeout=86400

# 在命令行内执行插入语句或分批脚本

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