Duplicate entry on auto-increment field?

[wiltonrossi]

Hello guys,

Yesterday I had a hell of a day due to a mysterious problem. There is a table in my site which is used to monitor access and for the generation of reports. Whenever the users visit certain pages a new record is generated inserting the user login (if he is logged), the page visited and the time it was visited.

It was working fine for two years until yesterday when I got the following error message:

----
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[TCX][MyODBC]Duplicate entry '278655' for key 1

/area_rest/MonitoraAcesso/default.asp, line 29
----

This file is reached via an include. The line is the one which inserts new data into the table and the number is, of course, the record, which goes into an auto-increment field. Needless to say that I never try to insert data into that field since it is not necessary.

So I tried to find what was going on and found out that the last record in the table is number 278654. In my despair, I thought that maybe the column had reached its limit. It was a 10 character int and so I turned it into a 14 character int.

It worked for a while and it started to accept new records, but, for my greatest surprise, I got the same error message again an hour or two later (with a different number, of course).

Anyone can tell me what is wrong?

Regards,

Wilton Rossi

 

[sleipnir214]

On auto_increment fields, I always set the column type to int unsigned[/u]. That way, I have twice the number of numbers to run through before there will be a problem.

I also never, ever, ever assign a value to an auto_increment column. It's just begging for trouble for no good reason.

Also changing a column from int(10) to int(14) does not change the internal storage of the table. The number in parentheses is not the number of bits to use to store the number, rather it is the maximum display size. If you have an int column that has overflowed, you must change it to bigint to give the column more room.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[wiltonrossi]

Hi Sleipnir! Thanks once more for another clear and prompt response.

The column in the table is of the kind int unsigned. And I didn't attempt to assign value to it since, as you said, it means trouble.

But if the column overflowed, how can we explain the fact that it worked for a while when I changed it to a 14 int and simply stopped working?

Do you think that I should change it to bigint in that case?

Regards,

W

 

[sleipnir214]

[Sorry about all the spurious boldface. That'll teach me to preview my posts.]

The value 278655 is not enough to overflow even a regular int column. And if you're using an unsigned int column, 4294967295 is the biggest number you can store anyway -- much larger than the number that caused your error.

I'd review your code. It looks like something somewhere is trying to assign to that column inappropriately.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!