Hello.
> Now if i have records 1 to 10 and i delete some records in between , say, 5, > 7, 8. Now when i restart my server, will these values of 5, 7 and 8 be > reused?? No, they won't be reused until your column has values more than 5,7 or 8. The AUTO_INCREMENT counter initialized to the maximum value in the column, and next inserts increment the counter. In your case it will be equal to 10, the next value will be 11. But! If you delete, say 9,10 - the next value will be 9 after the startup (in MyISAM table it will be 11, because it is stored in the table and not initialized on startup). > Does InnoDB reuse these gap values at any time ??? Again, if you don't add a row with a greater value after the transaction, the values will be reused after the startup. It is easy to make your own experiments. See: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from ia; +---+ | a | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql> insert into ia values(); Query OK, 1 row affected (0.00 sec) mysql> insert into ia values(); Query OK, 1 row affected (0.00 sec) mysql> select * from ia; +---+ | a | +---+ | 1 | | 2 | | 3 | +---+ mysql> delete from ia where 1=1; Query OK, 3 rows affected (0.00 sec) mysql> select * from ia; Empty set (0.00 sec) mysql> insert into ia values(); Query OK, 1 row affected (0.01 sec) mysql> select * from ia; +---+ | a | +---+ | 4 | +---+ We see that AUTO_INCREMENT counter reflects the changes which was made inside the transaction. The values less than 4 won't be reused after startup because we have inserted the record. But if we hadn't done this, the values after startup would have been reused. [EMAIL PROTECTED] wrote: > [-- text/plain, encoding quoted-printable, charset: us-ascii, 25 lines --] > > This is from the docs ..... "The auto-increment value of a deleted record > will only not be reused until the server is restarted, then it will be > reused. In other words, if you insert 10 rows into an InnoDB table and then > delete them the AUTO_INCREMENT value will stay at 11 until the server is > shutdown at which point it will revert to 1!!!"" > > Now if i have records 1 to 10 and i delete some records in between , say, 5, > 7, 8. Now when i restart my server, will these values of 5, 7 and 8 be > reused?? > > Does it mean that the autoincrement values of deleted records will be reused > only if ALL records are deleted and then server restarted?? > > From docs .... " Note that you may see gaps in the sequence of values > assigned to the AUTO_INCREMENT column if you roll back transactions that have > gotten numbers from the counter. " > Does InnoDB reuse these gap values at any time ??? > > Thanks > Priya -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
