Getting error "1366 Incorrect integer value: '1'" when importing file

Question

I'm trying to inline upload data stored in UTF-8 text files, and I have two problems. Firstly, there's currently no primary key set on this table, and it's not set to auto-increment or forced to be null at this point; the first column will be the intended primary key once all the data is loaded, and foreign keys will be added at that point.

I received the following error:

25 row(s) affected, 1 warning(s): 1366 Incorrect integer value: '1' for column 'idtable_file' at row 1 Records: 25 Deleted: 0 Skipped: 0 Warnings: 1

when trying to run this:

LOAD DATA LOCAL INFILE '/path' INTO TABLE sandr.table_file 
columns terminated by ','   
LINES terminated by '\n'
(idtable_file, owner_id, folder_id, @modified_date, @created_date, size, filename)
SET modified_date = STR_TO_DATE(@modified_date,'%d/%m/%Y %T'),
    created_date = STR_TO_DATE(@created_date,'%d/%m/%Y %T')

on this table:

CREATE TABLE `table_file` (
  `idtable_file` int(11) DEFAULT NULL,
  `owner_id` int(11) DEFAULT NULL,
  `folder_id` int(11) DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `size` int(11) DEFAULT NULL,
  `filename` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I'm doing something wrong but I've just started with MySQL so I'm stabbing in the dark a fair bit, any ideas on that? Also, though the above SQL query works fine in PowerShell when it's just this:

LOAD DATA LOCAL INFILE '/path' INTO TABLE sandr.table_file 
columns terminated by ','   
LINES terminated by '\n'

It bombs out with:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Fatal error encountered during command execution."

if I add the adjustment to the date fields.

Solution

25 row(s) affected, 1 warning(s): 1366 Incorrect integer value: '1' for column 'idtable_file' at row 1 Records: 25 Deleted: 0 Skipped: 0 Warnings: 1

I also have encountered this error. The thing to note is that

• the error is apparently absurd (it seems to say that "1", which is an integer, is an incorrect integer value), and
• it happens on the very first column of the very first row and only there.

If these two conditions hold, then in all probability the culprit is a hidden three-byte sequence which is smack at the beginning of the SQL file you're trying to load (it's called an UTF8 Byte-Order Mark).

In some cases the sequence is escaped in the error message and is shown recognizably, for example in this bug report. In other cases it is sent to the user as part of a value:

Incorrect integer value: '###1'  ...

but the terminal "eats" the BOM and what you see is the (now absurd) error

Incorrect integer value: '1' ...

To solve the problem, you need to open the file to be imported in some editor capable of removing the byte order mark (e.g. Notepad++).