MySQL Bugs: #15584: Server can not tell clients about generated keys

Bug #15584	Server can not tell clients about generated keys
Submitted:	8 Dec 2005 12:25	Modified:	18 Jun 2013 12:59
Reporter:	David Newcomb (OCA)	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
On a k,v table (described in #15445) if you do:
 replace into ('val1'),('val2'),('val3');
The generated keys are 1,2,3

If you do:
 replace into (null,'val_g1'), (3,'val3'),(2,'val2'),(null,'val_g2');
I would execpt the generated keys to be 4,3,2,5 to behave in the same way as an insert would. The specifed key numbers have been previously generated by the database so the clients would not make up future keys.

This way the client code would be the same for group inserts and group replaces.

How to repeat:
See bug #15445

Hi David!

Thank you for the feature request, and for your help in making MySQL even better!

Can you please clarify what you would like to see in this example:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.1-m11 |
+-----------+
1 row in set (0.00 sec)

mysql> show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `name` varchar(500) DEFAULT NULL,
  `value` varchar(500) DEFAULT NULL,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> replace into foo (value) values ('val1'),('val2'),('val3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+------+-------+----+
| name | value | id |
+------+-------+----+
| NULL | val1  |  1 |
| NULL | val2  |  2 |
| NULL | val3  |  3 |
+------+-------+----+
3 rows in set (0.00 sec)

mysql> replace into foo (id, value) values (null,'val_g1'), (3,'val3'),(2,'val2'),(null,'val_g2');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+------+--------+----+
| name | value  | id |
+------+--------+----+
| NULL | val1   |  1 |
| NULL | val2   |  2 |
| NULL | val3   |  3 |
| NULL | val_g1 |  4 |
| NULL | val_g2 |  5 |
+------+--------+----+
5 rows in set (0.00 sec)

Thanks!

Did you mean to create the table with "AUTO_INCREMENT=8"? If you did then I would expect "replace into foo (value) values ('val1'),('val2'),('val3');" to create the rows:

mysql> select * from foo;
+------+-------+----+
| name | value | id |
+------+-------+----+
| NULL | val1  |  8 |
| NULL | val2  |  9 |
| NULL | val3  | 10 |
+------+-------+----+
3 rows in set (0.00 sec)

and the "replace into foo (id, value) values (null,'val_g1'), (3,'val3'),(2,'val2'),(null,'val_g2');" to produce contents of:

mysql> select * from foo;
+------+--------+----+
| name | value  | id |
+------+--------+----+
| NULL | val1   |  8 |
| NULL | val2   |  9 |
| NULL | val3   | 10 |
| NULL | val_g1 | 11 |
| NULL | val3   |  3 |
| NULL | val2   |  2 |
| NULL | val_g2 | 12 |
+------+--------+----+

The first 3 records added carry on from auto_increment = 8 so 8,9,10 are automatically assigned.
The next replace uses null, so assign next auto_increment number 11. "(3,'val3')" and "(2,'val2')" don't have existing keys and so will be treated as inserts then "(null,'val_g2')" will pick the next auto_increment number of 12.

I think the original feature request was to help with JDBC's Statement.getGeneratedKeys() which returns a ResultSet of generated keys so the application programmer can match bulk insert's (or bulk replace's) with their corresponding record ids. Otherwise the application programmer has to do the inserts individually and then read back the last_insert_id, which is a significantly heavier operation when you have to do lots of them.

I think currently the last_insert_id only returns the last insert id which is a bit useless if you have just inserted 25 new records as the ids might not be consecutive.

I'm sorry for the confusion. The SHOW CREATE TABLE output was actually from *after* I had run the other statements, that's where the auto increment value of 8 came from.

Here's what I get when running them again (this time they are ordered here just as I ran them):

ysql> show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `name` varchar(500) DEFAULT NULL,
  `value` varchar(500) DEFAULT NULL,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> replace into foo (value) values ('val1'),('val2'),('val3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+------+--------+----+
| name | value  | id |
+------+--------+----+
| NULL | val1   |  1 |
| NULL | val2   |  2 |
| NULL | val3   |  3 |
| NULL | val_g1 |  4 |
| NULL | val_g2 |  5 |
| NULL | val1   |  8 |
| NULL | val2   |  9 |
| NULL | val3   | 10 |
+------+--------+----+
8 rows in set (0.00 sec)

mysql> replace into foo (id, value) values (null,'val_g1'),
    -> (3,'val3'),(2,'val2'),(null,'val_g2');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+------+--------+----+
| name | value  | id |
+------+--------+----+
| NULL | val1   |  1 |
| NULL | val2   |  2 |
| NULL | val3   |  3 |
| NULL | val_g1 |  4 |
| NULL | val_g2 |  5 |
| NULL | val1   |  8 |
| NULL | val2   |  9 |
| NULL | val3   | 10 |
| NULL | val_g1 | 11 |
| NULL | val_g2 | 12 |
+------+--------+----+
10 rows in set (0.00 sec)

Can you help me understand which part of that you would like to see changed in 5.7? It seems that this behavior is now what you wanted, correct? If not, please let me know.

Thanks again!

"Here's what I get when running them again (this time they are ordered here just as I ran them):"

I'm sorry that doesn't make any sense. According to your last post, you create a table AI is still 8, replace 3 records and now you have 8 records in the table.

Now I am even more confused. Perhaps you could create your session again from table create through to question.

OK :)

Here you go:

mysql> truncate table foo;
Query OK, 0 rows affected (0.09 sec)

mysql> show create table foo;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| foo   | CREATE TABLE `foo` (
  `name` varchar(500) DEFAULT NULL,
  `value` varchar(500) DEFAULT NULL,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> replace into foo (value) values ('val1'),('val2'),('val3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+------+-------+----+
| name | value | id |
+------+-------+----+
| NULL | val1  |  1 |
| NULL | val2  |  2 |
| NULL | val3  |  3 |
+------+-------+----+
3 rows in set (0.00 sec)

mysql> replace into foo (id, value) values (null,'val_g1'),
    -> (3,'val3'),(2,'val2'),(null,'val_g2');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+------+--------+----+
| name | value  | id |
+------+--------+----+
| NULL | val1   |  1 |
| NULL | val2   |  2 |
| NULL | val3   |  3 |
| NULL | val_g1 |  4 |
| NULL | val_g2 |  5 |
+------+--------+----+
5 rows in set (0.00 sec)

Yes that works as expected and I believe it has always worked like that.
But this bug report is *not* about that! This report is "Server can not tell clients about generated keys"

#15445 is marked as "Unsupported" and this Feature request is to ask that you support it.