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).
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).