Data truncated for column?

Question

After changing the data type of a MySql column in order to store Twilio call ids (34 char strings), I try to manually change the data in that column with:

update calls 
   set incoming_Cid='CA9321a83241035b4c3d3e7a4f7aa6970d' 
 where id='1';

However I get an error which doesn't make sense seeing as the column's data type was properly modified?

| Level ||| Code | Message | Warning | 1265 | Data truncated for column 'incoming_Cid' at row 1

Answer 1

By issuing this statement:

ALTER TABLES call MODIFY incoming_Cid CHAR;

... you omitted the length parameter. Your query was therefore equivalent to:

ALTER TABLE calls MODIFY incoming_Cid CHAR(1);

You must specify the field size for sizes larger than 1:

ALTER TABLE calls MODIFY incoming_Cid CHAR(34);

Answer 2

I had the same problem because of an table column which was defined as ENUM('x','y','z') and later on I was trying to save the value 'a' into this column, thus I got the mentioned error.

Solved by altering the table column definition and added value 'a' into the enum set.

Answer 3

However I get an error which doesn't make sense seeing as the column's data type was properly modified?

| Level | Code | Msg | Warn | 12 | Data truncated for column 'incoming_Cid' at row 1

You can often get this message when you are doing something like the following:

REPLACE INTO table2 (SELECT * FROM table1);

Resulted in our case in the following error:

SQL Exception: Data truncated for column 'level' at row 1

The problem turned out to be column misalignment that resulted in a tinyint trying to be stored in a datetime field or vice versa.

Answer 4

Your problem is that at the moment your incoming_Cid column defined as CHAR(1) when it should be CHAR(34).

To fix this just issue this command to change your columns' length from 1 to 34

ALTER TABLE calls CHANGE incoming_Cid incoming_Cid CHAR(34);

Here is SQLFiddle demo