Jochem shot full of holes. That's just part of the way InnoDB functions.
Keep in mind that this is only an issue when your server cycles, which
hopefully isn't very often. It seems kind of weird, but it's for
performance reasons, and not that big of a deal.
Cheers,
barneyb
> -----Original Message-----
> From: Spectrum WebDesign [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, January 06, 2004 11:22 AM
> To: CF-Talk
> Subject: RE: CF_MAX_ID - MySQL slowww
>
> ...Barney wrote...
>
> For InnoDB tables, the counter starts at one
> greater than the max value when the server starts, so if you
> delete the last row in a table and cycle the server, you'll
> get issued that same ID a second time.
>
> Do you're right???? How to skip it??? Transaction for DB
> level? Or CFTRANSACTION?
>
>
> ----- Original Message -----
> From: "Barney Boisvert" <[EMAIL PROTECTED]>
> Date: Tue, 6 Jan 2004 11:01:43 -0800
> To: CF-Talk <[EMAIL PROTECTED]>
> Subject: RE: CF_MAX_ID - MySQL slowww
>
> Autonumber is secure, regardless of transactions. It uses a
> centralized
> store to ensure that the same number is NEVER issued twice for a given
> table, within the same server instance. For MyISAM table,
> the same number
> will never be issued twice. For InnoDB tables, the counter
> starts at one
> greater than the max value when the server starts, so if you
> delete the last
> row in a table and cycle the server, you'll get issued that
> same ID a second
> time.
>
> barneyb
>
> > -----Original Message-----
> > From: Spectrum WebDesign [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, January 06, 2004 10:49 AM
> > To: CF-Talk
> > Subject: Re: CF_MAX_ID - MySQL slowww
> >
> > Thanks Jochem
> >
> > but autonumber is secure???
> >
> > Using Win2K/CFMX 6.1/MySQL 4.0.1 InnoDB Transaction Table
> > we're protected using autonumber fields????
> >
> > Inside CFTRANSACTION or only MySQL transaction do the job????
> >
> >
> > ----- Original Message -----
> > From: Jochem van Dieten <[EMAIL PROTECTED]>
> > Date: Tue, 06 Jan 2004 19:35:57 +0100
> > To: CF-Talk <[EMAIL PROTECTED]>
> > Subject: Re: CF_MAX_ID - MySQL slowww
> >
> > Spectrum WebDesign wrote:
> > >
> > > <cfquery name="getmaxID" datasource="#attributes.datasource#">
> > > select max(#attributes.PrimaryKEY#) as Max_ID
> > > from #attributes.tablename#
> > > </cfquery>
> >
> > Use the proper indexes.
> >
> >
> > > Why the hell would you want to do this?
> > >
> > > Because autonumber datatypes are not easily transferable
> > from database to database. They are also
> > > a pain in the neck to develop with, because once the record
> > has been created that number can never
> > > be used again.
> >
> > That depends on your database.
> >
> >
> > > This technique is not 100% necessary for Fusebox
> > applications, but after
> > > you play with autonumbers and try and move data from one
> > database to another you'll thank me.
> >
> > And after a concurrency conflict he will be cursed. Please take a
> > moment to think about the default transaction isolation level in
> > MySQL and the implications that has for your code. It is
> > currently not concurrency save.
> >
> >
> > > Do you have another option to Max_ID????
> >
> > Bite the bullet and let the database generate the primary key.
> >
> > Jochem
> >
> > --
> > I don't get it
> > immigrants don't work
> > and steal our jobs
> > - Loesje
> >
> >
> >
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
