Bug #85105 INSERT with CURSOR_TYPE_READ_ONLY hangs
Submitted: 21 Feb 2017 14:37 Modified: 17 Jan 2018 3:10
Reporter: Snild Dolkow Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: C API (client library) Severity:S3 (Non-critical)
Version:5.7.17 OS:Linux
Assigned to: CPU Architecture:Any
Tags: hang

[21 Feb 2017 14:37] Snild Dolkow
When trying execute an INSERT prepared statement with CURSOR_TYPE_READ_ONLY set, libmysqlclient will
hang forever.

The python library I was using was automatically setting CURSOR_TYPE_READ_ONLY on all prepared
statements. I'm not sure how that's intended to work in conjunction with INSERTs, but I'd sure
prefer an error over a hang.

I will attach the source for a short program that demonstrates the bug (hang.c + a Makefile for convenience).

The callstack is:

#0  0x00007efca5e2d81d in __libc_recv (fd=fd@entry=3, buf=buf@entry=0x2543680, n=n@entry=16384, flags=flags@entry=0)
    at ../sysdeps/unix/sysv/linux/x86_64/recv.c:28
#1  0x00007efca60952c3 in recv (__flags=0, __n=16384, __buf=0x2543680, __fd=3)
    at /usr/include/x86_64-linux-gnu/bits/socket2.h:44
#2  inline_mysql_socket_recv (src_file=0x7efca617e390 "/home/snild/mysql/mysql-5.7/vio/viosocket.c", 
    src_line=123, flags=0, n=16384, buf=0x2543680, mysql_socket=...)
    at /home/snild/mysql/mysql-5.7/include/mysql/psi/mysql_socket.h:823
#3  vio_read (vio=vio@entry=0x24e9bd0, buf=0x2543680 "\a", size=size@entry=16384)
    at /home/snild/mysql/mysql-5.7/vio/viosocket.c:123
#4  0x00007efca6095345 in vio_read_buff (vio=0x24e9bd0, buf=0x256e670 "", size=4)
    at /home/snild/mysql/mysql-5.7/vio/viosocket.c:166
#5  0x00007efca6072178 in net_read_raw_loop (net=net@entry=0x24e7920, count=4)
    at /home/snild/mysql/mysql-5.7/sql/net_serv.cc:672
#6  0x00007efca6072417 in net_read_packet_header (net=0x24e7920)
    at /home/snild/mysql/mysql-5.7/sql/net_serv.cc:762
#7  net_read_packet (net=0x24e7920, complen=0x7ffcfdd20a80)
    at /home/snild/mysql/mysql-5.7/sql/net_serv.cc:822
#8  0x00007efca607319c in my_net_read (net=net@entry=0x24e7920)
    at /home/snild/mysql/mysql-5.7/sql/net_serv.cc:899
#9  0x00007efca60679b7 in cli_safe_read_with_ok (mysql=mysql@entry=0x24e7920, parse_ok=parse_ok@entry=0 '\000', 
    is_data_packet=is_data_packet@entry=0x7ffcfdd20b4f "")
    at /home/snild/mysql/mysql-5.7/sql-common/client.c:1040
#10 0x00007efca6067c7f in cli_safe_read (mysql=mysql@entry=0x24e7920, 
    is_data_packet=is_data_packet@entry=0x7ffcfdd20b4f "")
    at /home/snild/mysql/mysql-5.7/sql-common/client.c:1173
#11 0x00007efca60623fc in execute (stmt=stmt@entry=0x24b0d30, packet=packet@entry=0x2515e80 "", 
    length=length@entry=31) at /home/snild/mysql/mysql-5.7/libmysql/libmysql.c:2171
#12 0x00007efca6062f51 in cli_stmt_execute (stmt=0x24b0d30)
    at /home/snild/mysql/mysql-5.7/libmysql/libmysql.c:2283
#13 0x00007efca60648b2 in mysql_stmt_execute (stmt=stmt@entry=0x24b0d30)
    at /home/snild/mysql/mysql-5.7/libmysql/libmysql.c:2664

My environment:
 * Computer A, running Ubuntu 16.04.1 LTS, with libmysqlclient.so.20.3.4, and mysql server 5.7.17
 * Computer B, running Ubuntu 14.04.5 LTS, with libmysqlclient.so.18.0.0, and no mysql server
 * Both computers are connecting to the same server running on Computer A.

Computer A gets stuck down in recv() waiting for more data.
Computer B runs the program just fine.

 * My tcpdumps show that the server has sent the "OK" message fine in both cases.
 * gdb shows that Computer A receives the "OK" message, and returns all the way up to execute()
   before going back down into recv() again.
 * The hang only happens if the cursor type is set to CURSOR_TYPE_READ_ONLY, and looks related to
   the CURSOR_TYPE_READ_ONLY check in execute() in libmysql.c

How to repeat:
    const char* sql = "INSERT IGNORE INTO `device` SET `serial`=?, `pretty`=?";
    mysql_stmt_prepare(stmt, sql, strlen(sql));
    [excluded for brevity: bind the params]
    unsigned long type = CURSOR_TYPE_READ_ONLY;
    mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, &type);
[21 Feb 2017 14:38] Snild Dolkow
a small program that demonstrates the bug

Attachment: hang.c (text/x-csrc), 3.15 KiB.

[21 Feb 2017 14:39] Snild Dolkow
convenient makefile for hang.c

Attachment: Makefile (application/octet-stream, text), 109 bytes.

[10 Mar 2017 10:03] Chiranjeevi Battula
Hello Snild Dolkow,

Thank you for the bug report and test case.
Verified this behavior as described.

[17 Jan 2018 3:10] Paul DuBois
Posted by developer:
Fixed in 5.7.22, 8.0.5, 9.0.0.

Using the C API, when trying to execute an INSERT prepared statement
with CURSOR_TYPE_READ_ONLY set, the client hung.