Comments (9)
看log提示权限有问题,还有就是connection was bad 的提示,表示这个连接失效了,是不是你隔离很久没有请求过来?
Ps:能不能给出更详细的信息,方便定位问题?
from kingshard.
是有很久没有连接进来。建议连接池定时执行select user();类似的命令,以确保所有链接池中的链接可用。目前ks日志就这么多。mysql服务正常,所以没有错误日志提供分析,系统也正常。ks也在设定的端口监听,只是不予许客户端连接。
from kingshard.
今天我会提交一个patch解决这个问题,谢谢反馈。
from kingshard.
数据库服务器有这些记录,我帖在下面。但ks启动后,肯定是没问题的,因为我是测试,所以没有具体的sql执行,一段时间后即发生。
ks在连接上后:
mysql -u kingshard -p -h 127.0.0.1 -P 3307 -c -D test
Logging to file '/opt/mysql/logs/query.log'
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 10003
Server version: 5.6.20-kingshard
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
[[email protected]]-[test]>show tables;
ERROR 1105 (HY000): connection was bad
[[email protected]]-[test]>show tables;
ERROR 1105 (HY000): connection was bad
退出后再次连接
mysql -u kingshard -p -h 127.0.0.1 -P 3307 -c -D test
Logging to file '/opt/mysql/logs/query.log'
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 10005
Server version: 5.6.20-kingshard
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
[[email protected]]-[test]>show tables;
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
退出后,稍后片刻再次连接:
mysql -u kingshard -p -h 127.0.0.1 -P 3307 -c -D test
Logging to file '/opt/mysql/logs/query.log'
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
mysqld.log中记录:
2015-12-08 23:01:13 139833937807104 [Warning] Access denied for user 'root'@'127.0.0.1' (using password: YES)
2015-12-08 23:01:13 139833938347776 [Warning] Access denied for user 'root'@'127.0.0.1' (using password: YES)
2015-12-08 23:01:22 139833938482944 [Warning] Access denied for user 'root'@'127.0.0.1' (using password: YES)
2015-12-08 23:01:23 139833938888448 [Warning] Access denied for user 'root'@'127.0.0.1' (using password: YES)
2015-12-08 23:01:23 139833938077440 [Warning] Access denied for user 'root'@'127.0.0.1' (using password: YES)
from kingshard.
已经提交了patch,请重新下载源码后,再试试。新的patch会清除失效的连接。记得反馈一下,谢谢。
from kingshard.
问题依然存在。群中提了一些建议
- 连接池增加一些属性,如连接创建时间、执行SQL次数、最近使用时间、空闲时间(可以根据最近使用算出来)、是否正在使用
- 增加连接池检测线程(如timer定时器之类),对于当前没有被使用的连接执行检测,策略可以是:
- 如果执行的SQL次数大于某一阈值,则直接销毁重建
- 否则执行如select user();、select @@Version;之类的命令,不能执行(即提示连接关闭)的销毁重建
- 以上可以通过异步销毁、执行命令,避免在连接检查的线程中操作
- 从连接池获取连接,如果执行用户SQL出现连接错,可以尝试多次重连,超过某一阈值,则标记node失效,如果是master则挂起服务,同时有定时器在后台继续检测
以上可以需要增加几个配置选项。
以上如果可以实现的话,可以顺带增加管理命令,如
admin server(opt,k,v)values('show','connections','config');
列出所有连接池连接的情况(id、创建时间、执行SQL次数,最后一次执行时间,空闲时长、节点等)
from kingshard.
空闲连接的问题,现在已经修复了一部分。你提到的方案考虑的很周到,但是在某些场景下,重建连接会导致数据不一致。所以目前的做法是取连接的时候,尽量保证连接的有效性,其他阶段发现连接失效,则直接报错,让客户端感知到。
看你提到的日志,应该是和用户名及权限相关,这是另一个问题了。
from kingshard.
Fixed
from kingshard.
Encountered the same problem: Error 1105: prepare error connection was bad
from kingshard.
Related Issues (20)
- kingshard支持2张以上的表关联吗?
- this maybe append nil db to n.Slave[]
- error
- 单独的select * from table_xxx for update 语句应该配置一个选项来决定是否走主库查询
- 求一个完整的Dockerfile HOT 1
- 请问各位 分库分表情况下 这种SQL语句能执行吗
- 还维护吗?好久没更新了 HOT 2
- 读区mysql package递归替换迭代是否更优
- 请问节点和子表的对应关系可以配置吗
- hash方式分表分库分表时,如果key 是一个int64的值,只会达到第一个节点的第一个表上,请问这个该怎么修改 HOT 1
- 云服务器ping时出错 HOT 2
- timeout设置问题.
- 4年不更新了 HOT 1
- 这个好像好久不更新了 HOT 2
- When does kingshard support mysql8.0? My company now uses mysql8.0. HOT 1
- kingshard QQ群还活跃吗? HOT 1
- 谁能把这项目救活?
- 在用kingshard的活跃用户在这里登记一下吧,咱们一起交流交流 HOT 2
- SQLSTATE[HY000]: General error: 1105 command *sqlparser.Set not supported now HOT 1
- 分表问题 HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from kingshard.