LAST_INSERT_ID() is wrong

yunmengmeng commented 2 years ago

I set two databases.The second is sharding database. When i use INSERT INTO .... SELECT LAST_INSERT_ID() AS ... to the first dabase,the tables's primary key type is auto-incrment,it saves in the first database,but when querying insert id,it's return value is wrong.Just retuns 0.

Which version of ShardingSphere did you use?

5.1.2

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

Expected behavior

LAST_INSERT_ID()

Actual behavior

0

Reason analyze (If you can)

It chooses the second database.

PartialSQLRouteExecutor

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

INSERT INTO tree1 (pid) VALUES (1); SELECT LAST_INSERT_ID() AS id;

yunmengmeng commented 2 years ago

now i use useGeneratedKeys="true" keyProperty="xxx" to replace SELECT LAST_INSERT_ID()

strongduanmu commented 2 years ago

Hi @yunmengmeng, thank you for your feedback. If you are using sharding feature, then I recommend using distributed id instead of mysql incrementing id.