MySQL Bugs: #115321: query hangs in "query end" state too long after killing

Bug #115321	query hangs in "query end" state too long after killing
Submitted:	13 Jun 2024 14:05	Modified:	13 Jun 2024 15:32
Reporter:	Eugeny Nt	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.2.0	OS:	Windows (11)
Assigned to:		CPU Architecture:	Any
Tags:	innodb, KILL, temporary table

Description:
It's impossible to interrupt INSERT into an InnoDB temporary table with KILL QUERY if inserting 150 million records.

How to repeat:
I have some `tab` with 150 million records, with auto-increment Primary Key `id`.

In one session execute queries:
CREATE TEMPORARY TABLE t ENGINE=INNODB AS SELECT id FROM tab LIMIT 0;
INSERT INTO t SELECT id FROM tab;

After some time (for me it is 1 minute) run KILL QUERY ... from another session to abort that insertion.

First session gets state "query end" and hangs 10 minutes.

The same works perfectly with MYISAM engine.

Hi Mr. Nt,

Thank you for your bug report.

However, this is not a bug.

There are many places in InnoDB code, where the KILL flag is not checked at all, because killing query would result in destroyed integrity of the data. This is common in all transactional engines. MyISAM is NOT a transactional engine.

This is documented in several places, like:

https://dev.mysql.com/doc/refman/8.4/en/kill.html

(INSERT is not mentioned in the above page)

Also here:

https://dev.mysql.com/doc/refman/8.4/en/sys-innodb-lock-waits.html

Then, there is a warning that KILL should not be used on InnoDB tables:

https://dev.mysql.com/doc/refman/8.4/en/crashing.html

Then, you have some explanations here:

https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-transaction-management.html

This is simply a known and documented behaviour.

Not a bug.

Hi, Team.

Thank you for your answer, but I can't find anything related to this problem from your links.

These views are empty for me:
SELECT  * from sys.innodb_lock_waits;
SELECT  * from sys.x$innodb_lock_waits;

And I am inserting to a temporary table, it's not visible for other sessions, so I do not understand how it can be related to transactions?

Hi Mr. Nt,

Those views can be empty for you.

However, we did not recommend you to run those views.

We just recommended you to read those pages , to show that this is all documented behaviour.

Also, in 8.0 and above, temporary tables are based on the InnoDB storage engine.

Not a bug.