# weird mirroring problem on auto\_increment fields

**URL:** <https://forums.percona.com/t/weird-mirroring-problem-on-auto-increment-fields/1072>\
**Category:** Other MySQL® Questions\
**Created:** [February 2, 2009, 6:18pm UTC](https://forums.percona.com/t/weird-mirroring-problem-on-auto-increment-fields/1072 "2009-02-02T18:18:13Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![lantopic](https://avatars.discourse-cdn.com/v4/letter/l/0ea827/32.png) [@lantopic](https://forums.percona.com/u/lantopic)\
**Post date:** [February 2, 2009, 6:18pm UTC](https://forums.percona.com/t/weird-mirroring-problem-on-auto-increment-fields/1072/1 "2009-02-02T18:18:13Z")

</div>

hello dear specialists.

we have a strange problem with a master-master mysql mirror.  
the system is set up on ubuntu 8.04, mysql 5.0 with updates  
that come with ubuntu.

the master-master replication was not difficult to establish,  
one server starts with offset 1 the other with offset 2 and  
auto\_increment\_increment = 2 on both servers. we add a record on  
server “A” in phpmyadmin: “B” gets the update and the auto\_increment rises correctly. this also works vice versa. as long as we do it in phpmyadmin. wonderful.

now i go to the mysql command prompt on “A” and enter:

mysql\> INSERT INTO `mytable_c`.`mytable_posts` ( `post_id` , `aktive` , `thread_id` , `user_id` , `date` , `last_editor` , `last_edit` , `edited` , `content` ) VALUES ( NULL , ‘1’, ‘0’, ‘0’, ‘0’, ‘0’, ‘0’, ‘0’, ‘this is my text’ );  
Query OK, 1 row affected (0.00 sec)

now strange things happen:

- the record is inserted in server “A”
- the auto\_increment field “post\_id” gets higher in “A”
- the record gets transferred and inserted in server “B”
- but the value of the auto\_increment field “post\_id” in “B” does not change.

we expected, that mysql would do that for us. adding this record in phpmyadmin worked. but not on mysql console. and what is most annoying: our application also doesn’t work as expected.

surely there is an explanation for this. is there anybody who could tell us what we did wrong?

kind regards,  
ralf

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [December 25, 2009, 10:17am UTC](https://forums.percona.com/t/weird-mirroring-problem-on-auto-increment-fields/1072/2 "2009-12-25T10:17:16Z")

</div>

Nothing is wrong. Replication is designed to produce the same results on both servers, which is what you’re seeing. You should not insert a row on A and then see it inserted with a different primary key value on B.

You should definitely not be writing to both servers.

In general, if you don’t understand auto-increment and replication, then you are playing with a loaded gun and you should limit yourself to simple master-slave replication, where nothing ever modifies data on the slave.