Bug #84470 mysql_stmt_close hangs when the statement is canceled with KILL QUERY
Submitted: 11 Jan 2017 11:20 Modified: 24 Sep 2022 6:26
Reporter: Sebastien FLAESCH Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S1 (Critical)
Version:8.0.19 OS:Debian (11)
Assigned to: CPU Architecture:Any
Tags: KILL QUERY mysql_stmt_prepare mysql_stmt_close hangs

[11 Jan 2017 11:20] Sebastien FLAESCH
Description:
I want to interrupt a long running statement with CTRL-C by starting a new connect to make a KILL QUERY.

I am using the same technique as the mysql client code.

The difference here is that my code is using PREPARED STATEMENTS with mysql_stmt_prepare() etc.

Problem: After interrupting the first query with CTRL-C, the call to mysql_stmt_close() hangs...

Maybe I am missing some new connection or statement option...?!?

IMPORTANT: This problem appeared in a 5.7.x, same code is working fine with 5.6(.16)

TIP: Seems that the problem is related to the read-only cursor type option!
If you comment out:
    unsigned long ct = (unsigned long) CURSOR_TYPE_READ_ONLY;
    s = mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (const void *) &ct);
Then it works ...

How to repeat:
Compile and execute the C sample provided as attachment.
[11 Jan 2017 11:21] Sebastien FLAESCH
C sample to reproduce the problem

Attachment: kill_prepared_query.c (text/x-csrc), 3.47 KiB.

[11 Jan 2017 11:22] Sebastien FLAESCH
Second C sample, using mysql_query() instead of prepared statements (works)

Attachment: kill_query.c (text/x-csrc), 2.71 KiB.

[19 Jan 2017 10:20] Chiranjeevi Battula
Hello Sebastien,

Thank you for the bug report.
Could you please provide more details like does the program always hang or is it intermittent and full call stack for the program when it hangs (- please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.
[19 Jan 2017 19:47] Sebastien FLAESCH
Yes, the program hangs every time we try this.

No, I will not provide a stack trace:

Please look at the attachments, I have provided the source file kill_prepared_query.c to let you reproduce.

This is pure libmysqlclient API calls so it's all on your side.

Thanks for looking at this.
[20 Feb 2017 11:53] Chiranjeevi Battula
Hello Sebastien,

Thank you for your feedback.
Verified based on internal discussion with dev's.

Thanks,
Chiranjeevi.
[4 Sep 2017 8:34] Sebastien FLAESCH
Hello, any news on this?
[16 Oct 2017 9:16] Sebastien FLAESCH
Hello, this problem still exists with 8.0.3, any chance to fix before GA?
Seb
[23 Oct 2018 13:56] Sebastien FLAESCH
Hi all,

Just tested with version 8.0.13: The problem still exists...

Can you please have a look?

Thanks
Seb
[7 Feb 2019 8:44] Sebastien FLAESCH
Hi all,

Just tested with version 8.0.15: The problem still exists...

Can you please have a look?

Thanks
Seb
[19 Dec 2019 16:57] Sebastien FLAESCH
Hi all,

Just tested with version 8.0.18: The problem still exists...

Can you please consider this?

Thanks
Seb
[25 Mar 2020 14:04] Sebastien FLAESCH
Just tested with version 8.0.19: The problem still exists...

Can you please consider this?

Thanks
Seb
[31 Jul 2020 14:02] Sebastien FLAESCH
Changed Version information to see if this issue will now be taken into account
[6 Aug 2020 15:27] Sebastien FLAESCH
Please can someone have a look at this?

I did some more tests, and it appears that the problem also occurs when you kill the executing query from another process (mysql command interpreter), using the KILL QUERY command.

Just try this:

1) Restart the kill_prepared_query program
2) In another terminal, connect to the server with mysql comment tool
3) Identify the thread id for above program with "show processlist;"
4) Execute "kill query <thread-id>;"
5) See the status of the kill_prepared_query program: it hangs at mysql_stmt_close() ...

First I thought that when the KILL QUERY is executed by the same process, inside a SIGINT signal handler, it could confuse the MySQL client because it's not recommended to do complex code inside such signal handler...

However, my last test proves that it's NOT related to signal handlers.

Something is wrong with the MySQL protocol when a prepared query is killed.

Or, let me know what is the correct programming pattern for killed prepared queries!

Seb
[23 Jun 2022 5:34] Sebastien FLAESCH
Back to this issue.

Just installed a new Debian 11 with latest MySQL 8.0.29 version...

Now I get also the problem when I try to interrupt a query that is waiting for a lock...

Changing Severity to critical, is anyone reading this?

Seb
[19 Jul 2022 7:41] Georgi Kodinov
Thank you for your bug report. I am reading it. But we get quite a few error reports so it might take some time to get to actually fixing it. Please be patient.
[1 Aug 2022 14:48] Sebastien FLAESCH
Some news about the issue of an UPDATE waiting for a lock:
It appears that it's related to the usage of Unix sockets.
When using the TCP protocol, it works as expected...
However, the problem is still there with a long running query.
[23 Sep 2022 21:25] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL 8.0.32 release, and here's the proposed changelog entry from the documentation team:

The mysql_stmt_close() C API function could stop responding after a
prepared statement was canceled using KILL QUERY.

Thank you for the bug report.
[24 Sep 2022 6:26] Sebastien FLAESCH
Christine Cole,

I am not sure to understand the resolution of this bug.

You wrote that it gets fixed in 8.0.32, but the documentation change seems to describe the bug.

The fact that mysql_stmt_close() will no longer respond is one thing, that could be workaround, but the problem is that it's impossible for the application to prepare an new statement and continue working. So the only option for the application would be to disconnect and reconnect to the server? This is not acceptable to me.

Can you please clarify?