From: Eric Frazier (efrazieric-agency.com)
Date: Wed Oct 24 2007 - 08:04:35 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

On 10/24/07, Eric Frazier <efrazieric-agency.com> 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
>>
>>
>> js wrote:
>>
>>> 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?

Restarting the server doesn't reset autoinc.. But that can happen when
you restore a backup, I don't remember what to avoid of the top of my
head, but look into mysqldump and do some tests. Best way to
understand.... But, you can avoid any problem with autoinc by just not
using it. If you must use it for replication it is quite safe to use it
if you are only replicating to a slave write only, so the slave is not
also another master(you are not doing inserts/updates on the slave as
well), or if you need to replicate in a circle use
auto-increment-increment etc. I think it is not a bad idea to use these
even if your slave is just a slave.

Bottom line, if you are designing a DB, for max safety avoid autoinc
entirely. It will save you headaches for a little extra work to start.
This is one area where MySQL still deserves some jeering because
Postgress had this figured out a long time ago with proper sequences
that are a lot easier to mange. With all of the features and cool stuff
MySQL has added in the last few years, I don't get why they haven't
fixed autoinc or added a true sequence type.

Eric

>>
>>
>>
>>
>>
>>
>>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]