How MyISAM handle auto_increment

---1581325369-1098246751-1317678397=:45140
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi, Folks: =0AI have questions regarding how MyISAM handles auto_incre=
ment clolumn? 1. is there a auto_increment counter for MyISAM to assig=
n a new value to auto_increment columns?  =0A2. if MyISAM has the count=
er, is the counter stored in memory or disk? Thnaks=A0
---1581325369-1098246751-1317678397=:45140--

Re: How MyISAM handle auto_increment

--------------enig7B4D9D8DDE410C7D1D13C9D8
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable



Am 03.10.2011 23:46, schrieb Angela liu:
> Hi, Folks:
>=20
>=20
> I have questions regarding how MyISAM handles auto_increment clolumn?
>=20
> 1. is there a auto_increment counter for MyISAM to assign a new value t=
o auto_increment columns?
> 2. if MyISAM has the counter, is the counter stored in memory or disk?

it is a table-property and you hould NOT touch it without godd reasons
because it is named AUTO



--------------enig7B4D9D8DDE410C7D1D13C9D8
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk6KMFAACgkQhmBjz394AnkcIQCePlm2tjOcQGObauiEsk/5 q6H8
fzgAn1griWaWP2pvCc94em54/7Ko5+44
=KT8U
-----END PGP SIGNATURE-----

--------------enig7B4D9D8DDE410C7D1D13C9D8--

Re: How MyISAM handle auto_increment

At 04:46 PM 10/3/2011, you wrote:
>Hi, Folks: I have questions regarding how MyISAM handles
>auto_increment clolumn? 1. is there a auto_increment counter for
>MyISAM to assign a new value to auto_increment columns?

Yes

> 2. if MyISAM has the counter, is the counter stored in memory or
> disk? Thnaks

It is stored with the table definition. It is only reset to 0 when
the table is (re)created. You can get the last AutoInc for the record
that was just added by "Select Last_Insert_Id()". See
http://dev.mysql.com/doc/refman/5.0/en/example-auto-incremen t.html

Mike



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How MyISAM handle auto_increment

--1855776764-42760726-1317684107=:90487
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Thanks, what about if mysqld restart, does auto_increment gets reset =A0?=
  =0AI saw this happened to Innodb, if table is empty and server restar=
t, auto_incremnet gets reset to 0 =0A________________________________=
=0AFrom: mos =0ATo: mysql@lists.mysql.com=0ASent: Monday=
, October 3, 2011 3:01 PM=0ASubject: Re: How MyISAM handle auto_increment=
At 04:46 PM 10/3/2011, you wrote:=0A> Hi, Folks: I have questions reg=
arding how MyISAM handles auto_increment clolumn? 1. is there a auto_increm=
ent counter for MyISAM to assign a new value to auto_increment columns?=0A=
=0AYes >=A0 2. if MyISAM has the counter, is the counter stored in me=
mory or disk? Thnaks It is stored with the table definition.=A0 It is =
only reset to 0 when the table is (re)created. You can get the last AutoInc=
for the record that was just added by "Select Last_Insert_Id()". See http:=
//dev.mysql.com/doc/refman/5.0/en/example-auto-increment.htm l Mike=0A=
=0A-- MySQL General Mailing List=0AFor list archives: http://lists.my=
sql.com/mysql=0ATo unsubscribe:=A0 =A0 http://lists.mysql.com/mysql?unsub=
=3Dyyll2087@yahoo.com
--1855776764-42760726-1317684107=:90487--

Re: How MyISAM handle auto_increment

At 06:21 PM 10/3/2011, Angela liu wrote:
>Thanks, what about if mysqld restart, does auto_increment gets reset ?

No. The next auto increment value stays with the table. As another
person already stated, you should never manually change the auto
increment value on a table that already has rows in it. MySQL always
handles the incrementing of an AutoInc field.


>I saw this happened to Innodb, if table is empty and server restart,
>auto_incremnet gets reset to 0

This shouldn't happen unless MySQL crashes. Perhaps you are
confusing the next Auto Increment value with the Last_Insert_Id()
(stored in server memory) which has a value only AFTER the user has
inserted a row. There can be a hundred connections (each with their
own session) adding rows to the same table at the same time.
Last_Insert_Id() will retrieve the autoinc value of the record that
was just inserted for YOUR session. You won't get someone else's
AutoInc value, only the autoinc value for the record that YOU just
inserted. So if you insert a record, wait 5 minutes, then execute a
"Select Last_Insert_Id()", you will get the correct autoinc value
that was used when YOUR record was inserted, even though another
hundred records were added while you waited to execute the Select
statement. You can never really reliably know what the autoinc value
will be for the record you are about to insert until AFTER the record
has been inserted and you execute Last_Insert_Id() to retrieve this
autoinc value.

I hope this clears it up.

Mike


>
>From: mos
>To: mysql@lists.mysql.com
>Sent: Monday, October 3, 2011 3:01 PM
>Subject: Re: How MyISAM handle auto_increment
>
>At 04:46 PM 10/3/2011, you wrote:
> > Hi, Folks: I have questions regarding how MyISAM handles
> auto_increment clolumn? 1. is there a auto_increment counter for
> MyISAM to assign a new value to auto_increment columns?
>
>Yes
>
> > 2. if MyISAM has the counter, is the counter stored in memory or
> disk? Thnaks
>
>It is stored with the table definition. It is only reset to 0 when
>the table is (re)created. You can get the last AutoInc for the
>record that was just added by "Select Last_Insert_Id()". See
>http://dev.mysql.com/doc/refman/5.0/en/example-auto-increme nt.html
>
>Mike
>
>
>
>-- MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=yyll2087@yahoo.com
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How MyISAM handle auto_increment

On Mon, 3 Oct 2011, Reindl Harald wrote:

>> I have questions regarding how MyISAM handles auto_increment clolumn?

> it is a table-property and you hould NOT touch it without godd reasons
> because it is named AUTO

I guess there are quite often good reasons to change it, which can be
done e.g. as

alter table north33b auto_increment=200001;

I often generate an empty table with the same structure as another table
with a statement like :

create table xxxx select * from yyyy limit 0;

(at this point I have to recreate also all indices etc. doing a show
create table xxxx and show create table yyyy and alter table yyyy for
anything which is missing)

The new table yyyy will have auto increment starting where xxxx ended.
This is good (assuming xxxx will not grow any more) to preserve an
UNIQUE sequence for all tables different for each table.

In case one wants yyyy to start at a "round number" one can just issue an
alter table and reset auto_increment.

The same if one tests some procedure to populate a new table yyyy, then
deletes everything, and wants that production population restarts from 1.

--
------------------------------------------------------------ ------------
Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
------------------------------------------------------------ ------------
Citizens entrusted of public functions have the duty to accomplish them
with discipline and honour
[Art. 54 Constitution of the Italian Republic]
------------------------------------------------------------ ------------
For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html
------------------------------------------------------------ ------------

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: How MyISAM handle auto_increment

On Wed, 5 Oct 2011, Jerry Schwartz wrote:

> Can't you use
> CREATE TABLE yyyy LIKE xxxx
> and then reset the auto-increment value?

Thanks. Since when does "create table like" exist? I was unaware of it,
but I see it exists in mysql 5.1. The tricks I described worked since 3.x
or thereabouts.

--
------------------------------------------------------------ ------------
Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
------------------------------------------------------------ ------------
Citizens entrusted of public functions have the duty to accomplish them
with discipline and honour
[Art. 54 Constitution of the Italian Republic]
------------------------------------------------------------ ------------
For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html
------------------------------------------------------------ ------------

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: How MyISAM handle auto_increment

>-----Original Message-----
>From: Lucio Chiappetti [mailto:lucio@lambrate.inaf.it]
>Sent: Thursday, October 06, 2011 3:18 AM
>To: Jerry Schwartz
>Cc: Mysql List
>Subject: RE: How MyISAM handle auto_increment
>
>On Wed, 5 Oct 2011, Jerry Schwartz wrote:
>
>> Can't you use
>> CREATE TABLE yyyy LIKE xxxx
>> and then reset the auto-increment value?
>
>Thanks. Since when does "create table like" exist? I was unaware of it,
>but I see it exists in mysql 5.1. The tricks I described worked since 3.x
>or thereabouts.
>
[JS] I don't know when it was introduced. I never used anything before 4.0,
and I don't remember when I first used it that command.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.giiresearch.com





>--
>----------------------------------------------------------- -------------
>Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
>----------------------------------------------------------- -------------
>Citizens entrusted of public functions have the duty to accomplish them
>with discipline and honour
> [Art. 54 Constitution of the Italian Republic]
>----------------------------------------------------------- -------------
>For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html
>----------------------------------------------------------- -------------
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org