将R数据框写入MariaDB时遇查询中丢失连接错误排查

根据你描述的情况——本地写入任意大小表都正常，但服务器上超过1000行就断开连接，且max_allowed_packet已设为16M、日志无相关记录——我整理了几个排查方向和解决办法：

一、临时快速解决：分批次写入数据 #

既然服务器能稳定处理1000行的写入，你可以先把数据拆分成小批次分批写入，绕过当前的连接限制：

# 加载数据（你的原命令）
cntxt <- read.delim("http://ec.europa.eu/eurostat/estat-navtree-portlet-prod/BulkDownloadListing?sort=1&file=comext%2FCOMEXT_METADATA%2FCLASSIFICATIONS_AND_RELATIONS%2FENGLISH%2FCN.txt", header = FALSE, quote = "", stringsAsFactors = FALSE)

# 连接数据库
library(RMySQL)
con <- dbConnect(MySQL(), dbname = "test")

# 先创建空表（覆盖原有表）
dbWriteTable(con, "cntxt", cntxt[0, ], row.names = FALSE, overwrite = TRUE)

# 按每1000行拆分数据
batch_size <- 1000
batches <- split(cntxt, ceiling(seq(nrow(cntxt)) / batch_size))

# 循环写入每个批次
for (batch in batches) {
  dbWriteTable(con, "cntxt", batch, row.names = FALSE, append = TRUE)
}

# 关闭连接
dbDisconnect(con)

二、排查根本原因 #

1. 检查MariaDB的超时设置 #

服务器上的wait_timeout和interactive_timeout可能设置过短，导致长时间的写入操作中途断开连接：

• 登录MariaDB命令行，查看当前设置：

SHOW VARIABLES LIKE '%timeout%';

如果这两个值很小（比如60秒），可以临时调整为300秒：

SET GLOBAL wait_timeout = 300;
SET GLOBAL interactive_timeout = 300;

• 要永久生效，编辑MariaDB配置文件（通常是/etc/mysql/mariadb.conf.d/50-server.cnf或/etc/mysql/my.cnf），在[mysqld]段添加：

wait_timeout = 300
interactive_timeout = 300

然后重启MariaDB服务：

sudo systemctl restart mariadb

2. 替换为RMariaDB驱动 #

RMySQL是较旧的驱动，官方更推荐使用RMariaDB包连接MariaDB，它的兼容性和稳定性更好：

install.packages("RMariaDB")
library(RMariaDB)

# 连接并写入数据
con <- dbConnect(MariaDB(), dbname = "test")
dbWriteTable(con, "cntxt", cntxt, row.names = FALSE, overwrite = TRUE)
dbDisconnect(con)

3. 检查单查询执行时间限制 #

MariaDB的max_execution_time参数会限制单查询的最长执行时间，如果写入操作超时会被强制断开：

• 查看当前设置：

SHOW VARIABLES LIKE 'max_execution_time';

• 如果值过小（比如10000毫秒=10秒），临时调整为60秒：

SET GLOBAL max_execution_time = 60000;

• 永久生效的话，在配置文件的[mysqld]段添加：

max_execution_time = 60000

然后重启服务。

4. 验证网络和数据库稳定性 #

尝试用MariaDB命令行工具导入同样的数据，排除R驱动的问题：

1. 先把R中的数据导出为CSV文件：

write.table(cntxt, "/tmp/cntxt.csv", sep = "\t", row.names = FALSE, quote = FALSE)

2. 登录MariaDB命令行，执行导入：

LOAD DATA INFILE '/tmp/cntxt.csv' INTO TABLE cntxt FIELDS TERMINATED BY '\t';

如果命令行导入也报错，说明是服务器的网络或数据库配置问题（比如防火墙TCP超时、磁盘IO瓶颈）；如果命令行导入正常，那问题大概率出在RMySQL驱动上，改用RMariaDB即可解决。

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