apache-shardingsphere-4.1.0-sharding-proxy-bin

Hi @shihuizhen
What is the correct result you expected? And why did you think the result is incorrect?

@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.

@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.

@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?

@shihuizhen Do you use jdbc?

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;
}

@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!

@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.

@tristaZero Can we get lastInsertId by jdbc ps.getGeneratedKeys() in version 4?

like this:

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;
}

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.

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.

@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.

@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.

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.

@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.

I just close the issue because of no response anymore.