weird mirroring problem on auto_increment fields

MySQL & MariaDB Other MySQL® Questions
1

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

2

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.