Description:
We had the following bad query in our binary log, which broke replication:
# at 560739576
#090129 1:54:56 server id 1 end_log_pos 560740096 Query thread_id=3752901 exec_time=0 error_code=0
use cupidil_boards;
SET TIMESTAMP=1233194096;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
SET @@session.sql_mode=0;
/*!\C utf8 */;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8;
CREATE DEFINER=`webil`@`%` PROCEDURE `add_community_article`(IN community_id INT, IN title VARCHAR(150), IN sub_title TEXT,IN body TEXT,in photo int, in date_pu
blished DATETIME, in writer VARCHAR(50))
BEGIN
insert ignore into community_article(community_id,title,sub_title,content,image,date_published,writer) values(community_id,title,sub_title,body,photo,date_publi
shed,writer);
select id from community_article where id=last_insert_id();
END */;
You'll notice that the ending */ isn't paired up with anything. My understanding is that invalid queries should never end up in the binary log.
How to repeat:
Run the following on the master (including all comments):
/* Procedure structure for procedure `add_community_article` */
/*!50003 DROP PROCEDURE IF EXISTS `add_community_article` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`webil`@`%` PROCEDURE `add_community_article`(IN community_id INT, IN title VARCHAR(150), IN sub_title TEXT,IN body TEXT,in photo int, in date_published DATETIME, in writer VARCHAR(50))
BEGIN
insert ignore into community_article(community_id,title,sub_title,content,image,date_published,writer) values(community_id,title,sub_title,body,photo,date_published,writer);
select id from community_article where id=last_insert_id();
END */$$
DELIMITER ;
Suggested fix:
Properly get rid of all comments before putting in the binary log.
