•  

Announcement

Announcement Module
Collapse
No announcement yet.

pt-online-schema-change: triggers breaking replication

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

  • pt-online-schema-change: triggers breaking replication

    #1
    Scenario: Simple Master->Slave setup. I want to add an index to a table on the slave. Replication is caught up and fine. I launch pt-osc to do the work on the slave, specifying "--no-swap-tables"

    After the script is done, running some simple checks to compare the two tables, I find that the COUNT(*) is off.

    Going through the masters binlog, I can see 14 DELETE statements which is exactly how many the COUNT() is off by.

    When one of the DELETE's came down the repl pipe, the trigger fired off but because those rows hadn't been copied to the new table yet, nothing happened.

    The same goes for UPDATE. Upon doing some checksums between the two tables, we find that values in the new table were not updated because when the trigger for UPDATE on orig table fired off, the row on new didn't exist yet.

    What's the workaround for this other than doing STOP SLAVE before running the script?
    Tags: None

  • #2
    Hi, Can you please provide below details? are you talking about MySQL routines (Stored Procedures/Triggers) for update/delete?

    - MySQL versions of Master/Slave
    - pt-online-schema-change command line which you are running
    - Version of pt-online-schema-change

    Comment


    • #3
      Hi,
      Let's assume I am running the most recent versions of everything. It won't make a difference anyways. Here is an example:

      Master has 1000 rows. Slave has same 1000 rows. I want to add an index on to the slave. I am going to use pt-osc. I start pt-osc and leave the slave repl threads running. pt-osc creates the _new table, creates the triggers and begins copying rows from the original table. Lets say that it has now copied the first 500 PK rows over. Someone updates their profile online. Their PK value is 700. UPDATE users SET favColor = 'blue' comes from the master through replication. This row gets updated in the original table. The AFTER UPDATE trigger is fired off in attempt to update the new table.

      The trigger fails because the new table does not yet have PK 700. What happens now?

      Comment


      • #4
        Hi,

        The triggers created by pt-osc take care of all updates happening during the schema change operation. And it applies not only to new inserts but also to any deletes/updates of rows not yet existing on new, temporary table created by pt-osc. And whether the updates come from replication thread on slave or from direct edit on that slave(sic!) it shouldn't matter here.

        You can compare the new and old table on slave after pt-osc run if you choose both --no-swap-tables and --nodrop-new-table options. Both tables should have all the rows updated during the operation in place and consistent with master.
        In case they don't you maybe hit some bug. And if that's the case, please provide exact info how to duplicate it.

        Comment


        • #5
          Wouldn't a failed trigger execution break replication though?

          If I get UPDATE x SET a = 1 WHERE PK = 899 thru repl, and row 899 has not yet been copied in to the new table, won't replication break saying it can't find the row?

          Comment


          • #6
            I am not sure what you mean by a "failed trigger execution". If the pt-osc trigger fails for some reason, pt-osc won't continue it's work, so the original table will not be touched, and there is no reason why replication could break.
            And if PK = 899 is not yet copied to the new table, it has nothing to do with replication event, being applied to the original table. As new table does not exist on master (it's name will be like _[table]_new), replication thread won't touch it.
            Note that pt-osc does the table rename, so in fact new<->old table switch as the pre-last step of it's job (the last one is old table drop if not switched off in options), and at this point both new and old tables are identical, except the modification you want, like new index, etc.

            Comment


            • #7
              Here's an example flow:

              start ocs on slave table
              triggers added for I/U/D in AFTER state (ie AFTER UPDATE..)
              copy to new table in progress
              app makes change on master, SET a = 677 WHERE PK = 400
              statement is replicated to slave
              slave thread executes above SQL and updates orig table
              trigger executes and tries to update new table
              new table has only copied up to PK = 300, thus the SQL in the trigger fails, thus halting replication with "Cant find row in table"

              Right? Keep in mind this is theory at this point, we haven't tried it in production to verify there is an issue. This was a point brought up by one of our DBAs and we want to get a confirmation/contradiction before continuing on.

              Comment


              • #8
                Matthew,

                I've tested this kind of scenario when updating rows not yet existing on new. temp table. All went right, no data drift.
                Note that pt-osc is not just a simple trigger, it's code is almost 8k lines
                Any way the best solution is to check that yourself - you can do this easily by altering big table, with very small --chunk-size ( so that it's very slow, and you will be able to make updates during it), --progress and --no-drop-new-table + --no-swap-tables to be able to compare results later.

                Comment


                • #9
                  I'll see if I can throw together a test case. Thanks for the comments!

                  Comment

                  Working...
                  X