Bug #4008 insert on duplicate fails depending on MERGE table INSERT_METHOD
Submitted: 4 Jun 2004 15:12 Modified: 25 Jun 2004 15:14
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1, 5.0 OS:Linux (Linux)
Assigned to: Sergei Golubchik CPU Architecture:Any

[4 Jun 2004 15:12] Matthias Leich
I have a MERGE table containing one record a(primary key)=6, b='six' .
I try to execute 
   insert into t1 set a=6, b='sechs'
   on duplicate key update a=a + 1, b='modified'    .
If INSERT_METHOD is FIRST this record will be updated to 7,'modified' which
is correct.
If INSERT_METHOD is LAST a get the error
 --error 1022 : ERROR 23000: Can't write, duplicate key in table 't1'
which is a wrong behaviour.

BTW: If I get a duplicate error the error number and message depends on the
     INSERT_METHOD though the reason is the same.
     FIRST: --error 1062, ERROR 23000: Duplicate entry '6' for key 1
     LAST:  --error 1022, ERROR 23000: Can't write, duplicate key in table 't1'

My environment: Intel PC with Linux(SuSE 9.0), 
                MySQL 4.1 source distrib. (last bk pull 2004-06-01)
                MySQL 5.0 source distrib. (last bk pull 2004-05-28)

How to repeat:
Please use my files for mysqltest.
  ml_first.test  test case with INSERT_METHOD=FIRST for comparison 
                 + works correct

  ml_last.test   test case with INSERT_METHOD=LAST  shows the problem
                 Please remove the "--1022" line before the last statement.
                 I inserted that line to get output from mysqltest. But
                 the insert should be successful !!

Suggested fix:
Please decide depending on the amount of work and the importance of the problem
  - Modify the manual chapter for MERGE tables
  - disable INSERT ... ON DUPLICATE for INSERT_METHOD=LAST or both methods
  - repair the code
[4 Jun 2004 15:28] Matthias Leich
positive case with INSERT_METHOD=FIRST

Attachment: ml_first.test (application/octet-stream, text), 670 bytes.

[4 Jun 2004 15:28] Matthias Leich
negative test case with INSERT_METHOD=LAST

Attachment: ml_last.test (application/octet-stream, text), 806 bytes.

[25 Jun 2004 15:14] Sergei Golubchik
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

Additional info:

fixed in 4.1.3