Bug #11299 prepared statement makes wrong SQL syntax in binlog which stops replication
Submitted: 13 Jun 2005 20:36 Modified: 26 Jul 2005 20:14
Reporter: Frank Binder Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:4.1.12 OS:Windows (Win2k)
Assigned to: Konstantin Osipov CPU Architecture:Any

[13 Jun 2005 20:36] Frank Binder 
Description:
A SQL query like this: "UPDATE `t`SET`d1`=`d1`-?WHERE `id`=1" (see the missing blank between ? and WHERE), where d1 is of type double, works well in the C prepared statement API but produces wrong SQL syntax in the binary log like:
"UPDATE `t`SET`d1`=`d1`-0WHERE `id`=1" which stops replication on slave

How to repeat:
Prepare a table like this:

CREATE TABLE `t`(
 `id` int(10) unsigned NOT NULL auto_increment,
 `d1` double NOT NULL default 0,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT `t`(`id`,`d1`) VALUES(1,10.0);

and use the following code snippet:

char *sql="UPDATE `t`SET`d1`=`d1`-?WHERE `id`=1";
/*                                                 ^^ no blank ! */
double val=0.0;
my_bool is_null=0;
MYSQL_BIND bind_param[1];
MYSQL_STMT* stmt;

bind_param[0].buffer_type = MYSQL_TYPE_DOUBLE;
bind_param[0].buffer = (char*)&val;
bind_param[0].buffer_length = 0;
bind_param[0].length = NULL;
bind_param[0].is_null = &is_null;
bind_param[0].is_unsigned = 0;

stmt = mysql_stmt_init(pMYSQL);
mysql_stmt_prepare(stmt, sql, strlen(sql));
mysql_stmt_bind_param(stmt, bind_param);
mysql_stmt_execute(stmt);

Suggested fix:
mysql_stmt_prepare(...) should reject this SQL query with a syntax error or the server should produce correct SQL syntax in the binary logs.
[14 Jul 2005 20:03] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27098
[14 Jul 2005 20:03] Konstantin Osipov 
Approved on IRC
[14 Jul 2005 20:21] Konstantin Osipov 
Fixed in 4.1 tree (tagged 4.1.14), merged into 5.0 tree (tagged 5.0.10)
[26 Jul 2005 20:14] Paul DuBois 
Noted in 4.1.14, 5.0.10 changelogs.