Thanks for your input.

I'm running MySQL 3.23.49-nt-log on php4.2.1. and the table in question
is of type: MyISAM.
I noticed that, upon returning to work Monday (today) I deleted * from
the table and the auto_increment column reverted back to starting from
one again.

However, today I'm deleting * from the same table and the auto_increment
numbers start where the last [now deleted] entry left off.

I assume then that this table type has some relation or other to a
database connection. By this I mean if the DB connection times out or
otherwise quits, the auto_increment column assumes to re-use keys and
start again from one??

So is there then a PHP-MySQL function that can be used right after a
Query that explicitly tells MySQL to close such a connection so that
columns of type: INT - auto_increment will *always* start from 1 if the
table contains no records (table is empty)??

Thanks a lot for your help. :-)

-----Original Message-----
From: Miles Thompson [mailto:[EMAIL PROTECTED]]
Sent: Saturday, August 24, 2002 12:12 AM
Cc: Ross Gerring
Subject: Re: [PHP-DB] auto_increment problem???


Check your docs to confirm this, as you are probably running a later 
version than I'm familiar with. If the table is defined as type "MyISAM"

the autoincrement numbers do not get reused, they steadily increase. You

can rely on them. The default table type is ISAM, and the autoincrement 
numbers do get reused.

How did I discover this? Same way you did, disappearing keys and very 
strange results on selects. Fortunately I discovered the difference
I had too much data, but it had me going for a while.

Cheers - Miles Thompson

At 05:29 PM 8/23/2002 +0800, Russ wrote:
>G'day folks:
>The way I have my MySQL table set up should work as follows:
>1). If table IS NOT empty, get MySQL to insert auto_increment value
>primary key column as normal, then get value of previous record's
>second, none primary ID column. (latter column used for lookup purposes
>(This is do-able as there is already an existing record from which to
>gather a second none-primary ID.)
>2). If table IS empty, insert auto_increment value as normal, into
>primary key column and *the same value* into the second, none-primary
>The thing is, as I'm using auto_increment as my primary key definition,
>and occassionally this table is emptied (deleted from), how can I get
>the second none primary ID column to reflect the new primary key
>auto_increment value of a new record, if no previous record exists??
>I hope you understand what I mean! ;-)
>Mr Russ Michell
>Web Applications Developer
>Tel: +61 (0)8 9321 3844
>Fax: +61 (0)8 6210 1364
>Post: PO Box 228, Innaloo, WA 6918, Australia
>Street: Suite 24, 158 William St, Perth, WA 6000, Australia
>"No proof of existence is not proof of non-existence."
>(Physicist: Stanton T. Friedman on Debunking Ufology)
>PHP Database Mailing List (
>To unsubscribe, visit:

PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to