Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

gorm使用，连接db报错 #1822

Closed
lz1988 opened this issue Mar 30, 2018 · 33 comments
Closed

gorm使用，连接db报错 #1822

lz1988 opened this issue Mar 30, 2018 · 33 comments
Labels
status:gorm_v1

Comments

@lz1988
Copy link

lz1988 commented Mar 30, 2018

[mysql] 2018/03/30 11:27:22 packets.go:36: unexpected EOF
[mysql] 2018/03/30 11:27:22 connection.go:311: invalid connection
[mysql] 2018/03/30 11:42:32 packets.go:36: unexpected EOF
[mysql] 2018/03/30 11:42:32 connection.go:311: invalid connection
[mysql] 2018/03/30 12:20:17 packets.go:36: unexpected EOF
[mysql] 2018/03/30 12:20:17 connection.go:311: invalid connection
[mysql] 2018/03/30 13:02:30 packets.go:36: unexpected EOF
[mysql] 2018/03/30 13:02:30 connection.go:311: invalid connection
[mysql] 2018/03/30 13:06:19 packets.go:36: unexpected EOF
@lz1988
Copy link
Author

lz1988 commented Mar 30, 2018 •
edited
Loading

packets.go

@scofier
Copy link

scofier commented May 25, 2018

问题解决了么？

@hyacinthus
Copy link

我在升级了一次后也遇到了这个问题
后台日志大量出现
[mysql] 2018/06/21 23:36:51 packets.go:36: unexpected EOF
(invalid connection)

虽然重试之后就连上了

但是会有影响 一部分比例的请求会失败

不知道怎么才能保持长连接

@hyacinthus
Copy link

后来我设置了服务器参数解决了。 就是服务器单方面关闭了连接。
但是比较诡异的是之前没这个问题，在我最近升级了一次后才出现的。
也许是mysql客户端改了什么默认参数吧。

@lz1988
Copy link
Author

lz1988 commented Aug 2, 2018 •
edited
Loading

目前我给的参数，问题依旧，不知道是不是gorm还需要哪里设置，或者mysql配置也需要调整
maxidleconns = 500
maxopenconns = 500
maxlifetime = 0

@lvzhihao
Copy link

lvzhihao commented Aug 7, 2018 •
edited
Loading

应该是连接重试的逻辑代码改动过了吧，我去掉服务中间的lb就没这些问题，lb基本都不会支持长连接，超时主动断开后就出现异常eof了。之前代码会尝试重连，现在会直接报错。

看了下应该是mysql的驱动变更带来的影响：
go-sql-driver/mysql#657
讨论了半天也没啥个结果，有赞成也有反对的，看里程碑应该在1.5才考虑解决。

我个人看暂时只能两个方案：

db.DB().SetConnMaxLifetime(60 * time.Second) //这个时间和lb的idle超时短就行了

db.DB().SetMaxIdleConns(0) //不要使用连接池

@lz1988
Copy link
Author

lz1988 commented Aug 9, 2018

如果设置了SetConnMaxLifetime，实际的使用当中是否有效？

@lz1988
Copy link
Author

lz1988 commented Aug 9, 2018

不使用SetMaxIdleConns，那么连接池的意义就不大了。

@lvzhihao
Copy link

lvzhihao commented Aug 9, 2018 via email

@lz1988
Copy link
Author

lz1988 commented Aug 15, 2018

这个参数给了值，依旧还是会报错

@hyacinthus
Copy link

只要客户端的时间短于服务器的时间就可以的，你需要检查下服务器上是多久超时。

@lvzhihao
Copy link

lvzhihao commented Aug 16, 2018 via email

@lz1988
Copy link
Author

lz1988 commented Aug 17, 2018

设置db.DB().SetConnMaxLifetime(60 * time.Second) 时间，但是还是会提示连接丢失的情况。

@lvzhihao
Copy link

lvzhihao commented Aug 20, 2018 via email

@lz1988
Copy link
Author

lz1988 commented Aug 20, 2018

mysql> SHOW VARIABLES LIKE '%timeout%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| connect_timeout | 10 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| wait_timeout | 28800 |
+------------------------------+----------+

@lz1988
Copy link
Author

lz1988 commented Aug 20, 2018

如果按照你说的，应该不会出现连接丢失的情况

@lvzhihao
Copy link

lvzhihao commented Aug 21, 2018 via email

@lz1988
Copy link
Author

lz1988 commented Aug 22, 2018

数据库是直接连接，没有使用中间件，我怀疑每次在获取db的时候，需要ping下，看下连接是否有效，否则重连。

@lz1988
Copy link
Author

lz1988 commented Aug 22, 2018

好像mysql driver貌似也有类似的问题
go-sql-driver/mysql#257 (comment)

@lvzhihao
Copy link

lvzhihao commented Aug 22, 2018 via email

@wangxf1987
Copy link

我的解决办法如下：
数据库做了lb，用nginx做的，策略如下：
`stream {
upstream galera_cluster {
server 172.19.146.xx:3306;
server 172.19.146.xx:3306;
server 172.19.146.xx:3306;

    zone tcp_mem 64k;
    zone tcp_backend 64k;
    zone tcp_servers 64k;
    least_conn;
}
server {
    listen       33306;
    proxy_pass   galera_cluster;
    proxy_timeout 300s;
    proxy_connect_timeout 300s;
}

}`

代码里，初始化连接时，设置：
db.DB().SetConnMaxLifetime(5 * time.Second)

再次回测，暂时未发现问题复现，持续跟踪中。。。。。。

@vmstar
Copy link

vmstar commented Aug 2, 2019

有结论么

1 similar comment
@m9rco
Copy link

m9rco commented Nov 6, 2019

有结论么

@lz1988
Copy link
Author

lz1988 commented Nov 10, 2019 via email

@m9rco
Copy link

m9rco commented Nov 11, 2019

我通过重连解决连该问题，虽然不是很好 。。。

@lz1988
Copy link
Author

lz1988 commented Nov 12, 2019 via email

@Awezome
Copy link

Awezome commented Feb 23, 2020 •
edited
Loading

很是诡异，把SetConnMaxLifetime设置了小于wait_timeout的时间，且SetMaxIdleConns设为0，线上还是出现了一个 invalid connection 错误日志，出错地方使用了事务。

db.DB().SetConnMaxLifetime(3 * time.Second)
db.DB().SetMaxIdleConns(0)

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 10    |
+---------------+-------+

return db.Transaction(func(tx *gorm.DB) error {
    updateRaw := tx.Table("xxxx").Where("xxxx").Update(data)
        if updateRaw.Error != nil {
	   return fmt.Errorf("tx update error : %w", updateRaw.Error)   //此处报错
        }
}

@lz1988
Copy link
Author

lz1988 commented Feb 24, 2020 via email

@Awezome
Copy link

Awezome commented Feb 26, 2020

试试这样，目前没有出现这个错误信息 #最大空闲连接数 maxidleconns = 500 # 最大连接数 maxopenconns = 500 # 最大生存时间 maxlifetime = 0
…
------------------ 原始邮件 ------------------ 发件人: "Raymon Zhang"<notifications@github.com>; 发送时间: 2020年2月23日(星期天) 中午12:57 收件人: "jinzhu/gorm"<gorm@noreply.github.com>; 抄送: "513245459"<513245459@qq.com>;"Author"<author@noreply.github.com>; 主题: Re: [jinzhu/gorm] gorm使用，连接db报错 (#1822) 很是诡异，把SetConnMaxLifetime设置了小于wait_timeout的时间，且SetMaxIdleConns设为0，线上还是出现了一个 invalid connection 错误日志。 db.DB().SetConnMaxLifetime(3 * time.Second) db.DB().SetMaxIdleConns(0) mysql> show variables like 'wait_timeout'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 10 | +---------------+-------+ — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe.

SetConnMaxLifetime 也设置成0？0就是不过期吧

@shallowclouds
Copy link

go-sql-driver 已经在 1.5.0 （go-sql-driver/mysql#934）版本修复了这个问题，但是gorm对这个的包装依然使用的是 1.4.1 版本，大家可以尝试自己导入最新的 go-sql-driver ，不使用 gorm 预先包装的 dialect（其实这个包装也就是帮你导入了go-sql-driver）。

import _ "github.com/go-sql-driver/mysql"

@GLYASAI GLYASAI mentioned this issue Apr 10, 2020
Merged
@donng
Copy link

donng commented Apr 16, 2020 •
edited
Loading

更新一下 go-sql-driver 包就可以了，导入其实不需要动，使用了 gorm 就尽量保证一致性嘛。gorm 已经在 master 分支更新了 go-sql-driver 版本，只是还没有打 tag。

go get -u github.com/go-sql-driver/mysql

@github-actions
Copy link

github-actions bot commented Jul 9, 2020

This issue will be automatically closed because it is marked as GORM V1 issue, we have released the public testing GORM V2 release and its documents https://v2.gorm.io/docs/ already, the testing release has been used in some production services for a while, and going to release the final version in following weeks, we are still actively collecting feedback before it, please open a new issue for any suggestion or problem, thank you

Also check out https://github.com/go-gorm/gorm/wiki/GORM-V2-Release-Note-Draft for how to use the public testing version and its changelog

@github-actions github-actions bot closed this as completed Jul 9, 2020
@nik-hil
Copy link

nik-hil commented Jan 25, 2023 •
edited
Loading

Faced the same issue in gorm V2. I m updating/inserting over 250k records in 4 min. These values worked for me.

	sqlDB.SetMaxIdleConns(50)
	sqlDB.SetMaxOpenConns(50)
	sqlDB.SetConnMaxLifetime(time.Minute * 2)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
status:gorm_v1
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests