MySQL Bugs: #32656: NDB: Duplicate key error aborts transaction in handler. Doesn't talk back to SQL

Bug #32656	NDB: Duplicate key error aborts transaction in handler. Doesn't talk back to SQL
Submitted:	23 Nov 2007 12:33	Modified:	23 Sep 2009 18:48
Reporter:	Konstantin Osipov (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	5.1	OS:	Any
Assigned to:	Tomas Ulin	CPU Architecture:	Any

Description:
When a duplicate error, the cluster storage engine aborts the entire transaction internally and any subsequent  SQL statement except rollback produces an error.

What should happen instead:

- duplicate error should not lead to a transaction abort in the first place.
Effects of the statement should be rolled back, but not the entire transaction.
Lack of this functionality, IMHO, makes the engine unusable with anything more or less
complex that runs with autocommit=0 and/or uses stored procedures, triggers,
functions and exception handlers.

- should the engine need to abort the entire transaction in case of an internal
error, it should communicate the fact back to SQL layer by setting:

1) thd->transaction_rollback_request

-- so that SQL/ can clear its own transaction state and
issue a distributed rollback in case more than one storage engine participates in the transaction. With binary logging on, one such engine would be the
binary log.

2) thd->is_fatal_sub_statement_error, to abort execution of a stored function or
trigger, since COMMIT or ROLLBACK, explicit or implicit, are disallowed inside
a stored function or trigger.

This is done by calling thd_mark_transaction_to_rollback.

Setting severity to S1 as this bug has multiple manifestations and effects, 
the test case covers only one of them.

See also bug#12054

How to repeat:
-- source include/have_ndb.inc
-- source include/not_embedded.inc

--disable_warnings
drop table if exists t1;
--enable_warnings

CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE=ndbcluster;
set autocommit=0;
insert into t1 (a) values (1);
--error ER_DUP_ENTRY
insert into t1 (a) values (1);
show warnings;
select * from t1;

This last statement leads to:
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
show warnings;
Level	Code	Message
Error	1296	Got error 630 'Tuple already existed when attempting to insert' from NDB
Error	1062	Duplicate entry '1' for key 'PRIMARY'

Got error 4350 'Transaction already aborted' from NDBCLUSTER

See also Bug#24989 for the test case inside a stored function or trigger.

This is also a documentation request: if any error leads to a transaction abort, it should be documented.

Most probably just another manifestation of the problem:

Testsuite funcs_1, ndb_trig_1011ext.test
(exists in the moment in 5.1,6.0)

Edited snip of the protocol:
----------------------------
<engine_to_be_used> is set to ndbcluster
set @@sql_mode='traditional';
create table t1 (f1 integer) engine = <engine_to_be_used>;
create table t2 (f2 integer) engine = <engine_to_be_used>;
create table t3 (f3 integer) engine = <engine_to_be_used>;
create table t4 (f4 tinyint) engine = <engine_to_be_used>;
insert into t1 values (1);
create trigger tr1 after insert on t1
for each row insert into t2 (f2) values (new.f1+1);
create trigger tr2 after insert on t2
for each row insert into t3 (f3) values (new.f2+1);
create trigger tr3 after insert on t3
for each row insert into t4 (f4) values (new.f3+1000);
set autocommit=0;
start transaction;
insert into t1 values (1);
ERROR 22003: Out of range value for column 'f4' at row 1
commit;
select * from t1 order by f1;
f1
1
1     <-------------- Why this row?
select * from t2 order by f2;
f2
2     <-------------- Why this row?
select * from t3 order by f3;
f3
3     <-------------- Why this row?
select * from t4 order by f4;
f4

If I set <engine_to_be_used> to InnoDB, than the tables
t2, t3, t4 are empty. This means InnoDB treats the
the insert into t1 including all triggered actions
as one (sub)transactional unit/atomic statement. 

The manual (6.0) says about NDB in
18.14.3. Limits Relating to Transaction Handling
...
There is no partial rollback of transactions. 
A duplicate key or similar error rolls back
the entire transaction.
This leads to the expectation that the tables
t2, t3, t4 must be empty or the manual is wrong.

I observed this behaviour in 5.0, 5.1 and 6.0
(last pull end of May 2008) and will therefore
disable ndb_trig_1011ext.test in these versions.
(These tests were unfortunately enabled and
contained expected results showing this problem.)

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/56261

2694 Tomas Ulin	2008-10-15
      Bug#32656 NDB: Duplicate key error aborts transaction in handler. Doesn't talk back to SQL

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/56434

2700 Tomas Ulin	2008-10-17
      Bug#32656 bug fix corrections

fixed in 6.2.16 and 6.3.19

Documented fix in NDB-6.3.19 changelog as follows:

Note that the section of the Manual quoted by Matthias already reads as follows:

        Rollbacks.  There are no partial transactions, and no partial rollbacks 
        of transactions. A duplicate key or similar error aborts the entire 
        transaction, and subsequent statements raise ERROR 1296 (HY000): Got 
        error 4350 'Transaction already aborted' from NDBCLUSTER. In such 
        cases, you must issue an explicit ROLLBACK and retry the entire 
        transaction.

        This behavior differs from that of other transactional storage engines 
        such as InnoDB that may roll back individual statements.

(I don't recall when this was changed.)

I have moved the 5.1 version of this item to 'Previous MySQL Cluster Issues Resolved in MySQL 5.1 and MySQL Cluster NDB 6.x' and updated it to read as follows: 

        *Rollbacks* Prior to MySQL Cluster NDB 6.3.19, the NDBCLUSTER storage 
        engine did not support partial transactions or partial rollbacks of 
        transactions. A duplicate key or similar error aborted the entire 
        transaction, and subsequent statements raised ERROR 1296 (HY000): Got 
        error 4350 'Transaction already aborted' from NDBCLUSTER. In such 
        cases, it was necessary to issue an explicit ROLLBACK and retry the 
        entire transaction.
          
        Beginning with MySQL Cluster NDB 6.3.19, this limitation has been 
        removed, and the behavior of NDBCLUSTER is now in line with that of 
        other transactional storage engines such as InnoDB which can roll back 
        individual statements. (Bug #32656)

(Note that there is no longer any 6.0 version of the MySQL Cluster chapter; this was removed some time ago since we do not currently offer Cluster support in version of MySQL deriving from 6.0.)

Set bug status to NDI until the effective NDB-6.2 version of the fix can be verified, because I was told we cloned off and released 6.2.16 on 08 October, which precedes dates of the commits shown here. (Otherwise, I need to change the release date as shown in the changelog.)

Thanks!

Omitted actual changelog entry in previous comment. Changelog entry for this fix reads as follows:

        A duplicate key error raised when inserting into an NDBCLUSTER table 
        caused the current transaction to abort, after which any SQL statement 
        other than a ROLLBACK failed. With this fix, the NDBCLUSTER storage engine 
        now supports rollback of individual statements in such cases, and it is no 
        longer necessary to issue an explicit ROLLBACK and then retry the entire 
        transaction.

Pushed into 6.0.7-alpha  (revid:tomas.ulin@sun.com-20081017074533-jfl0duq2at9j8vlx) (version source revid:tomas.ulin@sun.com-20081017085255-rtu4jfwjichyzgvx) (pib:5)

Added entry to ndb-6.2.17 changelog. Nothing to document for 6.0-main (see previous note).

Closed.

funcs_1.ndb_trig_1011ext.test is disabled in 5.1 (and mysql-trunk) due to this bug.

Either the test case should be enabled, or a bug should be re-opened,
or a new bug should be submitted and the test case should be disabled
due to it.

Re-opening this bug.

Submitted Bug#47654 to track disabled test.

Pursuant to BUG#46764, the changelog entry for this bug has been updated, and now reads as follows:

        A duplicate key or other error raised when inserting into an
        NDBCLUSTER table caused the current transaction to abort, after 
        which any SQL statement other than a ROLLBACK failed. With this 
        fix, the NDBCLUSTER storage engine now performs an implicit 
        rollback when a transaction is aborted in this way; it is no 
        longer necessary to issue an explicit ROLLBACK statement, and 
        the next statement that is issued automatically begins a new 
        transaction.

        NOTE. It remains necessary in such cases to retry the complete 
        transaction, regardless of which statement caused it to be 
        aborted.