List:General Discussion« Previous MessageNext Message »
From:David Griffiths Date:November 11 2006 4:49am
Subject:Re: InnoDB does not preserve AUTO_INCREMENT -- WTF!?
View as plain text  
Daevid,

That page looks a little misleading.

First, it says it's stored in main memory, not on disk.

Then it says that on server-startup, it finds the largest value in the 
table, and initialized it to that. So it is disk-based on startup, and 
then resides in memory thereafter.

This doesn't work like Oracle. I am not sure how MyISAM does it.

There are a few things you could do.

One solution is to insert a dummy row with an id of 10,000. On server 
startup, the largest value will be 10,000 and it will start incrementing 
past that.

Another solution (a bit more elegant but way more work) is to create 
your own sequence table (like Oracle does). Each row in the table 
represents a sequence, and you can initialize to anything you want.

A common function that returns a primary key value (using "SELECT... FOR 
UPDATE" on the sequence table) based on a sequence name will do 
essentially the same thing as Oracle does.

David

Ryan Stille wrote:
> I came up with a work around when we encountered this.  I don't 
> remember exactly (and I don't have access to that code anymore), but I 
> think we manually put a piece of code in our SQL setup scripts, before 
> any of our insert statements.  This 'mysql command' would set the next 
> available ID to whatever we wanted, its just that if you mysqldump the 
> database back out, then load it back in, this next ID setting won't be 
> remembered.  So we had to add it to our dump/setup script each time.  
> You'd have to look in the docs to find what command does this, its 
> something for setting the next auto increment id.  Hope this helps.
>
> -Ryan
>
> Daevid Vincent wrote:
>> http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-column.html
>>
>> We have recently switched several database tables from MYISM to 
>> INNODB, only
>> to find out this colossal design flaw in InnoDB tables.
>>
>> We ship out mySQL on our appliances in enterprise level scenarios. We 
>> often
>> like to start the AUTO_INCREMENT for several tables at 10,000 -- this 
>> way we
>> can reserve the lower 'block' of IDs for our own internal and 
>> 'default' use
>> so all customers have the same basic database schema. It also makes 
>> our code
>> easier to write as we can, in one easy swoop, make the ID's of any 
>> record <
>> 10000 immutable, uneditable, etc.
>>
>> Are there plans to fix what we perceive to be a tremendous bug? Why 
>> would
>> you make it so that the AUTO_INCREMENT value is stored in memory 
>> only?! What
>> use is that? I would have preferred if AUTO_INCREMENT threw an error on
>> InnoDB tables, this way we would have known this months ago instead 
>> of now
>> that we're well into this porting of tables, schema and now code.
>> This is such a subtle but significant change to the table-type that it
>> should have been made painfully obvious to anyone trying to switch 
>> types.
>> You are costing us many man-hours now of re-architecting existing 
>> code, and
>> trying to figure out a way to upgrade existing customers.
>> ...And yes, we *DO* pay you your $10,000 support contract.
>>
>> *sigh*
>>
>> This is very disappointing.
>>
>>
>>   
>
>
>
Thread
InnoDB does not preserve AUTO_INCREMENT -- WTF!?Daevid Vincent10 Nov
Re: InnoDB does not preserve AUTO_INCREMENT -- WTF!?Ryan Stille10 Nov
  • Re: InnoDB does not preserve AUTO_INCREMENT -- WTF!?David Griffiths11 Nov
Re: InnoDB does not preserve AUTO_INCREMENT -- WTF!?Miles Teg13 Nov
  • A little sort/group by help pleaseT.J. Mahaffey1 Dec