weird mirroring problem on auto_increment fields

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,

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.