Bug #21301 SQL Error updating table when primary key contains multiple columns

• Submitted: 26 Jul 2006 16:22
• Modified: 26 Aug 2006 23:34
• Reporter: Luther Maloney
• Status: No Feedback
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.15
• OS: Linux (RedHat/Fedora Core)
• CPU Architecture: Any

[26 Jul 2006 16:22] Luther Maloney

Description:
Error occurs when updating a table with a primary key. The primary key contains multiple columns. For example :

A table with the following definition (the first column is a foreign key reference to a separate table, the next two columns are the month and year):
create table table_name 
(
   column_1 int(10) unsigned NOT NULL,
   column_2 int(2) unsigned not null,
   column_3 int(4) unsigned not null,
   column_4 varchar(50) not null,
   column_5 timestamp not null default CURRENT_TIMESTAMP,
   primary_key(column_1,column_2,column_3)
);

And the following SQL statement : 
update table_name set column_4='Some Text', column_2=2, column_3=2002 where column_1=5

This SQL will generate an error, even when the values for column_2 and column_3 are not being changed. 

The specific error is :
java.sql.SQLException: Duplicate entry 'testuser-1-7-2001' for key 1

How to repeat:
A table with the following definition (the first column is a foreign key reference to a separate table, the next two columns are the month and year):
create table table_name 
(
   column_1 int(10) unsigned NOT NULL,
   column_2 int(2) unsigned not null,
   column_3 int(4) unsigned not null,
   column_4 varchar(50) not null,
   column_5 timestamp not null default CURRENT_TIMESTAMP,
   primary_key(column_1,column_2,column_3)
);

And the following SQL statement : 
update table_name set column_4='Some Text', column_2=2, column_3=2002 where column_1=5

Suggested fix:
recognize that the values for column_2 and column_3 were not being changed in the update statement, and execute the update.

[26 Jul 2006 23:34] MySQL Verification Team

Thank you for the bug report. Could you please provide the complete
test case (refered tables/inserts/update) which shows the behavior
reported.
Thanks in advance.

[27 Aug 2006 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".