Mysql Insert Query Returns Error 1062 (23000): Duplicate Entry '2147483647' For Key 'primary'

I've noticed an error during an insert query in my database. mysql> insert into users (name) values ('Gepp'); returned: ERROR 1062 (23000): Duplicate entry '2147483647' for ke

Solution 1:

Thank you guys for your help! There was a bad configuration of the table. The uid column had the primary key and the auto_increment attribute but in the project I'm working on users were created with a query like this:

INSERTINTO users(uid, name, email, encrypted_password, salt, created_at) VALUES('12342354355.54534543','bollo','sai','dsfsd','sdsdf','23')

The uid was generated by the PHP function uniqid("",true) and this caused the problem

select uid,id from users;
+------------+----+| uid        | id |+------------+----+|183|1||5224|2||5228|3||52288|4||515620|5||519030|6||5156147|8||5156151|9||5156205|10||5157726|11||52289002|12||515615576|13||2147483647|14|+------------+----+15rowsinset (0.00 sec)

As you can see a new uid, created by a query like the one above, was always greater than the previous one. Probably the auto_increment accepted only uid value greater than the last value inserted. I have been lucky for 14 registrations and then the uid value exceeded the maximum allowed by the definition of the column and caused the error.

I've solved the problem by removing the Primary_Key from the uid columm:

altertable users dropprimary key;

modified it again to remove the auto_increment attribute:

altertable users modify uid varchar(40) notnullunique;

and finally added a new column called id in order to track and count users registrations:

altertable users add id int(11) notnull auto_increment primary key;

In the end the error was caused by a bad organization of the database and the functions acting on it. My fault!

Solution 2:

I was getting a very similar duplicate PRIMARY value for the index when running a large script to input 1,000 of rows.

I dropped the primary index key, then ran my script, then re-enabled my "id" column as primary and now everything works fine.

Solution 3:

Its because the AUTO_INCREMENT is increasing IDs but your input data as the same ID. Remove Auto_Increment from table definition or remove IDs from input file.

Solution 4:

Recently I solved the similar issue, error code:1062 duplicate entry. From this page how to solve mysql error code:1062 duplicate key? I found that, this error is because of the primary key field data type reached its upper limit, also changing the data type from int to bigint may helpful but changing the data type is depending on your requirement. There is a workaround in that page, i think it will help you to understand this issue better, thank you.