Bug #9030 Traditional: Wrong SQLSTATE returned for string truncation
Submitted: 7 Mar 2005 21:20 Modified: 10 Mar 2005 13:57
Reporter: Trudy Pelzer Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0.3-alpha-debug OS:Linux (SuSE 9.1)
Assigned to: Assigned Account CPU Architecture:Any

[7 Mar 2005 21:20] Trudy Pelzer
Description:
Under sql_mode='traditional', 'strict-all-tables' and
'strict-trans-tables', too-long strings that are assigned 
to a string (either CHAR/VARCHAR, BINARY/VARBINARY,
TINYTEXT, TINYBLOB and so on) field must be rejected, 
with SQLSTATE 22001 "String data, right truncation". 
 
MySQL is correctly rejecting such assignments, 
but, in some cases, is returning the wrong SQLSTATE:
01000, which just means "warning" rather than "error". 

The error currently happens with TINYTEXT and TINYBLOB 
if the engine is InnoDB. 

How to repeat:
mysql> set sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (charcol char(255), varcharcol varchar(255), binarycol
binary(255), varbinarycol varbinary(255), tinytextcol tinytext, tinyblobcol
tinyblob) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 (charcol) values (repeat('x',256));
ERROR 1406 (22001): Data too long for column 'charcol' at row 1
-- This is the correct response.

mysql> insert into t2 (varcharcol) values (repeat('x',256));
ERROR 1406 (22001): Data too long for column 'varcharcol' at row 1
-- This is the correct response.

mysql> insert into t2 (binarycol) values (repeat('x',256));
ERROR 1406 (22001): Data too long for column 'binarycol' at row 1
-- This is the correct response.

mysql> insert into t2 (varbinarycol) values (repeat('x',256));
ERROR 1406 (22001): Data too long for column 'varbinarycol' at row 1
-- This is the correct response.

mysql> insert into t2 (tinytextcol) values (repeat('x',256));
ERROR 1265 (01000): Data truncated for column 'tinytextcol' at row 1
-- Incorrect; should return SQLSTATE 22001 with the same message

mysql> insert into t2 (tinyblobcol) values (repeat('x',256));
ERROR 1265 (01000): Data truncated for column 'tinyblobcol' at row 1
-- Incorrect; should return SQLSTATE 22001 with the same message

mysql> select * from t2;
Empty set (0.00 sec)
-- Correct.
[10 Mar 2005 13:57] Ramil Kalimullin
See bug #9029.