Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5, 10.0
-
None
-
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''
|