Description:
I have a simple master-slave replication setup where master runs 5.0.22-standard and slave runs 5.0.24-standard, both on Linux.
Few minutes ago Nagios (monitoring tool) raised an alert that replication went down. Here is what I got from SHOW SLAVE STATUS:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: db-f03-whg.bax
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000019
Read_Master_Log_Pos: 13722697
Relay_Log_File: relay-bin.000006
Relay_Log_Pos: 5001259
Relay_Master_Log_File: binlog.000019
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: 1064
Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*/' at line 1' on query. Default database: 'mic_forex'. Query: 'CREATE DEFINER=`mtn3907`@`localhost` TRIGGER `oanda_set_coeff` BEFORE INSERT ON `oanda` FOR EACH ROW begin SET NEW.coeff = IF(NEW.Pair LIKE "%JPY%", 100, 1); END */'
Skip_Counter: 0
Exec_Master_Log_Pos: 13632825
Relay_Log_Space: 5091131
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
[...]
Seconds_Behind_Master: NULL
1 row in set (0.00 sec)
It looks like an invalid statement somehow sneaked through to the binary log on master.
This is the relevant entry from relay-log on slave:
======
# at 5001259
#061202 23:42:27 server id 2003 end_log_pos 13633053 Query thread_id=82574 exec_time=0 error_code=0
use mic_forex;
SET TIMESTAMP=1165056147;
SET @@session.foreign_key_checks=0, @@session.sql_auto_is_null=1, @@session.unique_checks=0;
SET @@session.sql_mode=1342177280;
/*!\C utf8 */;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8;
CREATE DEFINER=`mtn3907`@`localhost` TRIGGER `oanda_set_coeff` BEFORE INSERT ON `oanda` FOR EACH ROW begin SET NEW.coeff = IF(NEW.Pair LIKE "%JPY%", 100, 1); END */;
======
How to repeat:
I don't know. Try to reissue the command quoted above.
Description: I have a simple master-slave replication setup where master runs 5.0.22-standard and slave runs 5.0.24-standard, both on Linux. Few minutes ago Nagios (monitoring tool) raised an alert that replication went down. Here is what I got from SHOW SLAVE STATUS: mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: db-f03-whg.bax Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000019 Read_Master_Log_Pos: 13722697 Relay_Log_File: relay-bin.000006 Relay_Log_Pos: 5001259 Relay_Master_Log_File: binlog.000019 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: 1064 Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*/' at line 1' on query. Default database: 'mic_forex'. Query: 'CREATE DEFINER=`mtn3907`@`localhost` TRIGGER `oanda_set_coeff` BEFORE INSERT ON `oanda` FOR EACH ROW begin SET NEW.coeff = IF(NEW.Pair LIKE "%JPY%", 100, 1); END */' Skip_Counter: 0 Exec_Master_Log_Pos: 13632825 Relay_Log_Space: 5091131 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 [...] Seconds_Behind_Master: NULL 1 row in set (0.00 sec) It looks like an invalid statement somehow sneaked through to the binary log on master. This is the relevant entry from relay-log on slave: ====== # at 5001259 #061202 23:42:27 server id 2003 end_log_pos 13633053 Query thread_id=82574 exec_time=0 error_code=0 use mic_forex; SET TIMESTAMP=1165056147; SET @@session.foreign_key_checks=0, @@session.sql_auto_is_null=1, @@session.unique_checks=0; SET @@session.sql_mode=1342177280; /*!\C utf8 */; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8; CREATE DEFINER=`mtn3907`@`localhost` TRIGGER `oanda_set_coeff` BEFORE INSERT ON `oanda` FOR EACH ROW begin SET NEW.coeff = IF(NEW.Pair LIKE "%JPY%", 100, 1); END */; ====== How to repeat: I don't know. Try to reissue the command quoted above.