RE: AUTO_INCREMENT Confusion
Hi! I think that for BDB and InnoDB tables MySQL first executes SQL SELECT MAX(auto-increment-column) FROM your-table for the table, and then adds 1 to the value it gets. It means that if you delete the row with the highest counter value, MySQL will assign it again to a new row. On the other hand, MySQL probably stores the counter value separately in a MyISAM table, and therefore it will not be reused even if the row is deleted. I will change InnoDB in a future version so that it executes the SELECT MAX... only when the table is opened the first time after the database startup, and after that will increment the counter monotonously. Regards, Heikki >Absolutely. I am using BDB tables and accessing them through mm.mysql JDBC. >In a way I'm relieved; for a while there I thought I was losing my ability >to read and understand a good technical book... >Kevin McBrearty > >ATG Automation Technologies Group Ltd. > -Original Message- > From: Paul DuBois [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, May 08, 2001 12:53 AM > To: Kevin McBrearty; [EMAIL PROTECTED] > Subject: Re: AUTO_INCREMENT Confusion>> > At 12:14 AM -0400 5/8/01, Kevin McBrearty wrote: > >I'm a bit confused about AUTO-INCREMENT behaviour. I have a table with an > >AUTO-INCREMENT column defined as int unsigned primary key. Records are > >regularly added and then later deleted from this table. If the> last record > >is deleted, the sequence begins at 1 again. > > > >Reading from MySQL (Paul Dubois) pages 94-95, it says that "the> values in an > >automatically generated series are strictly increasing and not> reused", even > >if the maximum value is deleted. This applies to 3.23 and up.> The sequence > >is restarted only if you delete all records from the table using> the DELETE > >FROM tbl_name syntax. > > > >I am deleting row by row (DELETE FROM tbl_name WHERE...). Shouldn't the > >sequence be preserved, i.e. if the last record in the table has> value 43 and > >I delete it, shouldn't the next record get 44 ? > > Yes, it should. I just tried it and it works. But then I changed the > table to type BDB and got the behavior you're describing, so it appears > the behavior differs betweeen table types. Are you using a BDB table? > > > > >I am running 3.23.37 on Suse 7.0 > >> >Thanks.> >> >Kevin McBrearty > >ATG Automation Technologies Group Ltd. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: AUTO_INCREMENT Confusion
Absolutely. I am using BDB tables and accessing them through mm.mysql JDBC. In a way I'm relieved; for a while there I thought I was losing my ability to read and understand a good technical book... Kevin McBrearty ATG Automation Technologies Group Ltd. > -Original Message- > From: Paul DuBois [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, May 08, 2001 12:53 AM > To: Kevin McBrearty; [EMAIL PROTECTED] > Subject: Re: AUTO_INCREMENT Confusion > > > At 12:14 AM -0400 5/8/01, Kevin McBrearty wrote: > >I'm a bit confused about AUTO-INCREMENT behaviour. I have a table with an > >AUTO-INCREMENT column defined as int unsigned primary key. Records are > >regularly added and then later deleted from this table. If the > last record > >is deleted, the sequence begins at 1 again. > > > >Reading from MySQL (Paul Dubois) pages 94-95, it says that "the > values in an > >automatically generated series are strictly increasing and not > reused", even > >if the maximum value is deleted. This applies to 3.23 and up. > The sequence > >is restarted only if you delete all records from the table using > the DELETE > >FROM tbl_name syntax. > > > >I am deleting row by row (DELETE FROM tbl_name WHERE...). Shouldn't the > >sequence be preserved, i.e. if the last record in the table has > value 43 and > >I delete it, shouldn't the next record get 44 ? > > Yes, it should. I just tried it and it works. But then I changed the > table to type BDB and got the behavior you're describing, so it appears > the behavior differs betweeen table types. Are you using a BDB table? > > > > >I am running 3.23.37 on Suse 7.0 > > > >Thanks. > > > >Kevin McBrearty > >ATG Automation Technologies Group Ltd. > > > > > >- > >Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > >To request this thread, e-mail <[EMAIL PROTECTED]> > >To unsubscribe, e-mail <[EMAIL PROTECTED]> > >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > -- > Paul DuBois, [EMAIL PROTECTED] > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: AUTO_INCREMENT Confusion
Absolutely. I am using BDB tables and accessing them through mm.mysql JDBC. In a way I'm relieved; for a while there I thought I was losing my ability to read and understand a good technical book... Kevin McBrearty ATG Automation Technologies Group Ltd. > -Original Message- > From: Paul DuBois [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, May 08, 2001 12:53 AM > To: Kevin McBrearty; [EMAIL PROTECTED] > Subject: Re: AUTO_INCREMENT Confusion > > > At 12:14 AM -0400 5/8/01, Kevin McBrearty wrote: > >I'm a bit confused about AUTO-INCREMENT behaviour. I have a table with an > >AUTO-INCREMENT column defined as int unsigned primary key. Records are > >regularly added and then later deleted from this table. If the > last record > >is deleted, the sequence begins at 1 again. > > > >Reading from MySQL (Paul Dubois) pages 94-95, it says that "the > values in an > >automatically generated series are strictly increasing and not > reused", even > >if the maximum value is deleted. This applies to 3.23 and up. > The sequence > >is restarted only if you delete all records from the table using > the DELETE > >FROM tbl_name syntax. > > > >I am deleting row by row (DELETE FROM tbl_name WHERE...). Shouldn't the > >sequence be preserved, i.e. if the last record in the table has > value 43 and > >I delete it, shouldn't the next record get 44 ? > > Yes, it should. I just tried it and it works. But then I changed the > table to type BDB and got the behavior you're describing, so it appears > the behavior differs betweeen table types. Are you using a BDB table? > > > > >I am running 3.23.37 on Suse 7.0 > > > >Thanks. > > > >Kevin McBrearty > >ATG Automation Technologies Group Ltd. > > > > > >- > >Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > >To request this thread, e-mail <[EMAIL PROTECTED]> > >To unsubscribe, e-mail <[EMAIL PROTECTED]> > >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > -- > Paul DuBois, [EMAIL PROTECTED] > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: AUTO_INCREMENT Confusion
At 12:14 AM -0400 5/8/01, Kevin McBrearty wrote: >I'm a bit confused about AUTO-INCREMENT behaviour. I have a table with an >AUTO-INCREMENT column defined as int unsigned primary key. Records are >regularly added and then later deleted from this table. If the last record >is deleted, the sequence begins at 1 again. > >Reading from MySQL (Paul Dubois) pages 94-95, it says that "the values in an >automatically generated series are strictly increasing and not reused", even >if the maximum value is deleted. This applies to 3.23 and up. The sequence >is restarted only if you delete all records from the table using the DELETE >FROM tbl_name syntax. > >I am deleting row by row (DELETE FROM tbl_name WHERE...). Shouldn't the >sequence be preserved, i.e. if the last record in the table has value 43 and >I delete it, shouldn't the next record get 44 ? Yes, it should. I just tried it and it works. But then I changed the table to type BDB and got the behavior you're describing, so it appears the behavior differs betweeen table types. Are you using a BDB table? > >I am running 3.23.37 on Suse 7.0 > >Thanks. > >Kevin McBrearty >ATG Automation Technologies Group Ltd. > > >- >Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
