Mysql insert query returns ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

Question

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 key 'PRIMARY'

It's the first time I get this error maybe this suggests that some kind of limit has been reached. Anyway I've checked in other posts complaining for the same error and found out that triggers may be the problem. Unfortunately it's not the case:

mysql> SHOW triggers;
Empty set (0.00 sec)

EDIT The structure of my users table is shown below:

> *************************** 1. row ***************************
  Field: uid
   Type: int(11)
   Null: NO
    Key: PRI
Default: NULL
  Extra: auto_increment
*************************** 2. row ***************************
  Field: name
   Type: varchar(50)
   Null: NO
    Key: 
Default: NULL
  Extra: 
*************************** 3. row ***************************
  Field: email
   Type: varchar(100)
   Null: NO
    Key: UNI
Default: NULL
  Extra: 
*************************** 4. row ***************************
  Field: encrypted_password
   Type: varchar(80)
   Null: NO
    Key: 
Default: NULL
  Extra: 
*************************** 5. row ***************************
  Field: salt
   Type: varchar(10)
   Null: NO
    Key: 
Default: NULL
  Extra: 
*************************** 6. row ***************************
  Field: descrizione
   Type: varchar(600)
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 7. row ***************************
  Field: motto
   Type: varchar(100)
   Null: NO
    Key: 
Default: 
  Extra: 
*************************** 8. row ***************************
  Field: status
   Type: varchar(100)
   Null: NO
    Key: 
Default: Hey new gambler! Share your thoughts!
  Extra: 
*************************** 9. row ***************************
  Field: game
   Type: varchar(100)
   Null: NO
    Key: 
Default: 
  Extra: 
*************************** 10. row ***************************
  Field: pokeroom
   Type: varchar(100)
   Null: NO
    Key: 
Default: 
  Extra: 
*************************** 11. row ***************************
  Field: score
   Type: int(11)
   Null: NO
    Key: 
Default: 0
  Extra: 
*************************** 12. row ***************************
  Field: created_at
   Type: datetime
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 13. row ***************************
  Field: updated_at
   Type: datetime
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 14. row ***************************
  Field: photo
   Type: varchar(500)
   Null: NO
    Key: 
Default: 
  Extra: 
*************************** 15. row ***************************
  Field: panorama
   Type: varchar(500)
   Null: NO
    Key: 
Default: 
  Extra:

How can I solve this problem?

`2147483647` is the largest `int` value for MySQL. Do you really have 2147483647 users? — Explosion Pills, Sep 05 '13 at 18:45
What's the Primary Key for the `users` table? You're probably going to need to set its value too, in the `INSERT INTO`, unless it was created as an `AUTO INCREMENT`. — Lynn Crumbling, Sep 05 '13 at 18:46
You've probably forgotten to set an auto_increment on your primary key field, and its default value happens to be max_int for that field. first insert will work, second insert will fail because the int didn't auto_increment. — Marc B, Sep 05 '13 at 18:50
The 'uid' column is the PRIMARY KEY and has also the AUTO INCREMENT Please see my edit. — UserK, Sep 05 '13 at 18:59
What does `select uid from users order by uid desc limit 1` return? — Lynn Crumbling, Sep 05 '13 at 18:59
I agree with @ExplosionPills http://stackoverflow.com/questions/7171312/what-is-the-max-number-if-i-store-int255-in-mysql — M Khalid Junaid, Sep 05 '13 at 19:20
@Lynn Crumbling +------------+ | uid | +------------+ | 2147483647 | +------------+ — UserK, Sep 05 '13 at 19:28

UserK · Accepted Answer · 2013-09-05T20:57:21.123

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:

INSERT INTO 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 |
+------------+----+
15 rows in set (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:

alter table users drop primary key;

modified it again to remove the auto_increment attribute:

alter table users modify uid varchar(40) not null unique;

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

alter table users add id int(11) not null 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!

I think the root of this problem is caused by uniqid("",true) returning an ID like "546e941cec14a4.31786554" which is then being cast to an integer by mySQL. This very quickly increases the max auto_increment value as you've seen. The solution is to use a unique index on UID and set ID to be the autoincrement field. — GuruBob, Nov 21 '14 at 01:25

score 1 · Answer 2 · answered Jun 20 '14 at 16:04

1

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.

answered Jun 20 '14 at 16:04

Wayne Roddy

19
1

In some cases, dropping PRIMARY key is not possible! People don't always use `PK_ID` as their primary key; it also can be a mixed key from multiple columns. – Zeinab Abbasimazar Nov 03 '15 at 12:39

score 1 · Answer 3 · answered Mar 13 '21 at 14:39

1

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.

answered Mar 13 '21 at 14:39

erptocoding

305
3
5

score 0 · Answer 4 · answered Apr 04 '22 at 16:04

My case:

-- No real duplicate entry for PRIMARY KEY (all entries were distinct)

-- No PRIMARY KEY overflow

-- My PHP/MySQL script worked fine for several days, no change in script

-- Suddenly, got "Duplicate entry '123456789' for key 'PRIMARY'"

Solution: REPAIR TABLE problem_table;

I suspect that aborting the script with ^C caused the problem.

score -2 · Answer 5 · answered Jan 21 '16 at 15:03

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.

Mysql insert query returns ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

5 Answers5

Linked