MySQL auto_increment fields Server version: 5.1.32-community-log

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_deffke@yahoo.de



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL auto_increment fields Server version:

--0016e644d684673f480470ba1b11
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

ALTER TABLE T1 AUTO_INCREMENT=1;
It's likely that you dropped every record and expected the auto_increment to
reset.

Jerry Wilborn
jerrywilborn@gmail.com


On Sun, Aug 9, 2009 at 1:17 PM, Ralph Deffke 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_deffke@yahoo.de
>
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--0016e644d684673f480470ba1b11--

Re: MySQL auto_increment fields Server version: 5.1.32-community-log

no this is not the point ! if I do this, the next auto_increment is still
10720
i can do what I want however the is defined long unsigned unique
auto_increment, what happens is in t1 the value is set to 10720 in t2 the
next value ist set to 10721 while the next record in t1 gets 10721.

it seems like the values in this field are unique OVER the whole database.

very strange to me...
ralph_deffke@yahoo.de


"Jerry Wilborn" wrote in message
news:3f4628f60908091202s67205256o222f22a0bfb61d60@mail.gmail .com...
> ALTER TABLE T1 AUTO_INCREMENT=1;
> It's likely that you dropped every record and expected the auto_increment
to
> reset.
>
> Jerry Wilborn
> jerrywilborn@gmail.com
>
>
> On Sun, Aug 9, 2009 at 1:17 PM, Ralph Deffke
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_deffke@yahoo.de
> >
> >
> >
> > --
> > PHP General Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL auto_increment fields Server version: 5.1.32-community-log

no this is not the point ! if I do this, the next auto_increment is still
10720
i can do what I want however the field is defined "long unsigned unique
auto_increment", what happens is in t1 the value is set to 10720 in t2 the
next value ist set to 10721 while the next record in t1 gets 10722.

it seems like the values in this field are unique OVER the whole database.

very strange to me...
ralph_deffke@yahoo.de


> "Jerry Wilborn" wrote in message
> news:3f4628f60908091202s67205256o222f22a0bfb61d60@mail.gmail .com...
> > ALTER TABLE T1 AUTO_INCREMENT=1;
> > It's likely that you dropped every record and expected the
auto_increment
> to
> > reset.
> >
> > Jerry Wilborn
> > jerrywilborn@gmail.com
> >
> >
> > On Sun, Aug 9, 2009 at 1:17 PM, Ralph Deffke
> 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_deffke@yahoo.de
> > >
> > >
> > >
> > > --
> > > PHP General Mailing List (http://www.php.net/)
> > > To unsubscribe, visit: http://www.php.net/unsub.php
> > >
> > >
> >
>
>



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.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:



"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

Re: MySQL auto_increment fields Server version:5.1.32-community-log

At 8:17 PM +0200 8/9/09, Ralph Deffke 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_deffke@yahoo.de


Why be concerned about it? What difference does it make?

Cheers,

tedd
--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL auto_increment fields Server version: 5.1.32-community-log

I would like to have a KNOWN status of my database after a NEW installation
of the application, because the further installation relais on information
stored in record 1 of each table.

"tedd" wrote in message
news:p06240801c6a4fe33175e@[192.168.1.100]...
> At 8:17 PM +0200 8/9/09, Ralph Deffke 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_deffke@yahoo.de
>
>
> Why be concerned about it? What difference does it make?
>
> Cheers,
>
> tedd
> --
> -------
> http://sperling.com http://ancientstones.com http://earthstones.com



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.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" wrote in message
news:91.F7.55947.DC74F7A4@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:
>
>

>
> "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

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" wrote in message
news:91.F7.55947.DC74F7A4@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:
>
>

>
> "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

Re: MySQL auto_increment fields Server version:5.1.32-community-log

At 12:47 AM +0200 8/10/09, Ralph Deffke wrote:
>I would like to have a KNOWN status of my database after a NEW installation
>of the application, because the further installation relais on information
>stored in record 1 of each table.

Sounds like a problem waiting to happen.

Cheers,

tedd

--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL auto_increment fields Server version: 5.1.32-community-log

On Sun, 09 Aug 2009 21:17:15 +0300, "Ralph Deffke"
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_deffke@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

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):




/Nisse

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php