AUTO_INCREMENT field

[Anon]

Hi, I have been wondering about this problem for some time now:

If I have a table with an AUTO_INCREMENT field with the type of TINYINT, what will happen if a value in that field exceed 255 (or the capacity for that field). Will that field reset and start from 0 again? If it don't, how can any application running for a long time if an AUTO_INCREMENT will sooner or later exceed the capacity for the field?

Thanks for any help

Peter

[[deleted]]

TINYINT has a max of 127.

It will store the max available value of 127. If there is a UNIQUE constraint on the auto_increment column, then the database will refuse to insert more records because there are no new values available.

This is only a problem if you choose to use a TINYINT. Normally you'd use an INT for the auto_increment column. That has a capacity of 4billion. By the time you used 4 billion rows, you're nolonger able to use MySQL :-)

[Anon]

But you don't need to have 4 billions rows to have values that exceed an AUTO_INCREMENT field or? I mean if you delete old rows, then the values will still be entered as the one after the highest value in that field. For example:
There are 126 rows in a AUTO_INCREMENT field with the values 1-126. Now I delete all the rows 1-125, the next row inserted will then still have the value 127. So what should I do to fix this?

Thanks

Peter

[[deleted]]

That's right, you don't need 4 billion rows to get to the limit, but you do need
to do 4 billion inserts before you reach the limit. At 10k inserts a day, that's still 400k days before you reach the limit, that's 1095 years. I don't think you'll really care about the problem anymore in the year 3096.

The solution: change the database model from TINYINT to INT.

TINYINT is only usefull if you are absolutely 101% sure that you will never need more than 127 values. An auto_increment is out of your control, so the limit should be set as high as possible (within reason)

Rather safe than sorry. :-)

[Anon]

Thanks for your answers, but still I'm wondering, is there a database that don't have this limit? Oracle maybe?

Peter

[[deleted]]

Ofcourse every database has this limit, because every datatype has a size limit.

The point is that the limit is so far away that in normal use you never reach it.
And if you do (which you wont), databases like Informix can be instructed to re-cycle unused numbers once the upper limit is reached.
But even then, once the table contains as many rows as there are numbers in the sequence, everything ends.

Like I said in my previous post, you can insert 10k rows for over 100 years before you reach mysql's limit.

So what are you worried about?

[mikeq]

Hi,

Oracle has a limit also, same as vincent explained. In Oracle you use Sequences which is an Oracle integer of up to 38 digits. You can also specify a Max value for a Sequence, when it reaches the Max it goes back to 1.

But as Vincent said you are unlikely to care after 1095 years.

If you are really worried about this don't use a auto_increment field, generate a primary key by some other method.

Mike

[Anon]

Hi Mike, can you give me a hint on what kind of methods you can use to generate 100% unique primary key?

Thanks

Peter

[mikeq]

Hi Peter,

I have used this in the past

---

$QueryStr = "select lpad(count(*)+1,5,'0') THE_ID from manager";

$Result = MySQL_Query($QueryStr);

$TheID = MySQL_Result($Result,0,0);

$TheName = StrToUpper(SubStr($firstname.$surname,0,5));
$TheNewID = $TheName.$TheID;

$TheNewID = Str_Replace(" ","S",$TheNewID);

This first generates a number based on max records in table and left pads it with 0, like 00012.

Then concatenates the manager first and last names and takes the first 5 characters, like
BOB M

joins the two together BOB M00012, then replaces any spaces with an S, so I end up with BOBSM00012.

This is then stored in a varchar(10) field.

I know there is a slim chance that the same key could be generated again, so it wouldn't meet your 100% uniqueness, it is probably about 99%. ;-)

My reason for using this was not because I thought I might go over the max value for an auto_increment field, but to protect my site from anyone guessing the key that may be passed as POST variables.

Hope this helps
Mike

[[deleted]]

Note the words: not 100%

The only 100% is using the auto_increment (or serial).
Anything you do inside a script, without a serial or auto_increment, can produce doubles. And why? because you can run two of those scripts at the same time, so they'd both get the same info from the database, and create the same key value.

[Anon]

How can I get started on serial? Can you give me a short explaination about the princip or point me to a url on this?

Thanks again

Peter

[[deleted]]

For serial, a good start is the Oracle manual.

[mikeq]

Hi Vincent,

Agreed, but in the script I had what are the chances of 2 or more people with the same name requesting the ID at the same time.

So, even if they did get a count of records at the same time they are still unlikely to generate the same key as it is also based on their name.

Mike

[[deleted]]

Well, if you think the risk is acceptable, go ahead :-)

Personally I'd rather not take the chance (and save myself some work in the process) and just use the numbers generated by the database.