You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/10/26 12:26:25 UTC
[GitHub] [shardingsphere] shihuizhen opened a new issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
shihuizhen opened a new issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922
example:
table:
CREATE TABLE `t_order_item` (
`item_id` bigint(20) NOT NULL,
`order_id` bigint(20) unsigned DEFAULT NULL,
`user_id` int(11) NOT NULL,
`status` varchar(45) DEFAULT NULL,
`creation_date` date DEFAULT NULL,
PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
config_sharding:
shardingRule:
tables:
t_order:
actualDataNodes: ds_${0..5}.t_order_${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_${order_id % 2}
# keyGenerator:
# type: SNOWFLAKE
# column: order_id
t_order_item:
actualDataNodes: ds_${0..5}.t_order_item_${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_item_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: item_id
case1:
INSERT INTO t_order_item(order_id,user_id,status,creation_date) VALUES (2111,21,' init',' 2017-08-31');
select @@IDENTITY as insert_id from t_order_item;
result is not correct!
case2:
INSERT INTO t_order_item(order_id,user_id,status,creation_date) VALUES (2111,21,' init',' 2017-08-31');
SELECT LAST_INSERT_ID();
result is not correct!
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717668738
> @Lucas-307
>
> ```
> String sql = "INSERT INTO t_order_item(order_id,user_id,status,creation_date) VALUES (2112,21,' init',' 2017-08-31')";
> try (Connection conn = DriverManager.getConnection(url, username, password);
> PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> ResultSet rs = ps.getGeneratedKeys();
> return rs.next() ? rs.getLong(1) : -1;
> }
> ```
>
> result is -1
>
> connect MySQL directly:
> result is correct!
@shihuizhen I can get id by this function in `master` version, I'm **not** sure it is ok in v4.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717677454
Hi @shihuizhen @Lucas-307
I did not look through all the conversation between you. If `PreparedStatement` comes from `ShardingSphereConnection`, `ps.getGeneratedKeys` can work well for sharding tables. However, I have to say there is a bug when get generated keys after a batch insert SQL, which has been fixed in the forthcoming 5.x release.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 edited a comment on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
Lucas-307 edited a comment on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717669435
@tristaZero Can we get `lastInsertId` by jdbc `ps.getGeneratedKeys()` in version 4?
like this:
```java
try (Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys(); // get GeneratedKeys by SS SnowFlake or others.
return rs.next() ? rs.getLong(1) : -1;
}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 edited a comment on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
Lucas-307 edited a comment on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717668738
> @Lucas-307
>
> ```
> String sql = "INSERT INTO t_order_item(order_id,user_id,status,creation_date) VALUES (2112,21,' init',' 2017-08-31')";
> try (Connection conn = DriverManager.getConnection(url, username, password);
> PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> ResultSet rs = ps.getGeneratedKeys();
> return rs.next() ? rs.getLong(1) : -1;
> }
> ```
>
> result is -1
>
> connect MySQL directly:
> result is correct!
@shihuizhen I can get id by this function in `master` version, I'm **not** sure if it is ok in `v4`.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] kimmking commented on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-719427011
@shihuizhen
1. Statement.RETURN_GENERATED_KEYS and using Key-generator in 4.x works well, but without key-gen doesn't.
you can wait for 5.0.0 version, this week release.
2. @@identity or last_insert_id has no plan to support now.
you can try to fix it.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] shihuizhen commented on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
shihuizhen commented on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717184273
```
String sql = "INSERT INTO t_order_item(order_id,user_id,status,creation_date) VALUES (2112,21,' init',' 2017-08-31')";
try (Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
return rs.next() ? rs.getLong(1) : -1;
}
```
result is -1
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 edited a comment on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
Lucas-307 edited a comment on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717054071
@shihuizhen Actually, I tested this two sql (`select @@IDENTITY` or `SELECT LAST_INSERT_ID()`), the result not only depends on which datasource but also database did you use.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu commented on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-997433763
I just close the issue because of no response anymore.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] shihuizhen commented on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
shihuizhen commented on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717881178
> > @Lucas-307
> > ```
> > String sql = "INSERT INTO t_order_item(order_id,user_id,status,creation_date) VALUES (2112,21,' init',' 2017-08-31')";
> > try (Connection conn = DriverManager.getConnection(url, username, password);
> > PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
> > ps.executeUpdate();
> > ResultSet rs = ps.getGeneratedKeys();
> > return rs.next() ? rs.getLong(1) : -1;
> > }
> > ```
> >
> >
> > result is -1
> > connect MySQL directly:
> > result is correct!
>
> @shihuizhen I can get id by this function in `master` version, I'm **not** sure if it is ok in `v4`.
Which do you using for this test？ mysql-jdbc or sharding-jdbc？
mysql-jdbc connect to sharding-proxy_v4.1.1 is not OK.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717074707
@shihuizhen Do you use jdbc?
```java
try (Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
return rs.next() ? rs.getLong(1) : -1;
}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717669435
@tristaZero Can we get `lastInsertId` by jdbc `ps.getGeneratedKeys()` in version 4?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] shihuizhen edited a comment on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
shihuizhen edited a comment on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717059349
@Lucas-307 Thanks.
Is there any way to get id of new row?
When connect MySQL directly, I can get the new row's PRIMARY KEY like this:
```
CREATE TABLE t_order_item_not_sharding (
item_id bigint(20) NOT NULL AUTO_INCREMENT,
order_id bigint(20) unsigned DEFAULT NULL,
user_id int(11) NOT NULL,
status varchar(45) DEFAULT NULL,
creation_date date DEFAULT NULL,
PRIMARY KEY (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
```
```root@mysqldb 15:38: [youhua]> INSERT INTO t_order_item_not_sharding(order_id,user_id,status,creation_date) VALUES (2111,21,' init',' 2017-08-31');
Query OK, 1 row affected (0.00 sec)
root@mysqldb 15:39: [youhua]> select @@IDENTITY as insert_id from t_order_item_not_sharding;
+-----------+
| insert_id |
+-----------+
| 2 |
| 2 |
+-----------+
2 rows in set (0.00 sec)
root@mysqldb 15:39: [youhua]> select item_id from t_order_item_not_sharding order by item_id desc limit 1;
+---------+
| item_id |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)```
When use sharding-proxy how to?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu closed issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] shihuizhen edited a comment on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
shihuizhen edited a comment on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717184273
@Lucas-307
```
String sql = "INSERT INTO t_order_item(order_id,user_id,status,creation_date) VALUES (2112,21,' init',' 2017-08-31')";
try (Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
return rs.next() ? rs.getLong(1) : -1;
}
```
result is -1
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717054071
@shihuizhen Actually, I tested this two sql, the result not only depends on which datasource but also database did you use.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 edited a comment on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
Lucas-307 edited a comment on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-718322898
> Hi @shihuizhen @Lucas-307
> I did not look through all the conversation between you. If `PreparedStatement` comes from `ShardingSphereConnection`, `ps.getGeneratedKeys` can work well for sharding tables. However, I have to say there is a bug when get generated keys after a batch insert SQL, which has been fixed in the forthcoming 5.x release.
@shihuizhen As @shihuizhen said, there is a bug in v4.x, which has been fixed in the forthcoming 5.x release.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] shihuizhen edited a comment on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
shihuizhen edited a comment on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717184273
@Lucas-307
```
String sql = "INSERT INTO t_order_item(order_id,user_id,status,creation_date) VALUES (2112,21,' init',' 2017-08-31')";
try (Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
return rs.next() ? rs.getLong(1) : -1;
}
```
result is -1
connect MySQL directly:
result is correct!
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] shihuizhen commented on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
shihuizhen commented on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717059349
@Lucas-307 Thanks.
Is there any way to get id of new row?
When connect MySQL directly, I can get the new row's PRIMARY KEY like this:
```
CREATE TABLE t_order_item_not_sharding (
item_id bigint(20) NOT NULL AUTO_INCREMENT,
order_id bigint(20) unsigned DEFAULT NULL,
user_id int(11) NOT NULL,
status varchar(45) DEFAULT NULL,
creation_date date DEFAULT NULL,
PRIMARY KEY (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
```
`root@mysqldb 15:38: [youhua]> INSERT INTO t_order_item_not_sharding(order_id,user_id,status,creation_date) VALUES (2111,21,' init',' 2017-08-31');
Query OK, 1 row affected (0.00 sec)
root@mysqldb 15:39: [youhua]> select @@IDENTITY as insert_id from t_order_item_not_sharding;
+-----------+
| insert_id |
+-----------+
| 2 |
| 2 |
+-----------+
2 rows in set (0.00 sec)
root@mysqldb 15:39: [youhua]> select item_id from t_order_item_not_sharding order by item_id desc limit 1;
+---------+
| item_id |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)`
When use sharding-proxy how to?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-718322898
> Hi @shihuizhen @Lucas-307
> I did not look through all the conversation between you. If `PreparedStatement` comes from `ShardingSphereConnection`, `ps.getGeneratedKeys` can work well for sharding tables. However, I have to say there is a bug when get generated keys after a batch insert SQL, which has been fixed in the forthcoming 5.x release.
@shihuizhen As @shihuizhen said, there is a bug in v4.1.1, which has been fixed in the forthcoming 5.x release.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717051656
@shihuizhen HI, I don't think `select @@IDENTITY as insert_id from t_order_item;` is correct sql. Maybe you mean `select @@IDENTITY as insert_id;`
Foucs on this Question.
As we know, there are several actual datasources.
`INSERT INTO t_order_item(order_id,user_id,status,creation_date) VALUES (2111,21,' init',' 2017-08-31')` will insert into a specific datasource.
but `select @@IDENTITY` or `SELECT LAST_INSERT_ID()` will execute in a random datasource.(do not have sharding keys)
So, we can't make sure `select @@IDENTITY` or `SELECT LAST_INSERT_ID()` is what you want.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-716969263
Hi @shihuizhen
What is the correct result you expected? And why did you think the result is incorrect?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 edited a comment on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
Lucas-307 edited a comment on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-718322898
> Hi @shihuizhen @Lucas-307
> I did not look through all the conversation between you. If `PreparedStatement` comes from `ShardingSphereConnection`, `ps.getGeneratedKeys` can work well for sharding tables. However, I have to say there is a bug when get generated keys after a batch insert SQL, which has been fixed in the forthcoming 5.x release.
@shihuizhen As @tristaZero said, there is a bug in 4.x, which has been fixed in the forthcoming 5.x release.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-718322217
> > > @Lucas-307
> > > ```
> > > String sql = "INSERT INTO t_order_item(order_id,user_id,status,creation_date) VALUES (2112,21,' init',' 2017-08-31')";
> > > try (Connection conn = DriverManager.getConnection(url, username, password);
> > > PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
> > > ps.executeUpdate();
> > > ResultSet rs = ps.getGeneratedKeys();
> > > return rs.next() ? rs.getLong(1) : -1;
> > > }
> > > ```
> > >
> > >
> > > result is -1
> > > connect MySQL directly:
> > > result is correct!
> >
> >
> > @shihuizhen I can get id by this function in `master` version, I'm **not** sure if it is ok in `v4`.
>
> Which do you using for this test？ mysql-jdbc or sharding-jdbc？
> mysql-jdbc connect to sharding-proxy_v4.1.1 is not OK.
sharding-jdbc master version.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717725692
> Hi @shihuizhen @Lucas-307
> I did not look through all the conversation between you. If `PreparedStatement` comes from `ShardingSphereConnection`, `ps.getGeneratedKeys` can work well for sharding tables. However, I have to say there is a bug when get generated keys after a batch insert SQL, which has been fixed in the forthcoming 5.x release.
OK Thanks.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] shihuizhen commented on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
shihuizhen commented on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-716954297
apache-shardingsphere-4.1.0-sharding-proxy-bin
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] shihuizhen edited a comment on issue #7922: Sharding-proxy How to get the Distributed Primary Key after inserted a new row?
Posted by GitBox <gi...@apache.org>.
shihuizhen edited a comment on issue #7922:
URL: https://github.com/apache/shardingsphere/issues/7922#issuecomment-717059349
@Lucas-307 Thanks.
Is there any way to get id of new row?
When connect MySQL directly, I can get the new row's PRIMARY KEY like this:
```
CREATE TABLE t_order_item_not_sharding (
item_id bigint(20) NOT NULL AUTO_INCREMENT,
order_id bigint(20) unsigned DEFAULT NULL,
user_id int(11) NOT NULL,
status varchar(45) DEFAULT NULL,
creation_date date DEFAULT NULL,
PRIMARY KEY (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
```
```
root@mysqldb 15:38: [youhua]> INSERT INTO t_order_item_not_sharding(order_id,user_id,status,creation_date) VALUES (2111,21,' init',' 2017-08-31');
Query OK, 1 row affected (0.00 sec)
root@mysqldb 15:39: [youhua]> select @@IDENTITY as insert_id from t_order_item_not_sharding;
+-----------+
| insert_id |
+-----------+
| 2 |
| 2 |
+-----------+
2 rows in set (0.00 sec)
root@mysqldb 15:39: [youhua]> select item_id from t_order_item_not_sharding order by item_id desc limit 1;
+---------+
| item_id |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
```
When use sharding-proxy how to?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org