mysql : Data too long for column on alter table modify column

3
votes

I am changing a column from text to varchar column.

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| ID           | bigint(19)   | NO   | PRI | NULL    |       |
| STATUS           | varchar(120) | YES  |     | NULL    |       |
| PRIORITY         | varchar(120) | YES  |     | NULL    |       |
| DESCRIPTION      | text         | YES  |     | NULL    |       |

when i execute the below query,

alter table StatInfo modify column DESCRIPTION varchar(255) NULL;

It says ERROR 1406 (22001): Data too long for column 'DESCRIPTION' at row 7

It doesn't truncates the value in the column and alters the table why?. where as in older version it works.

2
mysqlalter-table

2 Answers

3
votes

May be you need to check the sql mode, if it strict then it will show this error

When you change a data type using CHANGE or MODIFY, MySQL tries to convert existing column values to the new type as well as possible.

Warning This conversion may result in alteration of data. For example, if you shorten a string column, values may be truncated. To prevent the operation from succeeding if conversions to the new data type would result in loss of data, enable strict SQL mode before using ALTER TABLE (see Section 5.1.6, “Server SQL Modes”).

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

0
votes

I suggest you to
1) Copy that table on a temporary table (through an insert-select)
2) Alter the original table
3) Restore the values in the original table using the same procedure described in 1)