Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.5.23
-
None
Description
Given a simple autoincrement table:
CREATE TABLE InsertIdTest ( |
id INT NOT NULL AUTO_INCREMENT, |
one VARCHAR(45) NULL, |
PRIMARY KEY (id) |
)
|
an insert query returns the lastInsertId in the response package so it's accessible by clients:
INSERT INTO InsertIdTest (one) VALUES ("Hello World") |
but inserting data with a INSERT ... RETURNING doesn't return the lastInsertId:
INSERT INTO InsertIdTest (one) VALUES ("Bye Bye") RETURNING * |
Instead afterwards either the lastInsertId has to be extracted from the returned resultset, which not neccessarily must include the ID column, or an additional query to select the lastInsertId has to be executed.
compare PHP PDO client issue: https://github.com/php/php-src/issues/11503
Attachments
Issue Links
- is blocked by
-
MDEV-32102 Permit to have intermediate EOF in resultset
-
- Open
-
Activity
After CLIENT_DEPRECATE_EOF added, server still not populating Affected Rows or Last Insert ID In the OK packet:
Despire DEPRECATE_EOF in the client_flags, sql/sql_insert.cc +1334 is still sending EOF rather than OK inconsistent to https://mariadb.com/kb/en/result-set-packets/.
Thread 9 "mariadbd" hit Breakpoint 3.1, Diagnostics_area::set_eof_status (this=0x7f074c00ca10, thd=0x7f074c006eb8)
|
at /home/dan/repos/mariadb-server-10.5/sql/sql_error.cc:380
|
380 if (unlikely(is_error() || is_disabled()))
|
(gdb) bt
|
#0 Diagnostics_area::set_eof_status (this=0x7f074c00ca10, thd=0x7f074c006eb8) at /home/dan/repos/mariadb-server-10.5/sql/sql_error.cc:380
|
#1 0x00000000006b1918 in my_eof (thd=0x7f074c006eb8) at /home/dan/repos/mariadb-server-10.5/sql/sql_class.h:5388
|
#2 select_send::send_eof (this=0x7f074c012eb0) at /home/dan/repos/mariadb-server-10.5/sql/sql_class.cc:3179
|
#3 0x00000000006c6fdd in mysql_insert (thd=thd@entry=0x7f074c006eb8, table_list=0x7f074c0116b0,
|
fields=@0x7f074c00bdd8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f074c011f00, last = 0x7f074c011f00, elements = 1}, <No data fields>},
|
values_list=@0x7f074c00be20: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f074c0124a0, last = 0x7f074c0124a0, elements = 1}, <No data fields>},
|
update_fields=@0x7f074c00be08: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x16a2ef0 <end_of_list>, last = 0x7f074c00be08, elements = 0}, <No data fields>},
|
update_values=@0x7f074c00bdf0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x16a2ef0 <end_of_list>, last = 0x7f074c00bdf0, elements = 0}, <No data fields>}, duplic=DUP_ERROR, ignore=<optimized out>, result=0x7f074c012eb0) at /home/dan/repos/mariadb-server-10.5/sql/sql_insert.cc:1334
|
#4 0x00000000006fdd40 in mysql_execute_command (thd=thd@entry=0x7f074c006eb8) at /home/dan/repos/mariadb-server-10.5/sql/sql_parse.cc:4639
|
#5 0x00000000006f660e in mysql_parse (thd=thd@entry=0x7f074c006eb8,
|
rawbuf=0x7f074c011580 "INSERT INTO InsertIdTest (one) VALUES (\"Bye Bye\") RETURNING *", length=<optimized out>,
|
parser_state=parser_state@entry=0x7f07e808d5b0, is_com_multi=false, is_next_command=<optimized out>)
|
at /home/dan/repos/mariadb-server-10.5/sql/sql_parse.cc:8118
|
#6 0x00000000006f4951 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f074c006eb8, packet=packet@entry=0x7f074c0024c9 "",
|
packet_length=packet_length@entry=61, is_com_multi=false, is_next_command=false) at /home/dan/repos/mariadb-server-10.5/sql/sql_parse.cc:1891
|
#7 0x00000000006f69f2 in do_command (thd=0x7f074c006eb8) at /home/dan/repos/mariadb-server-10.5/sql/sql_parse.cc:1375
|
#8 0x00000000007e8e49 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x2a7c018, put_in_cache=true)
|
at /home/dan/repos/mariadb-server-10.5/sql/sql_connect.cc:1416
|
#9 0x00000000007e8cbb in handle_one_connection (arg=arg@entry=0x2a7c018) at /home/dan/repos/mariadb-server-10.5/sql/sql_connect.cc:1318
|
#10 0x0000000000aec5b6 in pfs_spawn_thread (arg=0x28a1a28) at /home/dan/repos/mariadb-server-10.5/storage/perfschema/pfs.cc:2201
|
#11 0x00007f07eaaae907 in start_thread () from /lib64/libc.so.6
|
#12 0x00007f07eab34870 in clone3 () from /lib64/libc.so.6
|
|
|
(gdb) p thd->client_capabilities & ( 1 << 24)
|
$2 = 16777216
|
Given what a complete anti-feature DEPRECATED_EOF is https://bugs.mysql.com/bug.php?id=100279, I doubt PHP would want to implement this.
Doesn't mean we shouldn't adhear to the protocol however.
Thanks georg
diff --git a/client/mysqltest.cc b/client/mysqltest.cc
|
index 69f241e8401..161c4f7abfd 100644
|
--- a/client/mysqltest.cc
|
+++ b/client/mysqltest.cc
|
@@ -7767,12 +7767,14 @@ void append_metadata(DYNAMIC_STRING *ds,
|
Append affected row count and other info to output
|
*/
|
|
-void append_info(DYNAMIC_STRING *ds, ulonglong affected_rows,
|
+void append_info(DYNAMIC_STRING *ds, ulonglong affected_rows, ulonglong last_insert_id,
|
const char *info)
|
{
|
char buf[40], buff2[21];
|
sprintf(buf,"affected rows: %s\n", llstr(affected_rows, buff2));
|
dynstr_append(ds, buf);
|
+ sprintf(buf,"last_insert_id: %s\n", llstr(last_insert_id, buff2));
|
+ dynstr_append(ds, buf);
|
if (info)
|
{
|
dynstr_append(ds, "info: ");
|
@@ -8071,7 +8073,7 @@ void run_query_normal(struct st_connection *cn, struct st_command *command,
|
query to find the warnings.
|
*/
|
if (!disable_info)
|
- append_info(ds, mysql_affected_rows(mysql), mysql_info(mysql));
|
+ append_info(ds, mysql_affected_rows(mysql), mysql_insert_id(mysql), mysql_info(mysql));
|
|
if (display_session_track_info)
|
append_session_track_info(ds, mysql);
|
@@ -8509,7 +8511,7 @@ void run_query_stmt(struct st_connection *cn, struct st_command *command,
|
otherwise.
|
*/
|
if (!disable_info)
|
- append_info(ds, mysql_stmt_affected_rows(stmt), mysql_info(mysql));
|
+ append_info(ds, mysql_stmt_affected_rows(stmt), mysql_stmt_insert_id(stmt), mysql_info(mysql));
|
|
if (display_session_track_info)
|
append_session_track_info(ds, mysql);
|
@@ -8868,7 +8870,7 @@ void run_execute_stmt(struct st_connection *cn, struct st_command *command,
|
otherwise.
|
*/
|
if (!disable_info)
|
- append_info(ds, mysql_stmt_affected_rows(stmt), mysql_info(mysql));
|
+ append_info(ds, mysql_stmt_affected_rows(stmt), mysql_stmt_insert_id(stmt), mysql_info(mysql));
|
|
if (display_session_track_info)
|
append_session_track_info(ds, mysql); |
With:
|
MDEV-31560.test |
CREATE TABLE t0 (
|
id INT NOT NULL AUTO_INCREMENT,
|
c1 VARCHAR(45) NULL,
|
PRIMARY KEY (id)
|
);
|
|
|
--enable_info
|
INSERT INTO t0 (c1) VALUES ("Hello World");
|
INSERT INTO t0 (c1) VALUES ("Hello World") RETURNING *;
|
--disable_info
|
DROP TABLE t0;
|
|
MDEV-31560.result |
CREATE TABLE t0 (
|
id INT NOT NULL AUTO_INCREMENT,
|
c1 VARCHAR(45) NULL,
|
PRIMARY KEY (id)
|
);
|
INSERT INTO t0 (c1) VALUES ("Hello World");
|
affected rows: 1
|
last_insert_id: 1
|
INSERT INTO t0 (c1) VALUES ("Hello World") RETURNING *;
|
id c1
|
2 Hello World
|
affected rows: 1
|
last_insert_id: 1
|
DROP TABLE t0;
|
expected last_insert_id 2. Or maybe 0 since it wasn't returned in the EOF packet.
Summary: It's a protocol issue, after INSERT the server sends an OK packet, which has a field for last insert id. After a result set it sends EOF packet that doesn't have a field for last insert id. The fix should be along the lines of CLIENT_DEPRECATE_EOF, sending OK instead of the EOF at the end of the result set. Needs to be coordinated with connectors
Attached is a packet capture using 8.2.9 and the PHP issue source. The interesting bit is the end:
We see the TEXT response packet followed by an EOF.
Looking at the protocol https://mariadb.com/kb/en/result-set-packets/
And EOF results from a missing CLIENT_DEPRECATE_EOF.
If that capability was there, an OK packet would result, and this OK Packet is the packet that has the last insert ID and rows affected.
If we look at the LOGIN REQUEST (packet 51), the Extended Capabilities has DEPRECATE_EOF not set.