MySQL Bugs: #17431: INSERT IGNORE INTO returns failed: 1296: err 4350 'Transaction already aborted'

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	CPU Architecture:	Any

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;

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.

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.

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).

Documented issue in 4.1/5.0/5.1 Cluster Limitations sections per Tomas' email.

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

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

Patch pushed to 5.0.20

Also pushed to 5.1.8

also effects UPDATE, could you please check this one too.

Created 
bug#18487: UPDATE IGNORE not supported for unique constraint violation of non-primary key

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!

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.

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.

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