Bug #113 Mysql hangs on multi table update on innodb table.

• Submitted: 1 Mar 2003 9:50
• Modified: 11 Mar 2003 2:23
• Reporter: SINISA MILIVOJEVIC
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 4.0
• OS: Any (all)
• CPU Architecture: Any

[1 Mar 2003 9:50] SINISA MILIVOJEVIC

Description:
Mysql hangs and possibly can not recover from a query on an innodb table.  

How to repeat:

CREATE TABLE parent(id INT NOT NULL,
      PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id  INT PRIMARY KEY, parent_id INT,
      INDEX par_ind (parent_id),
      FOREIGN KEY (parent_id) REFERENCES parent(id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
      ) TYPE=INNODB;

insert into parent set id=1;
insert into child set id=1, parent_id=1;
update parent,child set parent.id=parent.id+1, child.parent_id=parent.id+1;

Suggested fix:
Heikki's comment:

When I stepped it in gdb, it looked like a cursor in the primary index of
'parent' keeps progressing forward indefinitely. It keeps updating the row,
and the update puts the row ahead of the cursor.

Monty has solved the 'Halloween problem' in a normal UPDATE by storing the
rows to be updated to a temporary table, so that already updated rows cannot
dive up again and be updated a second time. Sinisa should do similarly.

[11 Mar 2003 2:23] Lenz Grimmer

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

This is a duplicate of bug #106