Bug #9029 Traditional: Wrong SQLSTATE returned for string truncation
Submitted: 7 Mar 2005 21:15 Modified: 11 Mar 2005 16:46
Reporter: Trudy Pelzer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3-alpha-debug OS:Linux (SuSE 9.1)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[7 Mar 2005 21:15] 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 VARCHAR, VARBINARY,
TINYTEXT and TINYBLOB if the engine is MyISAM. But 
it happens only with TINYTEXT and TINYBLOB if the engine
is InnoDB. I have filed a separate bug report for the
InnoDB error.

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

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

mysql> insert into t1 (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 t1 (varcharcol) values (repeat('x',256));
ERROR 1265 (01000): Data truncated for column 'varcharcol' at row 1
-- Incorrect; should return SQLSTATE 22001 with the same message

mysql> insert into t1 (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 t1 (varbinarycol) values (repeat('x',256));
ERROR 1265 (01000): Data truncated for column 'varbinarycol' at row 1
-- Incorrect; should return SQLSTATE 22001 with the same message

mysql> insert into t1 (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 t1 (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 t1;
Empty set (0.00 sec)
-- Correct.

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:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22894
[11 Mar 2005 16:46] Paul Dubois
Noted in 5.0.3 changelog.