1 33843327 Duplicate Entry Error In Key Query Sql
Contents |
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
Duplicate Entry For Key Primary In Mysql
About Us Learn more about Stack Overflow the company Business Learn more about mysql error 1062 duplicate entry for key primary hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join
#1062 - Duplicate Entry For Key
the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL duplicate entry error duplicate entry for key primary error even though there is no duplicate entry up vote 5 down vote favorite 1 I am using MySQL 5.1.56, MyISAM. My table looks like this: CREATE TABLE IF NOT EXISTS `my_table` ( `number` int(11) NOT NULL, `name` varchar(50) NOT NULL, `money` int(11) NOT NULL, PRIMARY KEY (`number`,`name`) ) ENGINE=MyISAM; It contains these two rows: INSERT INTO `my_table` (`number`, `name`, `money`) VALUES (1, 'S. Name', 150), (2, mysql duplicate entry for key 'Another Name', 284); Now I am trying to insert another row: INSERT INTO `my_table` (`number`, `name`, `money`) VALUES (2, 'S. Name', 240); And MySQL just won't insert it while telling me this: #1062 - Duplicate entry '2-S. Name' for key 'PRIMARY' I really don't understand it. The primary key is on the first two columns (both of them), so the row I am trying to insert HAS a unique primary key, doesn't it? I tried to repair the table, I tried to optimize the table, all to no avail. Also please note that I cannot change from MyISAM to InnoDB. Am I missing something or is this a bug of MySQL or MyISAM? Thanks. To summarize and point out where I think is the problem (even though there shouldn't be): Table has primary key on two columns. I am trying to insert a row with a new combination of values in these two columns, but value in column one is already in some row and value in column two is already in another row. But they are not anywhere combined, so I believe this is supposed to work and I am very confused
- Fix Error - WordPress Database Error Duplicate Entry for key PRIMARY for Query INSERT INTO wp_options September 12, 2016Pinal DaveSQL1 commentAs many of you know that this blog runs on WordPress and under the hood of WordPress there is a MySQL database.
Duplicate Entry '0' For Key 'primary' Mysql
MySQL database is quite good and is able to hold massive traffic this blog mysql duplicate entry for key unique receives every day. However, just like any database MySQL needs tuning as well as proper management of the same. In
Mysql Duplicate Entry For Key Primary Auto_increment
this blog post we will discuss about how I received a very weird error WordPress Database Error and how I resolved it.Last week, suddenly I got call from a friend that our blog is http://stackoverflow.com/questions/13132535/mysql-duplicate-entry-error-even-though-there-is-no-duplicate-entry loading very slow. Well, when a new blog post is published or newsletter is sent out, it is very common to see a spike in traffic and momentarily slowness in the website performance. However, in this case, the website was consistently running slow. After a while we found a couple of new problems on the site. Due to the slowness of the performance, we also found out that WordPress scheduler http://blog.sqlauthority.com/2016/09/12/mysql-fix-error-wordpress-database-error-duplicate-entry-key-primary-query-insert-wp_options/ was not publishing new blog posts as well as was not taking routine backup of the system.After careful diagnostic I figured out that the issue was with MySQL Database. When I checked the error log, I found the following error in the log. [Fri Sep 09 04:58:03 2016] [error] [client] WordPress database error Duplicate entry ‘3354142' for key ‘PRIMARY' for query INSERT INTO wp_options (option_name, option_value,autoload) VALUES (…) It was very clear that there was a primary key violation in the options table. However, the problem was not easy to solve as I had personally not done any transactions with this table or there was no new update or plugin changes in the recent time. My first attempt was to restore this particular table from older database backup (I take frequent backup my site and its database). Even this particular problem failed and I was not able to get rid of the error.Finally, I searched the internet but alas, there was no real help. At that time, I decided to do various trial and error. Trust me, I spend over 4 hours and various different tricks to get rid of this error. It was very clear to me that it was logical integrity error on the d
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 Overflow the company Business Learn more http://dba.stackexchange.com/questions/106294/add-unique-index-fails-with-duplicate-entry-error-but-no-duplicates-found about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database 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 The best answers are voted up and rise to the top Add duplicate entry 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 DEFAULT 0 ) ENGINE=MyISAM DEFAULT CHARACTER SET=UTF8 COLLATE utf8_general_ci; Required index: ALTER TABLE mytable ADD UNIQUE INDEX idx_key (field_a, duplicate entry for 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, field_e CHAR(1) NULL DEFAULT '', field_f SMALLINT UNSIGNED NOT NULL DEFAULT 0, UNIQUE KEY idx_key (field_a, field_b) ) ENGINE=MyISAM DEFAULT CHARACTER SET=UTF8 COLLATE utf8_general_ci; Update 3 Attempt 2 error solved To populate the table we use INSERT s
be down. Please try the request again. Your cache administrator is webmaster. Generated Sun, 02 Oct 2016 19:03:25 GMT by s_hv987 (squid/3.5.20)