Russ,
I'd pursue this on the mysql list as this behaviour appears erratic. I'd
have to check docs, but would assume that autoincrement data is kept in the
parent (mysql) table. If these keys are not sequential it doesn't matter.
(That's one of the great things about Visual FoxPro's sys(2015) funtion -
it generates a unique key, one simply has to call it and insert the
returned value.)
The alternative is maintaining a key table where the value of the last used
key (plus one) is stored for each table requiring a unique key. Then you're
off down a completely different track, with record locking issues, etc.
Much better to verify the way MySQL does it.
Miles
At 11:26 AM 8/26/2002 +0800, Russ wrote:
>Miles:
>
>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
>To: Russ; [EMAIL PROTECTED]
>Cc: Ross Gerring
>Subject: Re: [PHP-DB] auto_increment problem???
>
>
>Russ,
>
>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
>before
>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
>into
> >primary key column as normal, then get value of previous record's
> >second, none primary ID column. (latter column used for lookup purposes
> >elsewhere)
> >
> >(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
>ID
> >column.
> >
> >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! ;-)
> >Cheers.
> >
> >Russ
> >
> >Mr Russ Michell
> >Web Applications Developer
> >
> >Itomic.com
> >Email: [EMAIL PROTECTED]
> >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 (http://www.php.net/)
> >To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
