[SOLVED] cannot add new entry to db

[kev wood]

i am trying to add a new entry in a mysql db and keep getting this error.

 

Duplicate entry '127' for key 1

 

i have never seen this error before and dont know what it means.  anyone now where i can get an explanation of this error and maybe a pointer on how to get rid of this problem.

[Yesideez]

It means you're trying to add a record to your table and whatever you've got set as the primary key already has the same value existing that you're trying to add.

 

You'll need to remove the primary key field from your INSERT query in order to make it work.

[kev wood]

no that is what i was thinking it would be but i was wrong.  well not entirely wrong any way. 

 

it is a website that i had not built so the db was not set up by me and not set up in the same way as i would have set it up.  the primary id had been set up as a tinyint and unknown to me until about 5 min ago a tiny int can only hold up to 128 pieces of information so it is 0 > 127 which equates to 128.

 

the db was already holding 127 rows so it could not add another row to the db so it was trying to add the new row in row 127 which already exists.

 

to fix the problem i have just changed the data type of the row to an int and it is all working again now.

 

well at least i have reached my daily goal already in learning something new.  i will now have a relaxing day since i have only been in for 2 hours. ha ha if only.

[Yesideez]

If you set the field to TINYINT(3) UNSIGNED it can hold up to 255.

 

If there's a possibility that there's going to be more than 255 then change the datatype to something like MEDIUMINT.

[kev wood]

i have just read something that said that a tinyint could only hold up to 127 pieces of information and the tinyint in the db had been set as a tinyint(4) and would not hold anymore information than 127 rows.

 

strange.  if you say it can hold up to 255 with a tinyint(3) why would it not go passed 127 for a tinyint(4).  is tinyint(4) the wrong syntax for this data type can it only be set as a tinyint(3).

 

 

[Yesideez]

There are two states a numerical datatype can be in: SIGNED or UNSIGNED.

 

SIGNED can hold negative values and UNSIGNED can't

 

SIGNED TINYINT: -127 to +127

 

UNSIGNED TINYINT: 0 to 255

[Yesideez]

btw, you can't have TINYINT(4)

 

You can have 1-3.

 

Check this page out: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

 

Lists what values the numerical data types can hold.

[kev wood]

so from what i can see the db had been set up with a signed tinyint but the increment had started from 0 so it could have only ever got up to 127 as this is the highest number it can hold when it is a signed data type.

 

didnt think you could have that from what i have just been reading.  for what he had set the db up for i would have just used int for the id to start with.  i would not have had these problems to begin with and tinyint should not be used for a db as it is to small a number i feel. 

 

i had just found this page before your post.

 

http://www.codingforums.com/showpost.php?p=540233&postcount=6

[Yesideez]

Bearing in mind that INT is a silly large number do you really expect that number of lines?

 

I mostly use SMALLINT - just saves space in the database.

[kev wood]

well if i do use int at least i will not have to back to the db for a while. good point about not using the int as it is a ridiculously high number.