| Bug #48036 | NDB: UPDATE IGNORE not working together with unique constraint | ||
|---|---|---|---|
| Submitted: | 14 Oct 2009 9:48 | Modified: | 15 Nov 2009 20:23 |
| Reporter: | Martin Skold | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S3 (Non-critical) |
| Version: | mysql-5.1-telco-6.3 | OS: | Any |
| Assigned to: | Martin Skold | CPU Architecture: | Any |
[15 Oct 2009 8:22]
Bugs System
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/86902 3105 Martin Skold 2009-10-15 Bug#48036: NDB: UPDATE IGNORE not working together with unique constraint: disable batching of updates if updating unique index, added test case modified: mysql-test/suite/ndb/r/ndb_update.result mysql-test/suite/ndb/t/ndb_update.test sql/ha_ndbcluster.cc
[15 Oct 2009 12:58]
Bugs System
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/86978 3124 Martin Skold 2009-10-15 [merge] Merge modified: mysql-test/suite/ndb/r/ndb_alter_table_backup.result mysql-test/suite/ndb/r/ndb_auto_increment.result mysql-test/suite/ndb/r/ndb_insert.result mysql-test/suite/ndb/r/ndb_update.result mysql-test/suite/ndb/t/ndb_auto_increment.test mysql-test/suite/ndb/t/ndb_insert.test mysql-test/suite/ndb/t/ndb_update.test sql/ha_ndbcluster.cc sql/sql_update.cc
[15 Oct 2009 14:02]
Bugs System
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/86985 3080 Martin Skold 2009-10-15 [merge] Merge modified: mysql-test/suite/ndb/r/ndb_alter_table_backup.result mysql-test/suite/ndb/r/ndb_auto_increment.result mysql-test/suite/ndb/r/ndb_insert.result mysql-test/suite/ndb/r/ndb_update.result mysql-test/suite/ndb/t/ndb_auto_increment.test mysql-test/suite/ndb/t/ndb_insert.test mysql-test/suite/ndb/t/ndb_update.test sql/ha_ndbcluster.cc sql/sql_update.cc
[15 Oct 2009 14:03]
Bugs System
Pushed into 5.1.39-ndb-7.1.0 (revid:martin.skold@mysql.com-20091015140324-53ll4ja07nf7f72q) (version source revid:martin.skold@mysql.com-20091015140324-53ll4ja07nf7f72q) (merge vers: 5.1.39-ndb-7.1.0) (pib:13)
[15 Oct 2009 14:08]
Bugs System
Pushed into 5.1.39-ndb-7.0.9 (revid:martin.skold@mysql.com-20091015140912-ick0kiktabyhzkjh) (version source revid:martin.skold@mysql.com-20091015125902-uc7afycq0fxb1nvl) (merge vers: 5.1.39-ndb-7.0.9) (pib:13)
[15 Oct 2009 14:12]
Bugs System
Pushed into 5.1.39-ndb-6.3.28 (revid:martin.skold@mysql.com-20091015141245-p900c5li04ydixyj) (version source revid:martin.skold@mysql.com-20091015082313-ijjhe8h5xh3mbbdf) (merge vers: 5.1.39-ndb-6.3.28) (pib:13)
[15 Nov 2009 20:23]
Jon Stephens
Documented in the NDB-6.3.28 and 7.0.9 changelogs as follows:
For UPDATE IGNORE statements, batching of updates is now
disabled. This is because such statements failed when batching
of updates was employed if any updates violated a unique
constraint, to the fact a unique constraint violation could not
be handled without aborting the transaction.
Closed.
Description: When doing UPDATE IGNORE the batching of updates does not work if there is a unique constraint being violated due to the update. This is because the internal implementation of ignoring errors cannot handle the constraint violation without aborting the transaction. In this special case the old implementation where each row has to be read and verified for possible constraint violation has to be used. This means the batching of updates has to be turned off for this special case. How to repeat: mysql> create table xy(x int not null primary key, y int not null unique) engine = ndb; Query OK, 0 rows affected (2.21 sec) mysql> insert into xy values(1,1),(2,2),(3,3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> update ignore xy set y = 4 where x IN(1,2,3,4); ERROR 1296 (HY000): Got error 4350 'Transaction already aborted' from NDBCLUSTER Suggested fix: === modified file 'sql/ha_ndbcluster.cc' --- sql/ha_ndbcluster.cc 2009-09-24 14:54:21 +0000 +++ sql/ha_ndbcluster.cc 2009-10-14 09:26:37 +0000 @@ -3840,6 +3847,12 @@ ha_ndbcluster::delete_row_conflict_fn(en bool ha_ndbcluster::start_bulk_update() { DBUG_ENTER("ha_ndbcluster::start_bulk_update"); + if (!m_use_write && m_ignore_dup_key) + { + DBUG_PRINT("info", ("Batching turned off as duplicate key is " + "ignored by using peek_row")); + DBUG_RETURN(TRUE); + } DBUG_RETURN(FALSE); }