GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL Multi-Master Replication Problem on 5.1.55

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL Multi-Master Replication Problem on 5.1.55

    Hello,

    I am in the process of testing multi-master replication (two seperate write servers) and both of the masters are slaves of each other. Auto increment controls through even odd restoration.

    Master1 stores odd values while master2 stores even values. The only problem i found gaps in auto-increment column on master2. i.e. gaps are in even auto-increment.


    On Master 1:

    auto_increment_increment = 2
    auto_increment_offset = 1

    Create Table: CREATE TABLE `test` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM;

    INSERT INTO test (name) VALUES ('odd1'),('odd2'),('odd3');

    select * from test;
    +----+-------+
    | id | name |
    +----+-------+
    | 1 | odd1 |
    | 3 | odd2 |
    | 5 | odd3 |
    +----+-------+


    On Master 2:

    auto_increment_increment = 2
    auto_increment_offset = 2

    * verified Master1 data replicated on Master2.

    select * from test;
    +----+-------+
    | id | name |
    +----+-------+
    | 1 | odd1 |
    | 3 | odd2 |
    | 5 | odd3 |
    +----+-------+

    * Inserting records on Master2.

    INSERT INTO test (name) VALUES ('even1'),('even2'),('even3');

    select * from test;
    +----+-------+
    | id | name |
    +----+-------+
    | 1 | odd1 |
    | 3 | odd2 |
    | 5 | odd3 |
    | 6 | even1 |
    | 8 | even2 |
    | 10 | even3 |
    +----+-------+


    On master2 id column stores data from 6 And it leaves the starting point (auto_increment_offset = 2). Data should be stores from 2,4,6 and so on.

    Again after insertion on master1 it leaves odd id's 7, 9 and started after last even id i.e. 10

    On Master 1:


    INSERT INTO test (name) VALUES ('odd4'),('odd5'),('odd6');

    select * from test;
    +----+-------+
    | id | name |
    +----+-------+
    | 1 | odd1 |
    | 3 | odd2 |
    | 5 | odd3 |
    | 6 | even1 |
    | 8 | even2 |
    | 10 | even3 |
    | 11 | odd4 |
    | 13 | odd5 |
    | 15 | odd6 |
    +----+-------+


    Can someone please help on this behaviour. Both of the server's are on 5.1.55-log MySQL Community Server.

  • #2
    It won't really work the way you think it will.

    The auto_increment_offset is a _start_point_ in case there are _no_ rows in the table.

    And if there are rows in the table then mysql must (to ensure unique values) initialize the start point to something larger than the max value in the id column.

    So if you restart the server or if you change any of the auto_increment_offset or auto_increment_increment variables then it will recalculate the new start point to be the first value larger than the max value in the series defined by (offset + n * increment).

    manual
    Should one or both of these variables be changed and then new rows inserted into a table containing an AUTO_INCREMENT column, the results may seem counterintuitive because the series of AUTO_INCREMENT values is calculated without regard to any values already present in the column, and the next value inserted is the least value in the series that is greater than the maximum existing value in the AUTO_INCREMENT column.
    Read more about it in the manual.

    Comment


    • #3
      Don't do what you are doing. Never write to both servers at once. Save yourself from madness. It WILL go wrong, and you WILL spend days without sleep trying to get your data back.

      Or maybe this data is throw-away and it doesn't matter. In that case, go ahead

      Comment


      • #4
        Baron,

        Why you are so much afraid of this solution ?

        Actually i have two same sites from different geographical location and one of app will write on master1 (nearest data center.. call it in Asia) and second app will write on master2 (nearest data center - call it in USA) and both are synchronizing through master-master replication ?

        I forsee improved performance because all of my tables are MyISAM and this reduce lock contention which is huge advantage, distributes writes load (although it need to sync writes from other master later some time) and application write time will untimately reduce as well because app will write on nearest data center.

        Can you please let me know about the disadvantages ?

        Comment


        • #5
          The disadvantage is that you are allowing two different servers to accept changes and later they are supposed to agree on what the world looks like.

          What happens if a user on ServerA updates/inserts a new record which is the same as what a user on ServerB is doing?
          Since you don't have a global lock there is a potential risk that the same record are manipulated on both servers at the same time, now which record should be the correct one? How do you get a DBMS to solve this ambiguity?


          An analogy:
          Compare with if you and your brother are supposed to sell a limited amount of 1000 stocks in some kind of fruit company .
          You each have your own office (geographical separation), the phone calls of the hook all day from clients that wants to buy the shares immediately or the deal is off, so you have no time to ask the other what he has done.
          When the dust settles, do you think that you and your brother have been able to sell exactly 1000 shares (remember you can't agree in advance and split and say you get 500 each since what would happen if you got 700 clients and your brother only got 300)? What happens if you as a total sold 1234 shares? Who's (and which) client should get the bad news?


          Point is: trying to solve conflicting data afterwards is very hard compared to never letting there be a conflict in the first place by using a single point of alteration where a lock can be placed when needed to.

          And yes I agree with Baron that you sooner or later will get a lot of problems with a master-master replication and also strongly recommend a master-slave solution.

          The suggestion here if you want to be able to load balance is though that you in your application distinguish between "lazy" reads and absolute reads and writes. That way you can direct "lazy" reads to the slave where you accept that it might not be the exact accurate state due to a slight delay in replication.
          While absolute reads and all writes are always directed to the master. Hence you avoid conflict but are still able to distribute the load on several servers.

          Comment


          • #6
            Hi,

            Appreciate your thoughts on this.
            However, i already know the limitations you mentioned and i am ready to control confiliting issues on app level.
            Let suppose, i need to make sure two users can't sign in with same username. For all confliting factors on my app i am diverting all writes to one master only Master1 which is the primary master. How it will go ?

            Let me know any other scenario i need to keep in mind before implementing master-master replication.

            Thank you.

            Comment


            • #7
              I tell people not to do this because no matter how careful you are, someday you will get conflicts or failures and neither of your servers will have the complete and correct dataset. You will have to either throw one server's data away and treat the other's as correct, or you will have to sift through both servers and get the data you want from them to create a complete and correct dataset. I have seen this many, many times. And it can take a very long time to do -- many hours or even days -- starting at 2AM

              Comment

              Working...
              X