Bug #20095 Changing length of VARCHAR field with UTF8 collation does not truncate values

Description:
I setup an InnoDB table with a primary key field defined as VARCHAR(26) and then another plain field with the same definition.  Both fields have a UTF8 collation.  After inserting sample values, I changed the length of the fields to 20, but when browsing the data, the data was still 26 long!  From that point, if I dump the data and re-import it, the data is now truncated at 20 chars long.  I also tried this on a MyISAM table and I got the same problem.

I tried my best at searching for a dup bug but did not find one, sorry if it is a dupe.

How to repeat:
Recreate table:

SET FOREIGN_KEY_CHECKS=0;

SET AUTOCOMMIT=0;
START TRANSACTION;

DROP TABLE IF EXISTS `testing`;
CREATE TABLE IF NOT EXISTS `testing` (
  `primary_key` varchar(26) NOT NULL,
  `other_field` varchar(26) NOT NULL,
  PRIMARY KEY  (`primary_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `testing` (`primary_key`, `other_field`) VALUES ('abcdefghijklmnopqrstuvwxyz', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),
('zyxwvutsrqponmlkjihgfedcba', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ');

SET FOREIGN_KEY_CHECKS=1;

COMMIT;

Now change the length of both fields:

ALTER TABLE `testing` CHANGE `primary_key` `primary_key` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
CHANGE `other_field` `other_field` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

SELECT * FROM `testing`;

At first I thought it was a problem with just the primary key, but notice that both fields still have the full length of 26 in there.

Suggested fix:
The only workaround I know of is to either dump data and reimport, or do a SUBSTR() UPDATE type of thing on the fields changed.

Typo in OSX version...

Thank you for a bug report. Verified just as described with 5.0.23-BK on Linux:

mysql> CREATE TABLE IF NOT EXISTS `testing` (
    ->   `primary_key` varchar(26) NOT NULL,
    ->   `other_field` varchar(26) NOT NULL,
    ->   PRIMARY KEY  (`primary_key`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO `testing` (`primary_key`, `other_field`) VALUES
    -> ('abcdefghijklmnopqrstuvwxyz', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),
    -> ('zyxwvutsrqponmlkjihgfedcba', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from `testing`;
+----------------------------+----------------------------+
| primary_key                | other_field                |
+----------------------------+----------------------------+
| abcdefghijklmnopqrstuvwxyz | ABCDEFGHIJKLMNOPQRSTUVWXYZ |
| zyxwvutsrqponmlkjihgfedcba | ABCDEFGHIJKLMNOPQRSTUVWXYZ |
+----------------------------+----------------------------+
2 rows in set (0.01 sec)

mysql> ALTER TABLE `testing` CHANGE `primary_key` `primary_key` VARCHAR( 20 ) CHARACTER
    -> SET utf8 COLLATE utf8_general_ci NOT NULL ,
    -> CHANGE `other_field` `other_field` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE
    -> utf8_general_ci NOT NULL;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from `testing`;
+----------------------------+----------------------------+
| primary_key                | other_field                |
+----------------------------+----------------------------+
| abcdefghijklmnopqrstuvwxyz | ABCDEFGHIJKLMNOPQRSTUVWXYZ |
| zyxwvutsrqponmlkjihgfedcba | ABCDEFGHIJKLMNOPQRSTUVWXYZ |
+----------------------------+----------------------------+
2 rows in set (0.00 sec)

mysql> desc `testing`;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| primary_key | varchar(20) | NO   | PRI |         |       |
| other_field | varchar(20) | NO   |     |         |       |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

I think, it is a bug.

A simplier test demonstrating the same problem:

DROP TABLE IF EXISTS t1;
CREATE TABLE IF NOT EXISTS t1 (
  a varchar(26) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO t1 (a) VALUES
('abcdefghijklmnopqrstuvwxyz'),
('zyxwvutsrqponmlkjihgfedcba');

ALTER TABLE t1 CHANGE a a VARCHAR( 20 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL;

SELECT * FROM t1;

Result:

a
abcdefghijklmnopqrstuvwxyz
zyxwvutsrqponmlkjihgfedcba

The same problem happens with CHAR() column type.

Additional info to the previous comment: convert from CHAR to CHAR

DROP TABLE IF EXISTS t1;
CREATE TABLE IF NOT EXISTS t1 (
  a char(26) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO t1 (a) VALUES
('abcdefghijklmnopqrstuvwxyz'),
('zyxwvutsrqponmlkjihgfedcba');

ALTER TABLE t1 CHANGE a a CHAR( 20 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL;

SELECT * FROM t1;

abcdefghijklmnopqrst
zyxwvutsrqponmlkjihg

The same problem with convert CHAR to VARCHAR:

DROP TABLE IF EXISTS t1;
CREATE TABLE IF NOT EXISTS t1 (
  a char(26) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO t1 (a) VALUES
('abcdefghijklmnopqrstuvwxyz'),
('zyxwvutsrqponmlkjihgfedcba');

ALTER TABLE t1 CHANGE a a VARCHAR( 20 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL;

SELECT * FROM t1;

abcdefghijklmnopqrst
zyxwvutsrqponmlkjihg

The same problem when convert from VARCHAR to CHAR:

DROP TABLE IF EXISTS t1;
CREATE TABLE IF NOT EXISTS t1 (
  a VARCHAR(26) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO t1 (a) VALUES
('abcdefghijklmnopqrstuvwxyz'),
('zyxwvutsrqponmlkjihgfedcba');

ALTER TABLE t1 CHANGE a a CHAR( 20 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL;

SELECT * FROM t1;

a
abcdefghijklmnopqrst
zyxwvutsrqponmlkjihg

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/commits/23968

Please add to do_varstring2_mb the following check:

+  if (length < from_length)
+  {
+    if (current_thd->count_cuted_fields)
+      copy->to_field->set_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+                                  WARN_DATA_TRUNCATED, 1);
+  }

and test case for this code,
ok to push with changes above.

approved (with the warning added)

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/commits/24445

Pushed into 5.0.40-rpl and 5.1.18-rpl

Pushed into 5.0.42

Pushed into 5.1.18-beta

Noted in 5.0.42, 5.1.18 changelogs.

Changing a utf8 column in an InnoDB table to a shorter length did not
shorten the data values.