Bug #37183 insert ignore into .. select ... hangs after deadlock was encountered
Submitted: 4 Jun 2008 8:15 Modified: 12 Mar 2010 17:50
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:4.1.22, 5.0.64, 5.1.26, 6.0.6 OS:Any
Assigned to: Magne Mæhre CPU Architecture:Any

[4 Jun 2008 8:15] Shane Bester
Description:
using repeatable read isolation level, instead of returning a deadlock error or warning, the "insert ignore into .. select .." hangs forever (even if thread 2 is committed) when the select part gets a deadlock.

thread 2 succeeds, and the deadlock is visible in innodb_lock_monitor output.

How to repeat:
thread 1
---------

drop table if exists t1,t2;
create table t1(id int primary key,v int)engine=innodb;
insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7);
create table t2 like t1;

thread 2
---------
begin;
update t1 set v=id*2 where id=1;

thread 1
--------
begin;
update t1 set v=id*2 where id=2;

thread 2
--------
update t1 set v=id*2 where id=2;

thread 1
---------
insert ignore into t2 select * from t1 where id=1; #hangs

Suggested fix:
i think the insert ignore into .. select .. should return a warning instead of hanging.
[4 Jun 2008 8:18] MySQL Verification Team
this testcase crashed my 5.1.26-debug !!  Maybe it's not innodb bug afterall.

Version: '5.1.26-rc-debug'  socket: ''  port: 3307  Source distribution
Assertion failed: 0, file .\protocol.cc, line 416

mysqld.exe!my_sigabrt_handler
mysqld.exe!raise
mysqld.exe!abort
mysqld.exe!_wassert
mysqld.exe!net_end_statement
mysqld.exe!dispatch_command
mysqld.exe!do_command
mysqld.exe!handle_one_connection
mysqld.exe!pthread_start
mysqld.exe!_callthreadstart
mysqld.exe!_threadstart
kernel32.dll!FlsSetValue
[4 Jun 2008 8:31] MySQL Verification Team
for the purpose of this bug report, 5.1.26-release works as expected.
mysql> insert ignore into t2 select * from t1 where id=1;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show warnings;
Level | Code | Message                                    
Error | 1213 | Deadlock found when trying to get lock; try restarting 

So, this report is about 5.0 error handling I think.
[4 Jun 2008 10:29] Susanne Ebrecht
mysql> select version();
5.0.64-debug

Given test hangs as described.

mysql> select version();
5.1.26-rc-debug

Given test crash 5.1 bzr tree build from last night.

mysql> insert ignore into t2 select * from t1 where id=1;       
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> select version();
6.0.6-alpha-debug

Given test crash 6.0 bzr tree build from last night.

mysql> insert ignore into t2 select * from t1 where id=1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Using Falcon instead of InnoDB:
all works fine, no crashes and no hangs.

So this seems to be InnoDB related.
[4 Jun 2008 13:33] Heikki Tuuri
http://bugs.mysql.com/bug.php?id=36964 may be associated with this.
[10 Jun 2008 12:27] MySQL Verification Team
on 4.1.22 the insert..select errors out with a descriptive error :)

mysql> insert ignore into t2 select * from t1 where id=1;
ERROR 1105 (HY000): Unknown error
mysql>
[14 Oct 2008 6:53] Trent Lloyd
Affects customer.. this was report some time ago as http://bugs.mysql.com/bug.php?id=26126

Though that issue does not have much more info.. this one has test case.. same issue though.

Is there any progress on this? It's fairly bad that on 5.0 it hangs (and 5.1 even crashes!).. at least on 4.1 it would error out and could be handled in the application.
[22 Jan 2009 16:53] Heikki Tuuri
I guess this is a MySQL bug. If in insert ignore into .. select ..., InnoDB encounters a deadlock, then InnoDB probably returns an error to MySQL. But apparently, MySQL is not prepared to receive an error there and hangs.
[23 Sep 2009 18:36] Magne Mæhre
The problem seems to be caused by make_join_statistics failing, without setting proper error status.

join_read_const tries to read from 't1' (from the example code), which is locked by connection 2,  and (eventually) gets an ER_LOCK_WAIT_TIMEOUT.  The error state is not properly reported (error flag is returned, but diagnostics area is not populated), which finally triggers the assert.
[24 Sep 2009 8:45] Konstantin Osipov
Please re-triage (risk/effort changed).
[28 Sep 2009 9:40] Konstantin Osipov
For the record: the bug is in the implementation of IGNORE clause.
[27 Oct 2009 19:45] Magne Mæhre
This bug will be fixed by Konstantin's patch for #46539.  I will submit a separate test case as soon as that patch is in, and close this bug.
[27 Oct 2009 20:48] Konstantin Osipov
Assigning back to Magne (didn't notice his comment at first).
The bug is indeed fixed by my patch.
[11 Nov 2009 21:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90164

3196 Magne Mahre	2009-11-11
      Bug #37183 insert ignore into .. select ... hangs after 
                 deadlock was encountered
      
      The bug is caused by an inconsistent handling of the IGNORE
      clause.  A read from a const table caused a lock timeout
      (ER_LOCK_TIMEOUT) in innodb.  Since the IGNORE clause was
      given, the timeout was converted into a warning instead of
      an error, thus not populating the diagnostics area.  When
      innodb subsequently marked the transaction for rollback,
      mysql asserted since the diag.area was empty.
      
      This patch consists of only a test case, as the bug itself
      was fixed by the patch for Bug #46539
[12 Nov 2009 11:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90207

3198 Magne Mahre	2009-11-12
      Bug #37183 insert ignore into .. select ... hangs after 
                 deadlock was encountered
      
      The bug is caused by an inconsistent handling of the IGNORE
      clause.  A read from a const table caused a lock timeout
      (ER_LOCK_TIMEOUT) in innodb.  Since the IGNORE clause was
      given, the timeout was converted into a warning instead of
      an error, thus not populating the diagnostics area.  When
      innodb subsequently marked the transaction for rollback,
      mysql asserted since the diag.area was empty.
      
      This patch consists of only a test case, as the bug itself
      was fixed by the patch for Bug #46539
[12 Nov 2009 11:48] Magne Mæhre
Queued to 5.1-bugteam and mysql-pe
[2 Dec 2009 8:03] Bugs System
Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:magne.mahre@sun.com-20091112114333-0ziiuhhc1m2pv7v4) (merge vers: 5.1.41) (pib:13)
[16 Dec 2009 2:15] Paul DuBois
The patch here changes a test case. No changelog entry needed.

Setting report to NDI pending push into 5.5.x+.
[16 Dec 2009 8:37] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:44] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091124083136-iqm136jm31sfdwg3) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:50] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[16 Dec 2009 15:41] Paul DuBois
No changelog entry needed.
[12 Mar 2010 14:13] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:29] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:45] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[12 Mar 2010 17:50] Paul DuBois
No changelog entry needed.