Error of the day:
I had a script that used LOAD DATA INFILE to load data from a CSV file into a MySQL database table. It worked fine before, but after six months, I needed to use it again—and suddenly, this error kept appearing:
checked the script, verified that the file existed, and everything seemed fine… so what had changed?
It turns out that a few months ago, we had separated our servers, meaning MySQL now runs on a separate machine. Because of this, LOAD DATA INFILE could no longer find the file as it wasn’t stored locally on the MySQL server.
Solution:
To resolve this, I had to use:
This command allows the client (in this case, a PHP-based server) to access the file and then pipe it to the MySQL server.
The frustrating part of troubleshooting this was that most solutions online pointed to file permissions issues, when in reality, the problem was that the SQL server was remote and couldn’t access the file directly.
Important Note:
Due to security settings, you cannot usually use LOAD DATA LOCAL INFILE if the file is stored on the same server as MySQL. In such cases, you still need to use:
Hope this helps someone else in the future who runs into the same issue!