Ошибка data truncation

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

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.

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);

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.

In my case it was a table with an ENUM that accepts the days of the week as integers (0 to 6). When inserting the value 0 as an integer I got the error message «Data truncated for column …» so to fix it I had to cast the integer to a string. So instead of:

$item->day = 0;

I had to do;

$item->day = (string) 0;

It looks silly to cast the zero like that but in my case it was in a Laravel factory, and I had to write it like this:

$factory->define(App\Schedule::class, function (Faker $faker) {
    return [
        'day' => (string) $faker->numberBetween(0, 6),
        //
    ];
});

I had the same problem, with a database field with type «SET» which is an enum type.

I tried to add a value which was not in that list.

The value I tried to add had the decimal value 256, but the enum list only had 8 values.

1: 1   -> A
2: 2   -> B
3: 4   -> C
4: 8   -> D
5: 16  -> E
6: 32  -> F
7: 64  -> G
8: 128 -> H

So I just had to add the additional value to the field.

Reading this documentation entry helped me to understand the problem.

MySQL stores SET values numerically, with the low-order bit of the
stored value corresponding to the first set member. If you retrieve a
SET value in a numeric context, the value retrieved has bits set
corresponding to the set members that make up the column value. For
example, you can retrieve numeric values from a SET column like this:

mysql> SELECT set_col+0 FROM tbl_name; If a number is stored into a

If a number is stored into a SET column, the bits that are set in the
binary representation of the number determine the set members in the
column value. For a column specified as SET(‘a’,’b’,’c’,’d’), the
members have the following decimal and binary values.

SET Member  Decimal Value   Binary Value
    'a'                1          0001
    'b'                2          0010
    'c'                4          0100
    'd'                8          1000

If you assign a value of 9 to this column, that is 1001 in binary, so
the first and fourth SET value members ‘a’ and ‘d’ are selected and
the resulting value is ‘a,d’.

when i first tried to import csv into mysql , i got the same error , and then i figured out mysql table i created doesn’t have the character length of the importing csv field ,
so if it’s the first time importing csv

1. its a good idea to give more character length .
2. label all fields as varchar or text , don’t blend int or other values.

then you are good to go.