| Bug #4312 | ndb table, wrong behaviour on insert .. on duplicate key .. | ||
|---|---|---|---|
| Submitted: | 28 Jun 2004 17:59 | Modified: | 18 Nov 2004 17:25 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S2 (Serious) |
| Version: | 4.1 | OS: | Linux (Linux) |
| Assigned to: | Martin Skold | CPU Architecture: | Any |
[28 Jun 2004 18:00]
Matthias Leich
test case
Attachment: ml_err12.test (application/octet-stream, text), 679 bytes.
[23 Jul 2004 11:56]
Martin Skold
Seems to be a conflict with how INSERT IGNORE is currently handled. HA_EXTRA_NO_IGNORE_DUP_KEY is used for both, but instead of ignoring (using write) execute(_, IgnoreError) should be used, but we still need to know that it was duplicate key and return that to the caller since MySQL will then do the ON DUPLICATE KEY statement instead.
[31 Aug 2004 7:52]
MySQL Verification Team
Thank you for the bug report. I was able to repeat.
[23 Sep 2004 9:07]
Magnus Blåudd
The functionality INSERT ... ON DUPLICATE KEY .. and INSERT IGNORE has been disabled in NDB waiting for WL#1355 Rollback Savepoint in MySQL Cluster This new behaviour is tested in mysql-test/t/ndb_insert.test
[3 Nov 2004 11:57]
Tomas Ulin
Suggest we implement support by doing a read before insert in case of on duplicate key... performance hit, but it works will be oprimized in later version
[5 Nov 2004 12:38]
Martin Skold
Implemented fix through an extra read before insert, should be optimized later.
Description: The behaviour of ndb tables during insert .. on duplicate key update .. does not fit to the specification within the manual. Example: create table t1_ndb ( a int not null, b varchar(30), c varchar(10), primary key(a)) engine = ndb; insert into t1_ndb values (1,'one','one'); insert into t1_ndb set a= 1, b='eins' on duplicate key update a=a + 1, b=concat(b,'modified') ; select * from t1_ndb ; a b c 1 eins NULL (The old record was : 1 one one !!! ) That means, if a primary key collision occurs, the old record will be DELETED and the new one inserted. But the manual says, that there will be an UPDATE. And DELETE/INSERT may cause a loss of information ( see column c). The same example on a myisam type table provides correct values: select * from t1_myisam ; a b c 2 onemodified one My environment: Intel PC with Linux(SuSE 9.0), MySQL 4.1 source distrib. last ChangeSet@1.2014, 2004-06-27 How to repeat: Please use my test file, copy it to mysql-test/t ./mysql-test-run ml_err12 Suggested fix: Fine solution: Behaviour corresponding to specification (like myisam tables) acceptable solution: Error message and no modification of data, if the ndb storage engine is not able to execute the command corresponding to the specification (I know that the ndb engine can not execute a primary key update) bad solution: The manual describes the current behaviour of the ndb engine. That would be a bad solution, because the syntax kex word is UPDATE.