weird mirroring problem on auto_increment fields

  • Filter
  • Time
  • Show
Clear All
new posts

  • 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,

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