MySQL Bugs: #76935: mysql strict mode is inconsistent with error 1366

Bug #76935	mysql strict mode is inconsistent with error 1366 - invalid chars
Submitted:	4 May 2015 23:41	Modified:	5 May 2015 10:59
Reporter:	Morgan Tocker	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.6, 5.7.7, 5.1.75, 5.5.45, 5.6.26, 5.7.8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Strict mode is not strict enough, or at least inconsistent in how it applies.

How to repeat:
Verified in 5.6 and 5.7.7.

drop table if exists t1;
set sql_mode = 'strict_trans_tables';
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY auto_increment,a varchar(255)) DEFAULT CHARSET=utf8;
INSERT INTO t1 VALUES (NULL, 'aa');
UPDATE t1 SET a = X'ad'; # <-- produces an error when strict
UPDATE t1 SET a = NULL WHERE a = X'ad'; # <-- no error produced
ALTER TABLE t1 ADD INDEX (a);
UPDATE t1 SET a = NULL WHERE a = X'ad'; # <-- only a warning produced

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.02 sec)

mysql> set sql_mode = 'strict_trans_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY auto_increment,a varchar(255)) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES (NULL, 'aa');
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE t1 SET a = X'ad'; # <-- produces an error when strict
ERROR 1366 (HY000): Incorrect string value: '\xAD' for column 'a' at row 1
mysql> UPDATE t1 SET a = NULL WHERE a = X'ad'; # <-- no error produced
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD INDEX (a);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> UPDATE t1 SET a = NULL WHERE a = X'ad'; # <-- only a warning produced
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1

Warning (Code 1366): Incorrect string value: '\xAD' for column 'a' at row 1

Suggested fix:
All three updates should have presumably produced an error, since strict_trans_tables was enabled.

Hello Morgan,

Thank you for the bug report and test case.
Observed that 5.1,5.5,5.6 and 5.7 builds are affected.

Thanks,
Umesh

// 5.1.75

mysql> show variables like '%version%';
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.75              |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | unknown-linux-gnu   |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

mysql> show variables like '%sql_mode%';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set sql_mode = 'strict_trans_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY auto_increment,a varchar(255)) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (NULL, 'aa');
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE t1 SET a = X'ad'; # <-- produces an error when strict
ERROR 1366 (HY000): Incorrect string value: '\xAD' for column 'a' at row 1
mysql> UPDATE t1 SET a = NULL WHERE a = X'ad'; # <-- no error produced
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD INDEX (a);
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> UPDATE t1 SET a = NULL WHERE a = X'ad'; # <-- only a warning produced
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

// 5.5.45

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.5.45                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.45                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux2.6                     |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set sql_mode = 'strict_trans_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY auto_increment,a varchar(255)) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (NULL, 'aa');
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE t1 SET a = X'ad'; # <-- produces an error when strict
ERROR 1366 (HY000): Incorrect string value: '\xAD' for column 'a' at row 1

mysql> UPDATE t1 SET a = NULL WHERE a = X'ad';  # <-- no error produced
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD INDEX (a);
UPDATE t1 SET a = NULL WHERE a = X'ad'; 
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> UPDATE t1 SET a = NULL WHERE a = X'ad'; # <-- only a warning produced
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1

// 5.6.26

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.26                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.26-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | linux-glibc2.5                                          |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set sql_mode = 'strict_trans_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%sql_mode%';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY auto_increment,a varchar(255)) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (NULL, 'aa');
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE t1 SET a = X'ad'; # <-- produces an error when strict
ERROR 1366 (HY000): Incorrect string value: '\xAD' for column 'a' at row 1
mysql> UPDATE t1 SET a = NULL WHERE a = X'ad'; # <-- no error produced
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD INDEX (a);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> UPDATE t1 SET a = NULL WHERE a = X'ad'; # <-- only a warning produced
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1

// 5.7.8

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.7.8                                                   |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.7.8-rc-enterprise-commercial-advanced                 |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | linux-glibc2.5                                          |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql>  set sql_mode = 'strict_trans_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY auto_increment,a varchar(255)) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (NULL, 'aa');
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE t1 SET a = X'ad'; # <-- produces an error when strict
ERROR 1366 (HY000): Incorrect string value: '\xAD' for column 'a' at row 1
mysql> UPDATE t1 SET a = NULL WHERE a = X'ad'; # <-- no error produced
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD INDEX (a);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> UPDATE t1 SET a = NULL WHERE a = X'ad'; # <-- only a warning produced
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1