Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Posts
    1

    Unanswered: Error Code: 1022 duplicate key in table on insert

    CREATE TABLE `d_s_rep` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `p_id` bigint(20) NOT NULL,
    `i_id` bigint(20) NOT NULL,
    `d_id` bigint(20) NOT NULL,
    `s_id` bigint(20) NOT NULL,
    `t_id` bigint(20) NOT NULL DEFAULT '0',
    `as_id` bigint(20) NOT NULL,
    `ccount` bigint(20) DEFAULT '0',
    `dcount` bigint(20) NOT NULL DEFAULT '0',
    `icount` bigint(20) DEFAULT '0',
    `di` bigint(20) NOT NULL DEFAULT '0',
    `dhi` bigint(20) NOT NULL DEFAULT '0',
    `r` decimal(25,8) DEFAULT '0.00000000',
    `f_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    `t_date` datetime DEFAULT NULL,
    `t_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`,`f_date`),
    KEY `d_id` (`d_id`),
    KEY `p_id` (`p_id`),
    KEY `f_date` (`f_date`),
    KEY `i_id` (`i_id`),
    KEY `s_id` (`s_id`),
    KEY `t_id` (`t_id`),
    KEY `as_id` (`as_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11064336 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (TO_DAYS(f_date)) (PARTITION p2009OLD VALUES LESS THAN (733863) ENGINE = InnoDB, PARTITION p2009Q2 VALUES LESS THAN (733954) ENGINE = InnoDB, PARTITION p2009Q3 VALUES LESS THAN (734046) ENGINE = InnoDB, PARTITION p2009Q4 VALUES LESS THAN (734138) ENGINE = InnoDB, PARTITION p2010Q1 VALUES LESS THAN (734228) ENGINE = InnoDB, PARTITION p2010Q2 VALUES LESS THAN (734319) ENGINE = InnoDB, PARTITION p2010Q3 VALUES LESS THAN (734411) ENGINE = InnoDB, PARTITION p2010M10 VALUES LESS THAN (734442) ENGINE = InnoDB, PARTITION p2010M11 VALUES LESS THAN (734472) ENGINE = InnoDB, PARTITION p2010M12 VALUES LESS THAN (734503) ENGINE = InnoDB, PARTITION p2011M01 VALUES LESS THAN (734534) ENGINE = InnoDB, PARTITION p2011M02 VALUES LESS THAN (734562) ENGINE = InnoDB, PARTITION p2011M03 VALUES LESS THAN (734593) ENGINE = InnoDB, PARTITION p2011M04 VALUES LESS THAN (734623) ENGINE = InnoDB, PARTITION p2011M05 VALUES LESS THAN (734654) ENGINE = InnoDB, PARTITION p2011M06 VALUES LESS THAN (734684) ENGINE = InnoDB, PARTITION p2011M07 VALUES LESS THAN (734715) ENGINE = InnoDB, PARTITION p2011M08 VALUES LESS THAN (734746) ENGINE = InnoDB, PARTITION p2011M09 VALUES LESS THAN (734776) ENGINE = InnoDB, PARTITION p2011M10 VALUES LESS THAN (734807) ENGINE = InnoDB, PARTITION p2011M11 VALUES LESS THAN (734837) ENGINE = InnoDB, PARTITION p2011M12 VALUES LESS THAN (735173) ENGINE = InnoDB)


    In above mentioned table few inserts fronm insert query in code fails with error

    Total Number of diagnostic records: 1
    SQLSTATE: 23000
    Native Error Code: 1022
    [MySQL][ODBC 3.51 Driver][mysqld-5.1.30-community-log]Can't write; duplicate key in table 'd_s_rep'


    insert statment "does not" include values for id (NOT NULL AUTO_INCREMENT) and t_stamp (NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP).

    I read posts on various forums but could not get the root case for the problem.

    It would be great help if some one could guide me to resolve problem

    Thanks in advance

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is the SQL you are trying to insert with?
    and what does the actual SQL sent to the engine look like (ie a sample of the SQL with any tokens replaced with the actual values being sent.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    A quick observation, why would you want to include both the id and f_date as the primary key when id is auto incremented? It also means that the index is of no use unless you know the id because of the order in the constraint (id, f_date).
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Dec 2011
    Posts
    8
    insert statment "does not" include values for id (NOT NULL AUTO_INCREMENT) and t_stamp (NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP).
    This statement makes me think you meant to include t_stamp in your primary key, not f_date. Is that the issue?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •