CODEWITHSUNDEEP
mysqlsql
Chris Mccabe
Chris Mccabe

Reputation: 1951

two column primary key in mysql returning error and general funnyness

I have created a two column key in my mysql db (app_id, dated) and when i run the following query i get a problem

INSERT INTO `facebook_application_shares` (`user_id`, `app_id`, `dated`, `stats`) VALUES ('80', '269456199802533', '2012-04-24', '0')

I get the following error- even though the app_id is 269456199802533 and not 2147483647

Duplicate entry '2147483647-2012-04-24' for key 1

and then when i check the actual db table the value is actually "2147483647"

I am very much confused by this

enter image description here

Upvotes: 2

Views: 117

Answers (1)

Quassnoi
Quassnoi

Reputation: 425863

2147483647, or 0x7FFFFFFF, is the greatest number MySQL is able to store in an INT SIGNED column (which you app_id seems to be). Greater values just get truncated to it.

Make your app_id a BIGINT UNSIGNED.

Note that INT(100) is still a 4-bit integer, with an optional hint to an application not to show more than 100 digits from it:

This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

It's not that it is really able to store a googol - 1.

Upvotes: 4

Related Questions