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

Bug #47406	Insert statement never ends (InnoDB)
Submitted:	17 Sep 12:40	Modified:	20 Nov 12:36
Reporter:	Franck Wolff
Status:	Closed
Category:	Server: InnoDB	Severity:	S1 (Critical)
Version:	4.1.22	OS:	Linux (CentOS 5)
Assigned to:		Target Version:
Tags:	lock, insert

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...