MySQL Bugs: #10936: Cluster engine shows wrong key value for duplicate key error.

Bug #10936	Cluster engine shows wrong key value for duplicate key error.
Submitted:	28 May 2005 1:25	Modified:	27 Aug 2005 7:44
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	5.1.0	OS:	Linux (Linux)
Assigned to:	Martin Skold	CPU Architecture:	Any

Description:
While inserting data, I recieved a duplicate key error for key 99. The actual duplicate key value was 235. See below

mysql> use gotoslave;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  insert into t1 values (235,"core"), (099,"CG");
ERROR 1062 (23000): Duplicate entry '99' for key 1
mysql> select * from t1;
+-----+------+
| c1  | c2   |
+-----+------+
| 235 | core |
| 245 | leek |
|  33 | test |
| 255 | core |
|  13 | Jona |
| 207 | crsh |
|  14 | toma |
|  12 | Jeb  |
|  21 | test |
|   1 | texa |
+-----+------+
10 rows in set (0.08 sec)

mysql>  insert into t1 values (235,"core"), (099,"CG");
ERROR 1062 (23000): Duplicate entry '99' for key 1
mysql>  insert into t1 values  (099,"CG");
Query OK, 1 row affected (0.01 sec)

How to repeat:
mysql> use gotoslave;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  insert into t1 values (235,"core"), (099,"CG");
ERROR 1062 (23000): Duplicate entry '99' for key 1
mysql> select * from t1;
+-----+------+
| c1  | c2   |
+-----+------+
| 235 | core |
| 245 | leek |
|  33 | test |
| 255 | core |
|  13 | Jona |
| 207 | crsh |
|  14 | toma |
|  12 | Jeb  |
|  21 | test |
|   1 | texa |
+-----+------+
10 rows in set (0.08 sec)

mysql>  insert into t1 values (235,"core"), (099,"CG");
ERROR 1062 (23000): Duplicate entry '99' for key 1
mysql>  insert into t1 values  (099,"CG");
Query OK, 1 row affected (0.01 sec)

Suggested fix:
Show correct duplicate key value.

This is a known bug and is due to batching of inserts.
The MySQL server only remembers the last key in a
batch and returns that one. Saving all key values in a batch
would be expensive and space consuming. One alternative
would be for the possible suppression of what exact key
was the problematic one, in case of batching.

Will change so that duplicate key value is only shown when not batching inserts:
mysql> insert into t2 values(1);
Query OK, 1 row affected (0.04 sec)
 
mysql> insert into t2 values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 1
mysql> insert into t2 values(1),(2);
ERROR 1022 (23000): Can't write; duplicate key in table 't2'

Not sure that I agree with the proposed patch. Does the error happen rightaway?

If so should we not spit it out with the correct key that is duplicated. 

If not, when does the error occur? Just not understanding why show the correct duplicate is hard.
Thanks.

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/internals/26111

Cluster executes all inserts in parallel (or batches of max sized chunks).
When an error occurs in a batch this is reported back to the MySQL server.
The server only saves the last key value and can only report that.
For other storage engines the insert is synchronous, each new row is
inserted one by one (or at least checked for duplicates one by one) and
an error is returned immediately.
To adopt to this behavior we would have to insert one row at a time (or
at least do a pk read). This would be painfully slow on a cluster. Saving
all pk values in a batch just to be able to return one in case there is an
error, would waste a lot of space. Currently there is no way of turning
off the parallel batching, we could add that if applications want to debug
which key is the offending one in a batch.

The patch is pushed to 4.1 and merged to 5.0, will be
merged to 5.1 ASAP

Please document this one, since it has been pushed

Pushed to 4.1.13 and 5.0.10