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

> 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.


Why would you delete data from the slave?


>
> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=m...hilip.pjkh.com
>
>

> > 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.
>
>
> Why would you delete data from the slave?

The delete statement is for Master, not slave.

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@ic-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/...s-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
>
>
>
>
>
>
>
>
>

On 10/24/07, Eric Frazier <efrazier@ic-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/...s-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







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

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