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.
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.