flike / kingshard Goto Github PK
View Code? Open in Web Editor NEWA high-performance MySQL proxy
A high-performance MySQL proxy
unsupport sql will send to default node
Here is the testcase for sqlparser/sql_test.go
bitMask := 1 << 32
sql := fmt.Sprintf(`SELECT DISTINCT f_id FROM t_project WHERE NOT f_type & %d > 0 LIMIT 20 OFFSET 20`, bitMask)
testParse(t, sql)
First thank you for such a wonderful piece of software. I would like to know, is it production ready?
It would be helpful for me and many others if you could provide some benchmarks as well.
How good it is compared to Atlas and Fabric?
[[email protected]]-[test]>select * from test_shard_range where 1 = 1;
+----+------+------+-------+-----+------+
| id | str | f | e | u | i |
+----+------+------+-------+-----+------+
| 1 | abc1 | 3.14 | test1 | 255 | -127 |
| 2 | abc2 | 3.14 | test2 | 255 | -127 |
| 3 | abc3 | 3.14 | test3 | 255 | -127 |
| 4 | abc4 | 3.14 | test4 | 255 | -127 |
| 5 | abc5 | 3.14 | test5 | 255 | -127 |
| 6 | abc6 | 3.14 | test6 | 255 | -127 |
| 7 | abc7 | 3.14 | test7 | 255 | -127 |
+----+------+------+-------+-----+------+
7 rows in set (0.01 sec)
[[email protected]]-[test]>select id, count() from test_shard_hash where 1 = 1 group by id;
+----+----------+
| id | count() |
+----+----------+
| 1 | 7 |
| 2 | 7 |
| 3 | 7 |
| 4 | 7 |
| 5 | 7 |
| 6 | 7 |
| 7 | 7 |
+----+----------+
7 rows in set (0.02 sec)
[[email protected]]-[test]>select id, count() from test_shard_hash_0000 where 1 = 1 group by id;
+----+----------+
| id | count() |
+----+----------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
+----+----------+
7 rows in set (0.01 sec)
ks不支持跨库事务
当批量insert的时候比如500条记录,根据分片规则,
500条记录可能进入不同的node,如果node跨库,
这个时候就会导致不支持跨库事务的错误。
I write simple test golang code with xorm to connect kingshard, and run inserts and selects in range.
the ks yaml:
# server listen addr
addr : 0.0.0.0:9696
# server user and password
user : kingshard
password : kingshard
#if set log_path, the sql log will write into log_path/sql.log,the system log
#will write into log_path/sys.log
log_path : ./
# log level[debug|info|warn|error],default error
log_level : debug
#if set log_sql(on|off) off,the sql log will not output
#log_sql: off
#only log the query that take more than slow_log_time ms
#slow_log_time : 100
# only allow this ip list ip to connect kingshard
#allow_ips: 127.0.0.1
# node is an agenda for real remote mysql server.
nodes :
-
name : node1
# default max conns for mysql server
max_conns_limit : 100
# all mysql in a node must have the same user and password
user : root
password : fuxiaohei
# master represents a real mysql master server
master : 127.0.0.1:3306
# slave represents a real mysql salve server,and the number after '@' is
# read load weight of this slave.
slave : 127.0.0.1:3307@1
down_after_noalive : 32
# schema defines which db can be used by client and this db's sql will be executed in which nodes
schema :
db : kingshard_test1
nodes: [node1]
default: node1
shard:
-
table: locus
key: baby_id
nodes: [node1]
type: hash
locations: [4]
the db table:
CREATE TABLE `locus` (
`li_id` INT(11) NOT NULL AUTO_INCREMENT,
`baby_id` INT(11) NULL DEFAULT NULL,
`li_coordinates` VARCHAR(100) NOT NULL,
`li_bdmcoords` VARCHAR(100) NOT NULL,
`li_title` VARCHAR(50) NOT NULL,
`li_addtime` INT(11) NOT NULL,
`li_battery` INT(11) NOT NULL,
`li_start` INT(11) NOT NULL DEFAULT '0',
`li_end` INT(11) NOT NULL DEFAULT '0',
`li_type` TINYINT(4) NOT NULL,
`li_accuracy` SMALLINT(6) NOT NULL DEFAULT '50',
`li_dev_uuid` INT(11) NOT NULL DEFAULT '0',
`li_steps` INT(11) NOT NULL DEFAULT '0',
`li_runs` INT(11) NOT NULL DEFAULT '0',
`li_calory` INT(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`li_id`)
)
the simple golang code:
package main
import (
"fmt"
"math/rand"
_ "github.com/go-sql-driver/mysql"
"github.com/go-xorm/xorm"
)
func main() {
engine, err := xorm.NewEngine("mysql", "kingshard:kingshard@tcp(127.0.0.1:9696)/kingshard_test1")
fmt.Println(engine, err)
fmt.Println(engine.Ping())
sql := "INSERT INTO `locus` (`baby_id`, `li_coordinates`, `li_bdmcoords`, `li_title`, `li_addtime`, `li_battery`, `li_start`, `li_end`, `li_type`, `li_accuracy`, `li_dev_uuid`, `li_steps`, `li_runs`, `li_calory`) VALUES (%d, '24.4884,118.1823', '', '思明区望海路', 1447916575, 100, 1447916565, 1447916938, 5, 55, 25121, 0, 0, 0);"
mm := []int{}
func() {
for i := 0; i < 100; i++ {
m := rand.Intn(9999) + 1
_, err = engine.Exec(fmt.Sprintf(sql, m))
fmt.Println("insert", i, err)
mm = append(mm, m)
}
}() // maybe xorm close connection session with defer statement, so use function block, but seem useless.
sql = "SELECT li_id FROM locus WHERE baby_id = ? LIMIT 1"
func() {
for i, m := range mm {
_, err := engine.Query(sql, m)
fmt.Println("select", i, err)
}
}()
}
Then, start kingshard and run go code:
insert 70 <nil>
insert 71 <nil>
insert 72 <nil>
insert 73 <nil>
insert 74 <nil>
insert 75 <nil>
insert 76 <nil>
insert 77 <nil>
insert 78 Error 1105: parepre error 3 connection was bad
insert 79 Error 1105: parepre error 3 connection was bad
insert 80 <nil>
insert 81 <nil>
insert 82 <nil>
insert 83 Error 1105: parepre error 3 connection was bad
insert 84 Error 1105: parepre error 3 connection was bad
insert 85 <nil>
insert 86 <nil>
insert 87 <nil>
insert 88 <nil>
insert 89 Error 1105: parepre error 3 connection was bad
insert 90 <nil>
insert 91 <nil>
insert 92 <nil>
insert 93 <nil>
insert 94 Error 1105: parepre error 3 connection was bad
insert 95 Error 1105: parepre error 3 connection was bad
insert 96 <nil>
insert 97 <nil>
insert 98 <nil>
insert 99 Error 1105: parepre error 3 connection was bad
select 0 Error 1105: parepre error 3 connection was bad
select 1 <nil>
select 2 <nil>
select 3 <nil>
select 4 <nil>
select 5 <nil>
select 6 <nil>
select 7 <nil>
select 8 <nil>
select 9 <nil>
select 10 <nil>
select 11 <nil>
select 12 <nil>
select 13 <nil>
select 14 <nil>
select 15 <nil>
select 16 <nil>
select 17 <nil>
select 18 <nil>
select 19 <nil>
select 20 <nil>
select 21 <nil>
select 22 <nil>
select 23 <nil>
select 24 <nil>
select 25 <nil>
select 26 <nil>
select 27 <nil>
select 28 Error 1105: parepre error 3 connection was bad
select 29 Error 1105: parepre error 3 connection was bad
select 30 Error 1105: parepre error 3 connection was bad
select 31 Error 1105: parepre error 3 connection was bad
select 32 Error 1105: parepre error 3 connection was bad
select 33 Error 1105: parepre error 3 connection was bad
it blocks when select 33
.
就算new feature吧,proxy这块应该尽量能保持与原生sql语句的兼容。
I have add a function that is SQL sniffer for kingshard in my local branch. which should be treat as new feature , the sniffer will record all the SQL request, request time for the same SQL, result length, and error info.
it's help me a lot. it can find some unreasonable SQL requests and good for SQL performance optimize.
i just want to know whether it's reasonable to add it to kingshard. if you think so, i will give you the PR.
From golang wiki, import dot is not a good idiom except for test case.
Except for this one case, do not use import . in your programs. It makes the programs much harder to read because it is unclear whether a name like Quux is a top-level identifier in the current package or in an imported package.
前提:使用最新版本的ks
问题一: 我指定节点查询时,/node1/select * from table,可以读到node1节点的slave,如果不指定node1,(配置中default node是node1),则查询的是node1中的master
问题二 : 同一个节点内,master的地址不能配置到slave列表内,如果master的ip、port配知道slave,则启动时候报错
[main] "main" "not able to fi ll the pool" "" conn_id=0
error information
main.go:[80] - [main] "main" "not able to fill the pool
CalcPassword not match! detail:
2015/07/29 21:43:43 - INFO - server.go:[178] - [server] "NewServer" "Server running" "netProto=tcp|address=127.0.0.1:9696" conn_id=0
2015/07/29 21:43:50 - ERROR - conn.go:[221] - [ClientConn] "readHandshakeResponse" "error" "auth=[67 142 198 97 71 81 114 142 7 105 236 129 136 117 149 75 254 187 183 15]|checkAuth=[]|passworld=" conn_id=0
2015/07/29 21:43:50 - ERROR - conn.go:[96] - [server] "readHandshakeResponse" "ERROR 1045 (28000): Access denied for user '127.0.0.1:60394'@'kingshard' (using password: Yes)" "msg=read Handshake Response error" conn_id=10001
2015/07/29 21:43:50 - ERROR - server.go:[238] - [server] "onConn" "ERROR 1045 (28000): Access denied for user '127.0.0.1:60394'@'kingshard' (using password: Yes)" "" conn_id=0
目前的策略表名一定会被映射成tablename_####,但这个场景并不常见,绝大部分proxy工具也没有这么做,因为数据库的schema也需要维护开销,这样的做法会带给DBA更多的无趣的工作。在一个具体的database中把表名改成这样,我宁愿启动多个instance。
所以希望,
location中的数字是1的时候,使用yaml中配置的tablename,而不增加数字后缀。
或者增加一个选项可以忽略location,直接使用yaml中配置的tablename,而不增加数字后缀。
报错的详细信息如下:
go build -o bin/kingshard ./cmd/kingshard
load cmd/cgo: package cmd/cgo: open /home/work/.jumbo/lib/go/src/cmd/cgo: errno 530
make: *** [kingshard] Error 1
I've setup a ks to implement sharding:
3 nodes (pxc1, pxc2, pxc3) [don't pay attention to "pxc" these are standalone mysql]
the sharding configuration is the following:
schemas :
-
db : sbtest
nodes: [node1,node2,node3]
rules:
default: node1
shard:
-
table: sbtest1
key: k
type: hash
nodes: [node1, node2, node3]
locations: [1,1,1]
table_row_limit: 10000
I used "hash" (I was not able to use range).
So this is what's going on:
mysql> insert into sbtest1 values (1,1,'k was 1 at insert','ks');
Query OK, 1 row affected (0.01 sec)
mysql> insert into sbtest1 values (2,2,'k was 2 at insert','ks');
Query OK, 1 row affected (0.00 sec)
mysql> insert into sbtest1 values (3,3,'k was 3 at insert','ks');
Query OK, 1 row affected (0.00 sec)
mysql> insert into sbtest1 values (4,4,'k was 4 at insert','ks');
Query OK, 1 row affected (0.00 sec)
4 records added and in ks log we can see:
2015/10/14 09:00:59 - INFO - 192.168.56.6:40918->192.168.56.3:3306:insert into sbtest1_0001 values (1, 1, 'k was 1 at insert', 'ks')
2015/10/14 09:01:07 - INFO - 192.168.56.6:40918->192.168.56.4:3306:insert into sbtest1_0002 values (2, 2, 'k was 2 at insert', 'ks')
2015/10/14 09:01:12 - INFO - 192.168.56.6:40918->192.168.56.2:3306:insert into sbtest1_0000 values (3, 3, 'k was 3 at insert', 'ks')
2015/10/14 09:01:20 - INFO - 192.168.56.6:40918->192.168.56.3:3306:insert into sbtest1_0001 values (4, 4, 'k was 4 at insert', 'ks')
So far so good, and I can verify this on each shard directly:
pxc1 mysql> select * from sbtest1_0000;
+----+---+-------------------+-----+
| id | k | c | pad |
+----+---+-------------------+-----+
| 3 | 3 | k was 3 at insert | ks |
+----+---+-------------------+-----+
pxc2 mysql> select * from sbtest1_0001;
+----+---+-------------------+-----+
| id | k | c | pad |
+----+---+-------------------+-----+
| 1 | 1 | k was 1 at insert | ks |
| 4 | 4 | k was 4 at insert | ks |
+----+---+-------------------+-----+
pxc3 mysql> select * from sbtest1_0002;
+----+---+-------------------+-----+
| id | k | c | pad |
+----+---+-------------------+-----+
| 2 | 2 | k was 2 at insert | ks |
+----+---+-------------------+-----+
Now, let's insert again some records (a use twice the same key for id 6 & 7):
mysql> insert into sbtest1 values (4,4,'k was 4 at insert','ks');
Query OK, 1 row affected (0.00 sec)
mysql> insert into sbtest1 values (5,2,'k was 2 at insert','ks');
Query OK, 1 row affected (0.01 sec)
mysql> insert into sbtest1 values (6,1,'k was 1 at insert','ks');
Query OK, 1 row affected (0.00 sec)
mysql> insert into sbtest1 values (7,1,'k was 1 at insert','ks');
Query OK, 1 row affected (0.00 sec)
Nothing special, but in ks log:
2015/10/14 09:02:20 - INFO - 192.168.56.6:40918->192.168.56.4:3306:insert into sbtest1_0002 values (5, 2, 'k was 2 at insert', 'ks')
2015/10/14 09:02:33 - INFO - 192.168.56.6:40918->192.168.56.2:3306:insert into sbtest1_0000 values (6, 1, 'k was 1 at insert', 'ks')
2015/10/14 09:02:39 - INFO - 192.168.56.6:40918->192.168.56.3:3306:insert into sbtest1_0001 values (7, 1, 'k was 1 at insert', 'ks')
The last 2 entries go to a different shard ?!
Let's select all the records for k=1:
mysql> select * from sbtest1 where k=1;
+----+---+-------------------+-----+
| id | k | c | pad |
+----+---+-------------------+-----+
| 1 | 1 | k was 1 at insert | ks |
| 7 | 1 | k was 1 at insert | ks |
+----+---+-------------------+-----+
2 rows in set (0.00 sec)
Where is id 6 ?
Let's check on each shard directly:
pxc1 mysql> select * from sbtest1_0000;
+----+---+-------------------+-----+
| id | k | c | pad |
+----+---+-------------------+-----+
| 3 | 3 | k was 3 at insert | ks |
| 6 | 1 | k was 1 at insert | ks |
+----+---+-------------------+-----+
pxc2 mysql> select * from sbtest1_0001;
+----+---+-------------------+-----+
| id | k | c | pad |
+----+---+-------------------+-----+
| 1 | 1 | k was 1 at insert | ks |
| 4 | 4 | k was 4 at insert | ks |
| 7 | 1 | k was 1 at insert | ks |
+----+---+-------------------+-----+
pxc3 mysql> select * from sbtest1_0002;
+----+---+-------------------+-----+
| id | k | c | pad |
+----+---+-------------------+-----+
| 2 | 2 | k was 2 at insert | ks |
| 5 | 2 | k was 2 at insert | ks |
+----+---+-------------------+-----+
So that id 6 is indeed stored but in the wrong shard.
配置 addr : 127.0.0.1:8808 后
使用 mysqlclient 访问 10.46.127.52:8808 输入密码后,报错。错误码111
同样
addr : 10.46.127.52:8808 配置后,外部可以通过10.46.127.52连接成功了。
但是在本机使用 127.0.0.1 输入密码后同样是 111 的错误码
仅用一个节点做测试:
错误描述,where a.id=b.id应该等效内连接,ks有结果返回,mysql没有
/左表(分区)全部数据/
[[email protected]]-[test]>select * from test_shard_hash a where 1 = 1;
+----+------+------+-------+-----+------+
| id | str | f | e | u | i |
+----+------+------+-------+-----+------+
| 1 | abc1 | 3.14 | test1 | 255 | -127 |
| 2 | abc2 | 3.14 | test2 | 255 | -127 |
| 3 | abc3 | 3.14 | test3 | 255 | -127 |
| 4 | abc4 | 3.14 | test4 | 255 | -127 |
| 5 | abc5 | 3.14 | test5 | 255 | -127 |
+----+------+------+-------+-----+------+
5 rows in set (0.01 sec)
/右表全部数据/
[[email protected]]-[test]>select * from test_bug;
Empty set (0.01 sec)
/ks执行/
[[email protected]]-[test]>select * from test_shard_hash a,test_bug b where a.id = b.id;
+----+------+------+-------+-----+------+
| id | str | f | e | u | i |
+----+------+------+-------+-----+------+
| 1 | abc1 | 3.14 | test1 | 255 | -127 |
| 2 | abc2 | 3.14 | test2 | 255 | -127 |
| 3 | abc3 | 3.14 | test3 | 255 | -127 |
| 4 | abc4 | 3.14 | test4 | 255 | -127 |
| 5 | abc5 | 3.14 | test5 | 255 | -127 |
+----+------+------+-------+-----+------+
5 rows in set (0.01 sec)
/mysql执行/
[[email protected]]-[test]>select * from test_shard_hash_0000 a,test_bug b where a.id = b.id;
Empty set (0.01 sec)
Hi Team,
The package looks promising. I would like to get the architecture and usage docs in English.
from clause like this:
select table_name from information_schema.tables where table_schema = 'mysql' and table_type != 'VIEW'
report:
syntax error at position 49 near tables
ks.yaml配置
table: user
key: id
nodes: [node1, node2]
type: range
locations: [2,2]
本地,以及虚拟机mysql已经手动建好表user_0000,user_0001,user_0002,user_0003.
type:hash时候,代码运行没有报错。但是type:range报错:
2015/12/10 02:11:08 - OK - 0.2ms - 192.168.114.1:56657->127.0.0.1:3306:SET PROFILING = 1
2015/12/10 02:11:08 - OK - 0.2ms - 192.168.114.1:56657->127.0.0.1:3306:SET profiling_history_size = 0
2015/12/10 02:11:08 - OK - 0.1ms - 192.168.114.1:56657->127.0.0.1:3306:SET profiling_history_size = 15
2015/12/10 02:11:08 - OK - 1.4ms - 192.168.114.1:56657->127.0.0.1:3306:SHOW STATUS
2015/12/10 02:11:08 - ERROR - router.go:[293] - [Route] "BuildInsertPlan" "shard key not in key range" "" conn_id=0
2015/12/10 02:11:08 - ERROR - conn.go:[281] - [server] "Run" "shard key not in key range" "" conn_id=10001
myslq错误:
错误代码: 1105
shard key not in key range
time.Second * 30 unit is nano, Should be used time.Now().UnixNano()
感觉网络这块有些低效,考虑过把 vitess 底层连接 mysql 的cgo移植如何?
做成接口加载的方式,允许自由选择
use special database, but alway route to default database! detail:
2015/07/30 12:38:03 - INFO - 127.0.0.1:38894->127.0.0.1:3306:use shared
2015/07/30 12:38:10 - INFO - 127.0.0.1:38826->127.0.0.1:3306:select table_name from information_schema.tables where table_schema = 'shared' and table_type != 'VIEW'
2015/07/30 12:38:19 - ERROR - 127.0.0.1:38894->127.0.0.1:3306:select * from test_shard_hash
2015/07/30 12:38:19 - ERROR - conn_query.go:[416] - [ClientConn] "handleSelect" "ERROR 1146 (42S02): Table 'mysql.test_shard_hash' doesn't exist" "" conn_id=10002
2015/07/30 12:38:19 - ERROR - conn.go:[269] - [server] "Run" "ERROR 1146 (42S02): Table 'mysql.test_shard_hash' doesn't exist" "" conn_id=10002
2015/07/30 12:38:19 - INFO - 127.0.0.1:38894->127.0.0.1:3306:show warnings
2015/07/30 12:38:20 - INFO - 127.0.0.1:38894->127.0.0.1:3306:select database() as dbname
When I run a sql with LIMIT
, the below error will return:
2015/11/23 04:06:31 - ERROR - conn_stmt.go:[283] - [ClientConn] "handlePrepareSelect" "invalid limit limit v3" "" conn_id=11247
2015/11/23 04:06:31 - ERROR - conn.go:[281] - [server] "Run" "invalid limit limit v3" "" conn_id=11247
current ServerVersion is kingshard-1.0
change to 5.5.31-kingshard-1.0 will connect success!
这个看有人提过,不过已经close,但也没有实现,希望计划做的issue都标记todo,真正实现后再close,这样看issue列表比较方便,因为默认不显示已经close的ticket。
事务如何处理
I found a lot of log which is SELECT @@max_allowed_packet
. What's the meaning for that log?
2015/11/23 05:00:34 - OK - 2.1ms - 127.0.0.1:37793->rdsluy81s1v3599af9p3.mysql.rds.aliyuncs.com:3306:SELECT @@max_allowed_packet
2015/11/23 05:00:34 - OK - 6.5ms - 127.0.0.1:37788->rdsmmn7fziybqqn.mysql.rds.aliyuncs.com:3306:SELECT `f_id`, `f_title`, `f_desc`, `f_banner_url`, `f_participant_count`, `f_pic_count`, `f_city_map`, `f_last_update_time`, `f_is_banner_show`, `f_is_show`, `f_icon`, `f_remark`, `f_banner_list`, `f_create_time`, `f_update_time` FROM `t_post_topic` WHERE `f_id` = ?
When I run a select sql like above, it can' run on the slave server.
Is kingshard support readPool like amoeba?
log details
2015/11/08 17:29:23 - DEBUG - conn_query.go:[48] - [server] "sqlparser.Parse" "set names 'utf8mb4' collate 'utf8mb4_unicode_ci'" "" conn_id=0
2015/11/08 17:29:23 - DEBUG - conn_query.go:[48] - [server] "sqlparser.Parse" "set session sql_mode=''" "" conn_id=0
2015/11/08 17:29:23 - ERROR - conn_query.go:[51] - [server] "parse" "syntax error at position 21 near sql_mode" "hasHandled=false|sql=set session sql_mode=''" conn_id=0
2015/11/08 17:29:23 - ERROR - conn.go:[267] - [server] "Run" "syntax error at position 21 near sql_mode" "" conn_id=10001
谢谢
当insert到不存在的表,或其他sql语句导致出错后,ks将无法再连上。
提示master is down
目前ks启动时,如果配置的节点无法连接,则ks拒绝启动。建议:
ks进程正常启动,写错误日志(或者写等待节点上线,然后.....,就好象mysql启动一样),定时检查节点状态,当节点状态支持ks提供数据访问服务时提供proxy服务,否则对于所有的客户端连接给错误提示。
Use case:
The kingshard will connect to multiple slaves, the requests will go to the node with the minimum replication lag.
Also one question: does the proxy speak Mysql protocol? That is can I connect to the proxy like any other mysql server (from C program)?
If the functionality is not there, I would like to contribute. (Please provide the documentation in English)
大量代码涉嫌抄袭,还不标出源代码出处,悲哀!
ERROR - 192.168.7.7:34530->192.168.4.57:3306:select @@session.tx_read_only
怀疑是跟连接池处理有关。
2015/12/08 06:27:17 - ERROR - conn.go:[286] - [server] "Run" "connection was bad" "" conn_id=10004
2015/12/08 06:27:17 - ERROR - conn_query.go:[55] - [server] "preHandleShard" "connection was bad" "hasHandled=false" conn_id=0
2015/12/08 06:27:17 - ERROR - conn.go:[286] - [server] "Run" "connection was bad" "" conn_id=10004
2015/12/08 06:27:18 - ERROR - conn_query.go:[110] - [server] "getBackendConn" "ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)" "" conn_id=0
2015/12/08 06:27:18 - ERROR - conn_query.go:[55] - [server] "preHandleShard" "ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)" "hasHandled=false" conn_id=0
2015/12/08 06:27:18 - ERROR - conn.go:[286] - [server] "Run" "ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)" "" conn_id=10004
I got a lot of Can't create more than max_prepared_stmt_count statements
error when I use kingshard in my production environment.
conn.go:[281] - [server] "Run" "prepare error ERROR 1461 (42000): Can't create more than max_prepared_stmt_count statements (current value: 16382)" "" conn_id=11095
I wonder know does kingshard cache the stmt?
If it cached, How could I modify it's cache size?
新特性请求
2015/11/30 19:10:02 - ERROR - conn.go:[265] - [ClientConn] "Run" "runtime error: invalid memory address or nil pointer dereference" "stack=goroutine 1218 [running]:
github.com/flike/kingshard/proxy/server.(*ClientConn).Run.func1(0xc82019c540)
/home/www/sites/mysql_proxy/src/github.com/flike/kingshard/proxy/server/conn.go:261 +0x12d
github.com/flike/kingshard/proxy/server.(*ClientConn).writeResultset(0xc82019c540, 0xc820330002, 0x0, 0x0, 0x0)
/home/www/sites/mysql_proxy/src/github.com/flike/kingshard/proxy/server/conn_resultset.go:145 +0xe5
github.com/flike/kingshard/proxy/server.(*ClientConn).handlePrepareExec(0xc82019c540, 0x7ff2cf0a0038, 0xc8201be480, 0xc820538901, 0xf1, 0xc8200ced00, 0xd, 0xd, 0x0, 0x0)
/home/www/sites/mysql_proxy/src/github.com/flike/kingshard/proxy/server/conn_stmt.go:320 +0x3d7
github.com/flike/kingshard/proxy/server.(*ClientConn).handleStmtExecute(0xc82019c540, 0xc82019c6c1, 0xb2, 0xb2, 0x0, 0x0)
/home/www/sites/mysql_proxy/src/github.com/flike/kingshard/proxy/server/conn_stmt.go:242 +0xb80
github.com/flike/kingshard/proxy/server.(*ClientConn).dispatch(0xc82019c540, 0xc82019c6c1, 0xb2, 0xb2, 0x0, 0x0)
/home/www/sites/mysql_proxy/src/github.com/flike/kingshard/proxy/server/conn.go:316 +0x6d1
github.com/flike/kingshard/proxy/server.(*ClientConn).Run(0xc82019c540)
/home/www/sites/mysql_proxy/src/github.com/flike/kingshard/proxy/server/conn.go:278 +0xcd
github.com/flike/kingshard/proxy/server.(*Server).onConn(0xc820094510, 0x7ff2cf08cab8, 0xc82002a070)
/home/www/sites/mysql_proxy/src/github.com/flike/kingshard/proxy/server/server.go:249 +0x35a
created by github.com/flike/kingshard/proxy/server.(*Server).Run
/home/www/sites/mysql_proxy/src/github.com/flike/kingshard/proxy/server/server.go:262 +0x195
" conn_id=0
example like this:
mysql -h127.0.0.1 -P9696 -ukingshard -p
handshake successfully
mysql >show databases;
maybe you will get fault:
ERROR 1046 (3D000): No database selected
Error Log:
2015/12/05 14:36:08 - ERROR - conn_query.go:[55] - [server] "preHandleShard" "ERROR 1046 (3D000): No database selected" "hasHandled=false" conn_id=0
2015/12/05 14:36:08 - ERROR - conn.go:[281] - [server] "Run" "ERROR 1046 (3D000): No database selected" "" conn_id=10245
maybe need web interface.
Can't pass code snippet in sql_test.go
like below:
sql := "SELECT * FROM t_banner WHERE f_city_map & 8"
testParse(t, sql)
ks目前不支持所有的join语法
[[email protected]]-[test]>select * from test_shard_hash_0000 a inner join test_bug b on a.id = b.id;
Empty set (0.00 sec)
[[email protected]]-[test]>select a.* from test_shard_hash_0000 a left join test_bug b on a.id = b.id;
+----+------+------+-------+-----+------+
| id | str | f | e | u | i |
+----+------+------+-------+-----+------+
| 1 | abc1 | 3.14 | test1 | 255 | -127 |
| 2 | abc2 | 3.14 | test2 | 255 | -127 |
| 3 | abc3 | 3.14 | test3 | 255 | -127 |
| 4 | abc4 | 3.14 | test4 | 255 | -127 |
| 5 | abc5 | 3.14 | test5 | 255 | -127 |
+----+------+------+-------+-----+------+
5 rows in set (0.01 sec)
[[email protected]]-[test]>select a.* from test_shard_hash_0000 a right join test_bug b on a.id = b.id;
Empty set (0.01 sec)
[[email protected]]-[test]>select a.* from test_shard_hash_0000 a cross join test_bug b on a.id = b.id;
Empty set (0.00 sec)
[[email protected]]-[test]>select * from test_shard_hash a inner join test_bug b on a.id = b.id;
ERROR 1146 (42S02): Table 'test.test_shard_hash' doesn't exist
[[email protected]]-[test]>select * from test_shard_hash a left join test_bug b on a.id = b.id;
ERROR 1146 (42S02): Table 'test.test_shard_hash' doesn't exist
[[email protected]]-[test]>select * from test_shard_hash a right join test_bug b on a.id = b.id;
ERROR 1146 (42S02): Table 'test.test_shard_hash' doesn't exist
[[email protected]]-[test]>select * from test_shard_hash a cross join test_bug b on a.id = b.id;
ERROR 1146 (42S02): Table 'test.test_shard_hash' doesn't exist
[[email protected]]-[test]>
过了一个晚上没有连接kingshard,程序再连接会报connection was bad。必须重新启动kingshard才能程序正常运行
sql like "DELETE FROM sbtest1 WHERE id=2;" will go slave not master.
一下子搞不懂~
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.