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