我最近在mysql的slow log 发现很多这样的查询

Query_time: 1.199735 Lock_time: 0.000064 Rows_sent: 7777 Rows_examined: 15588 SET timestamp=1459002386; select item_id from t_user_like_0018 where u_id = 448237 and effective = 1 order by id desc;

而我们在代码里的查询语句是这样的： select item_id from t_user_like_0018 where u_id = 448237 and effective = 1 order by id desc limit 0,20;

很显然是kingshard的limit是自己处理的，这样的确是分表的情况下一种无奈的做法，但是就导致load了7777条结果

因为我们现在是通过u_id分表的，我不知道这种做法是否可行，在不垮节点的情况下（也就是查询条件里面带有分表字段的情况下），limit避免在kingshard这边完成

非常感谢！

可以将main.go对signal的处理改成如下：

sc := make(chan os.Signal, 1) signal.Notify(sc, syscall.SIGINT, syscall.SIGTERM, syscall.SIGQUIT, syscall.SIGPIPE)

go func() {
    for {
        select {
        case sig := <-sc:
            if sig == syscall.SIGINT || sig == syscall.SIGTERM || sig == syscall.SIGQUIT {
                golog.Info("main", "main", "Got signal", 0, "signal", sig)
                golog.GlobalSysLogger.Close()
                golog.GlobalSqlLogger.Close()
                svr.Close()
                monitor_svr.Close()
                return
            }

            if sig == syscall.SIGPIPE {
                golog.Info("main", "main", "Ignore broken pipe signal", 0)
            }
        }
    }
}()

一般来说，时间切分的自动可能不是时间类型，只是其中某部分包含时间，所以应该有参数可以指定字段时间格式化类型，所以在date_year、date_month、date_day三种类型中添加了一个配置参数，time_format，可以指定如何格式话时间，默认YYYY-mm-dd HH:MM:SS，Y代表年，m代表月，d代表天，H代表小时，M代表分钟，S代表秒，如果格式化参数中不是这几个字符，则跳过一个字符，同时年必须是4位，其他必须是两位。

发现有问题额 clone的最新的代码 kingshard的log日志 select search_hash.id, search_hash.info_hash, search_hash.category, search_hash.data_hash, search_hash.name, search_hash.extension, search_hash.classified, search_hash.source_ip, search_hash.tagged, search_hash.length, search_hash.create_time, search_hash.last_seen, search_hash.requests, search_hash.comment, search_hash.creator from search_hash_0003 where search_hash.id in (95982) 2016/05/13 00:45:58 - ERROR - conn_select.go:[125] - [ClientConn] "handleSelect" "ERROR 1054 (42S22): Unknown column 'search_hash.id' in 'field list'" "" conn_id=10144

原始sql应该是这样的 select search_hash.id, search_hash.info_hash, search_hash.category, search_hash.data_hash, search_hash.name, search_hash.extension, search_hash.classified, search_hash.source_ip, search_hash.tagged, search_hash.length, search_hash.create_time, search_hash.last_seen, search_hash.requests, search_hash.comment, search_hash.creator from search_hash where search_hash.id in (95982)

Hi,

SHOW FULL FIELDS and SHOW FULL COLUMNS doesn't work for me, i tried with and without USE DATABASE statement before. Tried different MySQL clients and still the same problem. I am using Yii Framework which needs those commands to cache table informations.

There was already another Issue about this problem but it didn't solve it and it is already closed. #180

regards

JDBC: 5.1.35 mysql: 5.6.22

sql: update TABLE set a=xx where b=xx;

直连mysql无论是否有字段更新都是返回1，不存在返回0 通过ks有字段更新返回1，没有更新和不存在的话返回0

请教下这个差异是JDBC的使用姿势不正确吗？还是正常现象？

环境如下： 系统版本 CentOS release 6.9 (Final) 2.6.32-696.3.1.el6.x86_64 php版本 PHP 5.6.9 php-mysql驱动版本 mysqlnd 5.0.11-dev - 20120503 kingshard版本 最新 go版本 go1.11.1

kingshard版本截图如下：

由于原来kingshard9月26日提交的版本出现连接无反应的问题问题，对kingshard升级至最新版。 升级后，连接问题得以解决，但出现了程序获取数据乱码现象

程序通过kingshard访问，获取数据乱码，截图如下：

程序直接连接mysql访问，获取数据正常，截图如下：

请问，这是怎么回事呢？

中间件运行多日后，会突然开始报错（如下），开始报错后分表查询功能失效，insert 功能正常。 重启中间件后报错消失，功能恢复正常

报错信息：

2017/10/12 10:17:55 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777262
2017/10/12 10:17:56 - ERROR - router.go:[414] - [Route] "BuildUpdatePlan" "plan have no criteria" "" conn_id=0
2017/10/12 10:17:56 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777263
2017/10/12 10:17:57 - ERROR - router.go:[414] - [Route] "BuildUpdatePlan" "plan have no criteria" "" conn_id=0
2017/10/12 10:17:57 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777264
2017/10/12 10:17:57 - ERROR - router.go:[414] - [Route] "BuildUpdatePlan" "plan have no criteria" "" conn_id=0
2017/10/12 10:17:57 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777265
2017/10/12 10:17:57 - ERROR - router.go:[414] - [Route] "BuildUpdatePlan" "plan have no criteria" "" conn_id=0
2017/10/12 10:17:57 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777266
2017/10/12 10:17:59 - ERROR - router.go:[414] - [Route] "BuildUpdatePlan" "plan have no criteria" "" conn_id=0
2017/10/12 10:17:59 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777267
2017/10/12 10:18:00 - ERROR - router.go:[414] - [Route] "BuildUpdatePlan" "plan have no criteria" "" conn_id=0
2017/10/12 10:18:00 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777268
2017/10/12 10:18:01 - ERROR - router.go:[414] - [Route] "BuildUpdatePlan" "plan have no criteria" "" conn_id=0
2017/10/12 10:18:01 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777269
2017/10/12 10:18:02 - ERROR - router.go:[414] - [Route] "BuildUpdatePlan" "plan have no criteria" "" conn_id=0
2017/10/12 10:18:02 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777271

配置信息

addr: 127.0.0.1
user: user
password: passwd

log_level : error
log_sql : off
slow_log_time : 100
log_path: /data/log/ks/

nodes:
-
    name: node1
    user: nodeuser
    password: nodepasswd
    master : 127.0.0.1:3306

schema:
    nodes: [node1]
    default: node1
    shard:
    -
        db: db1
        table: p_records
        key: created_at
        nodes: [node1]
        type: date_month
        date_range: [201705-202706]
    -
        db: db1
        table: r_records
        key: created_at
        nodes: [node1]
        type: date_month
        date_range: [201705-202706]

搭建kingshard集群，在3台kingshard机器前面挡一层LVS，但是LVS本身有机制会去定时探测比如9696端口是否正常，这是的协议肯定不是sql的这种协议，会导致一堆的"readHandshakeResponse" "connection was bad"，群主是否考虑在kingshard上面再开一个监听端口，用来给外围设备检测用的？

特性讨论

背景描述

为了实现多租户系统，并且为了在Mysql的物理连接中进行资源区分，现在我正在实现多用户特性。

配置文件参考

addr : 0.0.0.0:9696
users:
- 
 user: root
 password: root

-
 user: user_1
 password: pass_1

-
 user: user_2
 password: pass_2

-
 user: user_3
 password: pass_3

-
 user: user_4
 password: pass_4

log_level : debug
#log_sql: off
proxy_charset: utf8

nodes :
-
    name : node1
    max_conns_limit : 32
    users:
    -
      user: root
      password: root
    master : 10.211.55.3:3306

schema :
    db : 
    nodes: [node1]
    default: node1
    shard:
    -

实现方案

//新增一个UserPool来包裹backend.DB来实现用户级连接池
type UserPool struct {
    sync.RWMutex
    pools map[string]*DB
}

ps1： 以上方案已经初步实现。

ps2: 现在向大家征集需求，以及更好的实现方案。

怀疑是跟连接池处理有关。

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

当interactive_timeout和wait_timeout有任意一个值小于20时,kingshard的sys.log里会每隔几秒产生一条报错记录: ERROR - node.go:[104] - [Node] "checkMaster" "Ping" "db.Addr=127.0.0.1:3306|error=connection was bad" conn_id=0 但不影响连接mysql,也不影响执行sql语句.

只有两个值同时大于20时,才不会报这个错. 有哪个有经验的大哥能回答一下,在使用mysql proxy时,这两个值设多少合适? 谢谢

每个节点配置子表数量和与之对应和子表的映射关系，而非这种固定node1 :table_0000,table_0001，table_0002,table_0003 ，而是再保证子表后缀顺序连续（0-9999中任何一个数字都不缺少）的前提下，通过约束子表和节点关系（node1 :table_9999,table_8888，table_0001,table_****），通过配置来确定表和库的关系，这样后期调整节点和子表的关系，做完数据迁移，然后调整配置就可以了，并且也可以动态扩展新增子表