List:General Discussion« Previous MessageNext Message »
From:js Date:October 23 2007 11:20pm
Subject:Re: Replication and AUTO_INCREMENT; is it safe?
View as plain text  
Thank you for your reply.

But I couldn't under stand how --auto-increment-increment and
--auto-increment-offset
helps me avoid my problem.

Could you please explain?

On 10/24/07, Eric Frazier <efrazier@stripped> wrote:
> js wrote:
> > Hi list,
> >
> > Reading How AUTO_INCREMENT Handling Works in InnoDB[1] makes me
> > wonder how is it possible to replicate AUTO_INCREMENTed value to slaves.
> >
> > According to the doc,
> >
> > "If you specify an AUTO_INCREMENT column for an InnoDB table, the
> > table handle in the InnoDB data dictionary contains a special counter
> > called the auto-increment counter that is used in assigning new values
> > for the column. This counter is stored only in main memory, not on
> > disk."
> >
> > Let's say there are two server, A and B. A replicates its data to B, the slave.
> > A and B has a table that looks like(column 'id' is auto_increment field)
> >
> > id value
> > 1  a
> > 2  b
> > 3  c
> > 4  d
> >
> > If After "delete from table where id = 4" and restart mysqld on server B,
> > "insert into table (value) values(e)" is executed on server A.
> >
> > In this case, because A's internal counter is 4, table on A would be
> > 1 a
> > 2 b
> > 3 c
> > 5 e
> >
> > But B's would be different because restarting mysqld flushed InnoDB's
> > internal counter.
> > 1 a
> > 2 b
> > 3 c
> > 4 e
> >
> > Is this correct?
> > or MySQL is smart enough to handle this problem?
> >
> > Thanks.
> >
> > [1]http://dev.mysql.com/doc/refman/4.1/en/innodb-auto-increment-handling.html
> >
> >
> http://dev.mysql.com/doc/refman/5.0/en/faqs-general.html  See 28.1.5
>
> But there are more reasons to avoid auto-increment in mysql. I haven't
> run into the problem above, but I have had such problems when restoring
> backups. Make your data make sense, a mindless counting number just to
> make a table unique doesn't every make any sense. Session ids,
> timestamps, combinations of fields all make much better primary keys and
> it is safer overall to implement a "counter" function in your app than
> to trust mysql's
>
>
>
>
>
>
>
>
>
Thread
Replication and AUTO_INCREMENT; is it safe?js23 Oct
  • Re: Replication and AUTO_INCREMENT; is it safe?Philip Hallstrom23 Oct
    • Re: Replication and AUTO_INCREMENT; is it safe?js23 Oct
  • Re: Replication and AUTO_INCREMENT; is it safe?Harrison Fisk24 Oct
Re: Replication and AUTO_INCREMENT; is it safe?js24 Oct
  • Re: Replication and AUTO_INCREMENT; is it safe?Eric Frazier24 Oct