I have an integer field that tracks entries. The question I have is
can I set an option so that when auto_increment reaches it's maximum
integer value, it will start all over at zero. I'm deleting every 15
minute the earlier entries, so I'm not too worried about the table
growing too large. Can mysql be set to roll once it's max value is
reached?
Thanks
Mike
mmccaws2 wrote:
> I have an integer field that tracks entries. The question I have is
> can I set an option so that when auto_increment reaches it's maximum
> integer value, it will start all over at zero. I'm deleting every 15
> minute the earlier entries, so I'm not too worried about the table
> growing too large. Can mysql be set to roll once it's max value is
> reached?
No.
On Nov 11, 3:47 pm, p...@snake.net (Paul DuBois) wrote:
> mmccaws2 wrote:
> > I have an integer field that tracks entries. The question I have is
> > can I set an option so that when auto_increment reaches it's maximum
> > integer value, it will start all over at zero. I'm deleting every 15
> > minute the earlier entries, so I'm not too worried about the table
> > growing too large. Can mysql be set to roll once it's max value is
> > reached?
>
> No.
Ok damn
Paul DuBois schreef:
> mmccaws2 wrote:
>> can I set an option so that when auto_increment reaches it's maximum
>> integer value, it will start all over at zero. I'm deleting every 15
>> minute the earlier entries, so I'm not too worried about the table
>> growing too large. Can mysql be set to roll once it's max value is
>> reached?
>
> No.
Right, but there are other ways to deal with this.
1.a. Maybe the type of the auto_increment column isn't UNSIGNED BIGINT
yet.
1.b. If it already is, insert a "page" column that is 0 for all current
records, and set the key to {page, auto_inc_column}, and switch to page
1.
2. If the auto_increment column is not referenced by anything, a
frequent maintenance run to 'floor' the keys could be used.
3. And I suppose you could even disable the auto-increment, and set up
an auto-incrementing trigger that uses a separate table to hold the last
used value, etc.
I think that #3 looks the worst.
--
Affijn, Ruud
"Gewoon is een tijger."
On Nov 11, 2007 3:58 PM, mmccaws2
> I have an integer field that tracks entries. The question I have is
> can I set an option so that when auto_increment reaches it's maximum
> integer value, it will start all over at zero. I'm deleting every 15
> minute the earlier entries, so I'm not too worried about the table
> growing too large. Can mysql be set to roll once it's max value is
> reached?
May we ask what you are really trying to do? Perhaps there is another
way to accomplish your goals.
ER
On Nov 12, 7:47 am, pc88m...@gmail.com (E R) wrote:
> On Nov 11, 2007 3:58 PM, mmccaws2
>
> > I have an integer field that tracks entries. The question I have is
> > can I set an option so that when auto_increment reaches it's maximum
> > integer value, it will start all over at zero. I'm deleting every 15
> > minute the earlier entries, so I'm not too worried about the table
> > growing too large. Can mysql be set to roll once it's max value is
> > reached?
>
> May we ask what you are really trying to do? Perhaps there is another
> way to accomplish your goals.
>
> ER
Sure
My program reads in a set of device names from a syslog file once
every two minutes. For the purposes of my program, a device is
considered "old" after a half hour at which time the program removes
the old. Just for general accounting, I'm keeping track of device
quantities by just tracking the key which auto increments. This table
doesn't require a primary key, and I could just track quantities in
another table. The fields are deviceid, devicename, timestamp, and
perltime. I don't really need deviceid nor timestamp. I've never
used time fields in mysql and i use them to see that things are being
added and when. The perltime field, time(), is so that I can count
off the half hour in seconds. So I was just curious if there was a
way to set deviceid to smallint, and allow it to automatically roll
the number to 1 when the max int was reached. Less house keeping.
There are only a couple thousand devices that pop up as many as 10
times a day in syslog, so I could use a larger int or track it by
counting.
Always open for suggestions
Mike
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> Always open for suggestions
Well, in the spirit of the independent part of "database independent
interface for Perl", you could point your scripts to a different database
that has auto-cycling sequences (such as Postgres). Once set up, it will
never need to be tweaked (unless you stop deleting entirely for the
approximate size of what you set the sequence to). I can't think of a way
to do the equivalent in mysql without having to periodically check on
something, but switching to a bigint will buy you a *lot* of time
(e.g. more than you'd ever need), at the cost of having ever-increasing
identification numbers and slightly more storage.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200711121540
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFHOLv+vJuQZxSWSsgRA8MPAKDBHJ9on6YeVqLyBw310M2M8vaFEwCg 40yB
QmjApOVBDbjpfyIoZ/N6Oik=
=QInM
-----END PGP SIGNATURE-----