Uploaded image for project: 'Percona XtraDB Cluster'
  1. Percona XtraDB Cluster
  2. PXC-2199

DROP TRIGGER IF EXISTS increment unexpected GTID

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Pending Release
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 5.6.40, 5.7.22
    • Fix Version/s: 5.6.41, 5.7.23
    • Component/s: None
    • Security Level: Public Bug

      Description

      when we executed "DROP TRIGGER IF EXISTS" for not exist trigger, 
      the node GTID is incremented instead of cluster GTID. 

      Steps to Reproduce:

       

      Tested with:
      Server version: 5.7.22-22-57-log Percona XtraDB Cluster (GPL),da86071, WSREP version 29.26, wsrep_29.26  
      Server version: 5.6.40-84.0-56-log Percona XtraDB Cluster (GPL), Release rel84.0, Revision 4f77e2e, WSREP version 26.25, wsrep_26.25
      
      
      PXC cluster with 3 nodes:
      Cluster UUID: c334ed43-69ca-ee17-45d8-f817727f2ab0
      note1> show master status\G 
      *************************** 1. row ***************************
       File: mysql-bin13.000004
       Position: 997
       Binlog_Do_DB: 
       Binlog_Ignore_DB: 
      Executed_Gtid_Set: a27dba35-9634-11e8-a3e5-080027eb61ee:1,
      c334ed43-69ca-ee17-45d8-f817727f2ab0:1-9
      1 row in set (0.01 sec)
      
      node2> show master status\G 
      *************************** 1. row ***************************
       File: mysql-bin14.000005
       Position: 1016
       Binlog_Do_DB: 
       Binlog_Ignore_DB: 
      Executed_Gtid_Set: a27dba35-9634-11e8-a3e5-080027eb61ee:1,
      c334ed43-69ca-ee17-45d8-f817727f2ab0:1-9
      1 row in set (0.00 sec)
      
      node1> CREATE DATABASE d1;
      Query OK, 1 row affected (0.02 sec)
      node1> use d1;
      Database changed
      node1> create table t1(id int);
      Query OK, 0 rows affected (0.04 sec)
      node1> CREATE TRIGGER trigger1 BEFORE INSERT ON t1 FOR EACH ROW UPDATE t1 SET id = 1; 
      Query OK, 0 rows affected (0.02 sec)
      node1> show master status\G 
      *************************** 1. row ***************************
       File: mysql-bin13.000004
       Position: 1567
       Binlog_Do_DB: 
       Binlog_Ignore_DB: 
      Executed_Gtid_Set: a27dba35-9634-11e8-a3e5-080027eb61ee:1,
      c334ed43-69ca-ee17-45d8-f817727f2ab0:1-12
      1 row in set (0.00 sec)
      node2> show master status\G 
      *************************** 1. row ***************************
       File: mysql-bin14.000005
       Position: 1570
       Binlog_Do_DB: 
       Binlog_Ignore_DB: 
      Executed_Gtid_Set: a27dba35-9634-11e8-a3e5-080027eb61ee:1,
      c334ed43-69ca-ee17-45d8-f817727f2ab0:1-12
      1 row in set (0.00 sec)
      
      node1> DROP TRIGGER IF EXISTS trigger1; 
      Query OK, 0 rows affected (0.02 sec)
      
      node1> show master status\G 
      *************************** 1. row ***************************
       File: mysql-bin13.000004
       Position: 1733
       Binlog_Do_DB: 
       Binlog_Ignore_DB: 
      Executed_Gtid_Set: a27dba35-9634-11e8-a3e5-080027eb61ee:1,
      c334ed43-69ca-ee17-45d8-f817727f2ab0:1-13
      1 row in set (0.01 sec)
      node2> show master status\G 
      *************************** 1. row ***************************
       File: mysql-bin14.000005
       Position: 1736
       Binlog_Do_DB: 
       Binlog_Ignore_DB: 
      Executed_Gtid_Set: a27dba35-9634-11e8-a3e5-080027eb61ee:1,
      c334ed43-69ca-ee17-45d8-f817727f2ab0:1-13
      1 row in set (0.00 sec)
      node1> DROP TRIGGER IF EXISTS trigger1; 
      Query OK, 0 rows affected, 1 warning (0.00 sec)
      node1> show warnings;
      +-------+------+------------------------+
      | Level | Code | Message |
      +-------+------+------------------------+
      | Note | 1360 | Trigger does not exist |
      +-------+------+------------------------+
      1 row in set (0.00 sec)
      node1> show master status\G 
      *************************** 1. row ***************************
       File: mysql-bin13.000004
       Position: 1899
       Binlog_Do_DB: 
       Binlog_Ignore_DB: 
      Executed_Gtid_Set: a27dba35-9634-11e8-a3e5-080027eb61ee:1-2,
      c334ed43-69ca-ee17-45d8-f817727f2ab0:1-13
      1 row in set (0.00 sec)
      node2> show master status\G 
      *************************** 1. row ***************************
       File: mysql-bin14.000005
       Position: 1736
       Binlog_Do_DB: 
       Binlog_Ignore_DB: 
      Executed_Gtid_Set: a27dba35-9634-11e8-a3e5-080027eb61ee:1,
      c334ed43-69ca-ee17-45d8-f817727f2ab0:1-13
      1 row in set (0.00 sec)
      node1> DROP TRIGGER IF EXISTS trigger1; 
      Query OK, 0 rows affected, 1 warning (0.00 sec)
      mysql> show warnings;
      +-------+------+------------------------+
      | Level | Code | Message |
      +-------+------+------------------------+
      | Note | 1360 | Trigger does not exist |
      +-------+------+------------------------+
      1 row in set (0.00 sec)
      node1> show master status\G 
      *************************** 1. row ***************************
       File: mysql-bin13.000004
       Position: 2065
       Binlog_Do_DB: 
       Binlog_Ignore_DB: 
      Executed_Gtid_Set: a27dba35-9634-11e8-a3e5-080027eb61ee:1-3,
      c334ed43-69ca-ee17-45d8-f817727f2ab0:1-13
      1 row in set (0.00 sec)
      node1> DROP TRIGGER IF EXISTS trigger1; 
      Query OK, 0 rows affected, 1 warning (0.00 sec)
      node1> show master status\G 
      *************************** 1. row ***************************
       File: mysql-bin13.000004
       Position: 2231
       Binlog_Do_DB: 
       Binlog_Ignore_DB: 
      Executed_Gtid_Set: a27dba35-9634-11e8-a3e5-080027eb61ee:1-4,
      c334ed43-69ca-ee17-45d8-f817727f2ab0:1-13
      1 row in set (0.00 sec)
      
      node3> show master status\G
      *************************** 1. row ***************************
       File: mysql-bin15.000006
       Position: 234
       Binlog_Do_DB: 
       Binlog_Ignore_DB: 
      Executed_Gtid_Set: a27dba35-9634-11e8-a3e5-080027eb61ee:1-4,
      c334ed43-69ca-ee17-45d8-f817727f2ab0:1-13
      1 row in set (0.00 sec)
      

       

      Drop trigger of not existing trigger is probably not even handled by Galera

        Attachments

          Activity

            People

            • Assignee:
              krunal.bauskar Krunal Bauskar
              Reporter:
              lalit.choudhary Lalit Choudhary
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 5 hours
                5h