Bug #16378 Stored procedure puts garbage in the binary log
Submitted: 11 Jan 2006 15:26 Modified: 19 Jan 2006 13:22
Reporter: Gleb Paharenko Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.18/5.0.19 BK OS:Linux (Linux)
Assigned to: Assigned Account CPU Architecture:Any

[11 Jan 2006 15:26] Gleb Paharenko 
Description:
First reported at:
  http://lists.mysql.com/mysql/193731
Stored procedure puts garbage in the binary log, so I'm unable to use to restore the data

How to repeat:
[gleb@blend mysql-debug-5.0.18-linux-i686-glibc23]$ cat res
DROP TABLE IF EXISTS status_notifica_pmv;
DROP PROCEDURE IF EXISTS `pAggiornaStatusNotificaPMV`;
CREATE TABLE `status_notifica_pmv`(

                       `ip_addr_pmv` varchar(16) NOT NULL
                     ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DELIMITER $$
CREATE PROCEDURE `pAggiornaStatusNotificaPMV`( ipAddrPMV varchar(16)
)
BEGIN
   insert into status_notifica_pmv ( ip_addr_pmv
) values( ipAddrPMV);
END$$
DELIMITER ;
call pAggiornaStatusNotificaPMV('192.168.200.218' );

This makes such an entry in the binary log:

#060111 17:21:39 server id 18  end_log_pos 21808        Query   thread_id=18   exec_time=0      error_code=0
SET TIMESTAMP=1136992899;
insert into status_notifica_pmv ( ip_addr_pmv
) values(  NAME_CONST('ipAddrPMV',8��N�8_utf8'192.168.200.218'));
--My Comment                                               ^^^^^^^^^
# End of log file

The value 8,,N,8_utf8 can't be used with mysql command line client, so I'm unable to import the output of mysqlbinlog

Suggested fix:
Remove the garbage before utf8
[11 Jan 2006 15:47] MySQL Verification Team 
Thank you for the bug report.
[12 Jan 2006 7:18] enzo arlati 
Severity:	S3 (Non-critical) 
I don't known what mean Non-critical but:
This bug prevent the use of stored procedures on environment where replication are required.
After the dicovery the bug I must replace all the code based on stored procedure , whith code built inside the application program.
[13 Jan 2006 14:38] Martijn van den Burg 
Does this occur with latin1 as well?
[19 Jan 2006 13:22] Alexander Nozdrin 
This bug is a duplicate of BUG#16621 (INSERTs in Stored Procedures causes data corruption in the Binary Log for 5.0.18), which in turn, is related with BUG#15588 (String overrun during sp-vars.test).