Bug #90266 No warning when truncating a string with data loss
Submitted: 30 Mar 2018 19:14 Modified: 23 Jul 2018 14:19
Reporter: Carlos Tutte Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5/5.6/5.7/8.0 OS:Any
Assigned to: CPU Architecture:Any

[30 Mar 2018 19:14] Carlos Tutte
When converting from a string data type to a smaller data type with data loss (due to truncation), no warning is shown.
Even with Sql_mode = STRICT_TRANS_TABLES this operation succeeds.

How to repeat:
use test;
CREATE TABLE `table1` (
  id tinyint PRIMARY KEY,
  `summary` text

insert into table1 values (1, 'these are the first chars 1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111');

select id, summary, length(summary) from table1 \G
ALTER TABLE `table1` CHANGE `summary` `summary` TINYTEXT CHARACTER SET utf8;
select id, summary, length(summary) from table1 \G

Suggested fix:
Give a warning indicating data loss.
[31 Mar 2018 0:46] Miguel Solorzano
Thank you for the bug report.
[31 Mar 2018 6:34] Shane Bester
This is a serious bug,  it takes away characters that it shouldn't.  See my testcase

-- -------
drop table if exists t;
create table t (a text) engine=innodb default charset=utf8;
insert into t values (repeat('a',300));
select * from t;
alter table t change a a tinytext character set utf8;
show warnings;
select * from t;
select version();
-- --------

The end result is a shorter string than 255!

mysql> select * from t;
| a                                            |
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
1 row in set (0.00 sec)

mysql> select version();
| version()       |
| 8.0.12-tr-debug |
1 row in set (0.00 sec)
[23 Jul 2018 14:19] Paul Dubois
Posted by developer:
Fixed in 8.0.13.

When converting from a BLOB (or TEXT) type to a smaller BLOB (or
TEXT) type, no warning or error was reported informing about the
truncation or data loss. Now an appropriate error is issued in strict
SQL mode and a warning in nonstrict SQL mode.