List:General Discussion« Previous MessageNext Message »
From:Miles Teg Date:November 13 2006 1:57am
Subject:Re: InnoDB does not preserve AUTO_INCREMENT -- WTF!?
View as plain text  
> 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.

So insert one record with auto-increment id 9999 or 10000, and the algorithm 
should work as expected.

> 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?

If you think about it, there would obviously be a performance advantage to 
keeping the auto-increment value in memory rather than committing it to disk 
on every insert.

> 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.

I hate to sound like a jerk, but your design is what is costing you this 
redesign, not the behavior of InnoDB.  Generally I'd recommend 
auto-increment ids be opaque and not trying to "reserve" ranges of ids in 
the way you're doing.  But if you insist on doing it, whether it's MyISAM or 
InnoDB, why not insert a marker record at the end of your "reserved" range? 
How much rearchitecting is involved in that?

> ...And yes, we *DO* pay you your $10,000 support contract.
>
> *sigh*
>
> This is very disappointing.

Again, I don't want to be a jerk about it, but as a Systems Architect 
myself, I wouldn't deploy enterprise class systems without having a 
significant understanding of the behavior of the InnoDB database engine. 
Presuming that the behaviors of the two database engines are identical or 
even similar without actually researching the issue seems rather unwise 
doesn't it?

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