MySQL Bugs: #111560: A Prepare statement with cursor on will hang when executed after cursor closed

Bug #111560	A Prepare statement with cursor on will hang when executed after cursor closed
Submitted:	26 Jun 2023 9:33	Modified:	26 Jun 2023 13:37
Reporter:	Yongfeng Li	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	cursor, prepare statement

Description:
In 8.0.25, a prepare statement with cursor on will hang when executed after cursor closed.

But in 5.7.36, the same scenario will have expected result.

A unexpected network packets in 8.0.25：

0x00000000 (00000)   16000000 1673656c 65637420 2a206672   .....select * fr
0x00000010 (00016)   6f6d2074 6573742e 7431                om test.t1
get packet from server, session:0, datalen:88.

0x00000000 (00000)   0c000001 00010000 00020000 00000000   ................
0x00000010 (00016)   20000002 03646566 04746573 74027431    ....def.test.t1
0x00000020 (00032)   02743101 6101610c 3f000b00 00000303   .t1.a.a.?.......
0x00000030 (00048)   50000000 20000003 03646566 04746573   P... ....def.tes
0x00000040 (00064)   74027431 02743101 6201620c ff002800   t.t1.t1.b.b...(.
0x00000050 (00080)   0000fe00 00000000                     ........
get packet from client, session:0, datalen:14.

0x00000000 (00000)   0a000000 17010000 00010100 0000       ..............
get packet from server, session:0, datalen:88.

0x00000000 (00000)   01000001 02200000 02036465 66047465   ..... ....def.te
0x00000010 (00016)   73740274 31027431 01610161 0c3f000b   st.t1.t1.a.a.?..
0x00000020 (00032)   00000003 01100000 00200000 03036465   ......... ....de
0x00000030 (00048)   66047465 73740274 31027431 01620162   f.test.t1.t1.b.b
0x00000040 (00064)   0cff0028 000000fe 00000000 00070000   ...(............
0x00000050 (00080)   04fe0000 62000000                     ....b...
get packet from client, session:0, datalen:13.

0x00000000 (00000)   09000000 1c010000 00020000 00         .............
get packet from server, session:0, datalen:41.

0x00000000 (00000)   0b000001 00000100 00000431 3131610b   ...........111a.
0x00000010 (00016)   00000200 00020000 00043232 32620700   ..........222b..
0x00000020 (00032)   0003fe00 00420000 00                  .....B...
get packet from client, session:0, datalen:9.

0x00000000 (00000)   05000000 1a010000 00                  .........
get packet from server, session:0, datalen:11.

0x00000000 (00000)   07000001 00000002 000000              ...........
get packet from client, session:0, datalen:14.

0x00000000 (00000)   0a000000 17010000 00000100 0000       ..............
get packet from server, session:0, datalen:88.

0x00000000 (00000)   01000001 02200000 02036465 66047465   ..... ....def.te
0x00000010 (00016)   73740274 31027431 01610161 0c3f000b   st.t1.t1.a.a.?..
0x00000020 (00032)   00000003 01100000 00200000 03036465   ......... ....de
0x00000030 (00048)   66047465 73740274 31027431 01620162   f.test.t1.t1.b.b
0x00000040 (00064)   0cff0028 000000fe 00000000 00070000   ...(............
0x00000050 (00080)   04000000 22000000                     ...."...

the last COM_STMT_EXECUTE response is unexpected, which only has Column Definition, but no Resultset Row.

The right network packets in 5.7.36:

get packet from client, session:0, datalen:26.

0x00000000 (00000)   16000000 1673656c 65637420 2a206672   .....select * fr
0x00000010 (00016)   6f6d2074 6573742e 7431                om test.t1
get packet from server, session:0, datalen:88.

0x00000000 (00000)   0c000001 00010000 00020000 00000000   ................
0x00000010 (00016)   20000002 03646566 04746573 74027431    ....def.test.t1
0x00000020 (00032)   02743101 6101610c 3f000b00 00000303   .t1.a.a.?.......
0x00000030 (00048)   50000000 20000003 03646566 04746573   P... ....def.tes
0x00000040 (00064)   74027431 02743101 6201620c 2d002800   t.t1.t1.b.b.-.(.
0x00000050 (00080)   0000fe00 00000000                     ........
get packet from client, session:0, datalen:14.

0x00000000 (00000)   0a000000 17010000 00010100 0000       ..............
get packet from server, session:0, datalen:88.

0x00000000 (00000)   01000001 02200000 02036465 66047465   ..... ....def.te
0x00000010 (00016)   73740274 31027431 01610161 0c3f000b   st.t1.t1.a.a.?..
0x00000020 (00032)   00000003 01100000 00200000 03036465   ......... ....de
0x00000030 (00048)   66047465 73740274 31027431 01620162   f.test.t1.t1.b.b
0x00000040 (00064)   0c2d0028 000000fe 00000000 00070000   .-.(............
0x00000050 (00080)   04fe0000 62000000                     ....b...
get packet from client, session:0, datalen:13.

0x00000000 (00000)   09000000 1c010000 00020000 00         .............
get packet from server, session:0, datalen:41.

0x00000000 (00000)   0b000001 00000100 00000431 3131610b   ...........111a.
0x00000010 (00016)   00000200 00020000 00043232 32620700   ..........222b..
0x00000020 (00032)   0003fe00 00420000 00                  .....B...
get packet from client, session:0, datalen:9.

0x00000000 (00000)   05000000 1a010000 00                  .........
get packet from server, session:0, datalen:11.

0x00000000 (00000)   07000001 00000002 000000              ...........
get packet from client, session:0, datalen:14.

0x00000000 (00000)   0a000000 17010000 00000100 0000       ..............
get packet from server, session:0, datalen:148.

0x00000000 (00000)   01000001 02200000 02036465 66047465   ..... ....def.te
0x00000010 (00016)   73740274 31027431 01610161 0c3f000b   st.t1.t1.a.a.?..
0x00000020 (00032)   00000003 03500000 00200000 03036465   .....P... ....de
0x00000030 (00048)   66047465 73740274 31027431 01620162   f.test.t1.t1.b.b
0x00000040 (00064)   0c2d0028 000000fe 00000000 000b0000   .-.(............
0x00000050 (00080)   04000001 00000004 31313161 0b000005   ........111a....
0x00000060 (00096)   00000200 00000432 3232620b 00000600   .......222b.....
0x00000070 (00112)   00030000 00043333 33630b00 00070000   ......333c......
0x00000080 (00128)   04000000 04343434 64070000 08fe0000   .....444d.......
0x00000090 (00144)   22000000                              "...

the last packet is valid COM_STMT_EXECUTE response.

How to repeat:
Test scenario:

```
mysql_stmt_init()
mysql_stmt_prepare()  --- select ...

-- open cursor
unsigned long type = (unsigned long) CURSOR_TYPE_READ_ONLY;
mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (void *) &type);

-- set fetch rows
unsigned long prefetch_rows = 2;
mysql_stmt_attr_set(stmt, STMT_ATTR_PREFETCH_ROWS, (void *) &prefetch_rows);

mysql_stmt_execute()
while(1)
  mysql_stmt_fetch()  -- print rows

mysql_stmt_reset()

-- close cursor
unsigned long type = (unsigned long) CURSOR_TYPE_NO_CURSOR;
mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (void *) &type);

mysql_stmt_execute()     ---- happen exception
mysql_stmt_restore_result()
while(1)
   mysql_stmt_fetch()  -- print rows
```

A simple test program

Attachment: stmt-test.c (application/octet-stream, text), 5.61 KiB.

HI Mr. Li,

Thank you for your bug report.

We managed to repeat the behaviour that you have reported with latest release, 8.0.33:

  Row 1
    column1 (integer)  :  1
    column2 (string)   :  111a

  Row 2
    column1 (integer)  :  2
    column2 (string)   :  222b

and then the execution will hang on.

This is possibly a bug in the prepared statements. However, it is also possible that this is an intended change in 8.0, in which case this bug will become a documentation bug.

Verified.