Re: [PHP] Reseting the auto-increment number in a MySQL database.
On Jun 27, 2013, at 8:47 PM, Paul M Foster pa...@quillandmouse.com wrote: On Thu, Jun 27, 2013 at 11:47:28PM +0200, adriano wrote: 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 _ tedd.sperl...@gmail.com http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Reseting the auto-increment number in a MySQL database.
Tedd Sperling tedd.sperl...@gmail.com wrote: On Jun 27, 2013, at 8:47 PM, Paul M Foster pa...@quillandmouse.com wrote: On Thu, Jun 27, 2013 at 11:47:28PM +0200, adriano wrote: 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 _ tedd.sperl...@gmail.com 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. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Reseting the auto-increment number in a MySQL database.
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. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Reseting the auto-increment number in a MySQL database.
On Thu, Jun 27, 2013 at 11:47:28PM +0200, adriano wrote: 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 -- Paul M. Foster http://noferblatz.com http://quillandmouse.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Reseting the auto-increment number in a MySQL database.
Hi gang: I have a client where their next auto-increment number just jumped from 2300 to 10 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 _ t...@sperling.com http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Reseting the auto-increment number in a MySQL database.
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) On Wed, Jun 26, 2013 at 2:07 PM, Tedd Sperling t...@sperling.com wrote: Hi gang: I have a client where their next auto-increment number just jumped from 2300 to 10 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 _ t...@sperling.com http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Reseting the auto-increment number in a MySQL database.
On Wednesday, 26 June 2013 at 18:07, Tedd Sperling wrote: Hi gang: I have a client where their next auto-increment number just jumped from 2300 to 10 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; ALTER TABLE tbl AUTO_INCREMENT = 2301; -Stuart -- Stuart Dallas 3ft9 Ltd http://3ft9.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Reseting the auto-increment number in a MySQL database.
On Jun 26, 2013, at 1:13 PM, Stuart Dallas stu...@3ft9.com wrote: On Wednesday, 26 June 2013 at 18:07, Tedd Sperling wrote: Hi gang: I have a client where their next auto-increment number just jumped from 2300 to 10 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; 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 _ t...@sperling.com http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Reseting the auto-increment number in a MySQL database.
On 6/26/2013 1:30 PM, Tedd Sperling wrote: On Jun 26, 2013, at 1:13 PM, Stuart Dallas stu...@3ft9.com wrote: On Wednesday, 26 June 2013 at 18:07, Tedd Sperling wrote: Hi gang: I have a client where their next auto-increment number just jumped from 2300 to 10 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; 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 _ t...@sperling.com 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? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Reseting the auto-increment number in a MySQL database.
On Jun 26, 2013, at 1:32 PM, Jim Giner jim.gi...@albanyhandball.com wrote: 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 _ tedd.sperl...@gmail.com http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Reseting the auto-increment number in a MySQL database.
On Wed, Jun 26, 2013 at 7:35 PM, Tedd Sperling tedd.sperl...@gmail.comwrote: On Jun 26, 2013, at 1:32 PM, Jim Giner jim.gi...@albanyhandball.com wrote: 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 _ tedd.sperl...@gmail.com 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
Re: [PHP] Reseting the auto-increment number in a MySQL database.
On 06/26/2013 10:07 AM, Tedd Sperling wrote: Hi gang: I have a client where their next auto-increment number just jumped from 2300 to 10 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 _ t...@sperling.com 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. -- Jim Lucas http://www.cmsws.com/ http://www.cmsws.com/examples/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Reseting the auto-increment number in a MySQL database.
On Jun 26, 2013, at 3:23 PM, Matijn Woudt tijn...@gmail.com wrote: 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 _ tedd.sperl...@gmail.com http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
