Problem Using LOAD DATA INFILE

Databases
1

I’m attempting to upload an error_log file into a MySQL table.

The contents of the file are


2010-03-20 13:00:22|1024|Testing My Error Handler!|C:\\wamp\\www\\Framework\\framework\\index.php|5 
2010-03-20 13:00:25|1024|Testing My Error Handler!|C:\\wamp\\www\\Framework\\framework\\index.php|5 
2010-03-20 13:00:27|1024|Testing My Error Handler!|C:\\wamp\\www\\Framework\\framework\\index.php|5

The MySQL Query I am executing is…


LOAD DATE INFILE 'c:/wamp/framework_logs/error_log.php'
INTO TABLE Error_Log
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\\r\
'
(date, number, message, file, line);

When I executed this query everything is loaded properly into the table, except for the first item in the first row of the log file.

MySQL gives me the following warning: “Out of range value for column ‘date’ at row 1”

When I run “SELECT date from Error_Log” I can see that for the first row in the table, it just inserted 0000-00-00 00:00:00 instead of 2010-03-20 13:00:22. However, it got the second and third dates correct.

I thought it might be a file encoding issue, but the file is saved in ‘UTF8’ which is what I also using in MySQL.

Anyone have any ideas of what might be causing the first date not to be inserted properly?

2

The answer to your question will come from inspecting the file contents. If I were to guess, you probably have saved the file with a byte order mark. Your text editor probably has the option to save as utf8 without BOM.

3

Thanks for the tip. Looks like you were right and it was something my text editor did.

The file was actually originally created by the php error_log function.
However, i opened it in notepad and switched the encoding from ANSI to UTF8 and that seemed to have caused the problem.

I deleted the file and started over, letting the error_log create a new file, this time leaving it in ANSI encoding and it works properly now. Kind of weird that UTF8 would cause that problem though.