Bug #43723 Large data is truncated to NULL, should cause error
Submitted: 18 Mar 2009 11:16 Modified: 19 Mar 2009 11:55
Reporter: d di (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.67 OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[18 Mar 2009 11:16] d di
Description:
In data integrity mode, you expect the server to return an error if it truncates data.  This works when inserting into a table, but not when retrieving from a table.

How to repeat:
C:\>mysql -h 127.0.0.1 -u test -p

mysql> create table test.short (data varchar(2));

mysql> set sql_mode='';

mysql> insert into test.short values ('xxxxx');
Query OK, 1 row affected, 1 warning (0.00 sec)

... just a warning in 'old mode', ok ...

mysql> set sql_mode='strict_all_tables';

mysql> insert into test.short values ('xxxxx');
ERROR 1406 (22001): Data too long for column 'data' at row 1

... error in data integrity mode, so far so good ...

mysql> set sql_mode='strict_all_tables';

mysql> SELECT REPEAT('0123456789abcdef', 1048576);
+-------------------------------------+
| REPEAT('0123456789abcdef', 1048576) |
+-------------------------------------+
| NULL                                |
+-------------------------------------+
1 row in set, 1 warning (0.00 sec)

... whoops, data truncated to NULL with just a warning; no error.

Suggested fix:
put it in the todo list for the next wire protocol version
[18 Mar 2009 21:23] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read bug #27689 for explanation.
[19 Mar 2009 10:51] d di
I respectfully disagree.  If at any point the server throws away user data, and continues as if nothing has happened, then it is a bug.

Whether 'strict_all_tables' should enable full data integrity mode (as was also expected by the user in bug #27689 - hint), or additional sql_modes is to be invented, is pretty much an irrelevant detail.

I'm not sure exactly what you mean by the "please learn to write bug reports" commentary.  Is there a specific field you'd like me to set different?
[19 Mar 2009 11:55] Sveta Smirnova
Thank you for the feedback.

I think you are right and implementing additional sql_mode for such cases is good idea. Verified as "feature request".