[MDEV-4919] Packets out of order on a SELECT after calling a procedure with DELETE .. RETURNING Created: 2013-08-18  Updated: 2013-08-22  Resolved: 2013-08-22

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: 10.0.5

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-3814 Implement DELETE with result set (mwl... Closed

 Description    

CREATE TABLE t1 (i INT);
 
INSERT INTO t1 VALUES (1),(2);
 
--delimiter |
 
CREATE PROCEDURE p (a INT) 
BEGIN
 
  DELETE FROM t1 WHERE i = a RETURNING *;
 
  INSERT INTO t1 VALUES (a);
 
END |
 
--delimiter ;
 
 
 
CALL p(1);
 
SELECT * FROM t1;
 

MariaDB [test]> CALL p(1);
 
+------+
 
| i    |
 
+------+
 
|    1 |
 
+------+
 
1 row in set (0.03 sec)
 
 
 
MariaDB [test]> SELECT * FROM t1;
 
Error: Packets out of order (Found: 6, expected 1)
 
ERROR 2013 (HY000): Lost connection to MySQL server during query
 
MariaDB [test]>

The "Lost connection ..." error does not signify a server crash, only that the client got disconnected:

ERROR 2013 (HY000): Lost connection to MySQL server during query
 
MariaDB [test]> SELECT * FROM t1;
 
ERROR 2006 (HY000): MySQL server has gone away
 
No connection. Trying to reconnect...
 
Connection id:    7
 
Current database: test
 
 
 
+------+
 
| i    |
 
+------+
 
|    2 |
 
|    1 |
 
+------+
 
2 rows in set (0.03 sec)

bzr version-info

revision-id: knielsen@knielsen-hq.org-20130816131025-etjrvmfvupsjzq83
 
revno: 3674
 
branch-nick: 10.0-base



 Comments   
Comment by Sergei Petrunia [ 2013-08-21 ]

I have created a stored procedure P2() which is the same like the procedure P in the testcase, except that it has "SELECT * FROM t2" instead of DELETE ... RETURNING.

When I debug two statements, I observe these differences:

in mysql_execute_command(), sql_parse.cc:4354:
P2:
(gdb) p sp->m_flags
$32 = 8 // this is sp_head::MULTI_RESULTS

P:
(gdb) p sp->m_flags
$65 = 0

if sp_head::MULTI_RESULTS is set, then the code right below sets SERVER_MORE_RESULTS_EXISTS in thd->server_status.

This affects further execution. I've put breakpoints in relevant places:

P2:
Protocol::send_result_set_metadata flags=5
Protocol::send_eof (this=0x474ade0, server_status=2090, statement_warn_count=0)
(gdb) p/x server_status & 8
$60 = 0x8
Protocol::send_ok (this=0x474ade0, server_status=2082, statement_warn_count=0, affected_rows=1, last_insert_id=0, message=0x474f0ea "")

P:
Protocol::send_result_set_metadata (this=0x474ade0, list=0x7fff8c016980, flags=5)
Protocol::send_eof (this=0x474ade0, server_status=2082, statement_warn_count=0)
(gdb) p/x server_status & 8
$66 = 0x0
Protocol::send_ok (this=0x474ade0, server_status=2082, statement_warn_count=0, affected_rows=1, last_insert_id=0, message=0x474f0ea "")

Comment by Sergei Petrunia [ 2013-08-21 ]

This shows the error: when we run procedure P, we call send_eof() without SERVER_MORE_RESULTS_EXISTS. The client will get the EOF message and will not expect any further data. However, the server will later call send_ok(), which will cause client server protocol to go out of sync.

Comment by Sergei Petrunia [ 2013-08-21 ]

Committed a patch

Comment by Sergei Petrunia [ 2013-08-22 ]

Fix pushed to 10.0-base

Generated at Thu Feb 08 07:00:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.