MySQL Bugs: #5553: UPDATE IGNORE with multiple tables can lock up mysqld connection,reproducable

Bug #5553	UPDATE IGNORE with multiple tables can lock up mysqld connection,reproducable
Submitted:	13 Sep 2004 22:31	Modified:	23 Sep 2004 9:59
Reporter:	Miles Georgi	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.4	OS:	Linux (Linux 2.4.20 debian)
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
Once the following query is submitted to the server it never replies back and it becomes nonresponsive on that connection.  There is a duplicate key collision in the data if IGNORE is omitted.

UPDATE IGNORE table2,table1 set table2.colE = table2.colE + 1 
WHERE table1.colA = table2.colA AND 
(table1.colB & 4096) > 0 AND (colE + 1) < colF;

Here is what the tables look like:

CREATE TABLE `table1` (
  `colA` int(10) unsigned NOT NULL auto_increment,
  `colB` int(11) NOT NULL default '0',
  PRIMARY KEY  (`colA`)
);

CREATE TABLE `table2` (
  `colC` int(10) unsigned NOT NULL default '0',
  `colA` int(10) unsigned NOT NULL default '0',
  `colD` int(10) unsigned NOT NULL default '0',
  `colE` int(10) unsigned NOT NULL default '0',
  `colF` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`colC`,`colA`,`colD`,`colE`)
);

How to repeat:
I have a script that reproduces this bug every time and it is located at 

http://www.dispirit.com/sql/ignorebug.sql.bz2

This file loads specific data into the tables that will create a database called bugtest and demonstrate this bug

Simply decompress the above file with bunzip2 and then execute it from within mysql with

mysql> source ignorebug.sql

I have tested this on two different Linux kernels with mysql 4.1.1 and 4.1.4 binary distribution and the results are the same

Suggested fix:
unsure

compressed script that reproduces bug

Attachment: ignorebug.sql.bz2 (application/octet-stream, text), 2.01 KiB.

updated Synopsis to mention multiple tables

Hi,

Thank you for the bug report! I was able to reproduce the same behavior on 4.1.4 binary distribution. On the latest BK 4.1 tree I got error:

mysql> UPDATE IGNORE table2,table1 set table2.colE = table2.colE + 1 WHERE table1.colA = table2.colA AND (table1.colB & 4096) > 0 AND (colE + 1) < colF;
ERROR 1105 (HY000): Unknown error

Patch for review: 
Subject: 	bk commit - 4.1 tree (antony:1.2013) BUG#5553 
Date: 	Fri, 17 Sep 2004 01:46:51 +0100 (BST)	 
ChangeSet 
  1.2013 04/09/17 01:46:49 antony@ltantony.rdg.cyberkinetica.homeunix.net +3 
-0 
  Bug#5553 - Multi table UPDATE IGNORE fails on dup key 
    We don't want the update to abort when IGNORE is specified

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix applied to MySQL 4.0 repository

Hi, I noticed 4.1.5 came out and I don't see a post about this bug fix in the change log posted at http://lists.mysql.com/announce/222

just wanted to make sure that the fix is actually in this release and if so I will perform an upgrade tonight.

Thank you for the very quick fix!

miles

Via Bit Keeper I see it in the 4.0 tree's change set but not in the 4.1 tree's changeset.

I am assuming that 4.1 will be updated soon, is that correct?

Just making sure it didn't slip through the cracks.

miles

Correct.

It will be updated (at the latest) before the next 4.1 (or 5.0) release.

As a part or the release procedure we merge all changes from 3.23 up to the tree we release (that means, for 5.0 release we merge 3.23->4.0->4.1->5.0)