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.

Carman

Answer 1

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++).

Lascivious

I just need to figure out what's causing powershell to throw an exception, and that's a weeks worth of effort on my part pretty much done. Would you suggest me raising that as a separate question? – Carman

I've got a query and non query function, I'll paste in the non query function when I get back in the office, about 30 minutes. It's probably something obvious or a syntax issue... – Carman

I run the following function: – Carman

function Exec-NonQuery([string]$query) { $Connection.open() $command = $connection.CreateCommand() $command.CommandText = $query $RowsInserted = $command.ExecuteNonQuery() $command.Dispose() $Connection.Close() if ($RowsInserted) { return $RowInserted } else { return $query } } – Carman

then just : $SqlQuery = @" LOAD DATA LOCAL INFILE $path INTO TABLE sandr.table_file columns terminated by ',' LINES terminated by '\r\n' "@ } Exec-NonQuery $SqlQuery – Carman

Sorry, I can't seem to resolve the add a line break problem in this window :P I should probably just pack up the pc :P – Carman

Upvote from 2024! Thank you – Unhappy

@YanayLehavi I seem to remember I opened a bug in the MySQL list, believing that the BOM ought to be silently consumed and not passed to the storage engine. I can't find it now; possibly they deemed more appropriate to pass the BOM through in case it was an unquoted CSV text value. I suppose the 1366 error is better than introducing a subtle bug by "correctly" importing what would be incorrectly-deBOMed texts. – Lascivious