Duplicate entry '2147483647' for key 1

Strange problem I can't seem to get my head around. I have a table in a MySQL database with the following structure...

    CREATE TABLE IF NOT EXISTS `tblbaseprices` (
  `base_id` bigint(11) NOT NULL auto_increment,
  `base_size` int(10) NOT NULL default '0',
  `base_label` varchar(250) default NULL,
  `base_price_1a` float default NULL,
 开发者_JAVA技巧 `base_price_2a` float default NULL,
  `base_price_3a` float default NULL,
  `base_price_1b` float default NULL,
  `base_price_2b` float default NULL,
  `base_price_3b` float default NULL,
  `site_id` int(11) default NULL,
  PRIMARY KEY  (`base_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=134 ;

The last base_id I have in there is 132. I assume a couple of records have been deleted to auto_increment is set to 134, as you can see about. I am trying to run the following SQL statement, and when I do, I get the error "Duplicate entry '2147483647' for key 1".

INSERT INTO tblbaseprices (site_id, base_size, base_price_1a, base_price_2a, base_price_3a, base_price_4a) VALUES ('', '', '', '', '', '')

Does anybody have any ideas?

Many thanks!

2^31 − 1 = 2,147,483,647 

The number 2,147,483,647 is ... the maximum value for a 32-bit signed integer in computing

2147483647 is the largest int value for mysql. Just change the type from int to bigint.

With you code I got this error - Unknown column 'base_price_4a' in 'field list'. It means that you are trying to insert into another table (maybe in another schema), and that table has primary key INT and AUTO_INCREMENT=2147483647.

you've hit the 32-bit integer limit, thus preventing the auto increment from incrementing. switching your pk to bigint with a higher column length should fix the issue.

Also, if your PK is never going to be negative, switching to an unsigned int should give you more space.

Try changing the auto_increment column to bigint instead of int, then the max value would be '9223372036854775807' or even '18446744073709551615' if you make it unsigned (no values below 0).

Change your Auto_Increment to the last id in the column so it is continued where it left off.

Be sure you do not delete auto_increment, otherwise it will continue to produce the error.

You're inserting empty strings into numerical columns. As far as I can see, you're also inserting into a column that does not exist in the schema. My guess is this has something to do with your error.

signed and unsigned issue

alter table tblbaseprices
modify column site_id int(10) unsigned NOT NULL;

reference - http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

• make sure unsigned for foreign key (in this case could be the site_id)
• it could be caused by trigger,
• there is no int(11), the max it can go is int(10)
• there is no need to allow negative value for ID
• to be consistently using same data type for primary key

Today I got the error duplicate key 2147483647

I think it came out when I tried to insert a record into database from PhpMyAdmin, while typing, I also tried to enter the key value and it was eider lower than the current Next autoindex or I tried to type something like 99999999999999 as the key field, and that caused it to set Next autoindex to maximum

Anyway, the erorr was caused because Next autoindex was 2147483647 for that table. My table was empty so I fixed it by this query:

ALTER TABLE table_name AUTO_INCREMENT = 0

if your table contains data, then replace 0 with your maximum key plus 1

it's a database issue. check your phpmyadmin > your DB > structure, your primary key should be setted in "bigint", not just "int"

CREATE TABLE IF NOT EXISTS `tblbaseprices` (
  `base_id` bigint(11) NOT NULL auto_increment,
  `base_size` int(10) NOT NULL default '0',
  `base_label` varchar(250) default NULL,
  `base_price_1a` float default NULL,
  `base_price_2a` float default NULL,
  `base_price_3a` float default NULL,
  `base_price_1b` float default NULL,
  `base_price_2b` float default NULL,
  `base_price_3b` float default NULL,
  `site_id` int(11) default NULL,
  PRIMARY KEY  (`base_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=134 ;

A good explanation of that is here: http://realtechtalk.com/Duplicate_entry_2147483647_for_key_PRIMARY_MySQL_Error_Solution-2015-articles

Essentially you are trying to insert a value larger than the maximum size an INT supports which is literally the number being given to you in the error.

If you are importing data than one of the fields contains a larger value than the INT size. You could also modify your table to be a BIGINT which would take care of the issue as well (of course at the cost of extra disk space).

A common reason is that you are using some script generating large/random numbers. You should add some check to make sure the size is the same or lower than that maximum INT size of 2147483647 and you'll be good to go.

Duplicate entry '57147-2147483647' for key 'app_user' [ INSERT INTO user_lookup (user_id, app_id, app_user_id, special_offers, ip_address) VALUES ('2426569', '57147', '4009116545', 1, 1854489853) ]