mysql auto incriment weirdness

[ukgimp]

Hello

I am following an online tutorial that gets you to create a mysql db. Initially you insert a category that has a parents id field. It then gets you to update the first entry so that the auto increment is set to zero. That works fine but when you go to insert new records it starts auto numbering from 2. Why does this happen and is there a way around this?

Cheers

[Nick_W]

Hi ukgimp,

It's not weird ;) - It's the way it should work. The auto_increment does not neccessarily increment like 1,2,3 etc...

If you delete entries you'll find more like you've described when you add entries again...

I'm sure someone else will describe it better but, for now, just rest assured that all is well :)

Nick

[Birdman]

That does seem odd. I thought the first row would automatically be se to zero.

Cheers

[Nick_W]

I beleive it is with a fresh table. (newly created) - but once you've done stuff with it, even if you delete all the entries it will not start at zero again..

Wheres andreas? ;)

Nick

[andreasfriedrich]

Here I am, Nick! I was browsing the MySQL [mysql.com] manual for you guys ;).

An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. [...] If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will be reused with an ISAM, or BDB table but not with a MyISAM or InnoDB table. If you delete all rows in the table with DELETE FROM table_name (without a WHERE) in AUTOCOMMIT mode, the sequence starts over for all table types.

[mysql.com...]

[Birdman]

You are always on the job Andreas! Good info.

[ukgimp]

Thanks for that.

I did suspect that but the tutorial I was working to was suggesting something that was not happening. I will have to stop believing what they say :) and mess with it myself.

Regards

[andreasfriedrich]

Birdman [webmasterworld.com] wrote at 12:04 on Feb. 10, 2003 in message #6 [webmasterworld.com]

You are always on the job Andreas!

Quite literally so, or at least I hope to be in a couple of weeks. I´m working on an application for a job right now ;).

Andreas

[Birdman]

>>>working on an application

Good luck. If it's a programming job, I think you may be qualified;)

[andreasfriedrich]

Thanks Birdman :). It is not exactly programming but related. How´s that for being cryptic ;)?

[lorax]

Interesting thread on Auto_increment over at DevSheds Developer's Forum: forums.devshed.com/archive/4/2001/05/1/14393

Good luck Andreas - what a combination of skills - a programmer with a law degree! Who'd a thunk it. ;)

[edited by: jatar_k at 3:08 pm (utc) on Feb. 10, 2003]
[edit reason] delinked [/edit]