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