Mysql Unique Index Error
Contents |
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack mysql duplicate entry for key 2 Overflow the company Business Learn more about hiring developers or posting ads with us duplicate entry for key mysql Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database
Duplicate Entry For Key 'unique'
professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer
Duplicate Unique Key Found Wow
The best answers are voted up and rise to the top Add UNIQUE index fails with duplicate entry error, but no duplicates found up vote 1 down vote favorite Given table: CREATE TABLE mytable ( field_a CHAR(15) NOT NULL DEFAULT '', field_b MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, field_c SMALLINT UNSIGNED NOT NULL DEFAULT 0, field_d SMALLINT UNSIGNED NOT NULL DEFAULT 0, field_e CHAR(1) NULL DEFAULT '', field_f SMALLINT UNSIGNED NOT NULL mysql duplicate entry error DEFAULT 0 ) ENGINE=MyISAM DEFAULT CHARACTER SET=UTF8 COLLATE utf8_general_ci; Required index: ALTER TABLE mytable ADD UNIQUE INDEX idx_key (field_a, field_b); Data size is about 51 Mio. rows. Following problem: Attempt 1: If I try to create the index after the data is in the table it fails with duplicate key error. A select on the failed key returns only one(!) row. [23000][1062] Duplicate entry 'aaaaaaaaaaaaaaa-11111' for key 'idx_key' SELECT COUNT(*) FROM mytable WHERE field_a='aaaaaaaaaaaaaaa' AND field_b='11111' returns 1 (!) Attempt 2: If I create the index on an empty table or make a combined private key and then put the data into the table, the table contains only 27 of 51 Mio. rows (!). Is there some kind of a limit on the unique index or a bug? I use MariaDB 10.0.20. Please help. Update 1 A count of unique rows SELECT COUNT(DISTINCT field_a,field_b) from mytable; returns 50 Mio rows. So there are about 1 Mio duplicates. This however does not explain the 27 Mio from attempt 2 or wrong exception from attempt 1. Update 2 the table used by the attempt 2: CREATE TABLE mytable ( field_a CHAR(15) NOT NULL DEFAULT '', field_b MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, field_c SMALLINT UNSIGNED NOT NULL DEFAULT 0, field_d SMALLINT UNSIGNED NOT NULL DEFAULT 0
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings
#1062 Duplicate Entry For Key
and policies of this site About Us Learn more about Stack Overflow mysql add unique index the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation mysql duplicate entry for key primary Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; http://dba.stackexchange.com/questions/106294/add-unique-index-fails-with-duplicate-entry-error-but-no-duplicates-found it only takes a minute: Sign up PHP - detect mysql update/insertion failure due to violated unique constraint up vote 8 down vote favorite 2 This is kind of similar to this question: PHP MySQL INSERT fails due to unique constraint but I have a different twist. Let's say I have a table with only one column. The column's name http://stackoverflow.com/questions/8449540/php-detect-mysql-update-insertion-failure-due-to-violated-unique-constraint is "title" and it has a unique constraint placed on it. First I insert a row where title = "something". The next time I try to insert "something" it will fail due to a unique key constraint (which is good). What I'd like to do is allow it to fail, and check the error code provided by mysql to ensure it failed due to a unique key constraint. (i.e. let the database handle the uniqueness, and I just handle the error code and tell the user that title already exists when the result comes back). Is there a way to do this? php mysql share|improve this question asked Dec 9 '11 at 17:42 John Humphreys - w00te 14.8k1671154 add a comment| 6 Answers 6 active oldest votes up vote 5 down vote accepted http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html http://php.net/manual/en/function.mysql-errno.php I've had to do this in the past, and it's not fun: if( mysql_errno() == 1062) { // Duplicate key } else { // ZOMGFAILURE } A note on programming style (Credits to jensgram from this answer) You should always seek to avo
be duplicated records. If so a regular alter query http://www.stetsenko.net/2010/08/mysql-how-to-force-a-new-unique-index-to-drop-duplicated-rows/ to create a new unique index will return an error https://dev.mysql.com/doc/ndbapi/en/ndb-error-codes-constraint-violation.html like below. ERROR 1062 (23000): Duplicate entry ‘132653-47' for key 1 However keyword IGNORE allows to force a new unique index to drop duplicated rows. ALTER IGNORE TABLE `t` ADD UNIQUE INDEX `i` (`f1`, `f2`); In this case duplicate entry the output will be something like this: Query OK, 507 rows affected (0.02 sec) Records: 507 Duplicates: 0 Warnings: 0 So simply using IGNORE helps to save time on writing a custom script to clean up duplicates. 2 Comments so far Josh on January 10th, 2014 How duplicate entry for do we do this with the CREATE UNIQUE INDEX operation? Where does the word IGNORE go? Alex on April 8th, 2014 Hi Josh, Does not seem like CREATE UNIQUE INDEX supports IGNORE. Any specific reason you prefer CREATE INDEX over ALTER TABLE? Posting your comment. Leave a reply name (required) email ( will not be shown ) (required) website Most Popular MySQL: how to ignore checking of foreign key constraints for InnoDB tables 23,912 views MySQL: Group Concatination 16,561 views PHP: Daylight Savings Time Support 15,688 views PHP: How to get microseconds 7,479 views Anticipate vs Expect 6,722 views Categories Android CSS English Events Funny stuff JavaScript Management Movies Music MySQL Notes PHP Tools Yearly Archives 2016(4) 2015(4) 2014(5) 2013(14) 2012(13) 2011(30) 2010(50) 2009(70) 2008(19) Stay Updated RSS Articles Copyright ©2008-2012 Alex Stetsenko Powered by WordPress | Designed by Stephen Reinhardt
Connectors More MySQL.com Downloads Developer Zone Section Menu: Documentation Home MySQL Cluster API Developer Guide Preface and Legal Notices MySQL Cluster APIs: Overview and Concepts The NDB API The MGM API MySQL Cluster Connector for Java MySQL NoSQL Connector for JavaScript ndbmemcache—Memcache API for MySQL Cluster MySQL Cluster API Errors MGM API Errors Request Errors Node ID Allocation Errors Service Errors Backup Errors Single User Mode Errors General Usage Errors NDB API Errors and Error Handling Handling NDB API Errors NDB Error Codes: by Type No error Errors Application error Errors No data found Errors Constraint violation Errors Schema error Errors Schema object already exists Errors User defined error Errors Insufficient space Errors Temporary Resource error Errors Node Recovery error Errors Overload error Errors Timeout expired Errors Node shutdown Errors Internal temporary Errors Unknown result error Errors Internal error Errors Function not implemented Errors NDB Error Codes: Single Listing NDB Error Classifications ndbd Error Messages ndbd Error Codes General Errors VM Errors NDBCNTR Errors DIH Errors ACC Errors TUP Errors LQH Errors NDBFS Errors Sentinel Errors ndbd Error Classifications NDB Transporter Errors MySQL Cluster Internals Index Download this Manual PDF (US Ltr) - 5.3Mb PDF (A4) - 5.3Mb EPUB - 2.3Mb HTML Download (TGZ) - 2.2Mb HTML Download (Zip) - 2.3Mb MySQL Cluster API Developer Guide / ... / MySQL Cluster API Errors / NDB API Errors and Error Handling / NDB Error Codes: by Type / Constraint violation Errors 7.2.2.4 Constraint violation Errors The following table lists and describes NDB Constraint violationerrors (error type CV). Table 7.4 This table describes NDB API Constraint violation errors NDB Error Code MySQL Error Code NDB Error Type Error Message 255 HA_ERR_NO_REFERENCED_ROW CV Foreign key constraint violated: No parent row found 256 HA_ERR_ROW_IS_REFERENCED CV Foreign key constraint violated: Referenced row exists 630 HA_ERR_FOUND_DUPP_KEY CV Tuple already existed when attempting to insert 839 DMEC CV Illegal null attribute 840 DMEC CV Trying to set a NOT NULL attr