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

Create trigger is failing on PXC 5.6.29 node with error that binary logging is enabled but log_bin is OFF

    Details

    • Type: Bug
    • Status: Done
    • Priority: Low
    • Resolution: Fixed
    • Affects Version/s: 5.6.29-25.15, 5.6.38-26.23
    • Fix Version/s: 5.7.22
    • Component/s: None
    • Security Level: Public Bug
    • Labels:
    • Environment:

      We could reproduce this on PXC fresh installations as well.

      PXC 5.6.29

      PXC 5.6.38

      Description

      Hi,

      We have a single node PXC 5.6.29 sever and binary logging is disabled. When we tried to create a TRIGGER, its failing with below error.

       

      ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

       

       

      We have nothing specific about the environment to mention. We could reproduce this on a fresh 5.6.29 PXC node as well. Please see the below log that is captured when this is reproduced. 

      NOTE: We have this issue observed even on 5.6.38 PXC(latest) as well but in Percona Server 5.6.29 and in Vanilla MySQL 5.6.39, CREATE TRIGGER is working as expected.

       

      mysql> show grants ;
      +--------------------------------------------------------------------------+
      | Grants for TEST@127.0.0.1 |
      +--------------------------------------------------------------------------+
      | GRANT USAGE ON *.* TO 'TEST'@'127.0.0.1' IDENTIFIED BY PASSWORD <secret> |
      | GRANT ALL PRIVILEGES ON `test`.* TO 'TEST'@'127.0.0.1' |
      +--------------------------------------------------------------------------+
      2 rows in set (0.00 sec)
      
      mysql> select @@log_bin ; select @@sql_log_bin ; show global status like 'wsrep_ready' ;
      +-----------+
      | @@log_bin |
      +-----------+
      | 0 |
      +-----------+
      1 row in set (0.00 sec)
      
      +---------------+
      | @@sql_log_bin |
      +---------------+
      | 1 |
      +---------------+
      1 row in set (0.00 sec)
      
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | wsrep_ready | ON |
      +---------------+-------+
      1 row in set (0.00 sec)
      
       
      
      mysql> use test ;
      Database changed
      
      mysql> CREATE TABLE t1 (id INT(11), nae varchar(100));
      Query OK, 0 rows affected (0.00 sec)
      mysql>
      
      mysql> delimiter |
      mysql> create trigger t_after_insr after insert on t1 for each row begin insert t1 values(NEW.id, NEW.nae); end; |
      
      ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
      
       
      mysql> SELECT @@VERSION |
      +-------------------+
      | @@VERSION |
      +-------------------+
      | 5.6.29-76.2-25.15 |
      +-------------------+
      1 row in set (0.01 sec)
      
      mysql> DELIMITER ;
      
      mysql> show global status like 'wsrep_local_state_comment' ;
      +----------------------------+--------+
      | Variable_name | Value |
      +----------------------------+--------+
      | wsrep_local_state_comment | Synced |
      +----------------------------+--------+
      1 rows in set (0.00 sec)
      

       

       

        Attachments

          Expenses

            Activity

              People

              • Assignee:
                kenn.takara Kenn Takara
                Reporter:
                uday.varagani Uday Varagani
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 2 days
                  2d