MySQL Bugs: #47406: Insert statement never ends (InnoDB)

Bug #47406	Insert statement never ends (InnoDB)
Submitted:	17 Sep 2009 10:40	Modified:	20 Nov 2009 11:36
Reporter:	Franck Wolff	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.1.22	OS:	Linux (CentOS 5)
Assigned to:		CPU Architecture:	Any
Tags:	insert, lock

Description:
Hi,

We experience insert statements that never ends on one of our servers. This problem doesn't occure each time, and we can't figure out what are the conditions of this blocking issue.

"show full processlist" and "show engine innodb status" outputs are attached to this issue report.

The innodb status output is rather cryptic for us and we are stuck with problem for several weeks. The only way to workarround this problem is to restart the database (and, sometimes, we need to restart it several times before the problem vanish).

Any help would be very appreciated.

Best regards,
Franck Wolff.

How to repeat:
N/A

Suggested fix:
None.

You have INSETT ... SELECT ... Please, send the results of EXPLAIN for that SELECT, with ? substituted by some realistic values of parameters.

Looks like you just select to many rows to get the data you need to insert...

Hi,

Thanks for your quick response. I've attached the explain output.

Quote: "Looks like you just select to many rows to get the data you need to
insert..."

The strange thing is that the insert... select... is very fast when it works (and I mean *exactly* the same request with the same values).

Regards,
Franck.

Execution plan looks reasonably good.

Can it be so that you update one of the tables used in SELECT part concurrently? Or run more than one similar INSERT ... SELECT at the same time? 

I'd like to get the results of SHOW INNODB STATUS when INSERT ... SELECT hangs.

We have finally solved this problem by simplifying a lot the insert statement.

Thanks for your help anyway,
Franck.

Found a workaround...