MySQL Bugs: #25326: Since table has been partitioned, replication fails due to duplicate key

Bug #25326	Since table has been partitioned, replication fails due to duplicate key
Submitted:	29 Dec 2006 9:19	Modified:	12 Mar 2007 15:18
Reporter:	Chris O'Brien	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.1.14-beta	OS:	Linux (Linux 2.6)
Assigned to:		CPU Architecture:	Any
Tags:	partitioning, replication

Description:
Since partitioning my table "hosted_upload_ip" based on key "huip" into 30 different partitions, replication fails constantly with the following error message:

Last_Error: Error 'Can't write; duplicate key in table 'hosted_upload_ip'' on query. Default database: 'ff_primary'. Query: 'INSERT INTO `hosted_upload_ip` (`uid`,`filename`,`file_id`,`ip`,`timestamp`) VALUES ( '' , '2006-12-29-083034-goldrusdm_82' , '6438102' , '83.176.53.194' , '1167378578')'

If I manually run that query, it executes without issue and a simple SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; will fix replication.

How to repeat:
Partition a table based on primary key and setup replication.

Thank you for a problem report. Please, send SHOW CREATE TABLE results for that hosted_upload_ip table used.

CREATE TABLE `hosted_upload_ip` (
  `huip` bigint(20) NOT NULL AUTO_INCREMENT,
  `uid` bigint(20) NOT NULL DEFAULT '0',
  `filename` varchar(255) NOT NULL DEFAULT '',
  `file_id` bigint(20) NOT NULL DEFAULT '0',
  `ip` varchar(16) NOT NULL DEFAULT '',
  `timestamp` varchar(12) NOT NULL DEFAULT '',
  PRIMARY KEY (`huip`),
  KEY `uid` (`uid`),
  KEY `timestamp` (`timestamp`),
  KEY `file_id` (`file_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1977154 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (huip) PARTITIONS 30  */

This error is also being encountered frequently on the "files" table. Here's the create table statement:

CREATE TABLE `files` (
  `file_id` int(11) NOT NULL AUTO_INCREMENT,
  `category` int(11) NOT NULL DEFAULT '0',
  `game_type` varchar(50) NOT NULL DEFAULT '',
  `program_type` int(11) NOT NULL DEFAULT '0',
  `game_name` varchar(50) NOT NULL DEFAULT '',
  `file_name` varchar(75) NOT NULL DEFAULT '',
  `file_title` varchar(50) NOT NULL DEFAULT '',
  `file_descrip` varchar(255) NOT NULL DEFAULT '',
  `file_size` bigint(21) NOT NULL DEFAULT '0',
  `date_added` bigint(20) DEFAULT NULL,
  `downloads` int(11) NOT NULL DEFAULT '1',
  `author` varchar(25) NOT NULL DEFAULT '',
  `game` varchar(255) NOT NULL DEFAULT '',
  `ext_descrip` text NOT NULL,
  `picture` varchar(255) NOT NULL DEFAULT '',
  `good` int(11) NOT NULL DEFAULT '0',
  `bad` int(11) NOT NULL DEFAULT '0',
  `comments` tinyint(4) NOT NULL DEFAULT '0',
  `dl_var` tinyint(4) NOT NULL DEFAULT '3',
  `filepath` varchar(255) NOT NULL DEFAULT '',
  `ftpdir` varchar(255) NOT NULL DEFAULT '',
  `md5` varchar(40) NOT NULL DEFAULT '',
  `deduped` tinyint(4) NOT NULL DEFAULT '0',
  `unarchived` tinyint(4) NOT NULL DEFAULT '0',
  `archive_state` int(11) NOT NULL DEFAULT '0',
  `archive_readable` tinyint(4) NOT NULL DEFAULT '0',
  `auto_desc` tinyint(4) NOT NULL DEFAULT '0',
  `locked` int(11) DEFAULT NULL,
  `search` tinyint(4) NOT NULL DEFAULT '0',
  `xid` int(11) NOT NULL DEFAULT '0',
  `group` bigint(20) NOT NULL DEFAULT '0',
  `agegate` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`file_id`),
  KEY `downloads` (`downloads`),
  KEY `ftpdir` (`ftpdir`),
  KEY `file_name` (`file_name`),
  KEY `md5` (`md5`),
  KEY `file_size` (`file_size`,`deduped`),
  KEY `file_name_2` (`file_name`,`md5`,`unarchived`),
  KEY `date_added` (`date_added`),
  KEY `search` (`search`),
  KEY `ftpdir_3` (`ftpdir`,`md5`),
  KEY `filepath_2` (`filepath`,`file_size`),
  KEY `ftpdir_5` (`ftpdir`,`file_size`),
  KEY `game` (`game`),
  KEY `program_type` (`program_type`,`filepath`,`date_added`),
  KEY `file_id` (`file_id`,`downloads`),
  KEY `latest_path` (`date_added`),
  KEY `type_path` (`program_type`,`date_added`),
  KEY `filepath_3` (`md5`,`file_size`),
  KEY `author` (`author`),
  KEY `idauthorgame` (`file_id`,`author`,`game`),
  KEY `authorgame` (`author`,`game`),
  KEY `file_group` (`group`),
  KEY `idauthor` (`file_id`,`author`),
  KEY `authorpath` (`author`,`filepath`),
  KEY `lockdpathadded` (`locked`,`filepath`,`date_added`),
  KEY `filepath` (`filepath`)
) ENGINE=MyISAM AUTO_INCREMENT=6438062 DEFAULT CHARSET=latin1 PACK_KEYS=1 /*!50100 PARTITION BY KEY (file_id) PARTITIONS 25  */

I managed to get the errors to stop. I had to drop both tables and re-import the data into tables without partitioning. I then re-partitioned the files table, but not the hosted_upload_ip table. Errors went away.

A simple ALTER TABLE files COALESCE PARTITION 24; would not work. I had to drop and re-import. 

Perhaps there's some sort of issue with having two auto incrementing tables partitioned? Because I was running this setup for days without issue and as soon as I partitioned the hosted_upload_ip table, the issues started occuring.

Interesting. I looked a few moments ago and found this error message:

INSERT INTO ff_primary.`dls_daily` (`file_id`,`file_type`,`downloads`,`timestamp`,`filepath`,`md5`) VALUES ('6253479','0',1,'1167379201','/Terra/RAN_Online(en)_V1.80_20061111.exe','')

Here's the create table statement:

CREATE TABLE `dls_daily` (
  `file_id` int(11) NOT NULL DEFAULT '0',
  `file_type` smallint(6) NOT NULL DEFAULT '0',
  `downloads` int(11) NOT NULL DEFAULT '0',
  `timestamp` varchar(10) NOT NULL DEFAULT '',
  `filepath` varchar(255) NOT NULL DEFAULT '',
  `md5` varchar(35) NOT NULL DEFAULT '',
  PRIMARY KEY (`file_id`,`timestamp`),
  KEY `file_type` (`file_type`,`downloads`,`timestamp`),
  KEY `file_type_2` (`file_type`),
  KEY `downloads` (`downloads`),
  KEY `timestamp` (`timestamp`),
  KEY `filepath` (`filepath`),
  KEY `file_id` (`file_id`,`md5`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (file_id) PARTITIONS 350  */

Correction, same error message, different table.

I had the same experience. Large updates seem to stop after (3.9 million total number of queries as reported by mytop utility). I am running 5.1.14-beta on RedHat FC4. I was not even able to kill the process when it get to that state. The only thing to do is to restart the server. 

Here is my create():

CREATE TABLE traffic (
                device_id varchar(9),start_time datetime ,
                duration int(4), policy_id int(3), service varchar(14), proto  int(3),src_zone varchar(13), dst_zone varchar(13), action varchar(7),
                sent int(5), rcvd int(5), src varchar(15), dst varchar(15),
                src_port int(5), dst_port int(5),
                INDEX id_idx(device_id),
                INDEX time_idx(start_time),
                INDEX zone_idx(src_zone, dst_zone),
                INDEX port_idx(src_port, dst_port)
        )
        ENGINE = InnoDB
        PARTITION BY RANGE (TO_DAYS(start_time)) (
        PARTITION p61101 VALUES LESS THAN (TO_DAYS('2006-11-01')),
        PARTITION p61115 VALUES LESS THAN (TO_DAYS('2006-11-15')),
        PARTITION p61201 VALUES LESS THAN (TO_DAYS('2006-12-01')),
        PARTITION p61215 VALUES LESS THAN (TO_DAYS('2006-12-15')),
        PARTITION p0101 VALUES LESS THAN (TO_DAYS('2007-01-01')),
        PARTITION p0115 VALUES LESS THAN (TO_DAYS('2007-01-15')),
        PARTITION p0201 VALUES LESS THAN (TO_DAYS('2007-02-01')),
        PARTITION p0215 VALUES LESS THAN (TO_DAYS('2007-02-15')),
        PARTITION p0301 VALUES LESS THAN (TO_DAYS('2007-03-01')),
        PARTITION p0315 VALUES LESS THAN (TO_DAYS('2007-03-15')),
        PARTITION p0401 VALUES LESS THAN (TO_DAYS('2007-04-01')),
        PARTITION p0415 VALUES LESS THAN (TO_DAYS('2007-04-15')),
        PARTITION p0501 VALUES LESS THAN (TO_DAYS('2007-05-01')),
        PARTITION p0515 VALUES LESS THAN (TO_DAYS('2007-05-15')),
        PARTITION p0601 VALUES LESS THAN (TO_DAYS('2007-06-01')),
        PARTITION p0615 VALUES LESS THAN (TO_DAYS('2007-06-15')),
        PARTITION p0701 VALUES LESS THAN (TO_DAYS('2007-07-01')),
        PARTITION p0715 VALUES LESS THAN (TO_DAYS('2007-07-15')),
        PARTITION p0801 VALUES LESS THAN (TO_DAYS('2007-08-01')),
        PARTITION p0815 VALUES LESS THAN (TO_DAYS('2007-08-15')),
        PARTITION p0901 VALUES LESS THAN (TO_DAYS('2007-09-01')),
        PARTITION p0915 VALUES LESS THAN (TO_DAYS('2007-09-15')),
        PARTITION p1001 VALUES LESS THAN (TO_DAYS('2007-10-01')),
        PARTITION p1015 VALUES LESS THAN (TO_DAYS('2007-10-15')),
        PARTITION p1101 VALUES LESS THAN (TO_DAYS('2007-11-01')),
        PARTITION p1115 VALUES LESS THAN (TO_DAYS('2007-11-15')),
        PARTITION p1201 VALUES LESS THAN (TO_DAYS('2007-12-01')),
        PARTITION p1215 VALUES LESS THAN (TO_DAYS('2007-12-15')),
        PARTITION p80101 VALUES LESS THAN (TO_DAYS('2008-01-01')),
        PARTITION p9999 VALUES LESS THAN MAXVALUE
        );

All reporters:

Sorry for a delay with this report. Please, try to repeat with a newer version, 5.1.15-beta, and inform about the results.

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".