Duplicate entry on an AutoIncrement field?

[doni49]

Hi gang.  This one's got me stumped!  I've got a script that is supposed to add rows to a mySQL DB.  And for several weeks, it was working just great.  Then last week, every time it attempts to add a row, it throws an error message.

 

Here is the code that I'm using to insert the row:

INSERT `Log` SET `machineID`='14',`weight`='50',`reps`='12',`sets`='2',`goalMet`='1'

 

The DB structure for the Log table is shown in the attached jpg file.  Notice that the id field is set to AutoIncrement. 

 

Following is the error message that it throws when I attempt to add rows.

 

I cannot add fields to the database because: Duplicate entry '127' for key 1

 

The very last row that it successfully added was id #127.

 

[attachment deleted by admin]

[DavidAM]

Interesting, I always wondered what the symptoms were when you run out of auto_increment values.

 

You defined the ID as tinyint, which is a one-byte integer. Since you did not specify UNSIGNED, that column can range from -128 to +127. So you can not increment the value for a new row, since that would cause the ID value to overflow.

 

Since AUTO_INCREMENT columns should never be negative, you should always define auto_increment columns as UNSIGNED. An unsigned tinyint can range from 0 to 255. However, the server starts auto_increment columns at 1, so you can store 255 rows with a tinyint ID, if you switch it to unsigned.

 

Note: Before you ask, the (4) in the datatype (tinyint(4)) is the number of DISPLAY columns the server will use when you select the value. It has nothing to do with how large of an integer you can enter into the field. I really think this is a dumb design, but hey, they didn't ask me.

 

[doni49]

Never Mind.  I changed the id field from TinyInt to SmallInt and it's working now.

 

Edit:  I was just posting this when your reply posted.  So maybe I should also change it to UNSIGNED too?  I'll go ahead and do that.

 

Thanks!

[DavidAM]

If the Log table's ID is referenced from any other table (making it a foreign key in the other table), be sure you change the definition there as well. Foreign Keys should have the same data type definition (size and signed/unsigned) as the primary key that they are referring to.

[Pikachu2000]

Why not just go ahead and make it UNSIGNED INT so you don't have to change it again in a year?

[doni49]

If the Log table's ID is referenced from any other table (making it a foreign key in the other table), be sure you change the definition there as well. Foreign Keys should have the same data type definition (size and signed/unsigned) as the primary key that they are referring to.

 

Thanks.  I took care of that last night.  I just have learn one of these days how the different data types relate to each other--range of possible values, the amount of data space each takes etc.