Bug #30763 Multi-table UPDATE with transaction + non-transactional table assertion failure

• Submitted: 3 Sep 2007 4:26
• Modified: 12 Feb 2008 20:57
• Reporter: Thomas Hurst
• Status: Closed
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.1.21-beta-debug
• OS: Linux (any, 2.6.20.4 Ubuntu AMD64)
• Assigned to: Andrei Elkin
• CPU Architecture: Any

[3 Sep 2007 4:26] Thomas Hurst

Description:
With debug builds, a query of the form:

 UPDATE aTransTable,aNonTransTable SET aTransTable.foo=aNonTransTable.foo WHERE aTransTable.id=aNonTransTable.id;

Where aTransTable is an InnoDB table (presumably any transactional table) and aNonTransTable is a non-transactional table like MyISAM or HEAP, the following assertions are triggered:

sql_update.cc:1974 (trans_safe || !updated || thd->transaction.stmt.modified_non_trans_table)
sql_update.cc:1555 (trans_safe || !updated || thd->transaction.all.modified_non_trans_table)

(I hacked DBUG_ASSERT() to fprintf() assertion failures since I was trying to debug another bug, but it of course happens on unmodified sources too).

How to repeat:
Minimal(?) testcase SQL.  With a debug build this abort()s the server.

use test;
DROP TABLE IF EXISTS aTransTable,aNonTransTable;

CREATE TABLE aTransTable (
 id INT UNSIGNED NOT NULL PRIMARY KEY,
 foo INT UNSIGNED NOT NULL
) TYPE=INNODB;
INSERT INTO aTransTable VALUES (1,1);

CREATE TABLE aNonTransTable (
 id INT UNSIGNED NOT NULL PRIMARY KEY,
 foo INT UNSIGNED NOT NULL
) TYPE=MYISAM;
INSERT INTO aNonTransTable VALUES (1,1);

UPDATE aTransTable,aNonTransTable SET aTransTable.foo=aNonTransTable.foo WHERE aTransTable.id=aNonTransTable.id;

[3 Sep 2007 8:23] Sveta Smirnova

Thank you for the report.

Verified as described.

[6 Oct 2007 18:10] MySQL Verification Team

Bug #31431 was marked as a duplicate of this one.

[9 Oct 2007 9:01] Andrei Elkin

Bug #31472 is a duplicate.
On its page there is a patch that should fix the problem (have not run all the tests though).

[12 Oct 2007 9:55] Andrei Elkin

A patch that covers this bug is submitted for bug#29136. That bug's tests hit the same problem so that the optimal solution is to eliminate the cause of the tests which suffices for this bug as well.

[12 Feb 2008 15:21] Trudy Pelzer

Bug fixed by patch for bug#29136; setting correct status.

[12 Feb 2008 20:57] Paul DuBois

Noted in 5.0.54, 5.1.23, 6.0.4 changelogs.

A multiple-table UPDATE involving transactional and non-transactional
tables caused an assertion failure.