MySQL Bugs: #38719: Partitioning returns a different error code for a duplicate key error

Bug #38719	Partitioning returns a different error code for a duplicate key error
Submitted:	11 Aug 2008 13:36	Modified:	15 Mar 2009 8:35
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1/6.0	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
When the same query is executed against a non-partitioned and against a partitioned table, two different error codes are returned. This is definitely be a surprise for people who expect partitioned tables to behave the same way non-partitioned tables have always behaved.

See also bug #28842 

How to repeat:
create table t1 (f1 integer not null, primary key (f1));
create table t2 (f1 integer not null, primary key (f1)) PARTITION BY KEY (f1) PARTITIONS 2;

mysql> insert into t1 values (1),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Notice error number is 1062.

mysql> insert into t2 values (1),(1);
ERROR 1022 (23000): Can't write; duplicate key in table 't2'

Error number is 1022.

Suggested fix:
Partitioned tables should be made to return the old error code - 1062. Even if both error codes are listed in the manual, using both will cause nothing but confusion.

Thank you for the bug report. Verified as described.

Seems to be this in handler::get_dup_key:
   if (error == HA_ERR_FOUND_DUPP_KEY || error == HA_ERR_FOREIGN_DUPLICATE_KEY ||
       error == HA_ERR_FOUND_DUPP_UNIQUE || error == HA_ERR_NULL_IN_SPATIAL ||
       error == HA_ERR_DROP_INDEX_FK)
-    info(HA_STATUS_ERRKEY | HA_STATUS_NO_LOCK);
+    table->file->info(HA_STATUS_ERRKEY | HA_STATUS_NO_LOCK);
   DBUG_RETURN(table->file->errkey);
 }

(ha_partition::print_error uses file->print_error, so this would be called by the partition's handler, not the table's handler currently)

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/53249

2824 Mattias Jonsson	2008-09-04
      Bug#38719: Partitioning returns a different error code for
      a duplicate key error
      
      handler::get_dup_key used the called handler for the
      info call, but used table->file handler for errkey
      Fixed by using table->file->info() instead.

Reviews and OK to push by Mikael Ronström.

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/54475

2822 Mattias Jonsson	2008-09-23
      post push fix, Bug#38719, additional test cases updated

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/54476

2822 Mattias Jonsson	2008-09-23
      post push fix, Bug#38719, additional test cases updated

Pushed into 6.0.8-alpha  (revid:mattias.jonsson@sun.com-20080923073013-nbso609zpvzbmotm) (version source revid:mattias.jonsson@sun.com-20080923073013-nbso609zpvzbmotm) (pib:5)

Documented bugfix in the 6.0.8 changelog as follows:

        A duplicate key error raised when inserting into a partitioned table
        used a different error code from that returned by a duplicate key
        error raised when inserting into a table that was not partitioned.

Set bug status to Need Doc Info pending merge to 5.1.

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/66801

2804 Mattias Jonsson	2009-02-18
      Backport of bug#38719 from 6.0 to 5.1
      
      handler::get_dup_key used the called handler for the
      info call, but used table->file handler for errkey.
      Fixed by using table->file->info instead.
     @ mysql-test/r/partition_error.result
        Bug#38719: Partitioning returns a different error code for
        a duplicate key error
        
        Added test for verification
     @ mysql-test/t/partition_error.test
        Bug#38719: Partitioning returns a different error code for
        a duplicate key error
        
        Added test for verification
     @ sql/handler.cc
        Backport of bug#38719 from 6.0 to 5.1
        
        handler::get_dup_key used the called handler for the
        info call, but used table->file handler for errkey.
        Fixed by using table->file->info instead.

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/66805

2805 Mattias Jonsson	2009-02-18
      Backport of test results for Bug#38719 from 6.0 to 5.1
      
      post push fix, Bug#38719, additional test cases updated

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/67000

2806 Mattias Jonsson	2009-02-20
      pre push test result update for bug#38719
      
      Updated with the correct error message.

Backported and pushed to mysql-5.1-bugteam tree.

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/67051

2812 Mattias Jonsson	2009-02-20
      Backport of patch for bug#40003 from 6.0 to 5.1,
      related to the backport of the patch for bug#38719
     @ sql/ha_partition.cc
        bug#40003, archive does not handle dup_key.

Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:azundris@mysql.com-20090224070618-mr7stu6rfcvoj18g) (merge vers: 5.1.33) (pib:6)

Also noted in the 5.1.33 changelog. Closed.

Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:azundris@mysql.com-20090223123708-n9rf2to3g15br7za) (merge vers: 6.0.10-alpha) (pib:6)

Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)

Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)

Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)