Bug #28845 Check not performed b4 decreasing the size of a VARCHAR
Submitted: 1 Jun 2007 19:47 Modified: 22 Jun 2007 13:18
Reporter: Mr Wakazula Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.41 OS:Windows (2003 SP1)
Assigned to: Sveta Smirnova CPU Architecture:Any

[1 Jun 2007 19:47] Mr Wakazula
Description:
One would expect an error (or warning) to be thrown when the size of a VARCHAR is set to a value less than it's contents.

For example, let's suppose you have the following field: test_datatype.value VARCHAR(32).  If the column contains the value `123` and the field data type is changed to VARCHAR(2), no error (or warning) is thrown.

CONTEXT
- MySql Server 5.0.41
- MySql Query Browser 1.2.12

How to repeat:
CREATE TABLE `test`.`test_datatype` (
  `value` VARCHAR(32) NOT NULL,
  PRIMARY KEY (`value`)
)
ENGINE = InnoDB;
<<RESULT>> Table created

INSERT INTO test_datatype (value) VALUES('123');
<<RESULT>> Value inserted

ALTER TABLE `test`.`test_datatype` MODIFY COLUMN `value` VARCHAR(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
<<RESULT>> Operation completed successfully (no error or warning!)

SELECT * FROM `test_datatype`;
<<RESULT>>`123` is returned

ALTER TABLE `test`.`test_datatype` MODIFY COLUMN `value` CHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
<<RESULT>> Error: Data too long for column `value` (which makes sense)

Suggested fix:
An error (or warning) should be displayed saying something to the effect of:
"The data type VARCHAR(2) is smaller than the field's contents."
[4 Jun 2007 11:42] Heikki Tuuri
This is probably a MySQL server bug. It should observe that it must truncate data, and throw an error.
[4 Jun 2007 18:09] Sveta Smirnova
Thank you for the report.

I can not repeat described behaviour neither on Linux, nor on Mac OS X: always get warning "Warning 1265    Data truncated for column 'value' at row 1"

Please indicate accurate version of MySQL server you use.
[5 Jun 2007 13:21] Mr Wakazula
Sveta Smirnova says: "Please indicate accurate version of MySQL server you use."

As indicated in the ticket, the MySql Server version is 5.0.41.

If you require additional information, please specify as I'm not entirely sure what you are asking for.
[5 Jun 2007 13:37] Mr Wakazula
Sveta Smirnova says: "I can not repeat described behaviour neither on Linux, nor on Mac OS X"

I am running MySql Server 5.0.41 on Windows 2003 Enterprise SP1 and MySql Query Browser 1.2.12 on Windows Xp Pro SP2.

Are you able to test this configuration?
[5 Jun 2007 18:22] MySQL Verification Team
Thank you for the bug report. I was not able to repeat too the behavior
reported with 5.0.41 on Windows XP, I got error or warning messages
according the sql_mode setting on the server. I will attach a screen-shot.
[5 Jun 2007 18:23] MySQL Verification Team
Error

Attachment: QB-error.PNG (image/png, text), 47.78 KiB.

[5 Jun 2007 18:57] Sveta Smirnova
Thank you for the feedback.

Changed status to "Can't repeat", because we could not repeat bug with indicated version.
[6 Jun 2007 12:33] Mr Wakazula
I have attached a screen capture of MySql Query Browser not throwing an error.  

As outlined by the `Files` tab of this website, the data has been uploaded to: ftp://ftp.mysql.com/pub/mysql/upload/

bug-data-28845.avi - screen capture of MySql not throwing an error (7Mb)
readme-28845.txt - says "a screen capture of MySql not throwing an error"
[20 Jun 2007 8:38] Sveta Smirnova
screentshot

Attachment: bug28845.jpg (image/jpeg, text), 53.14 KiB.

[20 Jun 2007 8:38] Sveta Smirnova
Thank you for the feedback.

But you got error: see attched screenshot made from your .avi file.
[22 Jun 2007 13:18] Mr Wakazula
Please read the ticket carefully.

The problem is with ***V A R C H A R*** (not char as shown in your screen shot).

ALTER TABLE `test`.`test_datatype` MODIFY COLUMN `value` VARCHAR(2) CHARACTER SET utf8
COLLATE utf8_general_ci NOT NULL;
<<RESULT>> Operation completed successfully (no error or warning!)
[22 Jun 2007 13:19] Mr Wakazula
Screen capture from previously uploaded video (bug-data-28845.avi)

Attachment: bug-data-28845.jpg (image/pjpeg, text), 102.04 KiB.

[25 Jun 2007 9:37] Sveta Smirnova
Thank you for the feedback.

Bug is repeatable with Win an Mac version of MySQL 5.0.41, but is not repeatable with current sources. So I'll leave it as "Can't repeat". Please wait next release.
[25 Jun 2007 9:39] Sveta Smirnova
Repeatable bug:

$mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4731
Server version: 5.0.41 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `test`.`test_datatype` (   `value` VARCHAR(32) NOT NULL,   PRIMARY KEY (`value`) ) engine=innodb;
Query OK, 0 rows affected (0.68 sec)

mysql> INSERT INTO test_datatype (value) VALUES('123');
Query OK, 1 row affected (0.08 sec)

mysql> ALTER TABLE `test`.`test_datatype` MODIFY COLUMN `value` VARCHAR(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
Query OK, 1 row affected (0.32 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from test_datatype;
+-------+
| value |
+-------+
| 123   | 
+-------+
1 row in set (0.01 sec)

mysql> drop table test_datatype;
Query OK, 0 rows affected (0.08 sec)

mysql> \q
Bye

With new version:

$mysql50 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7710
Server version: 5.0.46-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `test`.`test_datatype` (   `value` VARCHAR(32) NOT NULL,   PRIMARY KEY (`value`) ) engine=innodb;
Query OK, 0 rows affected (0.25 sec)

mysql> INSERT INTO test_datatype (value) VALUES('123');
Query OK, 1 row affected (0.10 sec)

mysql> ALTER TABLE `test`.`test_datatype` MODIFY COLUMN `value` VARCHAR(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
Query OK, 1 row affected, 1 warning (0.11 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'value' at row 1 | 
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test_datatype;
+-------+
| value |
+-------+
| 12    | 
+-------+
1 row in set (0.00 sec)

mysql> drop table test_datatype;
Query OK, 0 rows affected (0.01 sec)

mysql> \q
Bye