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 Email Updates:
Status: Closed Impact on me:
None 
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
Triage: D2 (Serious)

[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] Shane Bester
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.