Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-91

Make pt-osc compatible with AFTER triggers

    XMLWordPrintable

    Details

    • Type: New Feature
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 3.0.2
    • Fix Version/s: 3.0.4
    • Component/s: None
    • Labels:
      None

      Description

      Currently pt-online-schema-change cannot run if table already has AFTER triggers. But it can, if add an option which instruct the tool to do following (see pseudo-code):

      The workflow to add triggers would be as follows (from our own experiences doing this with trigger-based tools):

      • lock the table in question (standard mysql lock tables write)
      • pull the existing trigger code, call that block 1
      • log trigger code into temporary file to avoid trigger definition loss in case of pt-osc failure
      • generate the trigger code for pt-osc, call that block 2
      • drop the existing trigger
      • create a new trigger with block 1 and block 2 appended, wrap them in /* BLOCK 1 START */ comments or similar if that makes it easier to parse out
      • repeat for other events (cover all of ins,upd,del)
      • unlock table
        To remove triggers, the reverse:
      • lock the table with write lock
      • pull the trigger code and parse out block 1
      • drop the trigger
      • create the trigger with only code from block 1
      • repeat for other event
      • unlock table

      Do not make this option default and add a warning about database can be in inconsistent state if the tool or MySQL Server is killed during execution.

      Launchpad: [https://bugs.launchpad.net/percona-toolkit/+bug/1491133

      ]

      Acceptance Criteria for params:

      Scenario The --preserve-triggers feature needs to delete the old table and triggers
                      and recreate them into for new table. Since it is not possible to have duplicated
                      trigger names, the old table and triggers MUST be deleted and recreated
      
      Feature: --preserve and --no-drop-old-table params
        When --preserve-triggers param is specified
          And --no-drop-old-table was specified
         Then Exit with an error message
      
      Feature: --preserve triggers and --no-swap-tables params
        When --preserve-triggers param is specified
          And --no-swap-tables was specified
         Then exit with an error message
      
      Feature: --preserve triggers and --no-drop-triggers params
        When --preserve-triggers param is specified
          And --no-drop-triggers was specified
         Then exit with an error message
      

       

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                carlos.salguero Carlos Salguero
                Reporter:
                carlos.salguero Carlos Salguero
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: