Bug #102965 bind param update skip strict sql_mode checking and cause replication stop
Submitted: 14 Mar 2021 23:21 Modified: 15 Mar 2021 7:31
Reporter: yuxiang jiang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7.33 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[14 Mar 2021 23:21] yuxiang jiang
Description:
After prepareing update statement ”update tsm4 set f1=f1+1 where f1=?;“, bind parameter like '1abc'. Execution of this statement will succeed. While in the same strict mode, execution of SQL "update tsm4 set f1=f1+1 where f1='1abc' " will return error at slave side. And more, the succeeded SQL will be written to binlog in binlog-format of statement or mix. This query event will cause slave broken.

How to repeat:
run SQLs below first
create database tdb2;
create table tsm4(f1 int, f2 varchar(10));
insert into tsm4 values (1, 'abc');

code below will reproduce the problem

#include "mysql.h"

#include <stdlib.h>
#include <stdio.h>
#include <string.h>

//create database tdb2;
//create table tsm4(f1 int, f2 varchar(10));

const char * sql = "update tsm4 set f1=f1+1 where f1=?;";
#define STRING_SIZE 50

int main(int argc, const char *argv[])
{
  MYSQL *mysql = NULL;
  MYSQL_STMT *stmt = NULL;
  my_bool reconnect = 1;

  mysql = mysql_init(mysql);
  if (!mysql)
  {
    puts("Init faild, out of memory?");
    return EXIT_FAILURE;
  }

  mysql_options(mysql, MYSQL_OPT_RECONNECT, &reconnect);

  if (!mysql_real_connect(mysql,       /* MYSQL structure to use */
                          "localhost",         /* server hostname or IP address */ 
                          "root",         /* mysql user */
                          "",          /* password */
                          "tdb2",           /* default database to use, NULL for none */
                          0,           /* port number, 0 for default */
                          "mysql.sock",        /* socket file or named pipe name */
                          CLIENT_FOUND_ROWS /* connection flags */ )) {
    puts("Connect failed\n");
    return EXIT_FAILURE;
  }
  printf("connection id: %ld\n", mysql_thread_id(mysql));
  
  stmt = mysql_stmt_init(mysql);
  if (!stmt)
  {
    fprintf(stderr, " mysql_stmt_init(), out of memory\n");
    exit(0);
  }

  if (mysql_stmt_prepare(stmt, sql, strlen(sql)))
  {
    fprintf(stderr, " mysql_stmt_prepare(), SELECT failed\n");
    fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
    exit(0);
  }
  fprintf(stdout, " prepare, SELECT successful\n");

  MYSQL_BIND    bind[1];
  char          str_data[STRING_SIZE];
  my_bool       is_null[1];
  unsigned long length[1];
  my_bool       error[1];

  /* STRING COLUMN */
  bind[0].buffer_type= MYSQL_TYPE_STRING;
  bind[0].buffer= (char *)str_data;
  bind[0].buffer_length= STRING_SIZE;
  bind[0].is_null= &is_null[0];
  bind[0].length= &length[0];
  bind[0].error= &error[0];

  strcpy(str_data, "1abc");
  length[0] = strlen(str_data);
  is_null[0] = 0;
  /* Bind the result buffers */
  if (mysql_stmt_bind_param(stmt, bind))
  {
    fprintf(stderr, " mysql_stmt_bind_result() failed\n");
    fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
    exit(0);
  }

  if (mysql_stmt_execute(stmt))
  {
    fprintf(stderr, " mysql_stmt_execute(), failed\n");
    fprintf(stderr, " %s %d\n", mysql_stmt_error(stmt), mysql_stmt_errno(stmt));
  }

  return 0;
}
[14 Mar 2021 23:34] yuxiang jiang
patch for this defect

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: sql_mode_update_fail.patch (application/octet-stream, text), 4.08 KiB.

[15 Mar 2021 7:31] MySQL Verification Team
Hello yuxiang jiang,

Thank you for the report and contribution.

regards,
Umesh