Reseting the auto-increment number in a MySQL database.

[Tedd Sperling]

Hi gang:

I have a client where their next auto-increment number just jumped from 2300 to 1000000000 for reasons not understood. They want it set back.

Options such as dropping the primary key and rebuilding the index is NOT possible -- this is a relational table thing.

So, is there a way (programmatically) to set the next number in an auto-increment?

Something like:

alter table abc auto_increment = 2301;

Any ideas of why this happened?

Cheers,


tedd

_____________________
***@sperling.com
http://sperling.com

[Samuel Lopes Grigolato]

You can try this: *ALTER TABLE tbl AUTO_INCREMENT = 100;*
*
*
*Source: *http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

(never did it myself, though)

Post by Tedd Sperling
I have a client where their next auto-increment number just jumped from
2300 to 1000000000 for reasons not understood. They want it set back.
Options such as dropping the primary key and rebuilding the index is NOT
possible -- this is a relational table thing.
So, is there a way (programmatically) to set the next number in an auto-increment?
alter table abc auto_increment = 2301;
Any ideas of why this happened?
Cheers,
tedd
_____________________
http://sperling.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[Stuart Dallas]

Post by Tedd Sperling
I have a client where their next auto-increment number just jumped from 2300 to 1000000000 for reasons not understood. They want it set back.
Options such as dropping the primary key and rebuilding the index is NOT possible -- this is a relational table thing.
So, is there a way (programmatically) to set the next number in an auto-increment?
alter table abc auto_increment = 2301;

ALTER TABLE tbl AUTO_INCREMENT = 2301;

-Stuart
--
Stuart Dallas
3ft9 Ltd
http://3ft9.com/

[Tedd Sperling]

Post by Stuart Dallas

Post by Tedd Sperling
I have a client where their next auto-increment number just jumped from 2300 to 1000000000 for reasons not understood. They want it set back.
Options such as dropping the primary key and rebuilding the index is NOT possible -- this is a relational table thing.
So, is there a way (programmatically) to set the next number in an auto-increment?
alter table abc auto_increment = 2301;

ALTER TABLE tbl AUTO_INCREMENT = 2301;
-Stuart

Thanks -- I needed confirmation on that.

It is something that i don't want to do.

Cheers,

tedd

_____________________
***@sperling.com
http://sperling.com

[Jim Giner]

Post by Tedd Sperling

Post by Stuart Dallas

Post by Tedd Sperling
I have a client where their next auto-increment number just jumped from 2300 to 1000000000 for reasons not understood. They want it set back.
Options such as dropping the primary key and rebuilding the index is NOT possible -- this is a relational table thing.
So, is there a way (programmatically) to set the next number in an auto-increment?
alter table abc auto_increment = 2301;

ALTER TABLE tbl AUTO_INCREMENT = 2301;
-Stuart

Thanks -- I needed confirmation on that.
It is something that i don't want to do.
Cheers,
tedd
_____________________
http://sperling.com

But more importantly - don't you need to figure out why it happened??
As well as correcting any inserts with the bogus id?

[Tedd Sperling]

But more importantly - don't you need to figure out why it happened?? As well as correcting any inserts with the bogus id?

Yes, I would like to know -- I'm open for suggestions.

Cheers,

tedd

_____________________
***@gmail.com
http://sperling.com

[Matijn Woudt]

Post by Jim Giner

Post by Jim Giner
But more importantly - don't you need to figure out why it happened??

As well as correcting any inserts with the bogus id?
Yes, I would like to know -- I'm open for suggestions.
Cheers,
tedd
_____________________
http://sperling.com

What storage engine are you using? InnoDB is known for it's auto increment
holes, but I didn't expect the holes to be that big.

- Matijn

[Tedd Sperling]

What storage engine are you using? InnoDB is known for it's auto increment holes, but I didn't expect the holes to be that big.

I have no idea.

It's not my database. It's Constant Contact's -- I just put stuff in it as per client's needs.

However, all of a sudden, it's been fixed -- everything is back to "normal" -- very strange.

Thanks to everyone.

Cheers,

tedd
_____________________
***@gmail.com
http://sperling.com

[Jim Lucas]

Post by Tedd Sperling
I have a client where their next auto-increment number just jumped from 2300 to 1000000000 for reasons not understood. They want it set back.
Options such as dropping the primary key and rebuilding the index is NOT possible -- this is a relational table thing.
So, is there a way (programmatically) to set the next number in an auto-increment?
alter table abc auto_increment = 2301;
Any ideas of why this happened?
Cheers,
tedd
_____________________
http://sperling.com

If mysql logging is turned on, you might be able to rummage through the
logs and see what happened and when it happened.

[adriano]

holes in sequence of auto increment happen using transaction for insert
new record but you don't commit transaction itself
it seems that the autoincrement is incremented anyway
at least this is my case.

[Paul M Foster]

Post by adriano
holes in sequence of auto increment happen using transaction for
insert new record but you don't commit transaction itself
it seems that the autoincrement is incremented anyway
at least this is my case.

I think what Tedd was referring to was something else. The "hole" was
quite large. I've seen this behavior myself, in PostgreSQL. From one
transaction to the next, there were over 10,000 skipped numbers, and
only me and my wife on the system. Some sort of bug, like a spinlock
that wasn't interrupted the way it should have been. I remember the
system taking forever to calm down before it gave the next transaction a
number way forward of the last one. I waited in front of my browser for
quite some time. But I couldn't explain why.

Paul

[Tedd Sperling]

Post by Paul M Foster

Post by adriano
holes in sequence of auto increment happen using transaction for
insert new record but you don't commit transaction itself
it seems that the autoincrement is incremented anyway
at least this is my case.

I think what Tedd was referring to was something else. The "hole" was
quite large. I've seen this behavior myself, in PostgreSQL. From one
transaction to the next, there were over 10,000 skipped numbers, and
only me and my wife on the system. Some sort of bug, like a spinlock
that wasn't interrupted the way it should have been. I remember the
system taking forever to calm down before it gave the next transaction a
number way forward of the last one. I waited in front of my browser for
quite some time. But I couldn't explain why.
Paul

Yes, it was something like what Paul said -- it was not a transaction skip.

I don't know what to think about it -- no explanation.

But, the problem suddenly vanished -- very strange.

Cheers,

tedd

_____________________
***@gmail.com
http://sperling.com

[Tamara Temple]

Post by Tedd Sperling

Post by Paul M Foster

Post by adriano
holes in sequence of auto increment happen using transaction for
insert new record but you don't commit transaction itself
it seems that the autoincrement is incremented anyway
at least this is my case.

I think what Tedd was referring to was something else. The "hole" was
quite large. I've seen this behavior myself, in PostgreSQL. From one
transaction to the next, there were over 10,000 skipped numbers, and
only me and my wife on the system. Some sort of bug, like a spinlock
that wasn't interrupted the way it should have been. I remember the
system taking forever to calm down before it gave the next transaction a
number way forward of the last one. I waited in front of my browser for
quite some time. But I couldn't explain why.
Paul

Yes, it was something like what Paul said -- it was not a transaction skip.
I don't know what to think about it -- no explanation.
But, the problem suddenly vanished -- very strange.
Cheers,
tedd
_____________________
http://sperling.com

I have no answers as to why the huge skip forward, the huge skip
backwards though makes me think someone else is doing something in the
database, and all the alarums and red flags are waving. I hope I'm
wrong.