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
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.5/5.6/5.7/8.0
• OS: Any
• CPU Architecture: Any

[30 Mar 2018 19:14] Carlos Tutte

Description:
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;
DROP TABLE IF EXISTS table1;
CREATE TABLE `table1` (
  id tinyint PRIMARY KEY,
  `summary` text
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

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;
SHOW WARNINGS;
select id, summary, length(summary) from table1 \G

Suggested fix:
Give a warning indicating data loss.

[31 Mar 2018 0:46] MySQL Verification Team

Thank you for the bug report.

[31 Mar 2018 6:34] MySQL Verification Team

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