Bug #17431 INSERT IGNORE INTO returns failed: 1296: err 4350 'Transaction already aborted'
Submitted: 15 Feb 2006 16:45 Modified: 27 Mar 2006 11:47
Reporter: Jonathan Miller Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:5.0, 5.1 (probably 4.1) OS:Linux (Linux 32 Bit OS)
Assigned to: Martin Skold

[15 Feb 2006 16:45] Jonathan Miller
Description:
TEST                            RESULT
-------------------------------------------------------
rpl_ndb_insert_ignore          [ fail ]

Errors are (from /home/ndbdev/jmiller/clones/mysql-5.1-new/mysql-test/var/log/mysqltest-time) :
mysqltest: In included file "./extra/rpl_tests/rpl_insert_ignore.test": At line 36: query 'INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a' failed: 1296: Got error 4350 'Transaction already aborted' from NDBCLUSTER
(the last lines may be the most important ones)

How to repeat:
eval CREATE TABLE t1 (
 a int unsigned not null auto_increment primary key,
 b int unsigned,
 unique (b)
) ENGINE=NDB;

eval CREATE TABLE t2 (
 a int unsigned, # to force INSERT SELECT to have a certain order
 b int unsigned
) ENGINE=NDB;

INSERT INTO t1 VALUES (NULL, 1);
INSERT INTO t1 VALUES (NULL, 2);
INSERT INTO t1 VALUES (NULL, 3);
INSERT INTO t1 VALUES (NULL, 4);
# An alternation of values which will conflict in t1 and will not.

INSERT INTO t2 VALUES (1, 1);
INSERT INTO t2 VALUES (2, 2);
INSERT INTO t2 VALUES (3, 5);
INSERT INTO t2 VALUES (4, 3);
INSERT INTO t2 VALUES (5, 4);
INSERT INTO t2 VALUES (6, 6);

INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a;

# Compare results

SELECT * FROM t1 ORDER BY a;
drop table t1;
[20 Feb 2006 15:57] Tomas Ulin
also changing

INSERT INTO t2 VALUES (1, 1);
INSERT INTO t2 VALUES (2, 2);
INSERT INTO t2 VALUES (3, 5);
INSERT INTO t2 VALUES (4, 3);
INSERT INTO t2 VALUES (5, 4);
INSERT INTO t2 VALUES (6, 6);

to

INSERT INTO t2 VALUES (1, 1);
INSERT INTO t2 VALUES (6, 6);

In the test case, will instead cause client to hang.  So looks like there is a second bug in play here as well.  The "join code" does not return correctly to the client inter certain conditions.
[23 Feb 2006 14:09] Martin Skold
The problem here is the unique index, cluster currently does not support
ignoring unique constraint violations. This is not a high prio task since no
customer has yet asked for it, hence putting bug in To be fixed later.
This limitation should be clearly documented in the manual though.
[23 Feb 2006 14:10] Martin Skold
A possible workaround is to remove the constraint by dropping the unique index,
do the inserts and then add the index again. In 5.1 this will be on-line (without copying
complete table).
[5 Mar 2006 8:03] Jon Stephens
Documented issue in 4.1/5.0/5.1 Cluster Limitations sections per Tomas' email.
[15 Mar 2006 11:08] Hartmut Holzgraefe
also affects REPLACE INTO:

DROP TABLE IF EXISTS t1;

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(i INT PRIMARY KEY, 
                j INT, 
                k INT, 
                UNIQUE INDEX(j)
               ) ENGINE=ndb;
INSERT  INTO t1 VALUES (1,1,23);
REPLACE INTO t1 (j,k) VALUES (1,42);
-- ERROR 1296 (HY000): Got error 4350 'Transaction already aborted' from ndbcluster
[15 Mar 2006 11:11] Hartmut Holzgraefe
just found the following while testing, not sure whether
it is actually the same problem or not:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(i INT PRIMARY KEY AUTO_INCREMENT, 
                j INT, 
                k INT, 
                UNIQUE INDEX(j)
               ) ENGINE = ndb;
INSERT  INTO t1 VALUES (1,1,23);
REPLACE INTO t1 (j,k) VALUES (1,42);

with ndb this gives
  ERROR 1062 (23000): Duplicate entry '2' for key 1
with myisam it works fine though
[24 Mar 2006 10:52] Martin Skold
Patch pushed to 5.0.20
[24 Mar 2006 12:40] Martin Skold
Also pushed to 5.1.8
[24 Mar 2006 13:50] Chandra Vanipenta
also effects UPDATE, could you please check this one too.
[24 Mar 2006 15:09] Martin Skold
Created 
bug#18487: UPDATE IGNORE not supported for unique constraint violation of non-primary key
[26 Mar 2006 17:42] Jon Stephens
Please verify:

1. INSERT IGNORE no longer fails (Y/N)?

2. REPLACE IGNORE no longer fails (Y/N)?

Understood that UPDATE IGNORE is being handled on a separate bug.

Changeset identifiers would also be helpful.

Thanks!
[27 Mar 2006 11:47] Jon Stephens
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:

Documented in 5.0.20 and 5.1.8 changelogs. Also updated relevant portions of Cluster Limitations in 5.0 and 5.1 Manuals.
[17 May 2006 9:40] Chandra Vanipenta
The fix for REPLACE INTO is not working properly. 
The table with text field type are not replaced.
We have also some serious problems with performance in the system with this fix
in Mysql version 5.0.20. Could you please take a look into.
[2 Jun 2006 5:27] Martin Skold
Note that the implementation of INSERT IGNORE for
Ndb is slow since it requires reading all keys to find
potential conflicts.
A new bug report has been opened:
Bug #19906  	REPLACE doesn't update TEXT fields correctly