Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL)
-
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''
|
Attachments
Activity
set @save_sql_mode=@@sql_mode;
|
set sql_mode='STRICT_TRANS_TABLES';
|
CREATE TABLE t1 (id int, count int);
|
insert into t1 values (1,1),(0,2);
|
--error ER_TRUNCATED_WRONG_VALUE
|
update t1 set count = count + 1 where id = '1bad';
|
|
|
prepare stmt from 'update t1 set count = count + 1 where id = ?';
|
set @a = '1bad';
|
--error ER_TRUNCATED_WRONG_VALUE
|
execute stmt using @a;
|
deallocate prepare stmt;
|
drop table t1;
|
set sql_mode=@save_sql_mode;
|
It is probleme of parameters assignment, following works as it should:
prepare stmt from "update t1 set count = count + 1 where id = '1bad'";
|
--error ER_TRUNCATED_WRONG_VALUE
|
execute stmt;
|
deallocate prepare stmt;
|
commit 2b62f4e5a3e257fafc0173431697d7350f08ef5f (HEAD > bb-5.5MDEV-17042, origin/bb-5.5-MDEV-17042)
Author: Oleksandr Byelkin <sanja@mariadb.com>
Date: Fri Jul 12 10:03:33 2019 +0200
MDEV-17042: prepared statement does not return error with SQL_MODE STRICT_TRANS_TABLES.
Use for parameters value conversion functions which issue warnings.
Please add a test for InnoDB, as in the bug report.
Also, in the tests for MyISAM, please change:
set session sql_mode = 'STRICT_TRANS_TABLES'; |
to
set session sql_mode = 'STRICT_all_TABLES'; |
Otherwise OK to push.
Thanks for the report!
Repeatable on Mariadb 5.5,10.0 (not on 10.1-10.3)
--error 1292