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

DROP TRIGGER IF EXISTS increment unexpected GTID

    Details

      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

        Smart Checklist

          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