sqlx写:断管

Question

我正在尝试将一个巨大的文本更新到一个MariaDB表中，但是不断地获取broken pipe错误。该列具有longtext数据类型，从我的日志中看，该数据的大小为~1374069 bytes。

我尝试将MaxConnLife更新到几个不同的值，比如wait_timeout的一半来自MariaDB，它被设置为28800 -> db.SetConnMaxLifetime(14400)

这就是我的DB conn看起来的样子

    loggerAdapter := zerologadapter.New(zerolog.New(os.Stdout))
    db := sqldblogger.OpenDriver(dsn, &mysql.MySQLDriver{}, loggerAdapter /*, using_default_options*/) // db is STILL *sql.DB

    db.SetConnMaxLifetime(14400)


    sqlxdb := sqlx.NewDb(db, "MySQL")

错误日志

{"level":"info","conn_id":"66g4YSswso2QqsQE","duration":0.289406,"query":"UPDATE campaigns SET vendor_products=? WHERE id=?","stmt_id":"Mygo_MuAaIuGYwe2","time":1605747034,"message":"PrepareContext"} 
[mysql] 2020/11/19 00:50:34 packets.go:145: write tcp IP->IP: write: broken pipe 
{"level":"ERROR","args":["[{\"Id\":1,\"UserId\":1,\"PlanId\":1,\"GroupI (1374069 bytes truncated)",130],"conn_id":"66g4YSswso2QqsQE","duration":2.138179,"ERROR":"invalid connection","query":"UPDATE campaigns SET vendor_products=? WHERE id=?","stmt_id":"Mygo_MuAaIuGYwe2","time":1605747034,"message":"StmtExecContext"} 
{"level":"ERROR","conn_id":"66g4YSswso2QqsQE","duration":0.00295,"ERROR":"driver: bad connection","query":"UPDATE campaigns SET vendor_products=? WHERE id=?","stmt_id":"Mygo_MuAaIuGYwe2","time":1605747034,"message":"StmtClose"} 

我正在使用的版本

github.com/go-sql-driver/mysql v1.4.1
go 1.13
github.com/jmoiron/sqlx v0.0.0-20181024163419-82935fac6c1a

知道我怎么解决这个问题吗？还是我应该想另一个解决办法？比如可能将文本存储到S3而不是SQL？

我做过一些研究，但这些都没有帮助。我发现的一些东西。

• https://github.com/go-sql-driver/mysql/issues/446
• what is the best solutions to mysql connection timeout?
• https://github.com/go-sql-driver/mysql/issues/529
• https://github.com/go-sql-driver/mysql/issues/116

Answer 1

增加max_allowed_packet回答了我的问题。

参考资料：https://mariadb.com/docs/reference/mdb/system-variables/max_allowed_packet/