最近在写一个导出数据库数据的小工具,但是发现对于超大表,就会包开头提到的错误。结论就是我们使用的go-sql-driver里面的rows查询类似一个游标,但是mysql中有超时查询时间,这个时间到了 我们使用的rows就失效了,所以就报错了。ps 一般来说 数据库的超时时间我们是无权修改的。
情况一:查询大数据超时
[mysql] 2022/07/01 12:02:04 packets.go:428: busy buffer
invalid connection
invalid connection Rows error.
例如我们查询的数据量比较大的话,可能会耗时比较长。
我们可以先看一下mysql数据库的超时时间:
SHOW VARIABLES LIKE '%timeout%'
connect_timeout | 10 |
---|---|
delayed_insert_timeout | 300 |
have_statement_timeout | YES |
innodb_flush_log_at_timeout | 1 |
innodb_lock_wait_timeout | 20 |
innodb_rollback_on_timeout | OFF |
interactive_timeout | 300 |
lock_wait_timeout | 31536000 |
net_read_timeout | 30 |
net_write_timeout | 60 |
rpl_stop_slave_timeout | 31536000 |
slave_net_timeout | 60 |
wait_timeout | 300 |
connect_time
connect_timeout指的是连接过程中握手的超时时间,即MySQL客户端在尝试与MySQL服务器建立连接时,MySQL服务器返回错误握手协议前等待客户端数据包的最大时限。默认10秒。
interactive_timeout / wait_timeout
MySQL关闭交互/非交互连接前等待的最大时限。默认28800秒。
lock_wait_timeout
sql语句请求元数据锁的最长等待时间,默认为一年。此锁超时对于隐式访问Mysql库中系统表的sql语句无效,但是对于使用select,update语句直接访问MySQL库中标的sql语句有效。
net_read_timeout / net_write_timeout
mysql服务器端等待从客户端读取数据 / 向客户端写入数据的最大时限,默认30秒。
slave_net_timeout
mysql从复制连结等待读取数据的最大时限,默认3600秒。
解决办法:
MySQL doesn't provide safe and efficient canceling mechanism. When context is cancelled or reached readTimeout
, DB.ExecContext
returns without terminating using connection. It cause "invalid connection" next time the connection is used.
MySQL :: MySQL 5.7 Reference Manual :: 8.9.3 Optimizer Hints
MAX_EXECUTION_TIME(N)
Example with a timeout of 1 second (1000 milliseconds):
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...
情况二:Rows未关闭的情况
首先, Rows 是执行 query 返回的结果,当我们执行 rows, err := tx.Query("SELECT 1")
拿到 Rows 时,数据还在 buffer 里面,并没有读出来。
这时如果有其他协程在这个事务里执行语句,要向 db 发包,就会出现 busy buffer,因为在一个事务里,只有一个连接可以用。
前面提到,把数据从 buffer 中读出来就可以重新利用这一条 mysqlConn 发包,那么把 Rows 中的数据读出来还会出现这个问题吗?是可以的,加上一句 for rows.Next(){rows1.Scan()}
就不会出现 busy buffer 了。
// 来自:go-sql-driver/mysql@1.4.0/rows.go func (rows *textRows) Next(dest []driver.Value) error { if mc := rows.mc; mc != nil { if err := mc.error(); err != nil { return err } // Fetch next row from stream // 这里会调用 mc.readPacket() 将 buffer 里的数据读出来。 return rows.readRow(dest) } return io.EOF }
当然关闭也是可以的,我们看一下关闭 rows 后 buffer 会怎样。会由 mysqlConn 来负责处理没有读的 buffer,会调用 mc.readUntilEOF() 将 buffer 里的内容全部读出来,直到 EOF。mc.discardResults() 也会调用 mc.readUntilEOF(),而 mc.readUntilEOF() 则会调用 mc.readPacket()。
// 来自:go-sql-driver/mysql@1.4.0/rows.go func (rows *mysqlRows) Close() (err error) { if f := rows.finish; f != nil { f() rows.finish = nil } mc := rows.mc if mc == nil { return nil } if err := mc.error(); err != nil { return err } // Remove unread packets from stream if !rows.rs.done { err = mc.readUntilEOF() } if err == nil { if err = mc.discardResults(); err != nil { return err } } rows.mc = nil return err }
当然了,打开的资源是要关闭的,因此只执行 for rows.Next(){rows1.Scan()}
是不够的,还要关闭 rows.
情况三:为什么不要在一个事务中执行并发的查询?
首先不要用并发的协程去操作同一个连接,实际上上面的分析也说明了,如果你这么做了,go-sql-driver/mysql
很有可能会返回给你一个 busy buffer。
一个事务只有一个连接,当调用 db.Begin()
开启一个事务时,sql.DB
会从连接池中取出一个连接或者创建一个新的连接分配给这个事务。
情况四:可以在一个事务中执行并发的更新吗?
是可以的,因为更新或者插入的操作调用的是 Exec()
,db 返回的 result 在这里就直接从 buffer 中读出来了。
情况五:
mysql 可以最大传输的数据量:
SHOW VARIABLES LIKE 'max_allowed_packet'
Variable_nameValuemax_allowed_packet
1073741824
参考地址:
https://github.blog/2020-05-20-three-bugs-in-the-go-mysql-driver/