Hello,

On Oct 23, 2007, at 11:23 AM, 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)
> <<cut>>
>
> Is this correct?
> or MySQL is smart enough to handle this problem?

The binary logs in MySQL store the generated auto_increment id and
use that instead of generating a new value on the slave.

If you run mysqlbinlog on a binary log, you will see an output
similar to:

# at 728
#071024 10:53:54 server id 1 end_log_pos 28 Intvar
SET INSERT_ID=3/*!*/;
# at 756
#071024 10:53:54 server id 1 end_log_pos 124 Query
thread_id=3 exec_timSET TIMESTAMP=1193237634/*!*/;
insert into ib_test values (NULL)/*!*/;


The SET INSERT_ID functionality will cause the next INSERT to use
that value for the auto_increment regardless of what it would have
generated.

Regards,

Harrison

--
Harrison C. Fisk, Principal Support Engineer
MySQL AB, www.mysql.com