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 aIf 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’.
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 aIf 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’.
Доброго времени суток! Проект на Yii2. При попытке обновить запись мне выдает:
Database Exception – yiidbException
SQLSTATE[01000]: Warning: 1265 Data truncated for column 'sale' at row 1
The SQL being executed was: UPDATE `house` SET `area`=100, `kitchen`=NULL, `live_area`=97, `room`=3, `floor`=NULL, `floors_qty`=NULL, `sale`='' WHERE `id`=1
Error Info: Array
(
[0] => 01000
[1] => 1265
[2] => Data truncated for column 'sale' at row 1
)
↵
Caused by: PDOException
SQLSTATE[01000]: Warning: 1265 Data truncated for column 'sale' at row 1
in /home/segnava/sites/goldhouse-new/vendor/yiisoft/yii2/db/Command.php at line 844Когда разрабатывал на OpenServer(Windows) и после загрузки на сервер всё работало. После того как перекинул все на Linux (LAMP), выдает вот такую ошибку. В чем может быть проблема. Читал что то на счет Strict SQL Mode, но не могу понять как это исправить. Подскажите пожалуйста в чем проблема? Заранее благодарен!
When you load data from file to a MySQL table, you might run into this error:
Data truncated for column 'column_name' at row #
That error means the data is too large for the data type of the MySQL table column.
Here are some common causes and how to fix:
1. Datatype mismatch.
First, check if the data type of the column is right for the input data. Maybe its defined length is smaller than it should be, or maybe there’s a misalignment that resulted in a value trying to be stored in a field with different datatype.
2. Wrong terminating character
If you manually insert each line into the table and it works just fine, the error occurs only when you load multiple lines, then it’s likely the command didn’t receive proper terminating character.
So check your file’s terminating character and specify it in the LOAD command
- If it’s terminated by a tab :
FIELDS TERMINATED BY 't'
- If it’s terminated by a comma
Then you’re good to go.
Need a good MySQL GUI? TablePlus provides a native client that allows you to access and manage MySQL and many other databases simultaneously using an intuitive and powerful graphical interface.
Download TablePlus for Mac.
Not on Mac? Download TablePlus for Windows.
Need a quick edit on the go? Download for iOS
This article demonstrates the possible reasons and solutions for an error, saying that Data is truncated for a column in MySQL.
Fix Data is Truncated for a Column Error in MySQL
Here, we will explore the possible reasons and solutions to get rid of an error saying MySQL data is truncated for a column.
Data Is Too Large
There are some scenarios when we have to load the data from a file instead of inserting manually into the MySQL table. While loading, the Data truncated for a column 'columnName' at row # error may occur. Why?
This is because the given data seems too big for a data type of the column in the MySQL table. For instance, you have a table where a value attribute is of type text which can only accommodate 65K characters of the provided data.
You will get this error when you insert data for the value column that exceeds 65K.
To solve this problem, you need to change the type that can accept more data, such as MEDIUMTEXT or LONGTEXT, which can accommodate 16MB and 4GB. You can also find some optimization tips here.
We can also truncate the data using SET ANSI_WARNINGS OFF and insert the records again, considering the maximum length of the column’s data type.
Invalid Data Is Passed
The Error Code: 1265. Data truncated for column 'a' at row 1 also occurs in MySQL if we try to insert invalid data.
For example, we have a table where the price field is type float and accepts the NULL values. See the following:
#create a table named `prices`
CREATE TABLE prices (ID INT NOT NULL, price FLOAT NULL);
#insert the first record
INSERT prices VALUES (1, '');
Here, we insert an empty string in the price column, a float type that takes NULL values. Remember, NULL and '' are not the same thing.
Still, if we try to insert an empty string in a column that takes NULL values, it would generate the Error Code: 1265. Data truncated for column 'a' at row 1. The simplest solution to this problem is passing the correct value of the right data type as required by the respective column of the price table.
We can solve it by specifying the NULL values explicitly instead of an empty string ('') or do not even write the values for that particular column in the INSERT statement. See the following snippet considering our example.
INSERT prices VALUES (1, NULL); #insert null explicitly
INSERT prices (ID) VALUES (2); #don't specify values for `price` column
Incorrect Terminating Character
If we insert every line manually in the MySQL table, everything goes well. The error occurs when we try to load more than one line at once and don’t get the terminating character properly.
To solve this, we need to check the terminating character of the file and specify in the LOAD command as follows.
#if the terminating character is `tab`
FIELDS TERMINATED BY 't'
#if the terminating character is a `comma`
FIELDS TERMINATED BY ','