What u have to take care of when using ShardingSphere-Proxy in your projects

[roodkcab]

Here's some problems we got when deploy ShardingSphere-Proxy in our projects. I'd like to share it with you, so that you can use this awesome software without those frustrating debug time I had. Hope you enjoy the journey.

1、DDL [solved]
The first problem we got is after a DDL, select returns nothing on that table. I restart ShardingSphere and everything went good. There must be something wrong. I did a quickly debug with tcpdump to see why does ShardingSphere return nothing. The problem is we use server side prepare and we didn't set ShardingSphere mode to cluster.

here is a diagram to illustrate our use case. As u can see, we set up a ShardingSphere cluster by inject it into each Pod of App running on K8S. Without mode set to cluster, the MetaData stored in memory wouldn't get flushed. For example we have a table with 3 columns. When using DDL to add a column, prepare statement will still return 3 columns, but 4 columns of data returned on execute statement, our database client didn't know how to handle this situation, and choose to return nothing.

and it's easy to fix this bug, just set up a etcd cluster and store all metadata in it by set mode cluster in server.yml

by the way if you use gh-ost to change database schema, it is safe if and only if (maybe) when ShardingSphere mode is cluster (I didn't give standalone mode a try, if you have, correct me), although nothing of metadata stored in etcd changed, server side prepare of select will get correct metadata. You'd better refresh metadata stored in etcd with distsql refresh table metadata $TableName manually.

2、last_insert_id
The second one is last_insert_id. MySQL manual recommend to generate a global increment id by update id_gen set max_id = last_insert_id(max_id + 1) where type = 1 and then use select last_insert_id() which is not ok when using ShardingSphere, since it has as connection pool, and you have to get last_insert_id from pdo connection directly. see 14144 for detail.

3、nested transaction with savepoint [solved]
We use Laravel, a most popular web develop framework in PHP world. Laravel can handle nested transaction using savepoint, but it will get an error. see 14145 for detail

4、XA transaction [solved]
ShardingSphere-Proxy can handle distribution transaction itself by just call begin, commit and rollback which is awesome. The problem is we have some old projects use bare XA transaction. But don't worry, much thanks to @zhfeng @jingshanglu @TeslaCN and many others for disucssing and developing this feature

5、timezone
We started shardingsphere in a container with UTC as default time zone, if u set it to GMT, make sure those timestamp fields in database is expected.

6、etcd limitation
since we use etcd to store our metadata, it has few limitation here that you MUST BE CAREFUL

• the first one is the size of etcd cluster.
  you can get current size of your etcd by etcdctl --write-out=table endpoint status, the default size limitation of etcd is 2GB, so don't forget to config auto rotation of etcd. If you happened to encounter database space exceeded exception on starting ShardingSphere, you have to compact data in etcd.

• the second one is the total number of table in all of schema configured to watch. see 14290 for detail.

[zhfeng]

Many thanks for your contribution to share this awsome use case ! I think it should be very helpful for using sharding-sphere in the real production environment.

[jingshanglu]

@menghaoranss Hey, can you see why metadata error ?

[menghaoranss]

@menghaoranss Hey, can you see why metadata error ?

I will follow up.