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

Are you sure that column has specified enough room for text of that length? — John Conde, Aug 06 '13 at 19:56
`ALTER TABLES calls MODIFY incoming_Cid STRING;` is what I did. — Zagstrug, Aug 06 '13 at 20:18
Deleted previous comment due to EDIT: I haven't specified that the string should be char(34) instead of char(1), but don't know how to so — Zagstrug, Aug 06 '13 at 20:53
What `DESC calls;` shows? There is something you're not telling us. If you would've changed the data type as you said it would work. Here is [sqlfiddle](http://sqlfiddle.com/#!2/887ef/1) that shows that your update statement works just fine. — peterm, Aug 06 '13 at 21:10
Im using MySQL 5.5.31-0ubuntu0.12.04.2 (Ubuntu), and I though i could use char as a datatype as written [here](http://dev.mysql.com/doc/refman/5.0/en/char.html) — Zagstrug, Aug 06 '13 at 21:11
@peterm Desc calls gives `incoming_Cid | char(1) | YES | UNI | NULL`. — Zagstrug, Aug 06 '13 at 21:12
Possible duplicate of [error 1265. Data truncated for column when trying to load data from txt file](http://stackoverflow.com/questions/14764080/error-1265-data-truncated-for-column-when-trying-to-load-data-from-txt-file) — Celik, Apr 05 '17 at 13:09

peterm · Accepted Answer · 2013-08-06T21:21:09.450

121

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

edited Aug 06 '13 at 21:21

answered Aug 06 '13 at 21:13

peterm

91,357
15
148
157

2

I was sending String for `ENUM` column, instead of integers. – M at Feb 17 '21 at 18:21
I got the error when calling this: `ALTER TABLE properties CHANGE verpackt verpackt FLOAT( 10, 3 ) NOT NULL DEFAULT '0.000' ` - before it was `FLOAT( 8, 3 ) NULL` - The problem is, that there are some values in "verpackt" already, so removing the NULL is not possible – rubo77 Apr 27 '21 at 13:20
I got this error trying to set an integer field to `"11 kg"`. I believe an earlier version of MySQL silently converted that to `11`. – Bob Stein Nov 28 '22 at 22:19

score 29 · Answer 2 · answered Apr 01 '17 at 08:37

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.

score 10 · Answer 3 · answered Aug 06 '13 at 21:18

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

score 6 · Answer 4 · edited Jun 20 '20 at 09:12

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.

iSWORD · Answer 5 · 2019-01-30T10:51:19.607

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),
        //
    ];
});

score 1 · Answer 6 · answered Nov 14 '19 at 14:36

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'.

score 1 · Answer 7 · edited Feb 25 '20 at 19:32

1

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

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

then you are good to go.

edited Feb 25 '20 at 19:32

Hari Krishnan

2,049
2
18
29

answered Feb 25 '20 at 17:19

Saidjon

31
3

score 1 · Answer 8 · answered Apr 19 '22 at 06:22

Check whether you are using the 'enum' datatype.If you are using 'enum' datatype then the items inside the enum datatype should be exactly match with the data which you are entering.Ex. You are taking the enum datatype like this: enum('book',stationery','others') then when you are inserting the data into the database you have to do like this: INSERT INTO database_name.table_name (column1,...columnn) VALUES(). THE value should include same items which you are mentioned within the bracket of enum datatype.

score 0 · Answer 9 · answered Mar 14 '22 at 17:10

0

my issue was I used single quote instead of double quotes which add extra words in Boolean column

answered Mar 14 '22 at 17:10

Mahmoud Magdy

662
10
13

score 0 · Answer 10 · answered Apr 03 '22 at 19:46

I Faced the same issue .In my case i was inserting a empty string for a numeric column.But by inserting a numeric value in string form for same column it got resolved e.g '12.56' --> numeric column will work but '' --> numeric column will give the above mentioned error. Note: For numeric columns in MySql we can pass values in double quotes also .

score 0 · Answer 11 · answered Dec 20 '22 at 22:48

0

In some cases this can be result of wrong type of input. For example, you have a column decimal(10,2) and the input isn't sanitized and is 7,7 but should be 7.7.

answered Dec 20 '22 at 22:48

user2812532

73
2
10

Data truncated for column?

11 Answers11

Linked