Error of the day this one – had a script which used Load Data Infile to load the data from a csv file to a MySQL database table – worked fine previously.
After 6 months needed to use it again – everyting seemed fine, except this error kept appearing.
Checked the script – file existed etc… so what had changed?
We had separated out our servers a few months earlier so Mysql now runs on a separate server, this means load data infile can no longer find the file.
To solve this problem use Load Data Local Infile – basically this uses the client (which in this case exists on a server running php) to access the file and pipe it to the mysql server.
The annoying thing about this error is that finding the solution isn’t that easy – as most people say that it’s file permissions etc.. when it could be that the SQL server is actually on a remote location and so you need to use LOAD DATA LOCAL INFILE (especially since in larger companies servers tend to be specialised).
NB Due to security settings you cannot usually use LOAD DATA LOCAL INFILE if the file is on the same server as the mysql – then you need to use LOAD DATA INFILE.
Anyway hope this helps someone else like me someday.