Ошибка sql 1265

I have table in mysql table
table looks like 

create table Pickup
(
PickupID int not null,
ClientID int not null,
PickupDate date not null,
PickupProxy  varchar (40) ,
PickupHispanic bit default 0,
EthnCode varchar(2),
CategCode varchar (2) not null,
AgencyID int(3) not null,

Primary Key (PickupID),
FOREIGN KEY (CategCode) REFERENCES Category(CategCode),
FOREIGN KEY (AgencyID) REFERENCES Agency(AgencyID),
FOREIGN KEY (ClientID) REFERENCES Clients (ClientID),
FOREIGN KEY (EthnCode) REFERENCES Ethnicity (EthnCode)
);

sample data from my txt file 
1065535,7709,1/1/2006,,0,,SR,6
1065536,7198,1/1/2006,,0,,SR,7
1065537,11641,1/1/2006,,0,W,SR,24
1065538,9805,1/1/2006,,0,N,SR,17
1065539,7709,2/1/2006,,0,,SR,6
1065540,7198,2/1/2006,,0,,SR,7
1065541,11641,2/1/2006,,0,W,SR,24

when I am trying to submit it by using 

LOAD DATA INFILE 'Pickup_withoutproxy2.txt' INTO TABLE pickup;

it throws error

Error Code: 1265. Data truncated for column ‘PickupID’ at row 1

I am using MySQL 5.2

Источник

Доброго времени суток! Проект на 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

TablePlus in Dark mode

Источник

Recently, while working on a project, I ran into a warning telling me that my “data was truncated for” one of my columns when I was importing a CSV file into one of my SQL tables.

Data truncated warning in MySQL

Pictured: The error in question.

Concerned that I had done something wrong, I Googled for a solution. Unfortunately, I didn’t find any answers there, so I ended up having to find the source of this warning myself.

What does “data truncated” mean?

Truncated means “cut short”, and “data truncated” warnings or errors refer to a value’s data being cut off at the end during the importing process (e.g. a value of “2.9823” being imported as “2.98”). These errors are important warnings, because it notifies us that our data has not been imported accurately.

Data truncated warnings usually happen because the imported value:

  1. Does not match the data type of the column they are being imported into, e.g. inserting “120e” into an INT (i.e. integer) column will cause the data to be inserted as 120, truncating the e at the back.
  2. Exceeds the maximum length of the column, e.g. a string “abcdef” being imported into a VARCHAR(2) will be truncated into “ab”.

Which was why my problem was so perplexing.

The problem

I wrote an SQL query to load the CSV file on the left into my table on the right (pictured below):

The CSV and the imported table

The CSV and the resulting table.

This was the SQL query I used:

LOAD DATA INFILE '/path/to/test.csv'
INTO TABLE tmp
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
IGNORE 1 ROWS
(id,test);

All my values have been imported correctly to the table, so what exactly is being truncated?

After some research and asking around, I found the answer: the r character at the end of “11” was being truncated.

Article continues after the advertisement:

The r character

The r character is part of the rn series of characters, used in Windows to denote a newline character. In all other operating systems, newlines are denoted using n, but because the CSV file was generated in Windows, it uses rn for newlines instead.

showing newline character

You can reveal these hidden characters in Notepad++ by going to View > Show Symbol > Show End of Line. CR (carriage return) represents r, while LF (line feed) represents n.

Why r was being read into the database

The r character was being read into the database because of this line in my query:

LOAD DATA INFILE '/path/to/test.csv'
INTO TABLE tmp
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
IGNORE 1 ROWS
(id,test);

The first row in my data has the following characters:

1;11rn

Which was split into 1 and 11r. As r is not considered an integer, it cannot be entered into the test column, which is defined as an INTEGER. Hence, it got truncated.

To fix this warning, all I had to do was to change the problematic line in my SQL query:

LINES TERMINATED BY 'rn'

And I would be fine even if I didn’t, because this is one of those rare cases where the data truncated warning is harmless!

Conclusion

In conclusion, this is an exploration of an interesting warning I had found while trying to import CSV files into a MySQL table. Remember to always be careful and check your queries before you start importing!

Leave a comment below if the article helped you!

Article continues after the advertisement:

Источник

Fixing a data truncated warning in MySQL

Recently, while working on a project, I ran into a warning telling me that my “data was truncated for” one of my columns when I was importing a CSV file into one of my SQL tables.

Data truncated warning in MySQL
Pictured: The error in question.

Concerned that I had done something wrong, I Googled for a solution. Unfortunately, I didn’t find any answers there, so I ended up having to find the source of this warning myself.

What does “data truncated” mean?

Truncated means “cut short”, and “data truncated” warnings or errors refer to a value’s data being cut off at the end during the importing process (e.g. a value of “2.9823” being imported as “2.98”). These errors are important warnings, because it notifies us that our data has not been imported accurately.

Data truncated warnings usually happen because the imported value:

  1. Does not match the data type of the column they are being imported into, e.g. inserting “120e” into an INT (i.e. integer) column will cause the data to be inserted as 120, truncating the e at the back.
  2. Exceeds the maximum length of the column, e.g. a string “abcdef” being imported into a VARCHAR(2) will be truncated into “ab”.

Which was why my problem was so perplexing.

The problem

I wrote an SQL query to load the CSV file on the left into my table on the right (pictured below):

The CSV and the imported table
The CSV and the resulting table.

This was the SQL query I used:

LOAD DATA INFILE '/path/to/test.csv'
INTO TABLE tmp
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
IGNORE 1 ROWS
(id,test);

All my values have been imported correctly to the table, so what exactly is being truncated?

After some research and asking around, I found the answer: the r character at the end of “11” was being truncated.

Article continues after the advertisement:

The r character

The r character is part of the rn series of characters, used in Windows to denote a newline character. In all other operating systems, newlines are denoted using n, but because the CSV file was generated in Windows, it uses rn for newlines instead.

showing newline character
You can reveal these hidden characters in Notepad++ by going to View > Show Symbol > Show End of Line. CR (carriage return) represents r, while LF (line feed) represents n.

Why r was being read into the database

The r character was being read into the database because of this line in my query:

LOAD DATA INFILE '/path/to/test.csv'
INTO TABLE tmp
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
IGNORE 1 ROWS
(id,test);

The first row in my data has the following characters:

1;11rn

Which was split into 1 and 11r. As r is not considered an integer, it cannot be entered into the test column, which is defined as an INTEGER. Hence, it got truncated.

To fix this warning, all I had to do was to change the problematic line in my SQL query:

LINES TERMINATED BY 'rn'

And I would be fine even if I didn’t, because this is one of those rare cases where the data truncated warning is harmless!

Conclusion

In conclusion, this is an exploration of an interesting warning I had found while trying to import CSV files into a MySQL table. Remember to always be careful and check your queries before you start importing!

Leave a comment below if the article helped you!

Article continues after the advertisement:

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка sql 1264
  • Ошибка sql 1172
  • Ошибка sql 1114
  • Ошибка sql 1101
  • Ошибка sql 1093