Re: [PHP-DB] Autoincrement Question

2001-05-11 Thread Angie Tollerson

hmmm, that's interesting...I believe you are right about the version glitch.  I went 
to another database we have running on another server, it has 3.22.32 as you do.  When 
I delete the last record and then add one it DID NOT reuse the number, but 
incrememented! 
However, v.33 IS NOT reusing numbers that have been deleted anywhere up to the last 
record. So it is working partway at least. I suppose it could be a glitch that needs 
to be reported on .33 .  I will submit it to mysql.com. 
Thanks!
Angie


>>> Miles Thompson <[EMAIL PROTECTED]> 05/11/01 09:17AM >>>
Angie,
Is there a bug here? How do you "re-add" a fourth record? Is 3.22.33 a 
development or a stable version?
With MySQL 3.22.32, and table type of MyISAM I did not get re-use of the 
auto_increment keys , no matter where the records were deleted or inserted.
Miles

At 03:24 PM 5/10/01 -0500, Angie Tollerson wrote:
>I am also doing it on 3.23.33 with MyISAM recover options off, is that 
>what is happening? like I said...if I delete record one, and add a fourth 
>record, It DOES NOT add it as 1.  But as 5.  however, if I delete 4, then 
>add a fourth again, it sets it as 4.  I'm not sure if that has anything to 
>do with my MyISAM settings or not..but there you go.
>
> >>> CC Zona <[EMAIL PROTECTED]> 05/10/01 03:11PM >>>
>[quotes restored to bottom-posting order, for clarity]
>
> > > > > If I have a database that has three records and I delete the entire
> > > > > third record, when another record is then added will the auto 
> increment
> > > > > number be 3 or 4? Thanks.
>
> > > > I will be 3 Jeff :)
>
> > > Only if the auto increment values started at zero.  Otherwise, the 
> number
> > > will be 4.
>
> > That is true if you delete a row in between other records..if you delete 2
> > the fourth record added would be 4, not 2.  BUT..in the default 
> installation
> > of mysql, if you delete the last record the auto increment will fill it's
> > place..trust me, I just tried it :)
>
>What version of MySQL and table type are you getting that on?  I just tried
>it with a MyISAM table in 3.23.33 (insert, immediate delete, immediate
>insert), and the pk incremented as expected.  No re-use of values.
>
>--
>CC
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED] 
>For additional commands, e-mail: [EMAIL PROTECTED] 
>To contact the list administrators, e-mail: [EMAIL PROTECTED] 
>
>
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED] 
>For additional commands, e-mail: [EMAIL PROTECTED] 
>To contact the list administrators, e-mail: [EMAIL PROTECTED] 


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED] 
For additional commands, e-mail: [EMAIL PROTECTED] 
To contact the list administrators, e-mail: [EMAIL PROTECTED] 



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]




Re: [PHP-DB] Autoincrement Question

2001-05-11 Thread Miles Thompson

Angie,
Is there a bug here? How do you "re-add" a fourth record? Is 3.22.33 a 
development or a stable version?
With MySQL 3.22.32, and table type of MyISAM I did not get re-use of the 
auto_increment keys , no matter where the records were deleted or inserted.
Miles

At 03:24 PM 5/10/01 -0500, Angie Tollerson wrote:
>I am also doing it on 3.23.33 with MyISAM recover options off, is that 
>what is happening? like I said...if I delete record one, and add a fourth 
>record, It DOES NOT add it as 1.  But as 5.  however, if I delete 4, then 
>add a fourth again, it sets it as 4.  I'm not sure if that has anything to 
>do with my MyISAM settings or not..but there you go.
>
> >>> CC Zona <[EMAIL PROTECTED]> 05/10/01 03:11PM >>>
>[quotes restored to bottom-posting order, for clarity]
>
> > > > > If I have a database that has three records and I delete the entire
> > > > > third record, when another record is then added will the auto 
> increment
> > > > > number be 3 or 4? Thanks.
>
> > > > I will be 3 Jeff :)
>
> > > Only if the auto increment values started at zero.  Otherwise, the 
> number
> > > will be 4.
>
> > That is true if you delete a row in between other records..if you delete 2
> > the fourth record added would be 4, not 2.  BUT..in the default 
> installation
> > of mysql, if you delete the last record the auto increment will fill it's
> > place..trust me, I just tried it :)
>
>What version of MySQL and table type are you getting that on?  I just tried
>it with a MyISAM table in 3.23.33 (insert, immediate delete, immediate
>insert), and the pk incremented as expected.  No re-use of values.
>
>--
>CC
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
>
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]




Re: [PHP-DB] Autoincrement Question

2001-05-10 Thread Angie Tollerson

I am also doing it on 3.23.33 with MyISAM recover options off, is that what is 
happening? like I said...if I delete record one, and add a fourth record, It DOES NOT 
add it as 1.  But as 5.  however, if I delete 4, then add a fourth again, it sets it 
as 4.  I'm not sure if that has anything to do with my MyISAM settings or not..but 
there you go.

>>> CC Zona <[EMAIL PROTECTED]> 05/10/01 03:11PM >>>
[quotes restored to bottom-posting order, for clarity]

> > > > If I have a database that has three records and I delete the entire
> > > > third record, when another record is then added will the auto increment
> > > > number be 3 or 4? Thanks.

> > > I will be 3 Jeff :)

> > Only if the auto increment values started at zero.  Otherwise, the number 
> > will be 4.

> That is true if you delete a row in between other records..if you delete 2 
> the fourth record added would be 4, not 2.  BUT..in the default installation 
> of mysql, if you delete the last record the auto increment will fill it's 
> place..trust me, I just tried it :)

What version of MySQL and table type are you getting that on?  I just tried 
it with a MyISAM table in 3.23.33 (insert, immediate delete, immediate 
insert), and the pk incremented as expected.  No re-use of values.

-- 
CC

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED] 
For additional commands, e-mail: [EMAIL PROTECTED] 
To contact the list administrators, e-mail: [EMAIL PROTECTED] 



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]




Re: [PHP-DB] Autoincrement Question

2001-05-10 Thread Paul Burney

on 5/10/01 12:59 PM, Angie Tollerson ([EMAIL PROTECTED])
wrote:

> That is true if you delete a row in between other records..if you delete 2 the
> fourth record added would be 4, not 2.  BUT..in the default installation of
> mysql, if you delete the last record the auto_increment will fill it's
> place..trust me, I just tried it :)

IIRC, an older version of MySQL (3.22.X maybe) fills in the auto_increment
values, leading to the relationship problem someone else discussed.  In the
later versions of MySQL (3.23.X ?) they changed this behavior to not reuse
keys unless you delete all rows from the table or something similar.

Hope that helps,

Paul

++
Paul Burney
Webmaster and Open Source Developer
Educational Technology Unit
Graduate School of Education and Information Studies
University of California, Los Angeles
(310) 825-8365
<[EMAIL PROTECTED]>

++


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]




Re: [PHP-DB] Autoincrement Question

2001-05-10 Thread CC Zona

[quotes restored to bottom-posting order, for clarity]

> > > > If I have a database that has three records and I delete the entire
> > > > third record, when another record is then added will the auto increment
> > > > number be 3 or 4? Thanks.

> > > I will be 3 Jeff :)

> > Only if the auto increment values started at zero.  Otherwise, the number 
> > will be 4.

> That is true if you delete a row in between other records..if you delete 2 
> the fourth record added would be 4, not 2.  BUT..in the default installation 
> of mysql, if you delete the last record the auto increment will fill it's 
> place..trust me, I just tried it :)

What version of MySQL and table type are you getting that on?  I just tried 
it with a MyISAM table in 3.23.33 (insert, immediate delete, immediate 
insert), and the pk incremented as expected.  No re-use of values.

-- 
CC

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]




Re: [PHP-DB] Autoincrement Question

2001-05-10 Thread Angie Tollerson

That is true if you delete a row in between other records..if you delete 2 the fourth 
record added would be 4, not 2.  BUT..in the default installation of mysql, if you 
delete the last record the auto_increment will fill it's place..trust me, I just tried 
it :)


>>> CC Zona <[EMAIL PROTECTED]> 05/10/01 02:07PM >>>
In article <[EMAIL PROTECTED]>,
 [EMAIL PROTECTED] ("Angie Tollerson") wrote:

> I will be 3 Jeff :)
> 
> 
> >>> "Jeff Oien" <[EMAIL PROTECTED]> 05/10/01 01:13PM >>>
> If I have a database that has three records and I delete the entire
> third record, when another record is then added will the auto increment
> number be 3 or 4? Thanks.

Only if the auto_increment values started at zero.  Otherwise, the number 
will be 4.

In answer to the question the poster seems to really be asking: 
auto_increment doesn't go back and re-use values from deleted rows.  What 
if you have data in other tables where a foreign key ties (undeleted) data 
in that table to the deleted row in the first table?  If the value was 
re-used, then the data in the second table would suddenly be establishing a 
bogus relationship to the new data in the first.

-- 
CC

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED] 
For additional commands, e-mail: [EMAIL PROTECTED] 
To contact the list administrators, e-mail: [EMAIL PROTECTED] 



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]




Re: [PHP-DB] Autoincrement Question

2001-05-10 Thread CC Zona

In article <[EMAIL PROTECTED]>,
 [EMAIL PROTECTED] ("Angie Tollerson") wrote:

> I will be 3 Jeff :)
> 
> 
> >>> "Jeff Oien" <[EMAIL PROTECTED]> 05/10/01 01:13PM >>>
> If I have a database that has three records and I delete the entire
> third record, when another record is then added will the auto increment
> number be 3 or 4? Thanks.

Only if the auto_increment values started at zero.  Otherwise, the number 
will be 4.

In answer to the question the poster seems to really be asking: 
auto_increment doesn't go back and re-use values from deleted rows.  What 
if you have data in other tables where a foreign key ties (undeleted) data 
in that table to the deleted row in the first table?  If the value was 
re-used, then the data in the second table would suddenly be establishing a 
bogus relationship to the new data in the first.

-- 
CC

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]




Re: [PHP-DB] Autoincrement Question

2001-05-10 Thread Miles Thompson

Jeff,

That depends - I'm assuming this is a MySql database because of 
"auto_increment".

If the table is declared with "type = MYISAM" when you add the other record 
the auto_increment number will be 4. If not it will be 3. This is important 
if you are using the field which is auto_increment (ed) as a foreign key.

Check your MySQL docs just to confirm this.

Cheers - Miles

At 01:20 PM 5/10/01 -0500, Angie Tollerson wrote:
>I will be 3 Jeff :)
>
>
> >>> "Jeff Oien" <[EMAIL PROTECTED]> 05/10/01 01:13PM >>>
>If I have a database that has three records and I delete the entire
>third record, when another record is then added will the auto_increment
>number be 3 or 4? Thanks.
>Jeff Oien
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
>
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]




Re: [PHP-DB] Autoincrement Question

2001-05-10 Thread Angie Tollerson

I will be 3 Jeff :)


>>> "Jeff Oien" <[EMAIL PROTECTED]> 05/10/01 01:13PM >>>
If I have a database that has three records and I delete the entire
third record, when another record is then added will the auto_increment
number be 3 or 4? Thanks.
Jeff Oien

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED] 
For additional commands, e-mail: [EMAIL PROTECTED] 
To contact the list administrators, e-mail: [EMAIL PROTECTED] 



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]