Bug #77288 Variables in alter event not replicated properly
Submitted: 10 Jun 2015 0:19 Modified: 23 Nov 2015 18:14
Reporter: Trey Raymond Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.6.24, 5.6.25, 5.6.26, 5.7.8 OS:Any
Assigned to: CPU Architecture:Any
Tags: alter, Event, replication, stored procedure

[10 Jun 2015 0:19] Trey Raymond
Description:
When using a stored procedure to alter an event (without prepared statements), variables are replicated as literal strings rather than their values, causing all slaves to fail.

This may affect statements other than alter event, or even code outside of stored procedures - should be clear once you track it down.

How to repeat:
-- master:

mysql> DELIMITER ;;
mysql>
mysql> CREATE DEFINER=`root`@`localhost` EVENT `run_partition_manager` ON SCHEDULE EVERY 50 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION PRESERVE ENABLE DO
    -> BEGIN
    -> set @happy_day=now();
    -> END;;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `schedule_partition_manager`()
    -> begin
    -> declare new_secs int unsigned;
    -> set new_secs=100;
    -> ALTER DEFINER='root'@'localhost' EVENT run_partition_manager ON SCHEDULE EVERY new_secs SECOND STARTS '2000-01-01 00:00:00' ENABLE;
    -> end;;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> show events\G
*************************** 1. row ***************************
                  Db: ytest
                Name: run_partition_manager
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 50
      Interval field: SECOND
              Starts: 2000-01-01 00:00:00
                Ends: NULL
              Status: ENABLED
          Originator: 180537070
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

mysql>
mysql> call schedule_partition_manager();
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> show events\G
*************************** 1. row ***************************
                  Db: ytest
                Name: run_partition_manager
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 100
      Interval field: SECOND
              Starts: 2000-01-01 00:00:00
                Ends: NULL
              Status: ENABLED
          Originator: 180537070
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

-- on all slaves:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: host-xxx.xxx.com
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000011
          Read_Master_Log_Pos: 97082728
               Relay_Log_File: mysqld-relay-bin.000374
                Relay_Log_Pos: 1723
        Relay_Master_Log_File: mysqld-bin.000011
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1054
                   Last_Error: Error 'Unknown column 'new_secs' in 'field list'' on query. Default database: 'ytest'. Query: 'ALTER DEFINER='root'@'localhost' EVENT run_partition_manager ON SCHEDULE EVERY new_secs SECOND STARTS '2000-01-01 00:00:00' ENABLE'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 97065988
              Relay_Log_Space: 2233
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1054
               Last_SQL_Error: Error 'Unknown column 'new_secs' in 'field list'' on query. Default database: 'ytest'. Query: 'ALTER DEFINER='root'@'localhost' EVENT run_partition_manager ON SCHEDULE EVERY new_secs SECOND STARTS '2000-01-01 00:00:00' ENABLE'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 180537070
                  Master_UUID: e85010d8-f553-11e4-87ef-fc15b40fcc54
             Master_Info_File: /home/y/var/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 150610 00:12:58
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

Suggested fix:
Make sure binary log entries sub in values (or add code to have the slave declare and set the variable, then use it).
[10 Jun 2015 9:27] MySQL Verification Team
Hello Trey Raymond,

Thank you for the report.
Observed this with 5.6.25, 5.6.26 and 5.7.8 builds.

Thanks,
Umesh
[10 Jun 2015 9:27] MySQL Verification Team
mtr test case

Attachment: rpl_bug77288.test (application/octet-stream, text), 934 bytes.

[23 Nov 2015 18:14] David Moss
Thanks for your feedback. This has been fixed in upcoming versions and the following was added to the 5.6.29 and 5.7.11 change logs:

Stored procedure local variables that were used in an ALTER EVENT statement were not being replicated correctly. This was related to the fact that CALL statements are not written into the binary log. Instead each statement executed in a stored procedure is binary logged separately, with the exception that the query string is modified so that uses of stored procedure local variables are replaced with NAME_CONST('spvar_name', 'spvar-value') calls. DDLs (which are always binary logged in statement mode irrespective of the current binary log format) can also use stored procedure local variables and this could cause them to not be replicated correctly. The fix ensures that any stored procedure local variables used in a query are replaced with NAME_CONST(...), except for the case when it is a DML query and the binary log format is ROW.