Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6979

simplify trigger rules for RBR triggers

    XMLWordPrintable

    Details

      Description

      Rules for RBR trigger invocations are too complex. At the moment it's

      • Update_row_event will invoke an UPDATE trigger
      • Delete_row_event will invoke a DELETE trigger
      • Write_row_event is a bit tricky. It is applied (yes, in MySQL too) as follows:
        • The slave tries to insert a row.
        • If the table has UNIQUE KEY constraints (or a PRIMARY KEY) and there is a conflicting row — it’ll be updated to have all values as in what should’ve been inserted.
        • But if the table also has FOREIGN KEY constraints, old row will be deleted and new row will be inserted. Two operations instead of one, so it’s slower, but guarantees that there will be no references to the old row after it disappears.

      That is, Write_row_event can invoke INSERT trigger, DELETE trigger, or UPDATE trigger, depending on whether a conflicting row exists and whether a table was referenced in a foreign key constraint.

      It would be easier to document and to understand if Write_row_event would aways invoke INSERT trigger, and optionally DELETE trigger. But never UPDATE. Then the rule would be

      • Write_row_event will delete the conflicting row if it exists (invoking DELETE trigger). Then it'll insert a new row (invoking INSERT trigger).

      Code-wise it would mean — never use the update optimization if rbr triggers are enabled.

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            serg Sergei Golubchik
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: