Duplicate Entry 1 For Key Primary

Более 2 768 348 ключей, кряков, кейводов и других серийников

Duplicate Entry 1 For Key Primary

How to fix: MySQL Duplicate entry for key PRIMARY on Auto_Increment Adrian Singer, 08-20-2009 We encountered an interesting error on a client database this past few days, where MySQL was issuing random errors, complaining about : Duplicate entry '1' for key 'PRIMARY' These errors were coming up on various tables at different times in the day, but the crazy thing is - the duplicate key error is on an auto-increment field! That's not normal Duplicate entry 1 for key primary. CREATE TABLE test (id int auto_increment, somevalue int, primary key (id)); There's no way you can generate a duplicate key error on the 'id' field, unless you manually pass the value of the auto_increment id field (which we were not) Running a OPTIMIZE TABLE statements (this rebuilds indexes) as well as manually running REPAIR TABLE for each table, but nothing helped. The table indexes were obviously corrupted in some way, although MySQL tools reported all is well. . . How to fix this problem The only way we could fix it, is by rebuilding the entire database from scratch. Dropping all tables and reloading them from a backup. Step 1 mysqldump -uUSER -pPASSWORD DBNAME > /tmp/my_backup. sql Replace USER with your MySQL username, PASSWORD with your MySQL password and DBNAME with the databasename you are looking to repair. Step 2 drop database DBNAME; jp, 08-22-2009 The duplicate key entry happens when you reach the upper limit of the auto increment field, in your case, being a signed int the upper boundary is 2147483647. Once the last record is with the id 2147483647, when you insert a new record the id should be 2147483648. But mysql reduce it inside the valid range, and tries to insert the id 2147483647, therefore it produces a duplicate key entry. IMHO it's better altering the index field, setting it to an unsigned int (4294967295) or an unsigned bigint (18446744073709551615). Probably the unsigned int is enough for your client's requirements and it uses 4 bytes instead of 8 of the big int so the index would do it better in terms of cpu and memory. ALTER TABLE test MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT; The current solution works because, you are reusing the ids of the erased rows, the duplicate key error will happen again depending on how may rows were deleted from the table previously. Ben, 09-16-2009 Adrian, which MySQL version are you using? I'm seeing this same problem occuring on a 5. 0. 51a MySQL InnoDB table. I'll ask my database admin to rebuild the table as you described. Unfortunately, I haven't found an official bug report of this, probably because it's hard to reproduce this problem. JP: My problematic table's primary key is an unsigned int and the primary key count is currently at 1000199, so nowhere near the upper boundary. Glenn, 12-03-2009 Hi Adrian, I'm seeing sort of a similar problem over here yet im not convinced that restoring the db is the only solution. You could: auto_increment_increment = 10 auto_increment_offset = 3 If the previous offset was 1 for example, you can never get a clash again. This just requires a server restart. However, I dont see them going away even after restart so it happens nomatter of what data was already stored. An alter table AUTO_INCREMENT= <old> * 10 for example should also do the trick, yet this is so slow once mysql feels like it has to use a temp table to do so. Whenever you use partitions in mysql that seems to happen a lot when altering these tables later. With huge tables/db's you don't have the choice of backup/restore or even use ALTER on every affected one. . . My search continues. . . Pushpinder Bagga, 04-30-2010 I was getting the error as Duplicate entry '127' for key 'PRIMARY' and the reason was my primary key id auto_increment field set to tinyint which has the maximum value of 127 so replacing it to medium int solved the error in seconds. . . cheers! Echo51, 05-02-2010 I had the same trouble on 127 on a TinyINT autoincrement, which i fixed by making it a INT instead, it seems kinda random, but that fixed it. . David, 06-28-2010 I'm having this problem but in what I assume is a VARCHAR column. The error is occurring when I update a Drupal databse to the latest version. My exact error is: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'user-menu' for key 'PRIMARY' A few people here have mentioned InnoDB tables. . . that cuts down the number of tables I have to check significantly so I guess I just have to try and work it out by hand. . . backup and restore definitely isn't working. lkajsdf, 07-09-2010 This problem happened to me when I used ssh to remotely import a 1. 1gb SQL dump. SSH timed out midway and upon trying to start importing the data where it left off, I was getting this error. "id" was a medium int (9). . I made it unsigned int (10) as per jp's comment. Worked like a charm! crist, 09-19-2010 Actually the problem seems deeper, i am having the same problem, in on a fresh DB. my key is declared as BIGINT(20), after in the insert of one row, the second row complain on 'duplicate key value 0'; and the key is declared auto_inc!! gamer, 09-22-2010 Full Hyperion, 11-02-2010 j5, 12-15-2010 In my case the table had a row with a value of '0' for the autoincrement column. Updating it to a number different from 0 (greater than 0, to be more precise) solved the problem. LarryK, 01-01-2011 Thank you so much for the great post. . . 1, 2, 3 worked like a charm. . . . this article is golden !! Just what i was looking for. Manis, 01-12-2011 I am facing similar issue in my company's QA DB. is there a simpler solution than reloading DB? Reloading DB will not a viable solution for production DB (Some tables contains millions of rows). So I am trying to find an answer which is good enough for production DB. Kussin, 01-25-2011 manual. . . Jezer, 02-14-2011 There is another reason why this error can come up, and it took me ages to find it but I finally got it. My co-worker, and thank goodness it's his last day today. . . had added a trigger to audit some DB operations. The trigger attempted to log the operation being undertaken to another table. He was inserting the primary key as 0 however there was already a record with the primary key of zero in the secret auditing table! So CHECK YOUR TRIGGERS! Sometimes the error is not caused by the table your trying to change, but the table your trigger is trying to change! It's very deceptive. I was pulling my hair out, the SQL was working on other databases, my column size was perfectly ok, I flushed and regenerated the tables and indexes of course no luck! TRIGGERS! Alien, 03-11-2011 kirk roberts, 03-17-2011 jim, 03-18-2011 Alexander Vassbotn Røyne-Helgesen, 03-25-2011 Emanuel, 04-05-2011 Eso me paso a mi, lo que pasaba es que en la tabla donde tengo las llaves foraneas tengo como index los campos foraneos, pero en el Index Type lo tenia como Unique y lo cambie a Normal y se soluciono el problema. Suerte autoincrement field have reached limit. So you can backup and start your data from beginning and it's work very well for me Duplicate entry 1 for key primary. . WendyMcF, 07-06-2011 jp's post applied to my situation: > The duplicate key entry happens when you reach the upper limit of the auto increment field I'm using a table that I update once a month, deleting all existing rows. Periodically, the Auto_increment primary key reaches its limit so I reset it. ALTER TABLE my_freshly_deleted_table AUTO_INCREMENT = 1; To see the next Autoindex: SHOW TABLE STATUS LIKE 'my_freshly_deleted_table'; Martin (Inverness), 10-10-2011 I had this issue on a client's site and found that whoever suggested this:- "In my case the table had a row with a value of '0' for the autoincrement column. Updating it to a number different from 0 (greater than 0, to be more precise) solved the problem. " Was absolutely correct. WHY an auto_increment field can't start at ZERO I don't know, but mySQL happily added in a third row once the first entry in the database was, in fact, 1 and not 0. So thank you for that. sohbet, 10-21-2011 Wateva, 12-17-2011 Thanks guys. . Managed to fix my forum with that. . Had a backup from 2hs before installing PHPNuke. . The portal 'nuked' the database with only "nuke_" tables. Not sure why it happened. It shouln't, at all. Anyway, only dropping the existing tables and importing the 2hs old backup did the trick. . Thanks for the tutorial. PS: Tried importing with PHPMyAdmin through the . /upload folder and got errors all around. Got to go telnet. Didn't use SU at all! And that was great cuz I had no SU access. Thanks again. . . Your tips were a life saver! davel, 12-27-2011 i had this same problem with dupe entry s i created a php script that updates the hidden field with an index value. this is not a fix but its another option. mr-magento, 01-04-2012 I changed The key EAA51B56FF092A0DCB795D1CEF812B7B from unique to Index. Is this going to cause issues down the road? Adorjan Princz, 02-21-2012 Same issue and the solution for me was to disable and re-enable the auto increment. I can't imagine what caused the problem because it was a newly created table and I encountered the issue when I was trying to insert the second row. Greg, 03-28-2012 I was having the same problem. I changed the attributes on the column. Unchecked auto increment, change int to varchar save the changes. insert two rows into the database which worked fine. Then delete the records entered, reset the attributes back to int and auto increment. worked fine for me. . . isfahan, 04-14-2012 hi. . I am facing a problem. I have a online form. when I fill this form and submit. my form submited well without any error. but when I went to the C-Panel for to check and Grab out this form data. I am finding values 0,0,0,0 in all fields. for example. I have 6 fields created in my form. 1 NAME 2. CITY NAME 3. E-mail id 4. Contact No 5. Pass and 6. Comments Area in my form. but when I go the C-panel for to get out the data from above field after completing form. I received values 0,0,0,0,0, in all fields. please help me out. web development, 06-04-2012 Hi, I had the case with MySQL 5. 1. I checked, analyzed and optimized the table and the issue was fixed. From my experience as DBA I guess either the analyze table or the optimize table does the trick since it is related to indexes. Rgds Jezer, thank you for sharing your solution. I had the same problem here and would NEVER associate the duplicate key error with the trigger! Kris, 06-12-2012 SqlExceptionHelper:144 - Duplicate entry '1449-8798' for key 'PRIMARY' But when I do a dump of the database I can't find the key '1449-8798' at all Vadim Sachno, 06-21-2012 The way I fixed such a problem was making a new primary key index and deleting the "PRIMARY" index. Just try something like ALTER TABLE `table_name` DROP INDEX `index_name` Jai vyas, 07-14-2012 Vincent, 08-14-2012 My solution was different but super easy. For me this error happened immediately after a server crash. It seems it must've crashed in the middle of a row insert so the db thought the new id had already been used but really it hadn't. So all I did was look at the options for the table using MySQL Workbench where it shows the autoincrement value. It turns out the next value was exactly the one it was trying to insert, so I just increased this value by 1 and that solved the problem. I'm sure there's a command line way to do that as well but I used MySQL Workbench. miryam, 10-17-2012 yamara, 11-20-2012 jamal, 10-14-2014 Rajeev Saxena, 05-01-2015 robby, 07-13-2015 i have duplicate entry 138903 for key primary problem can any one help me to fix this?? thanks a lot tobi, 08-04-2015 ludo, 03-01-2016 Dave Poole, 07-22-2016 The problem occurs when trying to inject a value into a field that is an auto_increment. An auto_increment in MySQL can have a minimum value of 1. Attempting to insert a zero value will cause MySQL to override the value and set it to 1 therefore when you try and insert the record you wanted to have a value of 1 you will get the duplicate error message. The solution is to set the @@SQL_MODE global variable before your statements and reset it afterwards. SET @OLD_SQL_MODE = @@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; { Your insert statements} miyashiroalia, 10-08-2016 Informative article ! my business partners recently encountered https://goo. gl/4sOmJy, to sign pdf ! It's quite uncomplicated to try out and it's useful , I received notice that they are offering a free promotion now Smithc589, 12-05-2016 YourSql, 01-02-2017 dhawk, 03-28-2017

Самые популярные ключи, кеи, кряки.

Популярные кеи, которые в данный момент ищут пользователи.

75

Размер базы файлов

898975 Серийники
2343409 Ключи
13020 Кряки
52568 Кейводы
Яндекс.Метрика