Insert goes to the wrong shard and the data is not visible from the proxy

[lefred]

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.

[flike]

Your description is very clear. This is not a bug.
The value of the shard key must in the first location of values in kingshard. In your case, kingshard forward SQLs using id. If you want shard SQLs using k. You need write SQL like this 'insert into sbtest1(k,id,c,pad) values (4,40,'k was 4 at insert','ks');'