Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17042

prepared statement does not return error with SQL_MODE STRICT_TRANS_TABLES.

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.0
    • Fix Version/s: 5.5.65
    • Component/s: Prepared Statements
    • Labels:
      None
    • Environment:
      CentOS 6.10 on VirtualBox 5.2.18
      MariaDB 10.0.36

      Description

      CREATE TABLE `a` (
        `id` int(11) NOT NULL,
        `count` int(11) NOT NULL DEFAULT '0',
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8
      

      > set session sql_mode = 'STRICT_TRANS_TABLES';
      > update a set count = count + 1 where id = '1bad';
      ERROR 1292 (22007): Truncated incorrect DOUBLE value: '1bad'
      > prepare stmt from 'update a set count = count + 1 where id = ?';
      Query OK, 0 rows affected (0.03 sec)
      Statement prepared
       
      > set @a = '1bad';
      Query OK, 0 rows affected (0.00 sec)
       
      > execute stmt using @a;
      Query OK, 1 row affected (0.39 sec)
      Rows matched: 1  Changed: 1  Warnings: 0
      

      sql_mode is not worked with prepared statement.
      it should be return error both, but prepared statement return OK.

      and if binlog_format is MIXED, sql_mode is saved on binlog.

      BEGIN
      /*!*/;
      # at 1559
      #180823 18:18:13 server id 2  end_log_pos 1673  Query   thread_id=21    exec_time=1     error_code=0
      use `10_0_36`/*!*/;
      SET TIMESTAMP=1535015893/*!*/;
      SET @@session.pseudo_thread_id=21/*!*/;
      SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
      SET @@session.sql_mode=2097152/*!*/;
      SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
      /*!\C utf8 *//*!*/;
      SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
      SET @@session.lc_time_names=0/*!*/;
      SET @@session.collation_database=DEFAULT/*!*/;
      update a set count = count + 1 where id = '1bad'
      /*!*/;
      # at 1673
      #180823 18:18:13 server id 2  end_log_pos 1700  Xid = 169
      COMMIT/*!*/;
      DELIMITER ;
      # End of log file
      

      So SQL thread failure:

      Last_SQL_Errno: 1292
      Last_Error: Error 'Truncated incorrect DOUBLE value: '1bad'' on query. Default database: '10_0_36'. Query: 'update a set count = count + 1 where id = '1bad''
      

        Attachments

          Activity

            People

            • Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              kkimura Keita Kimura
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: