[PHP] Re: MySQL auto_increment fields Server version: 5.1.32-community-log
On Mon, 10 Aug 2009 01:17:21 +0200, Ralph Deffke wrote: On Sun, 9 Aug 2009 20:17:15 +0200, Ralph Deffke wrote: I'm facing the fact that it seems that auto_increment fields in a table not start at 1 like it was in earlier versions even if I install mySQL brand new creating all tables new. it seems to me that auto_increments handling has I should mention that I use MyISAM as storage engine what makes it even more wiered. Also check out auto_increment_offset (introduced in 5.0.2): http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html#sysvar_auto_increment_offset /Nisse -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: MySQL auto_increment fields Server version: 5.1.32-community-log
On Sun, 09 Aug 2009 21:17:15 +0300, Ralph Deffke ralph_def...@yahoo.de wrote: Hi all, I'm facing the fact that it seems that auto_increment fields in a table not start at 1 like it was in earlier versions even if I install mySQL brand new creating all tables new. it seems to me that auto_increments handling has changed to older version. is somebody out there who can give me a quick background about auto_increment and how and if I can control the behavior of mySQL about them. ralph_def...@yahoo.de try: ALTER TABLE xxx AUTO_INCREMENT=1; -- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: MySQL auto_increment fields Server version: 5.1.32-community-log
On Sun, 9 Aug 2009 20:17:15 +0200, Ralph Deffke wrote: I'm facing the fact that it seems that auto_increment fields in a table not start at 1 like it was in earlier versions even if I install mySQL brand new creating all tables new. it seems to me that auto_increments handling has changed to older version. is somebody out there who can give me a quick background about auto_increment and how and if I can control the behavior of mySQL about them. Did you Google for it? I found the following page that might be relevant: http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html Beginning with MySQL 5.1.22, InnoDB provides a locking strategy that significantly improves scalability and performance of SQL statements that add rows to tables with AUTO_INCREMENT columns. ... InnoDB uses the following algorithm to initialize the auto-increment counter for a table t that contains an AUTO_INCREMENT column named ai_col: After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement: SELECT MAX(ai_col) FROM t FOR UPDATE; InnoDB increments by one the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. /Nisse -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: MySQL auto_increment fields Server version: 5.1.32-community-log
this is a very good startup for the issue. now the question is where does it get the value if there is no max(ai) or when I do an insert in an empty table with the ai field set to 1 where does the innoDB get the next ai value 10720 ? I assume that the SELECT MAX(ai_col) FROM t FOR UPDATE; return is incremented by 1 so how does this end up with 10720? anyway, ur statement shows that there has changed somethimg definately. but what? maid be there is some flag telling the kernel that ai fields should be unique throughout the database? some left behind of the cluster version of mySQL? Nisse Engström news.nospam.0ixbt...@luden.se wrote in message news:91.f7.55947.dc74f...@pb1.pair.com... On Sun, 9 Aug 2009 20:17:15 +0200, Ralph Deffke wrote: I'm facing the fact that it seems that auto_increment fields in a table not start at 1 like it was in earlier versions even if I install mySQL brand new creating all tables new. it seems to me that auto_increments handling has changed to older version. is somebody out there who can give me a quick background about auto_increment and how and if I can control the behavior of mySQL about them. Did you Google for it? I found the following page that might be relevant: http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html Beginning with MySQL 5.1.22, InnoDB provides a locking strategy that significantly improves scalability and performance of SQL statements that add rows to tables with AUTO_INCREMENT columns. ... InnoDB uses the following algorithm to initialize the auto-increment counter for a table t that contains an AUTO_INCREMENT column named ai_col: After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement: SELECT MAX(ai_col) FROM t FOR UPDATE; InnoDB increments by one the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. /Nisse -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: MySQL auto_increment fields Server version: 5.1.32-community-log
I should mention that I use MyISAM as storage engine what makes it even more wiered. Nisse Engström news.nospam.0ixbt...@luden.se wrote in message news:91.f7.55947.dc74f...@pb1.pair.com... On Sun, 9 Aug 2009 20:17:15 +0200, Ralph Deffke wrote: I'm facing the fact that it seems that auto_increment fields in a table not start at 1 like it was in earlier versions even if I install mySQL brand new creating all tables new. it seems to me that auto_increments handling has changed to older version. is somebody out there who can give me a quick background about auto_increment and how and if I can control the behavior of mySQL about them. Did you Google for it? I found the following page that might be relevant: http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html Beginning with MySQL 5.1.22, InnoDB provides a locking strategy that significantly improves scalability and performance of SQL statements that add rows to tables with AUTO_INCREMENT columns. ... InnoDB uses the following algorithm to initialize the auto-increment counter for a table t that contains an AUTO_INCREMENT column named ai_col: After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement: SELECT MAX(ai_col) FROM t FOR UPDATE; InnoDB increments by one the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. /Nisse -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
