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 <[EMAIL PROTECTED]> 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 > > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
